SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2010
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help Improve PHP Search Script

    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:


    Code PHP:
    <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. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by CBResources View Post
    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.
    1) in that case turn the name found into a hyperlink such as;
    PHP Code:
    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%" ;

    3) 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. #3
    SitePoint Zealot
    Join Date
    Oct 2010
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.
    PHP Code:
    if strlen$_GET['az'] !== ){
    // 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:
    PHP Code:

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

    foreach( 
    $letters as $letter ){

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




Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •