Trouble pulling data out of an sql table

Question

I have a html form tat my user can use to search through a table in my MYSQL database. By default if you just hit go it will display the entire table, however I would like them to be able select certain fields and my php form to search via the fields that are filled in.

I seem to be unable to find a way of doing this without writing a seperate query for all 11 inputs in the different combinations they could be entered in, which comes out at a total of 76 queries..

If anyone has a way to simplify this I would love any advice.

I have tried just running a query with the AND operator but that doesnt work as some variables can be left empty and that will return no result, not sure if that is what is upposed to happen, but that is what is happening.

my html and php:

http://jsbin.com/oquwid/1/edit

PHP

$sql = "SELECT * FROM  ".$tbl_name." 
WHERE fname='".$fname."' 
and lname='".$lname."' 
and city='".$city."'
and phone='".$pohne."'
and interest_inet='".$internet."'
and interest_tv='".$television."'
and interest_voice='".$voice."'
and submission_ip='".$ip."'
and inquiry_handled='".$handled."'";
$result = mysql_query($sql);

echo "<table border='1'>";
echo "<tr>";
$i = 0;

while ($i < mysql_num_fields($result))
{
    $meta = mysql_fetch_field($result, $i);   
    echo "<th>".$meta->name."</th>";
    $i++;
}

while ($row = mysql_fetch_row($result))
{
    echo '<tr>';

foreach($row as $item)
{
    echo "<td>".$item."</td>";
}

echo '</tr>';
echo $row;
}

echo "</table>";

Answer 1

You could append parts to the query depending on which are filled in:

if(!empty($fname) || !empty($lname) || !empty($city) || etc.etc.) {
   $sql = "SELECT * FROM  $tbl_name WHERE ";

   $queryParts = array();
   if($fname  != "") { 
      $queryParts[] = " fname='$fname'";
   }
   if($lname != "") {
      $queryParts[] = " lname='$lname'";
   }
   etc.etc.

   $sql .= implode(" AND ", $queryParts);

   // do query, etc.
}
else {
   // Don't do query if no parameters are specified
}

You also need to make sure that you escape all of your query parameters before you use them or risk having someone ravage your data.

The following uses loops to avoid duplicate code:

$fieldIsSpecified = false;
$queryFields = array('fname' => $fname, 'lname' => $lname, 'city' => $city, etc...);
foreach($queryFields as $column => $value) {
    if(!empty($value){
         $fieldIsSpecified = true;
         break;
    }
}
if($fieldIsSpecified) {
   $sql = "SELECT * FROM  $tbl_name WHERE ";

   $queryParts = array();
   foreach($queryFields as $column => $value) {
      if(!empty($value)) {
          $queryParts[] = " $column = '$value'";
      }
   }

   $sql .= implode(" AND ", $queryParts);

   // do query, etc.
}
else {
   // Don't do query if no parameters are specified
}

Answer 2

The reason you're query isn't working if a value is not filled in, is probably because the query results in this (given first name is empty)

SELECT * FROM $tbl_name WHERE fname=''

And there probably isn't a user having no first name.

Further, you considered adding a flag per requested info, and on base of that either add or remove the needed part to the select part of the query ?

For example,

$sql = "SELECT * FROM $tbl_name WHERE "; 
$queryChanged = false;
if (isset($fname)){
    if (!empty($fname)){
        $sql .= "fname='$fname' ";
        $queryChanged=true;
    }
}
if (isset($lname)){
    if (!empty($lname)){
        $sql .= ($queryChanged) ?  " AND lname='$lname'" : "lname='$lname'";
        $queryChanged = true;
    }
}
... //Continue the logic

I'd recommend you to read this post about select * as well as this about user input and how to handle it

Answer 3

this is how i am going to have to do it

php:`

//if just lname is set
if(empty($start_date) && empty($end_date) && empty($fname) && isset($lname) && empty($city) &&
 empty($internet) && empty($television) && empty($voice) && empty($phone) && empty($ip) && 
 empty($handled) && empty($not_handled)){ 
$sql = "SELECT * FROM  ".$tbl_name."
WHERE lname='".$lname."'";
$result = mysql_query($sql);
echo "<table border='1'>";
echo "<tr>";
$i = 0;
while ($i < mysql_num_fields($result))
{
$meta = mysql_fetch_field($result, $i);   
echo "<th>".$meta->name."</th>";
$i++;
}
while ($row = mysql_fetch_row($result))
{
echo '<tr>';
foreach($row as $item)
{
    echo "<td>".$item."</td>";
}   
echo '</tr>';
}
echo "</table>";
exit();
}


//if just city is selected
if(empty($start_date) && empty($end_date) && empty($fname) && empty($lname) && isset($city) &&
 empty($internet) && empty($television) && empty($voice) && empty($phone) && empty($ip) && 
 empty($handled) && empty($not_handled)){ 
$sql = "SELECT * FROM  ".$tbl_name."
WHERE city='".$city."'";
$result = mysql_query($sql);
echo "<table border='1'>";
echo "<tr>";
$i = 0;
while ($i < mysql_num_fields($result))
{
    $meta = mysql_fetch_field($result, $i);   
    echo "<th>".$meta->name."</th>";
$i++;
}
while ($row = mysql_fetch_row($result))
{
    echo '<tr>';
foreach($row as $item)
{
    echo "<td>".$item."</td>";
}   
echo '</tr>';
}
echo "</table>";
exit();
}

And etc... i am going to have to repeat this process until i cover all, 76 i believe, possibilites. thnkfully its just a lot of copy paste. thanks for the help everyone

Answer 4

First don't use MYSQL_*. Use PDO

Second, with your code, your are requiring all fields to be filled.

If you don't wanna do that then go this way:

You can use WHERE 1=1 , but it's not recommended !!!!!

 $sql = "SELECT * FROM  ".$tbl_name." WHERE confirm = '0' ";

 $sql .= "AND fname = ".$fname."";
 $sql .= "AND lname = ".$lname."";
 $sql .= "AND city = ".$city."";
 $sql .= "AND phone = ".$pohne."";
 $sql .= "ORDER BY date DESC";
 $result = mysql_query($sql);

 echo "<table border='1'>";
 echo "<tr>";
 $i = 0;

 while ($i < mysql_num_fields($result))
 {
 $meta = mysql_fetch_field($result, $i);   
 echo "<th>".$meta->name."</th>";
 $i++;
 }

 while ($row = mysql_fetch_row($result))
  {
  echo '<tr>';

  foreach($row as $item)
  {
  echo "<td>".$item."</td>";
   }

   echo '</tr>';
   echo $row;
     }

   echo "</table>";