|
PHP - Php Paging/mysql Query Limit
Hi. I'm trying to make a sort of a forum, and this is the part of the code that needs to be changed, only I don't know how.
Code: [Select] 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 id DESC"; // OREDER BY id DESC is order result by descending $result=mysql_query($sql); ?> <table width='50%' border="0" align="center" cellpadding="3" cellspacing="1"> <?php $limit=10; $page=0; $numrows = mysql_num_rows($result); $pages = intval($numrows/$limit); if ($numrows % $limit) { $pages++;} $current = ($page/$limit) + 1; if (($pages < 1) || ($pages == 0)) { $total = 1;} else { $total = $pages;} $result = mysql_query("SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $page,$limit"); while($rows=mysql_fetch_array($result)){?> <tr><td><font size="+2"><a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo $rows['topic']; ?></a><BR></font></td> <td width='0*'><? echo $rows['datetime']; ?></td></tr> <tr><td><? echo substr($rows['detail'], 0, 250)."..."; ?> <a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo "Citeste mai mult";} ?></a> </td></tr> <tr><tr><td> <? if ($page != 0) { // Don't show back link if current page is first page. $back_page = $page - $limit; echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a> \n");} for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it. { $ppage = $limit*($i - 1); if ($ppage == $page){ echo("<b>$i</b>\n");} // If current page don't give link, just text. else{ echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");} } if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link. $next_page = $page + $limit; echo(" <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>");} ?> </td></tr></tr><?php mysql_close(); ?> </table> I made a database with 4 fields and 11 entries. The script shows the last 10 entries just as it should, but when I click on the 'next' button, nothing happens (it should show the first entry, but it just refreshes the page, and still the last 10 entries/1st page are shown). I think it has something to do with the link, but I can't figure out what exactly. Can someone help, or at least give me a hint? Thanks in advance. Bye. Similar TutorialsI have these two tables...
schedule (gameid, homeid, awayid, weekno, seasonno)
teams (teamid, location, nickname)
This mysql query below gets me schedule info for ALL 32 teams in an array...
$sql = "SELECT
h.nickname AS home,
a.nickname AS away,
h.teamid AS homeid,
a.teamid AS awayid,
s.weekno
FROM schedule s
INNER JOIN teams h ON s.homeid = h.teamid
LEFT JOIN teams a ON s.awayid = a.teamid
WHERE s.seasonno =2014";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else {
// Placeholder for data
$data = array();
while($row = mysqli_fetch_assoc($schedule)) {
if ($row['away'] == "") {$row['away']="BYE";}
$data[$row['homeid']][$row['weekno']] = $row['away'];
$data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
}
}
However, I only want to get info for one specific team, which is stored in the $teamid variable. This should be very easy, right? I have tried multiple things, including this one below (where I added an AND statement of "AND (h.teamid=$teamid OR a.teamid=$teamid)"), but this one still outputs too much...
$sql = "SELECT
h.nickname AS home,
a.nickname AS away,
h.teamid AS homeid,
a.teamid AS awayid,
s.weekno
FROM schedule s
INNER JOIN teams h ON s.homeid = h.teamid
LEFT JOIN teams a ON s.awayid = a.teamid
WHERE s.seasonno =2014
AND (h.teamid=$teamid OR a.teamid=$teamid)";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else {
// Placeholder for data
$data = array();
while($row = mysqli_fetch_assoc($schedule)) {
if ($row['away'] == "") {$row['away']="BYE";}
$data[$row['homeid']][$row['weekno']] = $row['away'];
$data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
}
}
Below is the array that the above outputs. In a nutshell, all I want is that 1st array ([1]) which has, in this example, the Eagles full schedule. It's not giving me too much else and I guess I could live with it and just ignore the other stuff, but I'd rather be as efficient as possible and only get what I need...
Array ( [1] => Array ( [1] => Jaguars [2] => @ Colts [3] => Redskins [4] => @ 49ers [5] => Rams [6] => Giants [7] => BYE [8] => @ Cardinals [9] => @ Texans [10] => Panthers [11] => @ Packers [12] => Titans [13] => @ Cowboys [14] => Seahawks [15] => Cowboys [16] => @ Redskins [17] => @ Giants ) [27] => Array ( [1] => @ Eagles ) [28] => Array ( [2] => Eagles ) [4] => Array ( [3] => @ Eagles [16] => Eagles ) [14] => Array ( [4] => Eagles ) [15] => Array ( [5] => @ Eagles ) [3] => Array ( [6] => @ Eagles [17] => Eagles ) [] => Array ( [7] => @ Eagles ) [16] => Array ( [8] => Eagles ) [25] => Array ( [9] => Eagles ) [11] => Array ( [10] => @ Eagles ) [7] => Array ( [11] => Eagles ) [26] => Array ( [12] => @ Eagles ) [2] => Array ( [13] => Eagles [15] => @ Eagles ) [13] => Array ( [14] => @ Eagles ) ) I'm very new to this and really could use some help. I've got a Web app that has one form that collects data from the user and puts it into a mysql database and has another form that allows the user to select critiera to find records in the database and display them on the page. All this is working just fine, but now that my database is getting more data in it, I want to add functionality to display 10 records on a page with results page navigation links so the user can move forward and backward in the results set. This part is not working and I've put in echo statements to figure out what the code is doing. The problem I'm having is that when the selection critiera pulls more than 10 records from the database, the first page of results is correct per the selection criteria entered by the user on the select form. When the 'next' link is selected to review the second page of results, the query is executed again. But this time the form variables have been reset and the results now contains the entire contents of the db. The start record is set to look at the 11th instance of the results set, so the second page starts with the 11th record in the database instead of the 11th record in the original results set. The original select statement is built by determining which criteria is selected using $_POST against each form variable. How can I retain the form variables or the original select statement so the second execution of the select statement results in the same results set as the first? The other option that may be better is to retain the original results set and avoid re-executing the select statement altogether. But I don't know how to do that either. Any suggestions, code samples or adivice is much appreciated! 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, I need to interrupt the execution of an oracle query if it is taking more than 10 seconds, and give user a message informing him about execution timeout. I googled a lot but i didn't find anything useful. Is there any way to set a time limit to oci_execute. I cannot modify DB settings... Any Idea? Thnks I'm trying to do an sql query from php that includes 15 condition "groups" each consisting of 4 conditions. I have tried separating each condition group with brackets but still can't get it to work. Am I missing something? Thanks as always for everyone's help!! Tom Here is the code shown with line breaks for clarity: $query="SELECT * FROM d4dauctions WHERE (`user gender` LIKE '%$gender%' AND `auction area` LIKE '%$auctionarea%' AND `user age group` LIKE '%$agegroup%' AND `type of bid` LIKE '%$typeofbid%') OR ('$usergender' = 'Either' AND `auction area` LIKE '%$auctionarea%' AND `user age group` LIKE '%$agegroup%' AND `type of bid` LIKE '%$typeofbid%') OR ('$auctionarea' = 'Anywhere' AND `user gender` LIKE '%$gender%' AND `user age group` LIKE '%$agegroup%' AND `type of bid` LIKE '%$typeofbid%') OR ('$agegroup' = 'Any age' AND `user gender` LIKE '%$gender%' AND `auction area` LIKE '%$auctionarea%' AND `type of bid` LIKE '%$typeofbid%') OR ('$typeofbid' = 'Any type of bid' AND `user gender` LIKE '%$gender%' AND `auction area` LIKE '%$auctionarea%' AND `user age group` LIKE '%$agegroup%') OR ('$usergender' = 'Either' AND '$auctionarea' = 'Anywhere' AND `user age group` LIKE '%$agegroup%' AND `type of bid` LIKE '%$typeofbid%') OR ('$usergender' = 'Either' AND `auction area` LIKE '%$auctionarea%' AND '$agegroup' = 'Any age' AND `type of bid` LIKE '%$typeofbid%') OR ('$usergender' = 'Either' AND `auction area` LIKE '%$auctionarea%' AND `user age group` LIKE '%$agegroup%' AND '$typeofbid' = 'Any type of bid') OR (`user gender` LIKE '%$gender%' AND '$auctionarea' = 'Anywhere' AND '$agegroup' = 'Any age' AND `type of bid` LIKE '%$typeofbid%') OR (`user gender` LIKE '%$gender%' AND '$auctionarea' = 'Anywhere' AND `user age group` LIKE '%$agegroup%' AND '$typeofbid' = 'Any type of bid') OR (`user gender` LIKE '%$gender%' AND `auction area` LIKE '%$auctionarea%' AND '$agegroup' = 'Any age' AND '$typeofbid' = 'Any type of bid') OR ('$usergender' = 'Either' AND '$auctionarea' = 'Anywhere' AND '$agegroup' = 'Any age' AND `type of bid` LIKE '%$typeofbid%') OR ('$usergender' = 'Either' AND `auction area` LIKE '%$auctionarea%' AND '$agegroup' = 'Any age' AND '$typeofbid' = 'Any type of bid') OR ('$usergender' = 'Either' AND '$auctionarea' = 'Anywhere' AND `user age group` LIKE '%$agegroup%' AND '$typeofbid' = 'Any type of bid') OR ('$usergender' = 'Either' AND '$auctionarea' = 'Anywhere' AND '$agegroup' = 'Any age' AND '$typeofbid' = 'Any type of bid') ORDER BY RANK DESC "; I have searched for a solution to a code that I wrote, with unique numbers in the SQL statement of LIMIT at the end and using execute(). I have also set PDO::ATTR_EMULATE_PREPARES, false I know that there is ways to use bindValue() or bindParam() instead of execute() for this. However, the way I have it set up works, but is there a security flaw with the way I am using LIMIT and should I be using bindParam() instead of execute()?
<?php
if ($numbered_row == 0) {
$limitation = '';
} else {
$started_page = ($page_number - 1) * $items_on_each_page;
$limitation = ' LIMIT '. $started_page . ',' . $items_on_each_page;
}
$sql_query = "SELECT * FROM `myTable` WHERE `id` = :id AND `group` = :group AND `name` LIKE :name AND `country` = :country ORDER BY `date` DESC" . $limitation;
$query_result = $pdo_connecting->prepare($sql_query);
$query_result->execute(array(':id' => '73', ':group' => 'Furniture', ':name' => '%'.$name.'%', ':country' => $country));
?>
Edited November 21, 2018 by Cobra23 How can I limit the amount of time my while loop to only show the first 4 rows of my SQL table?
I am using this code to loop my whole table:
while($row = $results->fetch(PDO::FETCH_ASSOC))
{
echo '
<li>Mileage: '.number_format($row["Mileage"]).'</li>
<li>Engine size: '.$row["EngineSize"].'cc</li>
';} ?>
I only want to loop through the first 4 rows of my SQL table, I then want to duplicate the same code but start at the 5th row until the 8th row of the table, how can I do this?
Thanks, NickI feel like a tard for having to ask this, but I've spent way too much time trying to figure this out, so i've brought it to you guys. I have run into a an error running a while loop off a query: $rowcount= mysql_query("SELECT COUNT(*) FROM `shoutbox`"); $count = mysql_fetch_array($rowcount); $count = $count['COUNT(*)']; $said = mysql_query("SELECT * FROM shoutbox ORDER BY date ASC LIMIT ('$count' - 5),'$count' "); while($row = mysql_fetch_array( $said )){ //line 14 in the code echo $row['shouter'].': '.$row['contents'].'<br />'; } Code: [Select] mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mahngiel/public_html/shout.php on line 14This script works when I remove the limit counts, but then I have to descend the order and things are not in the position I want them in. I'm sure the problem is the limit vars, but dunno which way to go to fix it. thanks Let's say I have 1 table and 100 rows in this table. The table is simple and only contains an Auto Increment ID field and a load_count field. Hello! I want to make the users in the website able only to upload, for example, 1 GB, so that when a user finishes the 1 GB available for his files, he cannot upload more files. I know how to set up upload limit for a single file in the upload page, or even globally for anyone to upload. But I would like to know how to make each user has specific capacity, and how it is updated so that if he uploads 0.5 GB he has only 0.5 GB left. I thought of creating a column in the `users` or something concerning upload limit that I will set it (one GB for example). If I will do that, how will I be able to determine how much he uploaded? Any help would be appreciated. Thank you! 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. Here is my code: // Start MySQL Query for Records $query = "SELECT codes_update_no_join_1b" . "SET orig_code_1 = new_code_1, orig_code_2 = new_code_2" . "WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)"; $results = mysql_query($query) or die(mysql_error()); // End MySQL Query for Records This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this??? Code: [Select] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1 If you also have any feedback on my code, please do tell me. I wish to improve my coding base. Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference. Code: Code: [Select] mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)"); Full code: Code: [Select] <?php include_once("includes/config.php"); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title><? $title; ?></title> <meta http-equiv="Content-Language" content="English" /> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <link rel="stylesheet" type="text/css" href="style.css" media="screen" /> </head> <body> <div id="wrap"> <div id="header"> <h1><? $title; ?></h1> <h2><? $description; ?></h2> </div> <? include_once("includes/navigation.php"); ?> <div id="content"> <div id="right"> <h2>Create</h2> <div id="artlicles"> <?php if(!$_SESSION['user']) { $username = mysql_real_escape_string($_POST['username']); $password = mysql_real_escape_string($_POST['password']); $name = mysql_real_escape_string($_POST['name']); $server_type = mysql_real_escape_string($_POST['type']); $description = mysql_real_escape_string($_POST['description']); if(!$username || !$password || !$server_type || !$description || !$name) { echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>"; echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'> <option value='Any'>Any</option> <option value='PvP'>PvP</option> <option value='Creative'>Creative</option> <option value='Survival'>Survival</option> <option value='Roleplay'>RolePlay</option> </select></td></tr> <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>"; echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>"; } elseif(strlen($password) < 8) { echo "Password needs to be higher than 8 characters!"; } elseif(strlen($username) > 13) { echo "Username can't be greater than 13 characters!"; } else { $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1"); if(mysql_num_rows($check1) < 0) { echo "Sorry, there is already an account with this username and/or server name!"; } else { $ip = $_SERVER['REMOTE_ADDR']; mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)"); echo "Server has been succesfully created!"; } } } else { echo "You are currently logged in!"; } ?> </div> </div> <div style="clear: both;"> </div> </div> <div id="footer"> <a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop </div> </div> </body> </html> 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? I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate. What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop. It doesn't matter if I get to the next page using a header redirect or a form submit. I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. Any ideas? I see several great PHP/MySQL paging systems on google, but how can I make a paging system when I'm just showing all images in a directory, not showing all images from a database? I have no idea how I could convert one of those mysql systems to work for my needs. Here is my code: <?php $columns = "4"; $count = "0"; echo '<div style="overflow:auto; width:500px; height:266px;"><table cellpadding="0" cellspacing="10">'; if ($handle = opendir('.')) { while (false !== ($image = readdir($handle))) { if ($image != "." && $image != ".." && $image != "index.php") { if (strlen($image) > 11) { $short = substr($image,0,11); echo '<td align="center"><a href="'.$image.'" title="'.$image.'"><img src="'.$image.'" width="100" height="100"><br><div style="overflow:auto; width:100px;">'.$short.'...</div></a></td>'; } else { echo '<td align="center"><a href="'.$image.'" title="'.$image.'"><img src="'.$image.'" width="100" height="100"><br><div style="overflow:auto; width:100px;">'.$image.'</div></a></td>'; } $count++; if ($count == 4) { echo "</tr><tr>"; $count = 0; } } } closedir($handle); } echo "</table></div>"; ?> This code shows all images in a directory, 4 per row. I'd really appreciate some help with adding a paging feature to this. Thank you so much for the help! so my database has a page number next to each field. What I need to know how to do is make it so that the paging number, ie being able to select the page, if greater than one, will display 1-whatever the highest page number is. does this make sense? any help is appreciated. Hi Everyone, I need help in paging, i have a form with a lot of text boxes and drop down boxes, i need to use paging "Next..1..2..3", Please someone help me with it, i tried some codes but didn't work out. Thanks RAM |
|||||||