mysql query not working with drop down boxes

lindisfarne · September 28, 2016

Hi all,

The idea of this project was to create a sql query that would be created in relation to which item was selected in the drop down boxes.So for example If I clicked on an authors name,I would get up all the authors records,then if I wanted the authors+genre.I would select the author from the author drop down and the genre from the genre dropdown and so on.
The idea I had was to replace the author name with a variable,so insteda of the query reading:
$sql = "SELECT * FROM books WHERE author = 'Ken Davies'

I replaced the name with the variable $bird which in turn was already declared as author here
$bird = ( ! empty($_POST['author'])) ? $_POST['author'] : null;

But the code is only partly working.Each box works individually,but they wont combine.
So if I select author Ken Davies then I select genre,I get the genre up and all of the author not just ken davies and his genre.
Can anyone see what I am doing wrong,any help much appreciated,I have placed all the code on here to look at.

<html>
<head>
<title>My Page</title>
</head>
<body>
<br>
<form name="myform" action="authors3.php" method="POST">

<select name="author" size="2">
<option value="ken davies">ken davies</option>
<option value= "arthur smith">arthur smith</option>
<option value="gill rafferty">gill rafferty</option><br />
<option value="molly brown">molly brown</option><br />
<option value="gilbert riley">gilbert riley</option><br />
<input type = "submit" name = "submit" value = "go">

<select name="genre" size="4">
<option value="adventure">adventure</option>
<option value="biography">biography</option>
<option value="crime">crime</option><br />
<option value="romance">romance</option>
<option value="thriller">thriller</option>

<input type = "submit" name = "submit" value = "go">
<select name="year" size="4">
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
<option value="2005">2005</option>
<option value="2006">2006</option>
<option value="2007">2007</option>
<option value="2008">2008</option>

<input type = "submit" name = "submit" value = "go">

<select name="publisher" size="4">
<option value="blue parrot">blue parrot</option>
<option value="yonkers">yonkers</option>
<option value="zoot">zoot</option>

<input type = "submit" name = "submit" value = "go">

<?php

#variables created and tested
$bird = ( ! empty($_POST['author'])) ? $_POST['author'] : null;
$cat = ( ! empty($_POST['genre'])) ? $_POST['genre'] : null;
$mouse = ( ! empty($_POST['year'])) ? $_POST['year'] : null;
$goat = ( ! empty($_POST['publisher'])) ? $_POST['publisher'] : null;

$con = mysql_connect("localhost","root","");
If (!$con){
   die("Can not Connect with database" . mysql_error());
}
Mysql_select_db("authors",$con);

if (isset($bird) && isset($cat) && isset($mouse) && isset($goat))
{
$sql = "SELECT * FROM books WHERE author = '$bird' AND genre = '$cat' AND year= '$mouse' AND publisher = '$goat' ";}


else if (isset($bird))
{
$sql = "SELECT * FROM books WHERE author = '$bird' ";
}

if(!is_null($author)){

$sql.="AND author = $author";
}
else if (isset($cat))
{
$sql = "SELECT * FROM books WHERE genre = '$cat' ";

}

if(!is_null($genre)){

   $sql.="AND genre = $genre";
}
else if (isset($mouse))
{
$sql = "SELECT * FROM books WHERE year = '$mouse' ";
}

if(!is_null($year)){

   $sql.="AND year = $year";
}
else if (isset($goat))
{
$sql = "SELECT * FROM books WHERE publisher = '$goat' ";
}
if(!is_null($publisher)){

   $sql.="AND publisher = $publisher";
}

$myData = mysql_query($sql,$con);

echo"<table border=3>

<tr>
<th>id</th>
<th>author</th>
<th>title</th>
<th>publisher</th>
<th>year</th>
<th>genre</th>
<th>sold</th>
</tr>";

while($record = mysql_fetch_array($myData)){
   echo "<tr>";
   echo "<td>" . $record['id'] . "</td>";
   echo "<td>" . $record['author'] . "</td>";
   echo "<td>" . $record['title'] . "</td>";
   echo "<td>" . $record['publisher'] . "</td>";
   echo "<td>" . $record['year'] . "</td>";
   echo "<td>" . $record['genre'] . "</td>";
   echo "<td>" . $record['sold'] . "</td>";

   echo "<tr />";
}
echo "</table>";

