Page 1 of 1

PHP/MySQL Search: 'Next Page' Not Working

Posted: Wed Aug 13, 2008 11:36 am
by itsinmyhead
So, I've got a classified ad search on our website here: http://www.adkpennysaver.com/search.html

It's the same code (only had to change the database address/username/password info) as this test page: http://www.phenixdesigns.net/psaver/test/search.php

It worked the first day I uploaded it to the site. The next day, it wasn't working properly. There are some weird things happening - made even weirder because nothing was changed in the code.

When you perform a search that has more than one page of results, and you click "Next 10," nothing advances. The page changes and all, but it displays the same results as were there before (the first ten). I'm really puzzled as to what's going on here.

Here's the code...

Code: Select all

    <?php
 
  // Get the search variable from URL
 
  $var = @$_GET['q'];
  $trimmed = trim($var); //trim whitespace from the stored variable
 
// rows to return
$limit=10;
 
// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }
 
// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }
 
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect(); //(host, username, password)
 
//specify database ** EDIT REQUIRED HERE **
mysql_select_db() or die("Unable to select database"); //select which database we're using
 
// Build SQL Query  
$query = "select * from classifieds where Category like \"%$trimmed%\" or Description like \"%$trimmed%\"
  order by Category"; // 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>Sorry, your search: "" . $trimmed . "" 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 for
echo "<p>You searched for: "" . $var . ""</p>";
 
echo '<table border="1">
<TR><TD width="20%" align="center">Category</TD>
<TD width="80%" align="center">Description</TD></TR>';
 
while (list ($ID, $Category, $Description) = mysql_fetch_row($result)) {
echo "<TR><TD>$Category</TD>
<TD>$Description</TD></TR>";
}
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 "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp&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 "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }
 
$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
?>
 

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Wed Aug 13, 2008 1:08 pm
by califdon
There are several things wrong with your script. To begin with, you are not using mysql_real_escape_string() or any other hacker security precautions on the variables passed to the script from the URL--very dangerous!

Then, you are not checking to see if there is a value being passed as $s, so it is always set to zero by your script. Since your script uses $s as the starting row to display, that's what it is doing, always showing you the first 10 rows. If it worked properly before, you must have deleted some lines of code. What you have shown us will ALWAYS display the first 10 rows, because $s will always be zero.

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Wed Aug 13, 2008 1:18 pm
by itsinmyhead
califdon wrote:There are several things wrong with your script. To begin with, you are not using mysql_real_escape_string() or any other hacker security precautions on the variables passed to the script from the URL--very dangerous!

Then, you are not checking to see if there is a value being passed as $s, so it is always set to zero by your script. Since your script uses $s as the starting row to display, that's what it is doing, always showing you the first 10 rows. If it worked properly before, you must have deleted some lines of code. What you have shown us will ALWAYS display the first 10 rows, because $s will always be zero.
So, truth be told, I am a PHP/MySQL noob. I'm just learning this stuff, so if you have any more details on the suggestion you made, that would be great. I'll, of course, do some Googling to see what I can find, but case-specific advice is always a plus.

I'll play around with the stuff you mentioned, but there is nothing different from the code on the adkpennysaver.com website & the phenixdesigns.net website. The code is exactly the same, just changing the database url/username info.

That's what boggles me the most... the exact same code not working on one server but working on another.

Thanks for your help!

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Wed Aug 13, 2008 1:47 pm
by nowaydown1
The reason that it works on your other server is because your other server probably has the register_globals directive enabled and the variable $s is never initialized in your script. Instead what you need to do is get the value of 's' from the request string ($_GET superglobal)

Code: Select all

 
$s = $_GET['s'];
 
Also, be sure to clean up your incoming variables as califdon mentioned. If you're new to SQL injection, there's some good stuff around about what to do about it. Basically, you need to look at mysql_real_escape_string. Filtering on top of that couldn't hurt either.

This should get you started: http://phpsec.org/projects/guide/3.html#3.2

Good luck!

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Wed Aug 13, 2008 2:00 pm
by itsinmyhead
You are kings!

The code nowaydown posted gets everything working aces. Thank you so much!

I'll also take some time into looking at the stuff you both posted about security and all of that.

