in php mysql query will not combine to include all fields in table

Question

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 display that author with that genre and so on. The idea I had was to replace the author name with a variable,so instead 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' ";
}    
            '$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>

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Sep 28 '16 at 10:12
Simply indenting the code sensibly should now **SHOUT AT YOU** where your errors are. — RiggsFolly, Sep 28 '16 at 10:15
Hi RiggsFolly,Many thanks for the advice on PDO and mysqli,being new to php this is great advice.Also in your edit I keep getting the message...Parse error: syntax error, unexpected '=' in C:\xampp\htdocs\stackedit.php on line 69,any help on that appreciated.kind regards albert — simmalbert, Sep 28 '16 at 20:47

score 1 · Answer 1 · edited Sep 28 '16 at 11:02

$cond = "SELECT * FROM books where 1";

if (isset($bird))
{
  $cond .= "author = '$bird'";
}
if(!is_null($author)){

 $cond .= " AND author = $author";
}

if (isset($cat))
{
  $cond .= " and genre = '$cat' ";

}

if(!is_null($genre)){

   $cond .= "  AND genre = $genre";
}

and so on.....

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

Now my suggestion is

Please use mysqli_*()
Also take precaution for sql injection.

in php mysql query will not combine to include all fields in table

1 Answers1