Listing Records

[reggie]

I have created a search form that allows members to search for other members with 3 search criteria's. I have created the script that searches the database for all records with these matches and lists them in order of username. I want the script to list say 3,5 or 10 matches per page with a link to the next set of matches etc....Any suggestions. I did use sopmething like thisecho " <a href=\"$PHP_SELF?s=$news&q1=$var1&q2=$var2&q3=$var3\">Next 10 >></a>";but the link repeat the same 5 matches over again.This is the script:<?php// Get the search variable from URL $var1 = @$_GET['q1'] ; $var2 = @$_GET['q2'] ; $var3 = @$_GET['q3'] ; $trimmed1 = trim($var1); //trim whitespace from the stored variable $trimmed2 = trim($var2); //trim whitespace from the stored variable $trimmed3 = trim($var3); //trim whitespace from the stored variable// rows to return$limit=2; // check for an empty string and display a message.if ($trimmed1 == "") { echo "<p>please enter a search</p>"; exit; }if ($trimmed2 == "") { echo "<p>please enter a search</p>"; exit; }if ($trimmed3 == "") { echo "<p>please enter a search</p>"; exit; }// check for a search parameterif (!isset($var1)) { echo "<p>We dont seem to have a search parameter!</p>"; exit; }//connect to your database ** EDIT REQUIRED HERE **mysql_connect("localhost","root","admin"); //(host, username, password)//specify database ** EDIT REQUIRED HERE **mysql_select_db("db_loginarea") or die("Unable to select database"); //select which database we're using// Build SQL Query $query = "select * from users where lookingfor = '$trimmed1' and specify like '$trimmed2%' and buyrentshare = '$trimmed3' order by username"; // EDIT HERE and specify your table and field names for the SQL query $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults);if ($numrows == 0) { echo "<h4>Results</h4>"; echo "<p class='form'>Sorry, your search returned zero results</p>"; }// next determine if s has been passed to script, if not use 0 if (empty($s)) { $s=0; }// get results $query .= " limit $s,$limit"; $result = mysql_query($query) or die("Couldn't execute query");// display what the person searched forecho "<p class='form'>Looking for - ".$var1."<br>Specified - ".$var2."<br>To - ".$var3."</p>";// now you can display the results returnedif ($numrows == 0){}else{// begin to show results set$count = 1 + $s ;echo "<table border='0' align='center' class='form' cellpadding='2' cellspacing='0'>"; echo "<tr>"; echo "<td bgcolor='lightgrey' width='200'><b>Username</b></td>"; echo "<td bgcolor='lightgrey' width='200'><b>Looking For</b></td>"; echo "<td bgcolor='lightgrey' width='200'><b>Specify</b></td>"; echo "<td bgcolor='lightgrey' width='200'><b>Country</b></td>"; echo "<td bgcolor='lightgrey' width='200'><b>Town / City</b></td>"; echo "</tr>"; while ($row= mysql_fetch_assoc($result)) { $user = $row["username"]; $title = $row["lookingfor"]; $specify = $row["specify"]; $budget = $row["budget"]; $to = $row["buyrentshare"]; $with = $row["withwho"]; $location = $row["countrylocated"]; $town = $row["towncity"]; echo "<tr>"; echo "<td>$user</td>"; echo "<td>$title</td>"; echo "<td>$specify</td>"; echo "<td>$location</td>"; echo "<td>$town</td>"; echo "</tr>"; echo "<tr>"; echo "<td colspan='2'><b>view the profile of <a href=\"viewprofile.php?user=" . urlencode($row['username']) . "\">{$row['username']}</a></b></td>"; echo "</tr>"; echo "<tr>"; echo "<td height='10'></td>"; echo "</tr>"; $count++ ;}echo "</table>";$currPage = (($s/$limit) + 1);//break before paging echo "<br />"; // next we need to do the links to other results if ($s>=1) { // bypass PREV link if s is 0 $prevs=($s-$limit); print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< Prev 10</a>  "; }// calculate number of pages needing links $pages=intval($numrows/$limit);// $pages now contains int of pages needed unless there is a remainder from division if ($numrows%$limit) { // has remainder so add one page $pages++; }// check to see if last page if (!((($s+$limit)/$limit)==$pages) && $pages!=1) { // not last page so give NEXT link $news=$s+$limit; echo " <a href=\"$PHP_SELF?s=$news&q1=$var1&q2=$var2&q3=$var3\">Next 10 >></a>"; }$a = $s + ($limit) ; if ($a > $numrows) { $a = $numrows ; } $b = $s + 1 ; echo "<p class='form'>Showing results $b to $a of $numrows</p>";} ?>

[justsomeguy]

I've got several comments on this code. First, about this:

$var1 = @$_GET['q1'];$var2 = @$_GET['q2'];$var3 = @$_GET['q3'];$trimmed1 = trim($var1); //trim whitespace from the stored variable$trimmed2 = trim($var2); //trim whitespace from the stored variable$trimmed3 = trim($var3); //trim whitespace from the stored variable

You don't need the @ operators there, if you are getting a notice about an undefined index the solution would be to check if the variable is set first instead of just suppressing the message. Also, you might as well just condense these into 1 line and eliminate the extra variable. So that becomes this:

$var1 = trim($_GET['q1']);$var2 = trim($_GET['q2']);$var3 = trim($_GET['q3']);

Also, it's not a very good practice to have variables with names like $var1. When you come back and look at this code several months from now you will have no idea what $var1 is. Descriptive variable names are a good thing.When you check for your parameter here:

// check for a search parameterif (!isset($var1))

You are checking if $var1 is set. It will always be set, because you set it yourself on the top. So instead of that, check if the querystring variable is set:

// check for a search parameterif (!isset($_GET['q1']))

You also need to escape your variables in SQL statements if they are coming from user inputs like $_GET or $_POST. So your SQL statement needs to be escaped:

$query = "select * from users where lookingfor = '" . mysql_real_escape_string($var1) . "' and specify like '" . mysql_real_escape_string($var2) . "%' and buyrentshare = '" . mysql_real_escape_string($var1) . "' order by username"; // EDIT HERE and specify your table and field names for the SQL query

Then there's this:

// next determine if s has been passed to script, if not use 0if (empty($s)) {$s=0;}

You haven't set the variable $s anywhere, so it will always be empty and $s will always be set to 0. That's why you keep getting the same results. You probably want to be checking the querystring variable instead, and you want to cast it as an integer to avoid attacks.

if (empty($_GET['s']))  $s = 0;else  $s = intval($_GET['s']);

So now you have this block checking the database:

query = "select * from users where lookingfor = '" . mysql_real_escape_string($var1) . "' and specify like '" . mysql_real_escape_string($var2) . "%' and buyrentshare = '" . mysql_real_escape_string($var1) . "' order by username"; // EDIT HERE and specify your table and field names for the SQL query$numresults=mysql_query($query);$numrows=mysql_num_rows($numresults);if ($numrows == 0){echo "<h4>Results</h4>";echo "<p class='form'>Sorry, your search returned zero results</p>";}// next determine if s has been passed to script, if not use 0if (empty($_GET['s']))  $s = 0;else  $s = intval($_GET['s']);// get results$query .= " limit $s,$limit";$result = mysql_query($query) or die("Couldn't execute query");

You're executing essentially the same query twice, you might as well reorder the code and just send 1 query to the database.

// Build SQL Query$query = "select * from users where lookingfor = '" . mysql_real_escape_string($var1) . "' and specify like '" . mysql_real_escape_string($var2) . "%' and buyrentshare = '" . mysql_real_escape_string($var1) . "' order by username"; // EDIT HERE and specify your table and field names for the SQL query// next determine if s has been passed to script, if not use 0if (empty($_GET['s']))  $s = 0;else  $s = intval($_GET['s']);// get results$query .= " limit $s,$limit";$result = mysql_query($query) or die("Couldn't execute query");$numrows=mysql_num_rows($result);if ($numrows == 0){  echo "<h4>Results</h4>";  echo "<p class='form'>Sorry, your search returned zero results</p>";}

This is also useless:

// now you can display the results returnedif ($numrows == 0){}else{// begin to show results set

That's what the negation operator is for:

// now you can display the results returnedif ($numrows != 0){// begin to show results set

You also have this:

// next we need to do the links to other results  if ($s>=1) { // bypass PREV link if s is 0	$prevs=($s-$limit);	print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<Prev 10</a>&nbsp ";  }

$var has not been defined, so the q variable in the querystring won't contain anything. I'm not sure what you want there.So this is the total code. Other then the things I mentioned, you'll probably also want to stay away from single-letter variable names like $s, $a, and $b for the same reason that you won't know what they do when you look at the code later.

