PHP Search Script

**CBResources** · Apr 12, 2011 17:59

Hi all,

I've designed a "Name Finder" script which searches a database of names and returns the results based on the search string entered into a form.

I wanted to make a few changes but am a little stuck and hope somebody can help me.

The following is the current PHP Script:

Code PHP:

<?php
 
$dbHost = '*******';
$dbUser = '*******'; 
$dbPass = '*******';
$dbDatabase = '*******';
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());
 
mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
 
// Set up error check and result check array
$error = array();
$results = array();
 
// First check if a form was submitted
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // removes any html/javascript.
 
if (isset($_GET['searchgender'])) {
   $searchgenderTerms = trim($_GET['searchgender']);
   $searchgenderTerms = strip_tags($searchgenderTerms); // removes any html/javascript.
 
 
   if (strlen($searchTerms) < 3) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   }
 
   if (strlen($searchgenderTerms) < 3) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchgenderTermDB = mysql_real_escape_string($searchgenderTerms); // Prevents sql injection.
   }
}
   // If there are no errors, search begins...
   if (count($error) < 1) {
 
//The Query
$searchSQL = "SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE '{$searchTermDB}' AND `gender` LIKE '{$searchgenderTermDB}' ";
 
 
      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
 
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "Sorry, we have no record of the name<br /> {$searchTerms} ({$searchgenderTerms}).";
      }else {
         $results = array(); // And now display results...
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "Name: <span id=\"result\">{$row['name']}</span><br /> Gender: <span id=\"result\">{$row['gender']}</span><br /> Origin: <span id=\"result\">{$row['origin']}</span><br /> Meaning: <span id=\"result\">{$row['meaning']}</span><br /><br />"; $i++;
         }
      }
   }
}
 
function removeEmpty($var) {
   return (!empty($var)); 
}
?>

The changes I want to make:

1) The following part of the above script:

Code PHP:

   if (strlen($searchTerms) < 3) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   }

This prevents SQL injection. But I want to add a set of 26 links (A-Z) to allow people to click the first letter and bring up a list of names beginning with that letter. I can't do this with the above code active because such searches would be filtered out. So how do I remove the above piece of code and still prevent SQL injections.

2. The code which controls the form and how the results are displayed is as follows:

Code PHP:

<html>
   <body>
      <?php echo (count($error) > 0)?"<strong>Error</strong>: <br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?> <form method="GET" action="<?php echo $_SERVER['../../../PHP_SELF'];?>" name="searchForm"> <strong>Enter a name to find it's origin and meaning: </strong> <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /> <br /><br />
      <strong>Select Gender:</strong> <SELECT name="searchgender" value="<?php echo isset($searchgenderTerms)?htmlspecialchars($searchgenderTerms):''; ?>" />
      <option>Male</option>
      <option>Female</option>
      </SELECT>
      <br /><br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>

I want to change this so that the results are displayed as links instead. For example: I will search for Chris and the results will be displayed as a list of links such as "Chris, Christopher, Christian" and the links (when clicked) provide the full details.

The script can currently be seen in action at the following URL:

I've Got Kids!: Baby Name Finder

I hope somebody can help me make these changes

**Cute Tink** · Apr 15, 2011 11:08

So how do I remove the above piece of code and still prevent SQL injections.

Simple, just remove the piece of code that checks for the length:

PHP Code:


//if (strlen($searchTerms) < 3) { // Checks search term is longer than 3 characters.
//      $error[] = "Search terms must be longer than 3 characters.";
//   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
//   }

You can try just commenting out the lines, like I did here, or delete them entirely. However, if you want to keep the general restriction that the search has to have at least three characters, but still allow a-z searches, do this:

PHP Code:


$atoz = range( 'a', 'z' );
if (strlen($searchTerms) < 3 and !in_array($searchTerms, $atoz)) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   }

Then your a-z will still pass the first criteria and be included in the search. Alternately, you could pass it along with something other than the $searchTerms variable.