mysql_close($con);

?>
note: all four are working individually<br />
    not working when combined<br />


</form>
</body>
</html>

Edited September 28, 2016 by lindisfarne

mac_gyver · September 28, 2016

your form should use method='get' since you are determining what will be displayed on the page. also, don't use nonsense variable names like $bird, $cat, ... and in fact, if you aren't doing anything to the value in a variable, there's no good reason to copy one variable to another, just use the original variable.

next, when you are doing the same processing/operation on a set of data (your series of drop-down select menus), don't write out every possible combination of values. just make a list (array) that defines the expected inputs, then loop over this list to access the input data. you would produce and store each column = 'value' and store them in an array. then, just implode the array with the ' AND ' keyword to produce the WHERE clause in the query (implode will work even if there is only one entry.)

you would also want to dynamically produce the select/option menus, so that you can make the selection 'sticky' so that a user doesn't need to keep re-picking things they have already selected. you would do this by making the choices a list/array, then just loop over the list when you produce each select/option menu, outputting the 'selected' attribute in any existing selected option. you would also use these defining lists/arrays to validate the input data. if your list of possible choices are actually coming from a database table, you would query for this data to produce the select/option menu, rather than have them in a hard-coded array.

lastly, you should use a prepared query, with place-holders for each data value. when you switch your code from the obsolete mysql statements to use PDO (the best choice) or msyqli, you would also convert the query to a prepared query.

if i have some time, i will post an example.

edit: you should also separate the concerns in your code. the majority of the php logic should come first, then at the end have your html document/template that has a minimum of php logic in it. this will help make it easier to switch from the obsolete mysql statements, since the php logic using them will be grouped together and not be interspersed in the html markup for your document.

Edited September 28, 2016 by mac_gyver

cyberRobot · September 28, 2016

With the following code, $bird, $cat, $mouse, and $goat will always contain a value. They will be set to whatever was selected in the form or NULL:

$bird  = ( ! empty($_POST['author']))    ? $_POST['author']    : null;
$cat   = ( ! empty($_POST['genre']))     ? $_POST['genre']     : null;
$mouse = ( ! empty($_POST['year']))      ? $_POST['year']      : null;
$goat  = ( ! empty($_POST['publisher'])) ? $_POST['publisher'] : null;

So if the publisher field, for example is blank, $goat will be set to NULL. And your query will look something like this:

$sql = "SELECT *

FROM books

WHERE

author = 'ken davies'

AND genre = 'biography'

AND year = '2007'

AND publisher = 'null' ";

Unless you have a database record that looks like that (including the "null"), the query will not return anything. Instead you could try something like this

$whereClause = array();
if(!empty($_POST['author']))    { $whereClause[] = "author='"    . mysql_real_escape_string($_POST['author'])    . "'"; }
if(!empty($_POST['genre']))     { $whereClause[] = "genre='"     . mysql_real_escape_string($_POST['genre'])     . "'"; }
if(!empty($_POST['year']))      { $whereClause[] = "year='"      . mysql_real_escape_string($_POST['year'])      . "'"; }
if(!empty($_POST['publisher'])) { $whereClause[] = "publisher='" . mysql_real_escape_string($_POST['publisher']) . "'"; }

And then the query would be modified to the following:

$sql = "SELECT * FROM books WHERE " . implode(' AND ', $whereClause);

Note that the code is untested. But the query should only search fields where the user selected something in the dropdown.

Edited September 28, 2016 by cyberRobot

mac_gyver · September 28, 2016

promised example code -

// define, or get from an sql query, the choices for the select/option menus
$author_data = array();
$author_data[] ="ken davies";
$author_data[] = "arthur smith";
$author_data[] ="gill rafferty";
$author_data[] ="molly brown";
$author_data[] ="gilbert riley";

// define and populate the data for the other select/option menus. left up to you as a programming exercise.
$genre_data = array();

$year_data = range(2002,2008);

