Create Dropdown list PHP database search?

Question

I am trying to create a dropdown list search to search my database in PHP and my SQL. So far I have been able to create a simple text search which brings back the results needed when I type them into the search box. But I want to be able to select an option from the dropdown list instead of typing it in.

Here is my search.php code...

<?php

mysql_connect ("localhost", "root","")  or die (mysql_error());
mysql_select_db ("schoolsni");

$term = $_POST['term'];

$sql = mysql_query("SELECT * FROM  product WHERE  model LIKE '%$term%'");

while ($row = mysql_fetch_array($sql)){
    echo 'Product ID '.$row['product_id'];
    echo '<br/> Model: '.$row['model'];
    echo '<br/> quantity: '.$row['quantity'];
    echo '<br/><br/>';
    }

?>

And here is my HTML code...

<div class="UFcol1">
    <form action="search.php" method="post">
        <select name="term">
    <option value="product">product</option>
    <option value="product1">product 1</option>
    <option value="product3">product 3</option>
    <option value="product4">product 4</option>
    </select>
        <input type="submit" name="submit" value="Submit" />
    </form>
</div>

Your code is severable to SQL-injection. Anybody could manipulate your database, most likely even delete all your data. [Read more about SQL-injection](http://stackoverflow.com/search?q=[php]+sql+injection) — Jacco, Nov 28 '11 at 14:00
its on localside... not online at the minute! otherwise it would be password protected! — Christopher Jenks, Nov 28 '11 at 14:01
Still you should escape any user input data before you use them in any queries, don't rely on password protection alone — Bogdan, Nov 28 '11 at 14:03
noted... reading up on sql injection now... but any help with my question? — Christopher Jenks, Nov 28 '11 at 14:05
What's not working? What happens if you do SELECT * FROM product WHERE model LIKE '%product1%' in mysql — Nathan Q, Nov 28 '11 at 14:07
What's the problem? You seem to have already got a select box in your second block of code. Is the question about how to populate it? — Daren Chandisingh, Nov 28 '11 at 14:07
Daren, thats the select box populated, but what i want to so is, when 'product' is selected and I click search, i want it to bring back the appropriate result if you understand me? Nathan, that doesnt work but if i change it to SELECT * FROM product WHERE model LIKE 'product 1' it finds the result ok. — Christopher Jenks, Nov 28 '11 at 14:09
@ChristopherJenks I think $_POST['term'] will not have 'product 1' as value, but 'product2' — Nathan Q, Nov 28 '11 at 14:13
Ok, I'm looking to set $_POST['term'] to whatever I have selected in the select list so it can search through my database accordingly. So say i have 'product 1' selected in select list and I click search, I want to assign the string 'product 1' to $_POST['term'] so that can be searched in the database. — Christopher Jenks, Nov 28 '11 at 14:16
no unfortunately not... i have changed the code as you said. when i select the first option 'product' it brings back two results... but i would like that to bring all results. then 'product 1' should only return product 1 etc. — Christopher Jenks, Nov 28 '11 at 14:25

tom at zepsu dot com · Answer 1 · 2011-11-28T14:48:26.523

<?php 
$mysqli = new mysqli( 'host', 'user', 'pass', 'table' ); 
if( $_POST['submit'] ){
   $value = $mysqli->real_escape_string( $_POST['term'] );
  $result = $mysqli->query("SELECT * FROM product WHERE model LIKE %$value%");
while( $row = $result->fetch_assoc() ){
   echo $row['field'];
}
 }else{
   $results = $mysqli->query( "SELECT DISTINCT procucts.name from products WHERE products.stock = 1" );
   $options = '<select>';
   while( $row = $results->fetch_assoc( ) ){
    $options .= "<option value='{$row['name']}'>$row['name']</option>";
   }
   $options .= '</select>';
?>

   <div class="UFcol1">
  <form action="search.php" method="post">
    <?=$options?>
      <input type="submit" name="submit" value="Submit" />
   </form>
</div>
<?php } ?>

This is quick code and may not be 100% right I also left out things like checking your connection was made sucesfully. php.net has plenty of information and examples on this.

Edit: added self filling option list from disticnt database values.

Daren Chandisingh · Answer 2 · 2011-11-28T14:37:12.317

You could populate the select box using the table itself:

$sql = mysql_query('SELECT model FROM  product ORDER BY model');
$models = array();
while ($row = mysql_fetch_array($sql)){
    $models[] = $row;
}
?>
<form action="search.php" method="post">
  <select name="term">
  <?php
  foreach ($models as $model) {
  ?>
    <option value="<?php echo $model['model']?>"><?php echo $model['model']?></option>
  <?php
  }
  ?>
  </select>
  <input type="submit" name="submit" value="Submit" />
</form>

To get a list of common model roots, you could do something like this perhaps:

$sql = mysql_query('SELECT model FROM  product ORDER BY model');
$models = array();
while ($row = mysql_fetch_array($sql)){
    /*
     * Break the model name up into parts, assuming each part
     * is separated by a space. Then just use the first part
     * to build up an array of common model starts.
     * 
     * Because we're using the sub-part as a key, we'll only 
     * get a single instance of each one.
     */

    $modelParts = explode(' ', $row['model']);

    // e.g. $models['product'] = 'product'

    $models[$modelParts[0]] = $modelParts[0];
}

In fact, you could probably use this in order to build up a select box that has both the full model names, and the partial ones:

$sql = mysql_query('SELECT model FROM  product ORDER BY model');
$models = array();
while ($row = mysql_fetch_array($sql)){
    $modelParts = explode(' ', $row['model']);
    $models[$modelParts[0]][] = $row['model'];
}

// Equivalent to ending up with something like:
// $models['product'] = array('product 1', 'product 2');

Then you can loop through them and put out a list like this:

  <select name="term">
  <?php foreach ($models as $modelRoot => $modelEntries) { ?>
    <option value="<?php echo $modelRoot?>"><?php echo $modelRoot?></option>
    <?php foreach ($modelEntries as $model) { ?>
      <option value="<?php echo $model['model']?>"><?php echo $model['model']?></option>
    <?php } ?>
  <?php } ?>
  </select>

Ok, that would actually be a nice addition Daren, but is there anyway after the select box is populated where I can run a search? for example, say there is a number of products name 'product 2' and 'product 3', and in the select box i select just 'product'. When i click search i want to be able to return both of these results. i can doing purely on an input box but would prefer a select box. Thanks for your patience. — Christopher Jenks, Nov 28 '11 at 14:20
Your problem, then is coming up with a set of strings that are valid common substrings of your products, then? You may already have, say, categories of products, or product types. You could use that information instead of the product name. Otherwise, you could get all the product names, order them alphabetically and then build a list of the first parts. I'll explain what I mean in the answer above. — Daren Chandisingh, Nov 28 '11 at 14:27
Yea i understand what you mean... will give it a go and get back to you! thanks! — Christopher Jenks, Nov 28 '11 at 14:34
you could `SELECT DISTINCT products.name FROM products` to fill the list which will create a list of product names that match but won't repeat any, will add it to my code example above :D — tom at zepsu dot com, Nov 28 '11 at 14:43

score -1 · Answer 3 · answered Nov 28 '11 at 14:20

<?php

mysql_connect ("localhost", "root","")  or die (mysql_error());
mysql_select_db ("schoolsni");


$term = $_POST['term'];

$sql = mysql_query("SELECT * FROM  product WHERE  model LIKE '%$term%'");
?>
<select name='term'>
<?php
while ($row = mysql_fetch_array($sql)){
?>
  <option value='<?php echo $row['product_id']?>'><?php echo $row['model'];?></option>
<?php

}
?>
</select>

Create Dropdown list PHP database search?

3 Answers3