I want to change this so that the results are displayed as links instead.

This part is easy enough, though you'll have to modify the links themselves to get them to point where you want them to:

PHP Code:


<?php 
if (count($results) > 0)) {
   echo "You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />";
   foreach ( $results as $value ) {
      echo '<a href="viewMoreInfoPage.php?someRef=Ref">' . $value . '</a><br />';
   }
}
?>

**CBResources** · May 3, 2011 13:35

Originally Posted by Cute Tink

if you want to keep the general restriction that the search has to have at least three characters, but still allow a-z searches, do this:

PHP Code:


$atoz = range( 'a', 'z' );
if (strlen($searchTerms) < 3 and !in_array($searchTerms, $atoz)) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   }

Then your a-z will still pass the first criteria and be included in the search.

Hi & Thanks.

Sorry for the delay in responding - I had moved on to other areas of the site whilst I waited for some answers to this and have only just got around to getting back to checking it.

I've tried the above solution and when I enter a search time, like "b" and click Search it doesn't display any results... it redirects the page to the following URL:

Code:

http://xxxxx.com/babynames.php?search=b&searchgender=Male&submit=Search%21

But this page is completely blank.

Sorry but I've had to obscure the domain name because as of now this is a closed site and I have to protect my non disclosure agreement for my client until the site is ready to launch. But the full script is below:

Code PHP:

<phpcode><?php
 
$dbHost = 'localhost';
$dbUser = 'xxxxxxxxx'; 
$dbPass = 'xxxxxxxxx';
$dbDatabase = 'xxxxxxxxxxxxx';
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());
$params = array($_POST['name'], $_POST['gender']);
 
mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
 
// Set up error check and result check array
$error = array();
$results = array();
 
// First check if a form was submitted
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // removes any html/javascript.
 
if (isset($_GET['searchgender'])) {
   $searchgenderTerms = trim($_GET['searchgender']);
   $searchgenderTerms = strip_tags($searchgenderTerms); // removes any html/javascript.
 
 
$atoz = range( 'a', 'z' );
if (strlen($searchTerms) < 3 and !in_array($searchTerms, $atoz)) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   }  
 
}
 
   // If there are no errors, search begins...
   if (count($error) < 1) {
 
//The Query
$searchSQL = "SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE '%{$searchTermDB}%' AND `gender` LIKE '{$searchgenderTermDB}' ";
$stmt = sqlsrv_query($conn, $searchSQL, $params);       
 
      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
 
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "Sorry, we have no record of the name<br /> {$searchTerms} ({$searchgenderTerms}).";
      }else {
         $results = array(); // And now display results...
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "Name: <span id=\"result\">{$row['name']}</span><br /> Gender: <span id=\"result\">{$row['gender']}</span><br /> Origin: <span id=\"result\">{$row['origin']}</span><br /> Meaning: <span id=\"result\">{$row['meaning']}</span><br /><br />"; $i++;
         }
      }
   }
}
 
function removeEmpty($var) {
   return (!empty($var)); 
}
?>
 
<?php echo (count($error) > 0)?"<strong>Error</strong>: <br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?> <form method="GET" action="<?php echo $_SERVER['../../../PHP_SELF'];?>" name="searchForm"> <strong>Enter a name to find it's origin and meaning: </strong> <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /> <br /><br />
      <strong>Select Gender:</strong> <SELECT name="searchgender" value="<?php echo isset($searchgenderTerms)?htmlspecialchars($searchgenderTerms):''; ?>" />
      <option>Male</option>
      <option>Female</option>
      </SELECT>
      <br /><br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />" . implode("", $results):""; ?>

Could someone help me sort this out please?

**Cute Tink** · May 3, 2011 14:59

A blank page suggests a coding error. What is your error reporting set to? Your script should be putting something on the screen, even with an empty result. If you don't know, add this line to the top of the script:

error_reporting( -1 );

