Help Improve PHP Search Script

PHP
1

Hi all,

I’m developing a “Name Finder” script for a website that I am working on. I have the basic function working and I need some help improving the script.

Here is my code so far:

<phpcode><?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) {
      $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)); 
}
?>

So the above script is working fine… A user can come to the site, search for a name and if the name exists in the database it will return the name, along with the name’s origin & meaning.

Now there are a couple of improvements I want to make.

  1. I want to change the results slightly. Instead of it displaying the full record, I want it to just display the Name found as a hyperlink, which when clicked will then show the full record for that name. If I can achieve this, it will then open the door for the next improvement.

  2. I want to add the ability to click a letter “A, B, C, D… etc…” and display all of the results beginning with that letter.

  3. Lastly, I want the results to be displayed in columns so I can fit more on the page without scrolling.

I hope this all makes sense and if anybody could help me with modifying my above code to achieve either or all of the above, I would be very happy.

Many thanks.

2
  1. in that case turn the name found into a hyperlink such as;

echo "<a href='names.php?term=$name'>$name</a>"

which you pick up in names.php as $_GET[‘term’] and then do a second query picking up the fuller details.
2) This is simply a matter of using the widcard character in your sql statement.

… from mytable where name like “A%” ;

  1. That sounds more like a html display question, if you want to use tables then just split the results into groups of say, 3 and build each column that way - depending on whether you want:

1 | 2 | 3
4 | 5 | 6

or

1 | 2 | 3
4 | 5 | 6

3

Hi Cups & Thanks for your advice.

  1. I’ll work on your example and come back to you if I have any problems.

  2. I know how to use the wildcard character in my SQL Statement but if you noticed the code prevents searches of less than 3 characters in order to prevent SQL Injection… If I remove the “3 character” minimum, is there another way I can protect it from attacks?

  3. I’ll come back to this when I’ve sorted out the first two…

4

re: 2) Good question.

Yes you will need to change your check for just this a-z type of search.

The easiest would be to make sure that a-z search requests come in with a different variable, such as search.php?az=A

Then your filter can be extremely stringent.


if strlen( $_GET['az'] !== 1 ){
// send the user away

}else{
// construct your query

$where_query = 'where name like "' . $_GET['az'] . '"' ;

}

You could use a regex to enforce letters only too, but experience tells me that some times people use numbers to start their usernames.

Ask if you want a regex and someone will guide you.

ps here is a simple way to generate your a-z of links:



$letters = range('A', 'Z');

foreach( $letters as $letter ){

echo "<a href='search.php?az=$letter'>$letter</a> ";

}