Querying MySQL with PHP and javascript drop down menu

Question

I've looked all over for a few days now, but haven't found a solution to my problem. I'm writing some PHP to query from a MySQL database that I have setup on my WAMP server. I'm also learning PHP and HTML javascript as I go, so the syntax of both languages is still a little unfamiliar to me.

My goal is to have a drop down selector box is written in Java that allows the user to chose a filter to apply to the select query, something like this:

SELECT * from exampletable WHERE header = "selected_option"

Where 'exampletable' is the table existing in the SQL database, 'header' is a column within that table, and 'selected option' is the user's choice from the drop-down.

I have tried writing various HTML forms with actions that call the PHP file that contains the SQL query using the $_POST superglobal, but nothing seems to work. Any suggestions and examples of solutions would be amazing.

Thanks!

index.php (index.php is the front end with the user interface)

<!DOCTYPE HTML>
<html>
<form action="search.php" method="post">
    <select name="family">
            <option value="" selected="selected">Any family</option>
                <option value="capacitory">capacitor</option>
                <option value="resistor">resistor</option>
                <option value="ferrite bead">ferrite bead</option>
    </select>
    <input name="search" type="submit" value="Search>
</form>
</html>

search.php (search.php receives the selected option value and passes it into the MySQL query)

<!DOCTYPE HTML>
<html>
<head>
<style>
table {
    width: 100%;
    border-collapse: collapse;
}

table, td, th {
    border: 1px solid black;
    padding: 5px;
}

th {text-align: left;}
</style>
</head>

<body>
<?php

$con = mysqli_connect('localhost','root','kelly188','mysql');
mysqli_select_db($con,"testv2");

$varfam = $_POST['family'];

$query = "SELECT * FROM testv2 WHERE (family = $varfam)";

$result = mysqli_query($query);

if($result)
{
while ($row=mysql_fetch_array($result)){
echo "<tr>";
echo "<td>".$row['family']."</td>";
}
} else {
die(mysqli_error());
}
?>
</body>
</html>

You have provided too little code for us to go on to help where you are specifically stuck. Otherwise, this would be a very broad question, and off-topic. — IncredibleHat
– IncredibleHat, Commented Jun 27, 2018 at 16:26
"but nothing seems to work" What exactly did you try? What was the expected/desired result? What was the actual result? If you got errors, what were the exact errors? What debugging have you already done? — Patrick Q
– Patrick Q, Commented Jun 27, 2018 at 16:28
well for starters $result = mysqli_query($query); needs 2 params the first being the connection then you have to get the results what you have done will only output the query not the results of said query -- edit: sorry i just saw you are using results ignore last part — Avi Teller
– Avi Teller, Commented Jun 27, 2018 at 16:37
Also, you're mixing mysqli_ and the deprecated/removed mysql_ functions. Further, you should look into using prepared statements and bound parameters to fix the fact that your text values need to be quoted in the query. — Patrick Q
– Patrick Q, Commented Jun 27, 2018 at 16:42

Ralph Ritoch · Accepted Answer · 2018-06-27 16:59:50Z

3

You should use a prepared statement to prevent SQL injection. The mysql_fetch_array function has been removed from recent versions of PHP. Something more like the following would be more ideal.

if ($stmt = $con->prepare("SELECT * FROM testv2 WHERE (family = ?)")) {

    $stmt->bind_param("s",  $_POST['family']);
    $stmt->execute();

    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {

        echo "<tr>";
        echo "<td>".htmlentities($row['family'])."</td>";
        echo "</tr>";

    }   

    $stmt->close();
}

See PHP documentation: http://php.net/manual/en/mysqli.prepare.php

edited Jun 27, 2018 at 16:59

answered Jun 27, 2018 at 16:53

Ralph Ritoch

3,50030 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IncredibleHat Over a year ago

Much better advice than simply saying to put quotes around a variable.

Jonny1998 Over a year ago

Thanks for the suggestion, I really don't think I need to be concerned about injection however, as my script will never be run on a server with public access and the MySQL databse I'm querying from doesn't contain any sensitive information. I'm storing inventory information that can be found on a number of websites

AraByte · Accepted Answer · 2018-06-27 21:07:51Z

1

index.php

<form action="search.php" method="post">
<select name="family">
            <option value="" selected="selected">Any family</option>
            <option value="capacitory">capacitor</option>
            <option value="resistor">resistor</option>
            <option value="ferrite bead">ferrite bead</option>
</select>
<input name="search" type="submit" value="Search"/>
</form>

search.php

<?php
//////////////////////////////////
// Connect to database using PDO
$servername = "localhost"; 
$username = "test";
$password = "";
$dbname = "test_db";
$db_conn = new PDO("mysql:host=$servername;dbname=$dbname", $username,$password);
// End of database connection
////////////////////////////////////

if(isset($_POST['search']))
{
$family = $_POST['family'];
if(empty($_POST['family']))
{
$stmt = $db_conn->prepare("SELECT * FROM testv2");
$stmt->execute();
//we get the data
while($data = $stmt->fetch(PDO::FETCH_ASSOC))
{
echo $data['family'];
echo "<hr>";    
}   
}
else
{   
$stmt = $db_conn->prepare("SELECT * FROM testv2 WHERE family = :family");
$stmt ->bindParam(':family', $family);
$stmt->execute();
//we get the data
while($data = $stmt->fetch(PDO::FETCH_ASSOC))
{
echo $data['family'];
echo "<hr>";    
}
}
}
?>

edited Jun 27, 2018 at 21:07

answered Jun 27, 2018 at 17:00

AraByte

1549 bronze badges

9 Comments

Jonny1998 Over a year ago

Thanks for your detailed response, I have tried implementing it with my database info. I believe the connection works, but I'm simply not seeing any results from selecting one of the options in the drop down. I do apologize if I'm being obtuse and missing something fundamental.

AraByte Over a year ago

else, make sure the word inside $data['']; (in this case it's family) is a valid column name from the table testv2

AraByte Over a year ago

That's exactly what the code does, displaying all related "family" upon selection and submission, any other details or screenshot of the db ? maybe i'm missing something. you can add : $rows = $stmt->rowCount(); below $stmt->execute(); and then : echo $rows; this would return the number of related rows, we may get something out of this

AraByte Over a year ago

That's strange, you're sure the table name is testv2 as well ? it should at least display all data from that table if it's not getting the $_POST['family'] input

AraByte Over a year ago

We're all learning my brother, i just created the same exact table (with random values) and it's working perfectly ! you can use the edited search.php, just make sure you're using the right database connection details. IT'll works hopefully :)

|

Collectives™ on Stack Overflow

Querying MySQL with PHP and javascript drop down menu

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related