Search MySQL table

[missyevil]

Hi

I am trying to create a search tool for a website, using PHP, that allows the user to select options from a HTML form, seacrh the MySQL for matching entries, and then display these entries in a table.

After looooooads of reseacrh I'm (pretty) sure I have an independant "keyword" search working for one of the fields, but am having problems getting info on EXACT matching entried from a list, and I really need to be able to conbine the two into one seacrh form.

I *think* I need to use something like:

[code]SELECT DISTINCT fieldname1, fieldname2, fieldname3... FROM tablename WHERE values match;[/code]

but I am having issues!!

Appreciate any help offered, as I am now really a programmer and am still very new to PHP and MySQL.

Thanks

[swatisonee]

i dont know if i have understood your query correctly, but see the foll post of mine and its solution. in this i just type the first few letters of a word and the entire column in a table is checked and the results come up for further processing.

[a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=89193\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?showtopic=89193[/a]

[missyevil]

I'm probably not being very clear...

Basically, I have a database containing lots of different tables, one of which is 'vacancies' (job vacancies). I want a user to be able to come along and select job/business area and job location from a list (and maybe position type, but I'm ignoring that for now until I get it basically up and running), and enter optional keywords.

The HTML form for data entry looks something like this:

[code]<form action="search.php" method="post">

<fieldset>
  <input type="hidden" name="search" value="true" id="search" />

  <table border="0" cellspacing="1" cellpadding="1">

    <tr>
      <td><label for="job_area">Business area: </td>
      <td><select name="job_area" id="job_area" multiple size=5>
            <option value=""> ---- Select All ---- </option>
            <option value="banking">Banking and Finance</option>
            <option value="construction">Construction</option>
            <option value="consulting">Consulting Services</option>
            <option value="engineering">Engineering</option>
            <option value="health">Healthcare</option>
            <option value="hospitality">Hospitality / Leisure and Tourism</option>
            <option value="hr">Human Resources</option>
            <option value="it">IT and Computing</option>
            <option value="law">Law</option>
            <option value="media">Media</option>
            <option value="public">Public</option>
            <option value="retail">Retail</option>
            <option value="sales">Sales</option>
            <option value="transport">Transport</option>
        </select></label></td>
    </tr>

    <tr>
      <td><label for="job_location">Location: </td>
      <td><select name="job_location" id="job_location" multiple size=5>
            <option value="">---- Select All ----</option>
            <option value="london">London</option>
            <option value="nw">North East England</option>
            <option value="ne">North West England</option>
            <option value="mid">Midlands</option>
            <option value="se">South East England</option>
            <option value="sw">South West England</option>
            <option value="scotland">Scotland</option>
            <option value="ni">Northern Ireland</option>
            <option value="wales">Wales</option>
        </select></label></td>
    </tr>

    <tr>
      <td>Position Types: </td>
      <td><input type="checkbox" id="fulltime" name="job_status" value="fulltime" />
        <label for="fulltime">Full Time</label>
        <input type="checkbox" id="permanent" name="job_type" value="permanent" />
        <label for="permanent">Permanent</label>
        <br />
        <input type="checkbox" id="parttime" name="job_status" value="parttime" />
        <label for="parttime">Part Time</label>
        <input type="checkbox" id="job_type" name="jt" value="3" />
        <label for="temporary">Temporary</label>
        <br />
        <input type="checkbox" id="placement" name="job_type" value="placement" />
        <label for="placement">Placement/Internship</label>
        <input type="checkbox" id="seasonal" name="job_type" value="seasonal" />
        <label for="seasonal">Seasonal</label>
      </td>
    <tr>
      <td colspan=2><label for="keywords">Enter Keyword(s) (optional)</label></td>
    </tr>
    <tr>
      <td><input type="text" id="keywords" name="q" maxlength="200" />
      <br />example keywords: editor, manager<br />
      </td>
    </tr>

    <tr>
      
      <td><input type="submit" value="Submit Search" /></td>
      <td><input type="reset" value="Clear Search Criteria" /></td>
    </tr>

  </table>
</fieldset>[/code]

When the user submits the search criteria they have specified, I want to match it exactly to the correspondin tables in my vacancy table, and return a list of matched entries, in another table.

Thanks for replying!!

[swatisonee]

So each variable will have to exactly match each field of vacancy table right ?

so search.php will start as

[code]<?
$search = $_POST["search"];
$job_location= $_POST["job_location"];
$job_status = $_POST["job_status"];
and so on till all variables are listed

$sql= "SELECT * FROM `Vacancies` WHERE `Location`= '$job_location'  AND `Status`= '$job_status'  ORDER BY `Location` asc ";

$result= mysql_query[$sql] or die (mysql_error());

if ($myrow = mysql_fetch_array($result)
{
do
{
printf
and so on
} while ($myrow = mysql_fetch_array($result) )
}

?>[/code]

HTH .

[missyevil]

Ahhhh, that look about right.... I'll have a play and see what happens.

Thanks!!

[missyevil]

Search is now working, with a mixture of selections from menus, radio boxes, and a keyword search :D

Thanks for help.

Gonna post the code for anyone who's interested:

[code]// the basic SELECT statement
$select = 'SELECT *';
$from = ' FROM vacancies';
$where = ' WHERE 1=1';
$order = ' ORDER BY closing_date DESC;';

$job_area = $_POST['job_area'];
if ($job_area != '')  // a specific area has been specified
{
  $len=count($job_area);
  $count=0;
  $where .= ' AND (';
  foreach($job_area as $select_area)
  {  
     echo 'My select: '.$select;
     $where .= " job_area='$job_area[$count]'";
     if($count<$len-1) {$where .= ' OR ';}
     $count++;
  }
  $where .= ') ';
  
}

$job_location = $_POST['job_location'];
if ($job_location != '')  // a specific location has been specified
{
  $len=count($job_location);
  $count=0;
  $where .= ' AND (';
  foreach($job_location as $select_location)
  {  
     echo 'My select: '.$select;
     $where .= " job_location='$job_location[$count]'";
     if($count<$len-1) {$where .= ' OR ';}
     $count++;
  }
  $where .= ') ';
}

$job_status = $_POST['job_status'];
if ($job_status != '')    // a specific status (ft/pt) has been specified
{
  $len=count($job_status);
  $count=0;
  $where .= ' AND (';
  foreach($job_status as $select_status)
  {  
     echo 'My select: '.$select;
     $where .= " job_status='$job_status[$count]'";
     if($count<$len-1) {$where .= ' OR ';}
     $count++;
  }
  $where .= ') ';
}

$job_type = $_POST['job_type'];
if ($job_type != '')      // a specific job type has been specified
{
  $len=count($job_type);
  $count=0;
  $where .= ' AND (';
  foreach($job_type as $select_type)
  {  
     echo 'My select: '.$select;
     $where .= " job_type='$job_type[$count]'";
     if($count<$len-1) {$where .= ' OR ';}
     $count++;
  }
  $where .= ') ';
}

// Get keyword(s)
$keyword = $_POST['key'];
$trimmed = trim($keyword); //trim whitespace from the stored variable

if (isset($keyword))
{
  $where .= " AND job_title LIKE \"%$trimmed%\"";
}

?>

<h3>Search Results</h3>
<hr />
<table>

<?

$jobs = mysql_query($select . $from . $where . $order) or die (mysql_error());

if (!$jobs)
{
  echo '</table';
  exit('<p>Error retrieving vacancies from database! <br />'.
                 'Error: ' . mysql_error() . '</p>');
}

while ($job_area = mysql_fetch_array($jobs,MYSQL_ASSOC))
{
  echo "<tr>\n";
  $vacid = $job_area['vacid'];
  $job_title = htmlspecialchars($job_area['job_title']);
  echo "<td>$vacid</td>";
  echo "<td>$job_title</td>\n";
  echo "</tr>\n";
  
}[/code]

?>

[swatisonee]

Good but you might want to read this bit about sql injections

[a href=\"http://www.php.net/manual/en/security.database.sql-injection.php\" target=\"_blank\"]http://www.php.net/manual/en/security.data...l-injection.php[/a]

[a href=\"http://en.wikibooks.org/wiki/Programming:PHP:SQL_Injection\" target=\"_blank\"]http://en.wikibooks.org/wiki/Programming:PHP:SQL_Injection[/a]

especially with that $where = ' WHERE 1=1'; bit

I'm very new to php myself so if one of the real gurus happens to read this they may shed more light on whether i m being paranoid in this case or not !

[lead2gold]

[!--quoteo(post=358921:date=Mar 27 2006, 10:39 AM:name=swatisonee)--][div class=\'quotetop\']QUOTE(swatisonee @ Mar 27 2006, 10:39 AM) [snapback]358921[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Good but you might want to read this bit about sql injections

[a href=\"http://www.php.net/manual/en/security.database.sql-injection.php\" target=\"_blank\"]http://www.php.net/manual/en/security.data...l-injection.php[/a]

[a href=\"http://en.wikibooks.org/wiki/Programming:PHP:SQL_Injection\" target=\"_blank\"]http://en.wikibooks.org/wiki/Programming:PHP:SQL_Injection[/a]

especially with that $where = ' WHERE 1=1'; bit

I'm very new to php myself so if one of the real gurus happens to read this they may shed more light on whether i m being paranoid in this case or not !
[/quote]

Those articles (from the links) were great; Thank you for posting a followup like that!

[missyevil]

I agree!

I know I'll have to be more secure, but at the minute I'm not *too* worried, as currently this is just a project for uni.