Mysql select query using value from a php dropdown

Question

I started programming in php and I'm having a small doubt.

I'm trying to do a search the database using a value from a dropdown.

The problem is that the query always uses the last value of the dropdown.

Does anyone can help me find the error?

Why is research in where clause is always the last value of the dropdown?

Code

<tr><td>Technical:</td><td>

 <select>
    <?php
    $query = "SELECT idTechnical, name FROM technicals";
$result2 = mysql_query($query);
$options=""; 
while($row=mysql_fetch_array($result2)){   
    $id=$row["idTechnical"];   
    $thing=$row["name"]; 
     echo "<OPTION VALUE=$id>$thing</option>";
}
    ?>         
</select>

<?php
 if (isset($_POST['Next'])) { 


if($_REQUEST['Next']=='Search') {
    {
        $sql="select idTask, descTask, deadline, idTechnical from tasks where idTechnical  = '$id' order by deadline desc";
    $result=mysql_query($sql);
    $count=mysql_num_rows($result);
    } 
}
 }
?>

I select any value from dropdown, but only uses the last value in clause where :S

enter image description here

Sidenote: You should quote your variables `echo "";` you may get unexpected results. — Funk Forty Niner, Sep 11 '14 at 16:13

score 1 · Answer 1 · answered Sep 11 '14 at 16:33

Here is what I would do for the form (assuming you have a proper form tag with an action attribute that points to the correct PHP script):

<tr>
<td>Technical:</td>
<td>
    <select name="technical">
        <?php
            $query = "SELECT idTechnical, name FROM technicals";
            $result2 = mysql_query($query);
            $options="";
            while($row=mysql_fetch_array($result2)){
                echo '<option value='.$row["idTechnical"].'>
                    '.$row["name"].'
                    </option>';
            }
        ?>
    </select>
</td>

Then in the PHP script:

$sql='SELECT
    idTask,
    descTask,
    deadline,
    idTechnical
    FROM tasks
    WHERE idTechnical  = '.$_REQUEST['technical'].'
    ORDER BY deadline DESC';
$result=mysql_query($sql);
$count=mysql_num_rows($result);

This should do it for you.

But please note: The script above is a security risk because it leaves the door wide open for SQL injection

A better way to do this would be to use a PDO Prepared statement, like this:

$db = new PDO('mysql:host=CHANGE_THIS_TO_YOUR_HOST_NAME;
    dbname=CHANGE_THIS_TO_YOUR_DATABASE',
    'CHANGE_THIS_TO_YOUR_USERNAME',
    'CHANGE_THIS_TO_YOUR_PASSWORD');

$sql='SELECT
    idTask,
    descTask,
    deadline,
    idTechnical
    FROM tasks
    WHERE idTechnical  = :id
    ORDER BY deadline DESC';
$query = $db->prepare($sql);
$query->bindValue(':id', $_REQUEST['technical']);
$query->execute();
$count = $query->rowCount();

If you're just starting in PHP, I would highly recommend that you spend some time to become familiar with PDO Database querying. Good luck and happy coding!

Mysql select query using value from a php dropdown

1 Answers1

Linked