I do see one change you should make, if you want to get every name that starts with a letter, like "b":

$searchSQL = "SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE '{$searchTermDB}%' AND `gender` LIKE '{$searchgenderTermDB}' ";

I removed the first % from $searchTermDB, because that signals that you want it to start with that term, otherwise you would get all names that have a "b" in them.

Also, if you are not using the wildcard in the gender term, you should probably change that from LIKE to =.

Without setting up a database with values, I don't know what might be going wrong after a search. I would suggest upping your error reporting level and see what it says.

**StarLion** · May 3, 2011 16:02

I see a couple of things.

$con = mysql_connect($dbHost, $dbUser, $dbPass)
$stmt = sqlsrv_query($conn, $searchSQL, $params);
$searchResult = mysql_query($searchSQL)

Why are we executing 2 queries, on 2 entirely different database connections, and doing absolutely nothing with one of them?
Is sqlsrv_query defined somewhere? Because it's not a normal function.

**CBResources** · May 4, 2011 14:03

Hey Cute Think,

Thanks for pointing out the wildcard issue - I had intended to have it at the end only so I've changed that. I've also changed the LIKE for Gender to an = as per your suggestion.

I ran the script again once more after I made these changes and I no longer get a blank page. I do, however, still get an error for anything under 3 letters even if it is within the specified range of A-Z.

StarLion, I have commented out the line of code you brought to light. It doesn't seem to have any effect on the script so I assume I must have put it there in error. The script does the same thing with or without it.

You can try the script for yourself at http://ivegotkids.com/babynames.php - I've been given permission to disclose this on here.

So... Now the blank page issue is gone - any idea why my A-Z isn't working?

Here is the up to date code:

Code PHP:

<?php
 
$dbHost = 'xxxxxxxx';
$dbUser = 'xxxxxxxx'; 
$dbPass = 'xxxxxxxx';
$dbDatabase = 'xxxxxxxx';
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());
$params = array($_POST['name'], $_POST['gender']);
 
mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
 
// Set up error check and result check array
$error = array();
$results = array();
 
// First check if a form was submitted
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // removes any html/javascript.
 
if (isset($_GET['searchgender'])) {
   $searchgenderTerms = trim($_GET['searchgender']);
   $searchgenderTerms = strip_tags($searchgenderTerms); // removes any html/javascript.
 
 
$atoz = range( 'a', 'z' );
if (strlen($searchTerms) < 3 and !in_array($searchTerms, $atoz)) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   }  
 
}
 
   // If there are no errors, search begins...
   if (count($error) < 1) {
 
//The Query
$searchSQL = "SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE '{$searchTermDB}%' AND `gender` = '{$searchgenderTermDB}' ";
//$stmt = sqlsrv_query($conn, $searchSQL, $params);       
 
      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
 
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "Sorry, we have no record of the name<br /> {$searchTerms} ({$searchgenderTerms}).";
      }else {
         $results = array(); // And now display results...
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "Name: <span id=\"result\">{$row['name']}</span><br /> Gender: <span id=\"result\">{$row['gender']}</span><br /> Origin: <span id=\"result\">{$row['origin']}</span><br /> Meaning: <span id=\"result\">{$row['meaning']}</span><br /><br />"; $i++;
         }
      }
   }
}
 
function removeEmpty($var) {
   return (!empty($var)); 
}
?>
 
<?php echo (count($error) > 0)?"<strong>Error</strong>: <br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?> <form method="GET" action="<?php echo $_SERVER['../../../PHP_SELF'];?>" name="searchForm"> <strong>Enter a name to find it's origin and meaning: </strong> <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /> <br /><br />
      <strong>Select Gender:</strong> <SELECT name="searchgender" value="<?php echo isset($searchgenderTerms)?htmlspecialchars($searchgenderTerms):''; ?>" />
      <option>Male</option>
      <option>Female</option>
      </SELECT>
      <br /><br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />" . implode("", $results):""; ?>

