Jump to content

Archived

This topic is now archived and is closed to further replies.

missyevil

Search MySQL table

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites
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 .

Share this post


Link to post
Share on other sites
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]

?>

Share this post


Link to post
Share on other sites
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 !


Share this post


Link to post
Share on other sites
[!--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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.