$publisher_data = array();


// define a list/array of expected inputs for the dynamic WHERE clause
$choices = array();
$choices[] = 'author';
$choices[] = 'genre';
$choices[] = 'year';
$choices[] = 'publisher';

// produce the dynamic WHERE clause
$and_terms = array(); // holds each term for the WHERE clause

// loop over the defining array of choices
foreach($choices as $choice)
{
    if(isset($_GET[$choice]))
    {
        // the choice is set, add it to the terms to use
        $and_terms[] = "`$choice` = '$_GET[$choice]'"; // you need to either - validate that the input value is exactly one of the possible choices, properly escape the value using the database escape string function, or use a prepared query to supply the value to the sql statement. left up to you as a programming exercise.
    }
}

// db  connection - convert to PDO and use exceptions to handle errors. left up to you as a programming exercise.
$con = mysql_connect("localhost","root","");
If (!$con){
    die("Can not Connect with database" .  mysql_error());
}
mysql_select_db("authors",$con);


$where_clause = !empty($and_terms) ? 'WHERE ' . implode(' AND ', $and_terms) : '';

// build the sql query statement
$sql = "SELECT * FROM books $where_clause";
    
// execute the query and retrieve the data into an array
$result_data = array();

$myData = mysql_query($sql,$con);
while($row = mysql_fetch_assoc($myData))
{
    $result_data[] = $row;
}

// start the html document/template 
?>
<html>
<head>
<title>My Page</title>
</head>
<body>
<br>
<form>
<select name="author" size="2">
<?php
foreach($author_data as $value)
{
    $sel = isset($_GET['author']) && $_GET['author'] == $value ? ' selected': '';
    echo "<option value='$value'$sel>$value</option>\n"; // you need to apply htmlentities() to any data being output. left up to you as a programming exercise.
}
?>
</select>
<input type = "submit" value = "go">

<select name="genre" size="4">
<?php
// use code similar to the above to produce each set of select/option choices. left up to you as a programming exercise.
?>
<option value="adventure">adventure</option>
<option value="biography">biography</option>
<option value="crime">crime</option><br />
<option value="romance">romance</option>
<option value="thriller">thriller</option>
</select>
<input type = "submit" value = "go">

<select name="year" size="4">
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
<option value="2005">2005</option>
<option value="2006">2006</option>
<option value="2007">2007</option>
<option value="2008">2008</option>                                      
</select>
<input type = "submit" value = "go">

<select name="publisher" size="4">
<option value="blue parrot">blue parrot</option>
<option value="yonkers">yonkers</option>
<option value="zoot">zoot</option>
</select>
<input type = "submit" value = "go">
</form>

<?php
// you need to use css to style your table border. left up to you as a programming exercise.
echo"<table border='3'>
<tr>
<th>id</th>
<th>author</th>
<th>title</th>
<th>publisher</th>
<th>year</th>
<th>genre</th>
<th>sold</th>
</tr>";

foreach($result_data as $row)
{
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>"; // you need to apply htmlentities() to any data being output. left up to you as a programming exercise.
    echo "<td>" . $row['author'] . "</td>";
    echo "<td>" . $row['title'] . "</td>";
    echo "<td>" . $row['publisher'] . "</td>";
    echo "<td>" . $row['year'] . "</td>";
    echo "<td>" . $row['genre'] . "</td>";
    echo "<td>" . $row['sold'] . "</td>";
    echo "<tr />";
}
echo "</table>";
?>
</body>
</html>

Barand · September 28, 2016

I see your while loop still doesn't calculate a total of the 'sold' column.

Was your previous post

https://forums.phpfreaks.com/topic/302198-summing-up-the-values-in-an-array/

just to waste our time?

lindisfarne · September 29, 2016

Hi mac_gyver and cyber_robot,
I cannot thank you both enough for not only brilliant advice on mysql and PDO but some great code that has has solved the current prtoblem.Many many thanks and very much appreciated :happy-04:

Barand ,thanks for the reply,I haven't included the summing up because I still haven't figured out how to do it.

Many thanks to you all for your help

Barand · September 29, 2016

