[SOLVED] SQL SELECT using variable unless variable is empty

[fireice87]

Hey

Iv got a form where users input details that are then used as search criteria and it all works fine as long as all the details are enterd.

But if a variable is empty then this blank space is used as search criteria with doesnt match any records and thus creates no results.

 

Below is my code what i would like help with is modifing it so the SQL ignores the clauses that use empty variables.

<form id="Criteria" name="Criteria" method="post" action="gigs.php" >
<table width="244" height="102" border="0" cellspacing="1">
          <tr>
            <td width="67" height="24">Genre:    
              </td>
            <td width="170"> <select name="Genre" tabindex="3">
       <option value="All">All</option>
  <option value="Rock">Rock</option>
      <option value="Pop">Pop</option>
      <option value="Metal">Metal</option>
  <option value="Punk">Punk</option>
      <option value="Indie">Indie</option>
      <option value="RnB">RnB</option>
      <option value="Dance">Dance</option>
      <option value="Electronica">Electronica</option>
      <option value="Hip-Hop">Hip-Hop</option>
      <option value="Rap">Rap</option>
    </select></td></tr> <tr>
<td width="67">City/Town</td>
          <td width="170" ><input type="text" name="City" tabindex="4" /></td>
          </tr>
          <tr>
            <td>Date From: </td>
            <td><input type="text" name="DateF" value="dd/mm/yyyy" onfocus="this.select();lcs(this)" onclick="event.cancelBubble=true;this.select();lcs(this)">
            </td>    </tr>
		<td>Date To: </td>
            <td><input type="text" name="DateT" value="dd/mm/yyyy" onfocus="this.select();lcs(this)" onclick="event.cancelBubble=true;this.select();lcs(this)">
		</td>
      </tr> <tr> 	
		<td><input type="submit" name="Search" value="Search" tabindex="3" /></td>

The date is pulled from a javascript calander and then re formatted to match the dates stored in the database

Below is the reformating and the SQL search statement

<?php	  
 $DateTemp = substr($DateF, 6, 7)."-".substr($DateF, 3, 3). substr($DateF, 0, 2);
 $DateFS = str_replace("/", "-", $DateTemp );
 $DateTemp = substr($DateT, 6, 7)."-".substr($DateT, 3, 3). substr($DateT, 0, 2);
 $DateTS = str_replace("/", "-", $DateTemp );
 $City = 
    $sql = "Select * FROM giglist WHERE genre = '$Genre' AND location = '$City' AND date BETWEEN '$DateFS' AND '$DateTS' ORDER BY date"; ?>

Iv tried placing if else rules in to the SQL statment but to no success any help would be greatly appreciated

Thanks

 

 

[Barand]

If a value is left empty, say $city, then don't put "location='$city' " in the where clause.

 

I use this method

http://www.phpfreaks.com/forums/index.php/topic,167302.msg737240.html#msg737240

[fireice87]

Hy

iv looked at that example and attempted something smiliar its allmost there as when i leave the city field empty it displays all but when ever i type anything in to the city field it displays the or die error.

 

this is my new code to try and create the sql statment dependent on the city filed

<?php
if (empty($City)) 
{
$CitySQL = "";
}else
{
$CitySQL = "AND location = $City";
}
    $sql = "Select * FROM giglist WHERE genre = '$Genre' $CitySQL AND date BETWEEN '$DateFS' AND '$DateTS' ORDER BY date"; 
?>

[revraz]

Maybe this

<?php
if (empty($City)) 
{
$CitySQL = "";
}else
{
$CitySQL = "location = '$City'";
}
    $sql = "Select * FROM giglist WHERE genre = '$Genre', '$CitySQL' AND date BETWEEN '$DateFS' AND '$DateTS' ORDER BY date"; 
?>

[fireice87]

Hey thanks for the quick reply 

with the code formatted that way it errors out to the or die statement with the input empty or with a correct or incorrect value.

i think its the ', ' causing that so tried taking that out but leaving in the single quotes but goes back to how it was before.

displays all results if nothing is enterd but errors to the or die statment if the input field has a value

 

i think this is along the right lines though the formatting just isnt quite right

[roopurt18]

<?php
$CitySQL = '';
if (!empty($City)){
  $CitySQL = " AND location = '{$City}'";
}
$sql = "
  SELECT * 
  FROM `giglist` 
  WHERE `genre`='{$Genre}' 
  {$CitySQL} 
  AND `date` BETWEEN '{$DateFS}' AND '{$DateTS}' 
  ORDER BY `date`
"; 
?>

 

You had one area with improper single quotes in the query. You also have a column named date, which is a MySQL reserved word; it's best to enclose your column and table names in back ticks to avoid table or column name collisions with MySQL keywords.

[fireice87]

Thankyou very much for your help people that works perfectly roopurt18

 

[roopurt18]

You should note that you aren't cleaning any of your data before using it in the database which is how you get SQL injection attacks.  Also, if you have only a couple fields to search across the way I typed that out is fine; however if you have several of them you can condense the code using arrays and loops, which also makes it easier to maintain later.