Returnign NULL values from a form based query to a MySQL db

[cowbell]

Hi folks,
Sorry to be such a pest only days after my last glitch (hard to believe I’ve been writing code for 10 years, must be old age catching up on me at last) was resolved so masterfully on thus forum.

I have a simple form that queries a MySQL db…

<form name="form1" method="POST" action="qresults.php"> 
  <p>Instrument: 
    <input name="instrument" type="text" id="instrument">
  <p>

    <select name="county" id="county">
      <option value="" selected>All 
      <option value="county1">county1 
      <option value="county2">county2 
         </select>
<p>Instrument:  
    <select name="instrument" id="instrument">
    <option value="" selected>All</option>
      <option value="Guitar">Guitar</option>
      <option value="Bass">Bass</option>
      <option value="Drums">Drums</option>
       </select>
  </p>
Rest of form here….

…to allow a user search by county for a musician who plays a particular instrument to join their band.

Thanks to some very helpful folks here a few days ago, I managed to get a query working that will return a result if the user selects one of the ‘real’ options.

My problem now is what to do if the user selects ‘All’ as an option. In other words they want to see all the musicians in all the counties or all the guitarists in all the counties and so on.

My PHP on the existing qresults.php page that works is:

$sql =
    "SELECT *
         FROM $table_name WHERE county='" . $_POST['county'] . "' AND instrument='" . $_POST['instrument'] . "'";

So, how do I expand this to allow for NULL values?

It should be noted here that in the above example I’m using 2 fields (county and instrument), when I eventually get this working I’ll be using around 5 more fields (e.g. style, songwriter, etc) but for the purposes of keeping it uncluttered here I chopped it down to 2. From my old VB days I tried getting this to work with If, Then, Else statements but realised that by the time I had the remaining fields included I’d have a heap of unwieldy code to deal with.

Again, I truly appreciate the time people take with the brain dead like me.
Many thanks!!

Michael.

 

[jpadie]

from memory, html doesn't like a mixture of values and absent values. if no values are present in any option tag then html will assume that the text strigns equate to the values. i would therefore include a value of "All" for the all option.

then, as you say, assemble the sql string using if statements or switch.

i.e.

switch ($_POST['instrument'])
{
  case "All":
   $instrumentwhere = "";
   break;
  case "Guitar":
   $instrumentwhere = " instrument='Guitar' ";
   break;
  case "Bass":
   etc etc
   break;
}
//DO THE SAME FOR COUNTY AS WELL (CALLED $countywhere)

//then assemble the where clause
if (!empty($countywhere)) { $op = " AND ";}else{$op = "";}
if (empth($countywhere) && empty($intrumentwhere)) {$start = "";} else {$start=" WHERE ";}
$where = " $start $countywhere $op $instrumentwhere "

//now assemble the sql
$sql = "Select * from $table $where";

//then do the query
$results = mysql_query($sql);

 

[cowbell]

I figured there may be a way of getting the query to recognise that if there were a NULL value or even a value = All then it would treat it the same as SELECT * and ignore the preceding criteria.

 

[jpadie]

that's what my script does (although it relies on the value "All". you could achieve the same end with no value by changing the case "All": bit to default: instead and placing that segment at the end of the switch statement. Less pure though...

 

[cowbell]

but I do have to name each case meaning that if the end user adds a new instrument to the form it will mean altering the PHP script?

 

[jpadie]

not really - you could insert an "all" VALUE to the option and do something like:

if ($_POST['instrument'] === "All")
{
  $instrumentwhere= "";
}
else 
{
  $instrumentwhere = $_POST['instrument'];
}

 

[cowbell]

you're a star and a half! Many thanks again.