CODEWITHSUNDEEP
phpmysqlsql
tzu-hui chan
tzu-hui chan

Reputation: 21

Searching a database table from a web form

I want do a search in a sql table using a drop down menu.

For exmple, the user can chose color and then it will show all the items with that color.

Here is my code - I tried to use sql command with color red (rouge in French) it works; then I tried to use $couleur, it does not work. can someone check my sql command please. thank you so much.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<form name="" method="post" action="enquete.php">
<select name="couleur">
   <option ></option>
  <option value="rouge">rouge</option>
  <option value="pink">pink</option>



</select>




</select>
<input type="submit" value="submit" name="submit2" /> <br/>
</form>


</body>
</html>

<?php 

     //traitement pour decoulant couleur
    if (isset($_POST["couleur"]) && $_POST["couleur"]!="0"&& isset($_POST["submit"]) ){
        //connexion avec le serveur
        include_once("mesparametres.inc.php");
        //récupération données formulaire
        $couleur = $_POST["couleur"];
        $query = "SELECT * FROM `poisson` WHERE `couleur` LIKE 'rouge'";
        $query = "SELECT * FROM `poisson` WHERE `couleur` LIKE '\"$couleur\"'";
       $result = mysql_query($query) or die('Query failed: ' . mysql_error());


        // Printing results in HTML

        echo "<table border='1' width='300'>\n";

         $count = 0;
            while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
               echo "\t<tr>\n";
               foreach ($line as $col_value) {
                   echo "\t\t<td>$col_value</td>\n";
               }
               $count++;
               echo "\t</tr>\n";
            }

            echo "</table>\n";


        //fermeture connexion avec la BD
        mysql_close($idConnectDB); 
    }

 ?>

Upvotes: 0

Views: 423

Answers (4)

Dan Soap
Dan Soap

Reputation: 10248

This line

$query = "SELECT * FROM `poisson` WHERE `couleur` LIKE '\"$couleur\"'";

would actually search for the value "rouge", instead of the correct value rouge.

Change it to

$query = "SELECT * FROM `poisson` WHERE `couleur` LIKE '".$couleur."'";

However, this is prone to sql injection attacks. You should either switch to using prepared statements (see PHPs PDO for example) or at least use mysql_real_escape_string:

$query = "SELECT * FROM `poisson` ".
         "WHERE `couleur` LIKE '" . mysql_real_escape_string($couleur)."'";

Upvotes: 1

Fosco
Fosco

Reputation: 38526

I would change the offending line to:

$query = "SELECT * FROM poisson WHERE couleur LIKE '%" . mysql_real_escape_string($couleur) . "%'";

Note that LIKE is a text search... If you want to only pull results where the column is exactly what is in $couleur you should replace LIKE with = and remove the percent signs (wild-card matching.)

Also note that I wrapped the $couleur variable inside the mysql_real_escape_string() function to protect from SQL injection.

Upvotes: 3

Yuck
Yuck

Reputation: 50855

I believe you need to change this line:

$query = "SELECT * FROM `poisson` WHERE `couleur` LIKE '\"$couleur\"'";

to:

$query = "SELECT * FROM `poisson` WHERE `couleur` LIKE '".$couleur."'";

Upvotes: 1

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230346

You don't need extra quotes around $couleur.

Try this one: 

$query = "SELECT * FROM `poisson` WHERE `couleur` LIKE '$couleur'";

Upvotes: 1

Related Questions