**Cute Tink** · May 5, 2011 09:10

I see that it is coming back with an empty result. Try echoing your query out and running it through phpmyadmin or whatever other database administration system you have and see what happens.

Also, it looks like you have a couple other unnecessary lines:
$params = array($_POST['name'], $_POST['gender']);

function removeEmpty($var) {
return (!empty($var));
}

And you have some odd {} pairings. It looks like you open a set with if (isset($_GET['search'])) { and it doesn't close until right before function removeEmpty($var) {

Perhaps if you clean that up a bit, it will be easier to track down where the problem lies.

**CBResources** · May 6, 2011 00:14

Hi CT

I've removed the unnecessary lines of code you pointed out but I don't know about the odd {} pairings. I've tried a few combinations but whatever I change it to it seems to produce the "Search term must be at least 3 characters" error as soon as the page loads before anything is even entered into the box.

Perhaps you could give me a clue as to how to tidy it up a bit?

Here is the most up to date code once more:

Code PHP:

$dbHost = '********';
$dbUser = '********'; 
$dbPass = '********';
$dbDatabase = '********';
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());
 
 
mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
 
// Set up error check and result check array
$error = array();
$results = array();
 
// First check if a form was submitted
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // removes any html/javascript.
 
if (isset($_GET['searchgender'])) {
   $searchgenderTerms = trim($_GET['searchgender']);
   $searchgenderTerms = strip_tags($searchgenderTerms); // removes any html/javascript.
 
 
$atoz = range( 'a', 'z' );
if (strlen($searchTerms) < 3 and !in_array($searchTerms, $atoz)) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   }  
 
}
 
   // If there are no errors, search begins...
   if (count($error) < 1) {
 
//The Query
$searchSQL = "SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE '{$searchTermDB}%' AND `gender` = '{$searchgenderTermDB}' ";
 
 
      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
 
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "Sorry, we have no record of the name<br /> {$searchTerms} ({$searchgenderTerms}).";
      }else {
         $results = array(); // And now display results...
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "Name: <span id=\"result\">{$row['name']}</span><br /> Gender: <span id=\"result\">{$row['gender']}</span><br /> Origin: <span id=\"result\">{$row['origin']}</span><br /> Meaning: <span id=\"result\">{$row['meaning']}</span><br /><br />"; $i++;
         }
      }
   }
}
 
?>
 
<?php echo (count($error) > 0)?"<strong>Error</strong>: <br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?> <form method="GET" action="<?php echo $_SERVER['../../../PHP_SELF'];?>" name="searchForm"> <strong>Enter a name to find it's origin and meaning: </strong> <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /> <br /><br />
      <strong>Select Gender:</strong> <SELECT name="searchgender" value="<?php echo isset($searchgenderTerms)?htmlspecialchars($searchgenderTerms):''; ?>" />
      <option>Male</option>
      <option>Female</option>
      </SELECT>
      <br /><br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />" . implode("", $results):""; ?>

**Cute Tink** · May 6, 2011 08:23

Here is a quick tidy of it and I'll tell you what I did to get it a little more clean:

PHP Code:


<?php
$dbHost = '********';
$dbUser = '********';
$dbPass = '********';
$dbDatabase = '********';
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());
 
 
mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
 
// Set up error check and result check array
$error = array();
$results = array();
$searchTerms = '';
$searchgenderTerms = '';
 
// First check if a form was submitted

