Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post

    Drop down box search

    I am working on a search facilty that looks up a mysql database, I have two dropdown boxes "jobcategory" and "joblocation", i have some code for this but im wondering if im on right track and is this the best method of doing it?

    My search code:

    PHP Code:
    <?php
    include("database.php");

    $sql "SELECT * FROM job";

    if (
    $_POST["jobcategory"] && $_POST["jobcategory"]) {
      
    $sql .= " WHERE jobcategory LIKE '%{$_POST["jobcategory"]}%' AND joblocation '%{$_POST["joblocation"]}%'");
    }
    else if (
    $_POST["jobcategory"]) {
      
    $sql .= " WHERE jobcategory LIKE '%{$_POST["jobcategory"]}%'");
    }
    else if (
    $_POST["joblocation"]) {
      
    $sql .= " WHERE joblocation like '%{$_POST["joblocation"]}%'");
    }

    $query mysql_query($sql);

    while (
    $job mysql_fetch_array($query)){
        
    $jobtitle=$job["jobtitle"]; 
        
    $jobcategory=$job["jobcategory"]; 
       
    ?>
       
    <table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
        <tr>
            <td width="131"><font face="Verdana" size="2">Job Title</font></td>
            <td width="131"><font face="Verdana" size="2">Job Category</font></td>
            <td width="131"><font face="Verdana" size="2">Job Description</font></td>
        </tr>
        <tr>
            <td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
        </tr>
    </table>
      
    <?php
      

    ?>
    Last edited by PRodgers4284; 03-07-2008 at 04:48 PM.

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    You still aren't taking the proper security measures. Your code is succeptable to mysql injection. I posted a url a while back on one of your threads about writing secure php. I suggest you read it.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by _Aerospace_Eng_ View Post
    You still aren't taking the proper security measures. Your code is succeptable to mysql injection. I posted a url a while back on one of your threads about writing secure php. I suggest you read it.
    I know the code is not secure atm, im only testing it for now, just wanted to know if im on the right lines for developing the search facilty.

  • #4
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Something like this might be better
    PHP Code:
    <?php
    include("database.php");

    $sql "SELECT * FROM job";
    $jobcat mysql_real_escape_string(trim($_POST['jobcategory']));
    $jobloc mysql_real_escape_string(trim($_POST['joblocation']));
    if (
    $jobcat != '' && $jobloc != '') {
      
    $sql .= " WHERE jobcategory LIKE '$jobcat%' AND joblocation '%$jobloc%'";
    }
    else if (
    $jobcat != '' && $jobloc == '') {
      
    $sql .= " WHERE jobcategory LIKE '%$jobcat%'";
    }
    else if (
    $jobloc != '' && $jobcat == '') {
      
    $sql .= " WHERE joblocation LIKE '%$jobloc%'";
    }

    $query mysql_query($sql) or die(mysql_error());

    if(
    mysql_num_rows($query) > 0)
    {
    while (
    $job mysql_fetch_array($query))
    {
        
    $jobtitle=$job["jobtitle"]; 
        
    $jobcategory=$job["jobcategory"]; 
       
    ?>
       
    <table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
        <tr>
            <td width="131"><font face="Verdana" size="2">Job Title</font></td>
            <td width="131"><font face="Verdana" size="2">Job Category</font></td>
            <td width="131"><font face="Verdana" size="2">Job Description</font></td>
        </tr>
        <tr>
            <td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
        </tr>
    </table>
      
    <?php
      

      }
      else
      {
      echo 
    '<p>There are no search results with the search criteria you entered.</p>';
      }
    ?>
    Pay attention to what you actually code. This made no sense
    PHP Code:
    if ($_POST["jobcategory"] && $_POST["jobcategory"]) { 
    Also your queries were incorrect. You should have been getting errors but AGAIN you have NO error checking so you don't know.
    PHP Code:
    $sql .= " WHERE jobcategory LIKE '%{$_POST["jobcategory"]}%'"); 
    Don't need the ) on the end. I suggest you turn on php error reporting (search that).
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #5
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by _Aerospace_Eng_ View Post
    Something like this might be better
    PHP Code:
    <?php
    include("database.php");

    $sql "SELECT * FROM job";
    $jobcat mysql_real_escape_string(trim($_POST['jobcategory']));
    $jobloc mysql_real_escape_string(trim($_POST['joblocation']));
    if (
    $jobcat != '' && $jobloc != '') {
      
    $sql .= " WHERE jobcategory LIKE '$jobcat%' AND joblocation '%$jobloc%'";
    }
    else if (
    $jobcat != '' && $jobloc == '') {
      
    $sql .= " WHERE jobcategory LIKE '%$jobcat%'";
    }
    else if (
    $jobloc != '' && $jobcat == '') {
      
    $sql .= " WHERE joblocation LIKE '%$jobloc%'";
    }

    $query mysql_query($sql) or die(mysql_error());

    if(
    mysql_num_rows($query) > 0)
    {
    while (
    $job mysql_fetch_array($query))
    {
        
    $jobtitle=$job["jobtitle"]; 
        
    $jobcategory=$job["jobcategory"]; 
       
    ?>
       
    <table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
        <tr>
            <td width="131"><font face="Verdana" size="2">Job Title</font></td>
            <td width="131"><font face="Verdana" size="2">Job Category</font></td>
            <td width="131"><font face="Verdana" size="2">Job Description</font></td>
        </tr>
        <tr>
            <td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
        </tr>
    </table>
      
    <?php
      

      }
      else
      {
      echo 
    '<p>There are no search results with the search criteria you entered.</p>';
      }
    ?>
    Pay attention to what you actually code. This made no sense
    PHP Code:
    if ($_POST["jobcategory"] && $_POST["jobcategory"]) { 
    Also your queries were incorrect. You should have been getting errors but AGAIN you have NO error checking so you don't know.
    PHP Code:
    $sql .= " WHERE jobcategory LIKE '%{$_POST["jobcategory"]}%'"); 
    Don't need the ) on the end. I suggest you turn on php error reporting (search that).
    Hey thanks for the help, im getting an error stating:

    "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%Co.Antrim%'' at line 1".

    Is this to do with the version of mysql im using?

  • #6
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    PHP Code:
    if ($jobcat != '' && $jobloc != '') {
      
    $sql .= " WHERE jobcategory LIKE '{$jobcat}%' AND joblocation '%{$jobloc}%'";
    }
    else if (
    $jobcat != '' && $jobloc == '') {
      
    $sql .= " WHERE jobcategory LIKE '%{$jobcat}%'";
    }
    else if (
    $jobloc != '' && $jobcat == '') {
      
    $sql .= " WHERE joblocation LIKE '%{$jobloc}%'";

    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #7
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by _Aerospace_Eng_ View Post
    PHP Code:
    if ($jobcat != '' && $jobloc != '') {
      
    $sql .= " WHERE jobcategory LIKE '{$jobcat}%' AND joblocation '%{$jobloc}%'";
    }
    else if (
    $jobcat != '' && $jobloc == '') {
      
    $sql .= " WHERE jobcategory LIKE '%{$jobcat}%'";
    }
    else if (
    $jobloc != '' && $jobcat == '') {
      
    $sql .= " WHERE joblocation LIKE '%{$jobloc}%'";


    The script now works, if i go directly to the search.php script, it displays all the records in the database. Im using the following basic test form to carryout the search:

    Code:
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>Job Search</title>
    </head>
    
    <body>
    <form method="post" action="search.php">
    <select name="jobcategory">
       <option value="Construction">Construction</option>
         <option value="Banking and Insurance">Banking and Insurance</option>
         <option value="Sales">Sales</option>
       <option value="Other">Other</option>
    </select>
    <select name="joblocation">
         <option value="Co.Antrim">Co.Antrim</option>
           <option value="Co.Down">Co.Antrim</option>
         </select><input type="submit" />
    </form>
    
    </body>
    
    </html>
    When i run the script from this page i get the same error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%Co.Antrim%'' at line 1"

  • #8
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Post your current search.php page please. Reason it displays all of the records is because you have this as your first sql statement
    PHP Code:
    $sql "SELECT * FROM job"
    and then you run the query.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #9
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by _Aerospace_Eng_ View Post
    Post your current search.php page please.
    Code:
    <?php
    include("database.php");
    
    $sql = "SELECT * FROM job";
    $jobcat = mysql_real_escape_string(trim($_POST['jobcategory']));
    $jobloc = mysql_real_escape_string(trim($_POST['joblocation']));
    if ($jobcat != '' && $jobloc != '') {
      $sql .= " WHERE jobcategory LIKE '{$jobcat}&#37;' AND joblocation '%{$jobloc}%'";
    }
    else if ($jobcat != '' && $jobloc == '') {
      $sql .= " WHERE jobcategory LIKE '%{$jobcat}%'";
    }
    else if ($jobloc != '' && $jobcat == '') {
      $sql .= " WHERE joblocation LIKE '%{$jobloc}%'";
    }  
    
    $query = mysql_query($sql) or die(mysql_error());
    
    if(mysql_num_rows($query) > 0)
    {
    while ($job = mysql_fetch_array($query))
    {
        $jobtitle=$job["jobtitle"]; 
        $jobcategory=$job["jobcategory"]; 
       ?>
       
    <table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
        <tr>
            <td width="131"><font face="Verdana" size="2">Job Title</font></td>
            <td width="131"><font face="Verdana" size="2">Job Category</font></td>
            <td width="131"><font face="Verdana" size="2">Job Description</font></td>
        </tr>
        <tr>
            <td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
            <td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
        </tr>
    </table>
      
    <?php
      } 
      }
      else
      {
      echo '<p>There are no search results with the search criteria you entered.</p>';
      }
    ?>

  • #10
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Check your first line. There should probably be a LIKE in there somewhere.
    ||||If you are getting paid to do a job, don't ask for help on it!||||


  •  

    Posting Permissions

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