<?php// Get the search variable from URL$var1 = trim($_GET['q1']);$var2 = trim($_GET['q2']);$var3 = trim($_GET['q3']);// rows to return$limit=2; // check for an empty string and display a message.if ($var1 == ""){  echo "<p>please enter a search</p>";  exit;}if ($var2 == ""){  echo "<p>please enter a search</p>";  exit;}if ($var3 == ""){  echo "<p>please enter a search</p>";  exit;}// check for a search parameterif (!isset($_GET['q1'])){  echo "<p>We dont seem to have a search parameter!</p>";  exit;}//connect to your database ** EDIT REQUIRED HERE **mysql_connect("localhost","root","admin"); //(host, username, password)//specify database ** EDIT REQUIRED HERE **mysql_select_db("db_loginarea") or die("Unable to select database"); //select which database we're using// Build SQL Query$query = "select * from users where lookingfor = '" . mysql_real_escape_string($var1) . "' and specify like '" . mysql_real_escape_string($var2) . "%' and buyrentshare = '" . mysql_real_escape_string($var1) . "' order by username"; // EDIT HERE and specify your table and field names for the SQL query// next determine if s has been passed to script, if not use 0if (empty($_GET['s']))  $s = 0;else  $s = intval($_GET['s']);// get results$query .= " limit $s,$limit";$result = mysql_query($query) or die("Couldn't execute query");$numrows=mysql_num_rows($result);if ($numrows == 0){  echo "<h4>Results</h4>";  echo "<p class='form'>Sorry, your search returned zero results</p>";}// display what the person searched forecho "<p class='form'>Looking for - ".$var1."<br>Specified - ".$var2."<br>To - ".$var3."</p>";// now you can display the results returnedif ($numrows != 0){  // begin to show results set  $count = 1 + $s;  echo "<table border='0' align='center' class='form' cellpadding='2' cellspacing='0'>";  echo "<tr>";  echo "<td bgcolor='lightgrey' width='200'><b>Username</b></td>";  echo "<td bgcolor='lightgrey' width='200'><b>Looking For</b></td>";  echo "<td bgcolor='lightgrey' width='200'><b>Specify</b></td>";  echo "<td bgcolor='lightgrey' width='200'><b>Country</b></td>";  echo "<td bgcolor='lightgrey' width='200'><b>Town / City</b></td>";  echo "</tr>";    while ($row = mysql_fetch_assoc($result))  {	$user = $row["username"];	$title = $row["lookingfor"];	$specify = $row["specify"];	$budget = $row["budget"];	$to = $row["buyrentshare"];	$with = $row["withwho"];	$location = $row["countrylocated"];	$town = $row["towncity"];	echo "<tr>";	echo "<td>$user</td>";	echo "<td>$title</td>";	echo "<td>$specify</td>";	echo "<td>$location</td>";	echo "<td>$town</td>";	echo "</tr>";	echo "<tr>";	echo "<td colspan='2'><b>view the profile of <a href=\"viewprofile.php?user=" . urlencode($row['username']) . "\">{$row['username']}</a></b></td>";	echo "</tr>";	echo "<tr>";	echo "<td height='10'></td>";	echo "</tr>";	$count++;  }  echo "</table>";  $currPage = (($s/$limit) + 1);  //break before paging  echo "<br />";  // next we need to do the links to other results  if ($s>=1) { // bypass PREV link if s is 0	$prevs=($s-$limit);	print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<Prev 10</a>&nbsp ";  }    // calculate number of pages needing links  $pages=intval($numrows/$limit);    // $pages now contains int of pages needed unless there is a remainder from division    if ($numrows%$limit) {	// has remainder so add one page	$pages++;  }    // check to see if last page  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {	// not last page so give NEXT link	$news=$s+$limit;		echo " <a href=\"$PHP_SELF?s=$news&q1=$var1&q2=$var2&q3=$var3\">Next 10 >></a>";  }    $a = $s + ($limit);  if ($a > $numrows) { $a = $numrows; }  $b = $s + 1;  echo "<p class='form'>Showing results $b to $a of $numrows</p>";}?>

[reggie]

For some reason the new code only lists the limit. if the limit is 2 it only lists 2. even if it found 10. the next link doesnt appear...

[justsomeguy]

