[SOLVED] PHP/MySQL Search Function....

[vapour_]

Hi.

 

I am quite new to PHP and mySQL and have just been working on a database search function for my website, the search function includes a search form on the main page which then goes to a results page to display the results. I want the script to either display "No results found." , "Please enter a search." or results that match the request. The script is ok to display the "no results found." and "please enter a search" but wont display real results when i test it. This is the script i wrote. (Please note that i am very new to this, and hope i havent made a really stupid mistake.  :-\)

 

These are the variables from the top of the page:

 

<?php

include("db_connect.php");

$query = "SELECT username, email FROM users WHERE username LIKE '%".$search."%'";

$result = mysql_query($query);

$num_rows = mysql_num_rows($result);

?>

 

And this is what i'm using to display the results:

 

<?php

if ($num_rows == 0) {

echo 'No results found.';

}

else if (!$_POST['search']) {

echo 'Please enter a search.';

} else {

$search = $_POST['search'];

echo ' <table width="100%">

          <tr>

              <td width="40%"><strong>Username</strong></td>

              <td width="60%"><strong>Email</strong></td>

            </tr>

            <tr><?php while ($row = mysql_fetch_assoc($result)) { ?>

              <td><?php echo $row["username"]; ?></td>

              <td><?php echo $row["email"]; ?></td>

            </tr> <?php } ?>

          </table>';

}

?>

 

Thanks heaps.

[phpzone]

You need to construct your $query after $search = $_POST['search'];  so move the $search up to the top.

 

Also, add a 'die' to your query line at least whilst you first create the script.

 

$search = $_POST['search'];
$query = "SELECT username, email FROM users WHERE username LIKE '%".$search."%'";
$result = mysql_query($query) or die ( mysql_error() );
$num_rows = mysql_num_rows($result);

 

Aside from that, you also have your database loop with an echo, which isn't going to work.

 

the !$_POST['search'] check should come at the top of the script.

 

Umm.. there is quite a lot wrong with this code I'm afraid.

 

[vapour_]

Just rearranged a few things...solved. Its still messy but its working.

 

code is now:

 

 <?php
	  if ($num_rows == 0) {
echo 'No results found.';
} 
else if (!$_POST['search']) {
echo 'Please enter a search.';
} else { ?> <table width="100%">
            <tr>
              <td width="40%"><strong>Username</strong></td>
              <td width="60%"><strong>Email</strong></td>
            </tr>
            <tr><?php while ($row = mysql_fetch_assoc($result)) { ?>
              <td><?php echo $row["username"]; ?></td>
              <td><?php echo $row["email"]; ?></td>
            </tr> <?php } ?>
          </table><?php } ?>

 

Which is with :

 

$search = $_POST['search'];

$query = "SELECT username, email FROM users WHERE username LIKE '%".$search."%'";

$result = mysql_query($query) or die ( mysql_error() );

$num_rows = mysql_num_rows($result);

at the top.

 

Thanks.

[phpzone]

For reference and to help a new coder out, here is roughly how I would have done it, with comments, I hope this helps you along, nice to hear you got it working though!!

 

DISCLAIMER: I've not run this, there could be errors, nice exercise to debug if there is

 

<?php

//
// use require_once, if the file doesn't load it will tell you, no brackets
// needed so - no ()
//

require_once 'db_connect.php';

//
// get the $_POST variable, notice the =@ means if the key 'search' doesn't
// exist, no error will be raised, $search will contain ''
//

$search =@ (string)$_POST['search'];

//
// we check if $search was empty (could use empty() function, I use strlen)
//

if ( strlen( $search ) == 0 )
{

	exit( "You must supply a search string" );

}

//
// we won't get here if strlen == 0 was matched, because the exit() ends the script,
// no elseif required
//

$query = "SELECT username, email FROM users WHERE username LIKE '%". mysql_real_escape_string($search) ."%'";

//
// we contruct the query above (note mysql_real_escape_string, very important for security), 
// then execute below, using die/mysql_error to quit with the database error if one occurs
//

$result = mysql_query($query) or die( mysql_error() );

//
// we check if $result is true, which means the query worked, although because
// of the die() we probably wouldn't get here, if you leave off the die then
// no error would be raised, but checking $result would tell you the query
// failed
//

if ( $result == true ) // could shorten to 'if ( $result )' implying true
{

$num_rows = mysql_num_rows($result);

if ($num_rows == 0) 
{

	echo 'No results found.';

}
else
{
	while ( $row = mysql_fetch_assoc( $result ) )
	{
		//
		// I use HEREdoc syntax in the loop, so we don't have open/close php
		// tags everywhere, looks much neater
		//

		print <<<_HTML_ROW

		  <table width="100%">
		   <tr>
		      <td width="40%"><strong>Username</strong></td>
		      <td width="60%"><strong>Email</strong></td>
		    </tr>
		    <tr>
		      <td>{$row["username"]}</td>
		      <td>{$row["email"]}</td>
		    </tr>
		  </table>

_HTML_ROW;
// it is VERY important the HEREDoc tag on the line above, has NO content after it on the same
// line, and that it is in column 0 against the left in your editor.

	} // end while
} // end else
} // end if
?>

[vapour_]

Thanks so much for the tips. 

 

the way you did it makes way more sense than my n00bish script....

 

thanks again.