if (isset($_GET['search']) and isset($_GET['searchgender'])) {

   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // removes any html/javascript.
   
   $searchgenderTerms = trim($_GET['searchgender']);
   $searchgenderTerms = strip_tags($searchgenderTerms); // removes any html/javascript. 
 
   $atoz = range( 'a', 'z' );
   if (strlen($searchTerms) < 3 and !in_array($searchTerms, $atoz)) { // Checks search term is longer than 3 characters.
      $error[] = "Search terms must be longer than 3 characters.";
   }  
   else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // Prevents sql injection.
   } 
        
   // If there are no errors, search begins...
   if (count($error) < 1) {
     
      //The Query
      $searchSQL = "SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE '{$searchTermDB}%' AND `gender` = '{$searchgenderTermDB}' ";
     
      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
          
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "Sorry, we have no record of the name<br /> {$searchTerms} ({$searchgenderTerms}).";
      }
        
      else {
        
         // And now display results...
         $i = 1;
            
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "Name: <span id=\"result\">{$row['name']}</span><br /> " .
               "Gender: <span id=\"result\">{$row['gender']}</span><br /> " .
               "Origin: <span id=\"result\">{$row['origin']}</span><br /> " .
               "Meaning: <span id=\"result\">{$row['meaning']}</span><br /><br />"; 
                    
            $i++;
         }

      }
        
   }

}
?>
 
<?php echo (count($error) > 0)?"<strong>Error</strong>: <br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?> 
<form method="GET" action="<?php echo $_SERVER['../../../PHP_SELF'];?>" name="searchForm"> 
<strong>Enter a name to find it's origin and meaning: </strong> 
<input type="text" name="search" value="<?php echo htmlspecialchars($searchTerms); ?>" /> <br /><br />
<strong>Select Gender:</strong> 
<SELECT name="searchgender" value="<?php echo htmlspecialchars($searchgenderTerms); ?>" />
<option>Male</option>
<option>Female</option>
</SELECT><br /><br />
<input type="submit" name="submit" value="Search!" />
</form>
<?php echo (count($results) > 0)?"You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />" . implode("", $results):""; ?>

First I established $searchTerms and $searchgenderTerms as blank strings before even checking if the form was submitted, then you don't have to worry if the variables exist.

Then I combined the two isset() checks for the $_GET variables so it will only process the search if they both exist (you had it this way too, but this is a little cleaner and easier to read with how I have done the tabs). If you indent for each if statement, then you can read the processing order a little better. You did this somewhat, but it was hard to see that the search process was under the first if statement. I hope it doesn't come through too messed up, because I use Notepad++ and it tabs instead of using spaces and sometimes it displays differently.

I broke up some of your lines to make it a little easier to see all of the code without having to scroll left and right. This is just my preference, but I find it helps in the long run.

I did test part of your script and it should properly allow a-z, but nothing else less than 3 letters. At this point, if you are still getting an error that there are no results, then you should echo $searchSQL and copy it into phpmyadmin (or whatever database administration setup you use) and see what results you get. If you get the results there, then there's a different error on your page and we can try to track it down.

**CBResources** · May 6, 2011 14:41

Hi CT,

Thanks again for continuing to help.

I've made the changes you've suggested. I no longer get the "must be over 3 characters" error message when the search is in the range of a-z which is good... I do, however, get an error stating there are no results even if I enter a full name exactly as it is stored in the database.

I have run the SQL command "SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE '{$searchTermDB}%' AND `gender` = '{$searchgenderTermDB}" directly in phpmyadmin, replacing the {$searchTermDB} and {$searchgenderTermDB} with a name and gender and it works perfectly so the problem is somewhere in this script.

I suspect it has something to do with this part of it:

Code PHP:

if (mysql_num_rows($searchResult) < 1) {
         $error[] = "Sorry, we have no record of the name<br /> {$searchTerms} ({$searchgenderTerms}).";
      }
 
      else {
 
         // And now display results...
         $i = 1;
 
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "Name: <span id=\"result\">{$row['name']}</span><br /> " .
               "Gender: <span id=\"result\">{$row['gender']}</span><br /> " .
               "Origin: <span id=\"result\">{$row['origin']}</span><br /> " .
               "Meaning: <span id=\"result\">{$row['meaning']}</span><br /><br />"; 
 
            $i++;
         }
 
      }
 
   }
 
}
?>