Then the condition to check if the next link should be displayed is coming back false:if (!((($s+$limit) / $limit) == $pages) && $pages!=1) {So maybe right before that, print the values of everything you are comparing to find out what is going on:

echo "<br>s: {$s}<br>limit: {$limit}<br>pages: {$pages}<br>";

[reggie]

I still cant work it out. I'll show you the whole code:This is the code for search.php

<h3>Search Information:</h3><p><b>All Fields are Required to find Search Results. Try using the Advanced search to narrow your search results.</font></b><br><table><tr><td width="120">Looking For:</td><td><form name="form" action="searchresults.php" method="get"><select size="1" name="q1" tabindex="" class="form"><option value=""> </option><option value="agricultural">Agricultural</option><option value="business">Buisness</option><option value="building">Building Equipment / Material</option><option value="cosmetics">Cosmetics</option><option value="electrics">Electrics</option><option value="electronics">Electronics</option><option value="flying">Flying</option><option value="furniture">Furniture</option><option value="health">Health</option><option value="industrial">Industrial</option><option value="leisure">Leisure</option><option value="marine">Marine</option><option value="pets">Pets / Animals</option><option value="professional-tools">Professional Tools / Equipment</option><option value="projects">Projects</option><option value="property">Property</option><option value="services">Services</option><option value="sports">Sport</option><option value="travel">Travel</option><option value="vehicles">Vehicles</option><option value="misc">Misc</option></select></td></tr><tr><td width="120">Specify:</td><td><input size="20" name="q2" type="text" id="specify" tabindex="" class="form"></td></tr><tr><td width="120">To:</td><td><select size="1" name="q3" tabindex="" class="form"><option value=""> </option><option value="buy">Buy</option><option value="rent/hire">Rent / Hire</option><option value="share">Share Expenses</option></select></td></tr><tr><td><input type="submit" name="Submit" value="Search" /></td></form></tr></table>

Then searchresults.php is what you gave me. Basically i want to list all records that are found with the criteria used from the search form q1,q2,q3 which are then passed to searchresults to $var1,$var2 and $var3. All i want is for the script to list the first 5 (for example) on one page and if any other records found to list them on the next page, and so on. I dont reallt know where 's' comes into it. i used this scripts from a free php site, and edited it. Im guessing s is the amount of records / results found...Hope this helps.....

[justsomeguy]

I believe that $s is the first record to show. So if you are showing 10 per page and you are on page 3, $s is 30. But that's why I was saying descriptive variable names are a good thing, it eliminates this type of confusion. But I need to know what the output is from the line of code I pasted above, that will tell us why the next link is not being shown. Paste that line of code above right before this part:

// check to see if last page  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {	// not last page so give NEXT link	$news=$s+$limit;		echo " <a href=\"$PHP_SELF?s=$news&q1=$var1&q2=$var2&q3=$var3\">Next 10 >></a>";  }

Then run a search and see what it prints out. It should show what the values of $s, $limit, and $pages are at that point. Those three variables are used in the IF statement, and if the statement is failing we need to know why.

[reggie]

OK. I have completed a search that should have found 4 matching records. I wanted to show 2 recrods per page so i set the limit to 2. It listed 2 records that were found and this was the outcome:s: 0limit: 2pages: 1Showing results 1 to 2 of 2.But there were 4 records that should have been listed. so it should have shown another page and printed showing results 1 to 2 of 4.Any idea's. Thanks for all the help.

[justsomeguy]

OK, then the value of pages is wrong, it should be two. So pages is calculated here:$pages=intval($numrows/$limit);And if $limit is 2, and $pages is 1, then $numrows must also be 2 instead of 4.I realize why it's doing that, I think I might have screwed that up. There is a limit statement on the SQL query, so of course the SQL statement is always returning 2 rows instead of 4, because the limit is telling it to do that. So, if the goal is still to only have 1 query, then we need to modify things a little. First, we need to remove the limit statement so that $numrows is accurate:

// get results//$query .= " limit $s,$limit"; //remove this$result = mysql_query($query) or die("Couldn't execute query");$numrows=mysql_num_rows($result);

Now we need to seek to the first row that you want to display:

mysql_data_seek($result, $s);

So now we need to determine when to stop displaying rows. So if $limit rows should be displayed on every page, and we are starting on row $s, then the last row should be row $s + $limit - 1. So you already have a variable called $count that starts at $s + 1, so when $count equals $s + $limit then we stop. So change this:

while ($row = mysql_fetch_assoc($result))

to this:

while ($row = mysql_fetch_assoc($result) && $count < $s + $limit)