Thank you so much for the help!

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Wed Aug 13, 2008 3:09 pm
by itsinmyhead
I looked around at some examples of mysql_real_escape_string(), and I think I may have it?

Is it as simple as adding:

Code: Select all

mysql_real_escape_string($query)
to my code above (under the "Build SQL Query" note)?

Apologies in advance for possibly being a dope about this, haha... your help is greatly appreciated.

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Wed Aug 13, 2008 6:18 pm
by califdon
itsinmyhead wrote:I looked around at some examples of mysql_real_escape_string(), and I think I may have it?

Is it as simple as adding:

Code: Select all

mysql_real_escape_string($query)
to my code above (under the "Build SQL Query" note)?

Apologies in advance for possibly being a dope about this, haha... your help is greatly appreciated.
Not quite, but you're close. Apply the mysql_real_escape_string() to each variable that you receive as input from a user, via GET or POST superglobal variables. For example:

Code: Select all

  $var = mysql_real_escape_string($_GET['q']);
And lose that @ -- it suppresses any warnings or error messages.

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Thu Aug 14, 2008 8:44 am
by itsinmyhead
Calfidon - I tried what you posted, but I'm getting two error messages:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in E:\Inetpub\Virtuals\adkpennysaver.com\Web\results.php on line 112

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in E:\Inetpub\Virtuals\adkpennysaver.com\Web\results.php on line 112

I'm not really sure what to do with that one, heh.

Thanks again for all of your help!

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Thu Aug 14, 2008 12:32 pm
by califdon
What version of PHP are you using? The mysql_real_escape_string() function was introduced in PHP 4.3.0, according to the manual: http://us3.php.net/mysql_real_escape_string. It also says that the function calls a MySQL library function, so an outdated version of MySQL or a misplaced library could also cause problems.

But in the error message, I notice that it's talking about being unable to even connect to the server, and it shows a questionable Windows path where it's apparently expecting to find the server: E:\Inetpub\Virtuals\adkpennysaver.com\Web\results.php. That doesn't look right.

Maybe someone else can spot what's wrong here.

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Thu Aug 14, 2008 12:44 pm
by itsinmyhead
According to the phpMyAdmin page, the Client Version of MySQL is 5.0.18 & PHP version 5.1.2.

Thanks again for your help thus far. Weird stuff, man... hopefully someone has some ideas.

In addition to this, I do have other questions about what I'm trying to do. I'd like to have links on the Search page for the different sections of our Classifieds. I'd like to, when each link is clicked, all of the ads from that section are displayed.

I know how to display just those ads on a page, but I'm hoping that there's a better way than to create a different page for each section and just link to those. Is there a way to have one page with these links, and when they're clicked, the info from the database is pulled (instead of just linking to, say, automotive.php)?

Thanks again!

Edit:

I know that I can do this: http://www.adkpennysaver.com/results.php?s=&q=SEARCH QUERY

and return results. However, is there a way to limit that to just displaying results from the "Category" column instead of searching through all of the categories AND descriptions?

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Thu Aug 14, 2008 2:35 pm
by itsinmyhead
I found a post on another forum here that looks to be attempting what I am.

The second reply looks like it makes sense... but when I try to implement it, I get the following error:

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ',' or ';' on line 13

Line 13 is this (I'm going to copy & paste right from that post, so nothing is changed from the original intention):

Code: Select all

echo "<a href='?page="' . $link["name"] . '"'>"' . $link["link_name"] . '"</a><br />";

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Thu Aug 14, 2008 2:55 pm
by nowaydown1
Try:

Code: Select all

 
echo "<a href=\"?page=" . $link["name"] . "\">" . $link["link_name"] . "</a><br />";
 
Also in regard to your mysql_real_escape_string function call giving you a warning, are you connected to the database when that function call occurs? You have to have a valid connection already open to use it.

Re: PHP/MySQL Search: 'Next Page' Not Working

Posted: Thu Aug 14, 2008 3:12 pm
by itsinmyhead
I am connected, yes.

Erased my last post, as I am a dope.

Anyway. It's not giving me any errors now, it's just not printing anything at all. Still tooling around with it, and will update if I figure it out, but feel free to kick away at it, too, if you'd like.

Thanks a million for all of the help. You guys are all fantastic here!

Edit: I got it! Plugged away at it most of the morning and figured out everything that I was trying to do.

Thanks again for the help!