I understand this opens with first checking if the number of results is less than 1 and if so it displays the error and then the "else" should continue with the script if the results are equal to or greater than 1... it seems it's preferring the error message over all.

**Cute Tink** · May 6, 2011 14:49

That's why I say to echo the query on your page itself. You need to know what your page is sending to the database and maybe you'll notice something is not quite right about it.

**CBResources** · May 6, 2011 14:58

This is what I get when I echo the search.

SELECT name, gender, meaning, origin FROM names WHERE `name` LIKE 'A%' AND `gender` = '' Error:
Sorry, we have no record of the name
A (Male).

**CBResources** · May 6, 2011 15:04

Fixed it...

Changed {searchgenderTermDB} to {searchgenderTerms} and it works

Thanks for all your help.

**CBResources** · May 6, 2011 15:11

Moving on now...

I'm on to the part where I change how the results are displayed so that they are displayed as links rather than a huge list...

I've got your code from your first reply:

Code PHP:

<?php 
if (count($results) > 0)) {
   echo "You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />";
   foreach ( $results as $value ) {
      echo '<a href="viewMoreInfoPage.php?someRef=Ref">' . $value . '</a><br />';
   }
}
?>

I don't want to load a new page... I want the results to be displayed in place of the list of links with a "back to results" link to go back to the list if they want to once they have read the details.

Does this make sense?

**Cute Tink** · May 6, 2011 15:39

I think so. What you will need to do then is pass the search choices along with the link to the details and then build the search link on the next page:

PHP Code:


echo '<a href="viewMoreInfoPage.php?searchTerms=' . $searchTerms . '&amp;searchgenderTerms=' . $searchgenderTerms . '&amp;name=' . $value . '">' . $value . '</a><br />';

That will pass everything to the next page. Then your link back will have to be careful to submit exactly the same variables names, otherwise your search won't recognize the results:

PHP Code:


echo '<a href="yoursearchpage.php?search=' . $_GET['searchTerms'] . '&amp;searchgender=' . $_GET['searchgender'] . '">Back to results</a>';

If that's not what you were thinking of, let me know.

**CBResources** · May 6, 2011 16:36

Hi CT,

I've done as you suggested.

It still lists them all out in their complete format instead of just the names as links. Only now the whole thing becomes a link and when clicked, it just resets the search form.

This is the code:

Code PHP:

<?php 
if (count($results) > 0) {
   echo "You searched for: <span id=\"terms\">{$searchTerms} ({$searchgenderTerms})</span> - Here are your results:<br /><br />";
   foreach ( $results as $value ) {
      echo '<a href="babynames.php?searchTerms=' . $searchTerms . '&amp;searchgenderTerms=' . $searchgenderTerms . '&amp;name=' . $value . '">' . $value . '</a><br />';
   }
}
?>

And you can see what it does here:

http://ivegotkids.com/babynames.php

**Cute Tink** · May 7, 2011 10:32

The links aren't closing. If you look at the HTML produced, you can see that the name is a long value covering all of the result parts. We need to make a small adjustment to how the link is made.

If we change the result array a bit:

PHP Code:


