|
PHP - Limit Rows
Hi guys I have a problem. I limit the displayed rows by 10 (which works) and then a link saying "next" or "previous"
The link itself works, but the site always just displays teh first 10 data entries. What do I do wrong? Thank you! Code: [Select] <?php $host="****"; // Host name $username="****"; // Mysql username $password="****"; // Mysql password $db_name="****"; // Database name $tbl_name="sp_users"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); //$sql="SELECT * FROM $tbl_name ORDER BY user_id"; $sql="SELECT * from sp_users,sp_schools where sp_users.user_id=sp_schools.school_id ORDER BY school_name LIMIT 0, 10"; $result=mysql_query($sql); $num_rows=mysql_num_rows($result); ?> <?PHP //check if the starting row variable was passed in the URL or not if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) { //we give the value of the starting row to 0 because nothing was found in URL $startrow = 0; //otherwise we take the value from the URL } else { $startrow = (int)$_GET['startrow']; } ?> <?PHP //this part goes after the checking of the $_GET var $fetch = mysql_query("SELECT * FROM sp_users,sp_schools where sp_users.user_id=sp_schools.school_id LIMIT $startrow, 10")or die(mysql_error()); ?> <style type="text/css"> <!-- .style2 {font-weight: bold} .style3 { font-family: Arial, Helvetica, sans-serif; color: #000000; } .style10 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #000000; } #Layer1 { position:absolute; left:803px; top:36px; width:65px; height:28px; z-index:1; } #Layer2 { position:absolute; left:707px; top:19px; width:143px; height:39px; z-index:1; } #Layer3 { position:absolute; left:247px; top:463px; width:175px; height:53px; z-index:2; } --> </style> <title>User overview</title> </html> <div id="Layer2"> <form action="<?= $_SERVER['PHP_SELF'] ?>" method="post"> <input type="hidden" name="foo" value="<?= $foo ?>" /> <input type="submit" name="submit" value="Refresh" /> </form></div> <div id="Layer3"><?PHP //now this is the link.. echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+10).'">Next</a>'; ?> <?PHP $prev = $startrow - 10; //only print a "Previous" link if a "Next" was clicked if ($prev >= 0) echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'">Previous</a>'; ?></div> <table width="779" border="0" align="left" cellpadding="0" cellspacing="1" bgcolor="#996600"> <tr> <td width="777"> <div align="left"> <table width="779" border="1" cellspacing="0" cellpadding="3"> <tr> <td colspan="6" align="center"><div align="center" class="style1 style3"><strong>SchoolPorta.com Users / Total: <?php echo $num_rows ?></strong></div></td> </tr> <tr> <td width="342" align="center"><span class="style2">school</span></td> <td width="62" align="center"><span class="style2">Name</span></td> <td width="104" align="center"><span class="style2">Lastname</span></td> <td width="130" align="center"><span class="style2">Email</span></td> <td width="64" align="center"><span class="style2">Update</span></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td><span class="style10"><? echo $rows['school_name']; ?></span></td> <td><span class="style10"><? echo $rows['user_first_name']; ?></span></td> <td><span class="style10"><? echo $rows['user_surname']; ?></span></td> <td><span class="style10"><a href="mailto:<?php echo $rows['user_login']; ?>"><?php echo $rows['user_login']; ?></a></span></td> <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style10">update</a></td> </tr> <?php } ?> </table> </div></td> </tr> </table> <div align="left"> <p> </p> <p> </p> <p> </p> <p> <?php mysql_close(); ?> </p> </div> Similar TutorialsI am trying to include a dropdown menu on a page so that a user can choose how many records will be shown and return that data back to the same page. what I have so far is.... The Form Code: [Select] <form action="PHP_SELF" method="post" name="records_per_page" target="_self"> Number of Records per page <select name="records_per_page" id="records_per_page"> <option value="5">5</option> <option value="10">10</option> <option value="15">15</option> <option value="20">20</option> <option value="25">25</option> <option value="30">30</option> <option value="40">40</option> <option value="50">50</option> </select> </form> The Query // How many rows to show per page $rowsPerPage = 25; // Retrieve all the data from the seasons table $result = mysql_query ("SELECT *, DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate, DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate FROM seasons LIMIT "; if ($_POST['records_per_page'] = '') $result .= "'$rowsPerPage' "); else $result .= "'$records_per_page' "); or die(mysql_error()); The error I get is Code: [Select] Parse error: syntax error, unexpected ';' in /homepages/46/d98455693/htdocs/content/test/seasonslist2.php on line 28which refers to the line LIMIT "; Having posted the code into Dreamweaver it also shows errors for the two options in the if statement $result .= "'$rowsPerPage' "); $result .= "'$records_per_page' "); so it is clearly that I have set the code up incorrectly, probably with misplacement of ;'s. What I am trying to do is when the page loads normally, the records should be limited to $rowsPerPage but if a user has specified a number of rows in the dropdown it should show that number. Once I have that sorted I will also be trying to implement a'Start from Record Number' option. As always, any advice would be immensely appreciated! Thanks in advance Steve Hi all, I need your help as I have got a problem with display the 50 rows in the search results. I want to limit 50 rows in per page and if I have less than 50 rows like 21 rows in the page 2 then display the 21 rows. I have got a problem with the limit, because when I tried to use `LIMIT 0, 50`, it will display total 71 rows which I only want to display no more than 50 in per page. Here is what I have tried:
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT 0, 50");
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT $offset, $limit");
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
Here is the full code:
<?php
// Initialize the session
session_start();
//Connect to the database
require_once('config.php');
$searchemail = '%' . 'gmail.com' . '%';
$searchsubject = '%' . 'gmail.com' . '%';
$searchmessage = '%' . 'gmail.com' . '%';
$mailbox = $link->prepare("SHOW TABLES");
$mailbox->execute();
$folders = array();
$total = 0;
$total_rows = 0;
$i = 0;
while ($folder = $mailbox->fetch(PDO::FETCH_NUM)) {
$folder = $folder[0];
if (strpos($folder, 'users') !== false) {
$folder = str_replace('users', '', $folder);
}
else
{
$folders[$i] = $folder;
}
$i++;
}
foreach($folders as $folder) {
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT 0, 50");
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
if ($search_mailbox->rowCount() > 0) {
$total += $search_mailbox->rowCount();
}
}
$page = 1;
$limit = 50;
//$per_page = 50;
//$pages = ceil($total / $per_page);
$offset = ($page - 1) * $limit;
foreach($folders as $folder) {
$search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT $offset, $limit);
$search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
if ($search_mailbox->rowCount() > 0) {
foreach($search_mailbox->fetchAll() as $k => $row) {
$email_number = $row['id'];
$search_from = $row['from_email'];
$search_subject = $row['subject'];
$total_rows++;
echo $search_subject . '.........................' . $total_rows;
echo "<br>";
}
}
}
?>
I am unable to fetch the first 50 rows in the page 1 and I am also unable to fetch the 21 rows in the page 2. What I am trying to achieve is when I am in page 1, I want to display the first 50 rows, then in the page 2 if I have another 50 rows then display the 50 rows, but if I have less than 50 rows like 21 rows then display the 21 rows. Can you please show me an example how I can start with first 50 rows when I am in the page 1 then in page 2 display 21 rows to make in total 71 rows? Any advice would be much appreicated. Thanks in advance. I am building a commenting system, it's sort of like facebooks comments. I have it right now so it loads the first last 3 comments in the database, and then there's a button to view all comments. I have it working fine, except when you add another comment to it, and then click the view all comments, This is the script that loads onload showing the last 3 comments $limit = 3; $query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$id' ORDER BY id DESC LIMIT $limit"); This is the php script that runs when you click view more //($start_id would be 3 in this case) $query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$comment_id' ORDER BY id DESC LIMIT $start_id, 1000"); so to try to show you what's happening: OnLoad--------------------------------|---When you add more comments, (let's just say u add 1 comment before u click view comments)---- comment 9 --------------------------| comment 10 comment 8 --------------------------| comment 9 comment 7 --------------------------| comment 8 ------------------------------------------| comment 7 ----to be loaded onclick-------------|------What's loaded onclick--- comment 6 --------------------------| comment 7 comment 5 --------------------------| comment 6 comment 4 --------------------------| comment 5 comment 3 --------------------------| comment 4 comment 2 --------------------------| comment 3 comment 1 --------------------------| comment 2 ------------------------------------------| comment 1 (see how "comment 7" is being shown twice in a row) I have a commenting system and i have a limit of a certain number of comments to be shown. What i want to do is have a button on the bottom of the page at the end of the comments that are showing and when you click it ajax loads the next certain number of of rows (but not all of them),and then you click it again and it shows more of them, etc. So for example. comment 1 comment 2 comment 3 comment 4 --click button--(loads 4 more)--- comment 5 comment 6 comment 7 comment 8 --click button--(loads 4 more)-- comment 9 comment 10 comment 11 comment 12 etc. until there are no more rows. what's the best way to do this? (I know how to do the ajax and all, i just need help with the script to select the rows) Thanks. Unless buffer overflows or breaking out of code to perform a new command are problems that have been solved.... I am trying to figure out the proper PHP method for setting a boundary on a variable within a script. I have this variable $name which is fed a value from $_POST['name'] from a form field. Now this form field is limited in the HTML to accept only 20 characters, but someone could easily edit the form or outgoing post data. So I want to know how to limit the variable size in the script. In other languages it could be something like this: var name(20). So how do I do that in PHP? what Im basically trying to do is just like a phpmyadmin function... you select rows you want to update with a checkbox and then it takes you to a page where the rows that are clicked are shown in forms so that you can view and edit info in them... and then have 1 submit button to update them all at once. I have 2 queries that I want to join together to make one row
This code works fine without the LIMIT 5, but it lists all the results. With the LIMIT Code: [Select] $query = 'SELECT * FROM wp_playerRank WHERE year="2011" LIMIT 5 ORDER BY rankClass ASC'; $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) { Here is the error I'm getting: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/jwrbloom/public_html/resources/players/rank2011_top5.php on line 26 Hello friends, if i've database table (mytable) has the following ids 1 2 3 4 5 6 and i want to get it with limit the first (1 to 3 ) only Code: [Select] $sql ="select * from mytable LIMIT 3"; this will show the first 3 (1,2,3) how then i write code that shows which is after 3 so it shows me 4 5 6 and if there any way i can say Code: [Select] $sql ="select * from mytable LIMIT (first half of ids)"; and (shows 1,2,3..ect till half) Code: [Select] $sql ="select * from mytable LIMIT (second half of ids)"; will (shows 4,5,6...ect till end) thank you im using the floowing code to pull all alerts from the database. as you can see i have 3 types of alertsd. Profile alerts, forum alerts and topic alerts. They are sorted and placed on the screen under their respective header. However i want to limit it so it only displays 4 of each type of alert. I cant use LIMIT in the query because that would limit all alerts meaning only 4 alerts in total would show up and i just need to limit each alert type. any ideas? $alert_query = $link->query("SELECT a.a_aid, a.a_alert_type, a.a_time_alerted, a.a_fid, a.a_poster, a_alert_read, a.a_tid, c.f_name as cat_name, f.f_fid, f.f_name, t.t_name, u.u_avatar, u.u_avatar_cropped FROM ".TBL_PREFIX."alerts as a LEFT JOIN ".TBL_PREFIX."forums as f ON (f.f_fid = a.a_fid) LEFT JOIN ".TBL_PREFIX."topics as t ON (t.t_tid = a.a_tid) LEFT JOIN ".TBL_PREFIX."forums as c ON (c.f_fid = f.p_id) LEFT JOIN ".TBL_PREFIX."users as u ON (u.u_username = a.a_poster) WHERE a.a_user_name = '$user_name' ORDER BY a_time_alerted ") or die(print_link_error()); $alert_info = $alert_query->fetchAll(); $pm_alert_list = ''; $num_pm_alerts = 0; $num_forum_alerts = 0; $num_topic_alerts = 0; foreach($alert_info as $key => $val) { $alert_info[$key]['a_alert_read'] == 0 ? $color = '#f5dfaf' : $color = '#f4f4f4'; // if alert is a profile message alert if($alert_info[$key]['a_alert_type'] == 1) { $pm_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">'; $pm_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" />'.profile_link($alert_info[$key]['a_poster']).' posted on your wall</p>'; $pm_alert_list .= '<p class="alert_time"> on '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>'; $pm_alert_list .= '</dd>'; $num_pm_alerts++; } if($alert_info[$key]['a_alert_type'] == 2) { $forum_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">'; $forum_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" /><strong><a href="'.$config['asf_root'].'category/'.create_url($alert_info[$key]['cat_name']).'/forum/'.create_url($alert_info[$key]['f_name']).'">'.$alert_info[$key]['f_name'].'</a></strong> has a new topic</p>'; $forum_alert_list .= '<p class="alert_time"> '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>'; $forum_alert_list .= '</dd>'; $num_forum_alerts++; } if($alert_info[$key]['a_alert_type'] == 3) { $topic_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">'; $topic_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" /><strong><a href="'.$config['asf_root'].'category/'.create_url($alert_info[$key]['cat_name']).'/forum/'.create_url($alert_info[$key]['f_name']).'/topic/'.create_url($alert_info[$key]['t_name']).'">'.$alert_info[$key]['t_name'].'</a></strong> has a new post</p>'; $topic_alert_list .= '<p class="alert_time"> '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>'; $topic_alert_list .= '</dd>'; $num_topic_alerts++; } } Im wondering if someone can shed some light on possibly the best way to do this. I currently have a website which recieves 26000-37000 visits a month. The host got on my case about to many MySql connections and with help from someone here decided a cache system was best. the cache system is now in place and only updates if the cached file is older than 5 days. I am still recieving high connections to my database and wonder if it is spam as i have a search box. the search box once filled in redirects to a page which queries the database for the keyword and displays results and because it was just selecting and not inserting into database i didnt go for the captcha. this is why i suspect spam, I have thought about limiting the amount of search queries per time limit by ip but I am unsure of how to tackle this and if it will help against spam bots? a google search has only found an answer which stores every visitors ip in a database and keeps count there which I cant see the benefit as it is creating more work for the DB. I have thought maybe creating a session variable that increments with every search and when searching checks this value but I am not to sure if this would help as it would be down to cookie settings. can anyone shed some light on a possible solution to this. I am not expecting you to write this for me but for now just to help with the best way to tackle the problem. Thank you for your time. hello, is there a wat to limit the following to only the first 25 characters? $customername=$row1["customer"]; thanks Hi, This line of code works just fine for me: $sql_quest = 'SELECT id, username FROM user ORDER BY id ASC LIMIT 0, 30'; I want to be able to set the integer values dynamically and I thought something like this would work: $sql_quest = 'SELECT id, username FROM user ORDER BY id ASC LIMIT' . $my_int_value . ',' . $my_int_value + 30; But it doesn't... Any ideas? Hi, I have a timestamp mysql field that I'm trying to see if something happened within a time limit. The field is updated with the time when the action is complete so I just need to figure out how to see if it's within a time. I currently have a defined variable set titled "MAX_RESPONSE_TIME" and that's in seconds. By default "MAX_RESPONSE_TIME" is equal to 300 seconds. How can I see if a mysql timestamp field is within 300 seconds? Hi, I'm fairly new to all of this and I'm not quite sure what to do. I'm trying to create a field in my database that's 4 hours and 10 minutes into the future from the current time. What data type do I set my field as in the mysql database? How would I populate that mysql field via php.. would I use something like NOW(), or use the php date() function? Also, how would I be able to tell if my field has "expired" (the 4 hours and 10 minutes has elapsed)? I am in need of a alimit on the explode() fucntion but reading through the php explode manual on php.net there doesn't seem to be one that is right for me. Basically I'm making a command system for a php chat and it has a command called ban. I have code that find out if it is a command or a normal string, I also have code that starts to break up the command into segments that can be places in the database. I want to split up "ban user lengthID "Reason why you were banned"" using explode($string, " ") but I notice that that will also break up the "reason why you are banned". How do I get it to split up the command but NOT thereason why you were banned string? Confusing I know, Please ask any questions that would help me explain it better Thanks in advance! Is there a limit to how many items can be stored in an array? My code looks in a directory of photos and writes the file names into the one-dimensional array. I just switched from my test folder to my actual photo folder - about 600 photos - and when I ran my script with var_dump enabled, my array only goes up to 128 items (0-127). I sure hope arrays aren't that wimpy?! Each photo is about 1-2MB in size, but since I am only storing the file name, I would expect that I could easily store thousands of elements in this array. What is going on?
Alright so I have some code for pagination... the problem is on the middle pages it will display double the number of results... (IE if its supposed to display 5, it displays 10) Code: [Select] // Number of messages per page: $user_message_number = 5; $page = $_GET['page']; if($page <= 1){ $low = 0; $high = $user_message_number; } else { $low = ($page - 1) * $user_message_number; $high = $page * $user_message_number; } $messages = mysql_query("SELECT * FROM messages WHERE recipient='$user_id' ORDER BY id DESC LIMIT $low, $high") or die(mysql_error()); while($pull_messages = mysql_fetch_array($messages)) { // Displays content } That's all of the code that would directly effect the outcome... Any thoughts/comments/suggestions are greatly appreciated. hello, I have a form i am using for a raffle that i have weekly. the problem is i want to limit how many times they can register. I am using a basic form that collects name, email, and IP ( which is hidden ). When it gets submitted there is time stamp as well using this date("m/d/y : H:i:s", time() I would like to restict registering by one hour at a time. can you please give me some help with this. I have thought of maybe using cookies, but some of my smarter members can bypass that. So i think i will either use email or IP . any help would be very appreciated |
|||||||