Barand ,thanks for the reply,I haven't included the summing up because I still haven't figured out how to do it.

It isn't difficult - add three lines of code, as I told you

                    $total = 0;                                    // initialise total

                    while($record = mysql_fetch_array($myData)){
                        echo "<tr>";
                        echo "<td>" . $record['id'] . "</td>";
                        echo "<td>" . $record['author'] . "</td>";
                        echo "<td>" . $record['title'] . "</td>";
                        echo "<td>" . $record['publisher'] . "</td>";
                        echo "<td>" . $record['year'] . "</td>";
                        echo "<td>" . $record['genre'] . "</td>";
                        echo "<td>" . $record['sold'] . "</td>";
                        echo "<tr />";
 
                        $total += $record['sold'];                 // accumulate total
                    }

                    echo "<tr><td colspan='6'>Total:</td><td>$total</td></tr>";        //  output total

                    echo "</table>";

lindisfarne · September 29, 2016

Hi Barand,

Many thanks for your reply,the problems is was that I wanted the total in just one box at the base of the "sold" array.The code you suggested results in a total after each record in the table is displayed.Is there a way I could just get a final total figure at the bottom of the sold column.Please see the result below as it stands. Any suggestions appreciated and thanks for your help.

Kind Regards
Lindisfarne

id 	author 	title 	publisher 	year 	genre 	sold
1	ken davies	xpatrol	zoot	2007	adventure	175000
Total:	175000
2	ken davies	dawn watch	zoot	2007	adventure	260000
Total:	435000
3	ken davies	zero hour	zoot	2008	adventure	279000
Total:	714000
9	ken davies	mr evans	zoot	2007	adventure	155000
Total:	869000
10	ken davies	revenge	zoot	2005	crime	225000
Total:	1094000
11	ken davies	backdrop	blue parrot	2003	crime	175000
Total:	1269000
12	ken davies	firefly	zoot	2004	crime	64000
Total:	1333000
13	ken davies	newstime	zoot	2004	adventure	450000
Total:	1783000
14	ken davies	the department	zoot	2004	crime	275000
Total:	2058000
15	ken davies	switch	zoot	2005	adventure	180000
Total:	2238000

Edited September 29, 2016 by lindisfarne

Barand · September 29, 2016

post the code that produced that output

lindisfarne · September 29, 2016

Hell there Barand

echo"<table border=1>

<tr>
<th>id</th>
<th>author</th>
<th>title</th>
<th>publisher</th>
<th>year</th>
<th>genre</th>
<th>sold</th>
</tr>";
  $total = 0;    

while($record = mysql_fetch_array($myData)){
	echo "<tr>";
	echo "<td>" . $record['id'] . "</td>";
	echo "<td>" . $record['author'] . "</td>";
	echo "<td>" . $record['title'] . "</td>";
	echo "<td>" . $record['publisher'] . "</td>";
	echo "<td>" . $record['year'] . "</td>";
	echo "<td>" . $record['genre'] . "</td>";
	echo "<td>" . $record['sold'] . "</td>";	
	echo "<tr />";
	 $total += $record['sold'];   
	 echo "<tr><td colspan='6'>Total:</td><td>$total</td></tr>";  
	
}
echo "</table>";



mysql_close($con);



?>
 
                                                                                                                                                                                                                                                                                                    

</form>
</body>
</html>

I have reproduced duced the code here,it is the same as my main code above but with your code added. :happy-04:

Barand · September 29, 2016

I spoonfed you code and you still got it wrong ::) Don't give up the day job.

If you look at the code I gave you, the line outputting the code is after the end of the while() loop.

You have include that line within the loop, which is why it keeps repeating.

lindisfarne · September 30, 2016

Aha!! got it,thanks Barand :happy-04: many thanks for that.Being an ancient cobolist all these curly brackets are quite confusing ::)

Edited September 30, 2016 by lindisfarne

Barand · September 30, 2016

COBOL? - can't be many of us left. Although I haven't used it since around 1980.

lindisfarne · September 30, 2016

Hi Barand ::) COBOL Yes! that big bad beautiful language

Sign In

mysql query not working with drop down boxes

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information