while ($row = mysql_fetch_assoc($searchResult)) {
            $results[$row['name']] = "Name: <span id=\"result\">{$row['name']}</span><br /> " .
               "Gender: <span id=\"result\">{$row['gender']}</span><br /> " .
               "Origin: <span id=\"result\">{$row['origin']}</span><br /> " .
               "Meaning: <span id=\"result\">{$row['meaning']}</span><br /><br />";

Then we can do this:

PHP Code:


foreach ( $results as $key => $value ) {
      echo '<a href="babynames.php?searchTerms=' . $searchTerms . '&amp;searchgenderTerms=' . $searchgenderTerms . '&amp;name=' . $key . '">' . $value . '</a><br />';
   }

That assumes that you would pass the name to the next page to get whatever other information that you want to view. That should clean up the page a little.

**Cups** · May 7, 2011 12:00

Something is bemusing me here, how many "gender terms" are there?

is it not just male/female ?

**logic_earth** · May 7, 2011 13:13

Off Topic:

Originally Posted by Cups

Something is bemusing me here, how many "gender terms" are there?

is it not just male/female ?

Hermaphrodite?

Gender-less?

**Cups** · May 7, 2011 14:32

Off Topic:

Or even tax collectors.

**CBResources** · May 10, 2011 08:46

Hi CT,

It's cleaned up the links a bit now but still displaying everything immediately, not just the names... see for yourself:

http://ivegotkids.com/babynames.php

Also, it's still returning to a blank form when either of the names are clicked.

It needs to display just the names as search results, and then display the rest of the details for a specific name once it's clicked on.

And Cups - It is just male and female... I don't think anybody will be looking for names for their baby hermaphrodites

**Cups** · May 10, 2011 09:25

In that case searchgenderterms is in fact a boolean value.

1=male
0-female

which corresponds to a far easier query, the equivalent of:

Code:

..... AND gender = 1;

Plus it is easier to filter and sanitize.

PHP Code:


$gender=0; // default to female
if( isset($_GET['gender'] && (int)$_GET['gender'] == 1 ){
$gender= 1;
}

Code:

"..... AND gender = $gender";

You could use 'male', 'female' instead of 1, 0 if you wanted.

**Cute Tink** · May 10, 2011 09:40

If you only want to display the name on the first page, then we need to adjust your query to reduce unnecessary overhead and adjust the $results array accordingly. In a way, we will be restoring it to what it was before (changed snippets only):

PHP Code:


$searchSQL = "SELECT name FROM names WHERE `name` LIKE '{$searchTermDB}%' AND `gender` = '{$searchgenderTerm}' ";

while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = $row['name'];
}

foreach ( $results as $value ) {
      echo '<a href="babynames.php?searchTerms=' . $searchTerms . '&amp;searchgenderTerms=' . $searchgenderTerms . '&amp;name=' . $value . '">' . $value . '</a><br />';
   }

Those are the lines that you would change to show just the names on the search page. I think I kept the query right, based on what you said for the gender part before.

And I agree with cups on the simplification of the query, but I would argue for extra security reasons. If you want to stick with male/female, then this would simplify your security for it:

PHP Code:


$searchgenderTerm = ( $_GET['gender'] != 'male' ? 'female' : 'male' );

Didn't think about that before, but when it is one or the other, this is easier than using mysql_real_escape_string() or htmlspecialchars(), etc.

**Cups** · May 10, 2011 11:47

@Cute Tink, yeah, agree on the security method as long as gender is actually set, and if you plump for male / female flags.

From the db point of view though I admit I prefer 1/0 - but that is just a matter of personal choice.

The thing is CBResources, you are muddying your code by terming it searchgenderterm, if you HTML field had the name="gender" ( and maybe id="gender") and you used the variable name $gender in PHP and your database field was called `gender`, well it would all be a) easier to type b) easier to read and ultimately you would make fewer errors and also be far more likely to want to come back and edit this code a few months down the line.

I have a second nagging question, what happens if/when a name is spelt the same for both male and female?

**Cute Tink** · May 10, 2011 12:47

On that point, I realized I didn't type something right:

PHP Code:


$searchgenderTerm = ( $_GET['searchgender'] != 'male' ? 'female' : 'male' );

and it should be inside the if statement that checks whether $_GET['searchgender'] is set.

Whether it is 1/0, m/f or male/female, it all works the same if the column is set to enum and it is consistent. Like you said, it is whatever you prefer.

As for what happens when the same name is for both male and female, like Robin perhaps, there would have to be two database entries, one for each, because some times there are different meanings or origins.

User Tag List

Thread: PHP Search Script

Thread Tools

Display

PHP Search Script

Bookmarks

Bookmarks

Posting Permissions