PHP form field crashing query if NULL

[galahad2]

Hi, for some reason a field in a PHP form I'm using is causing my mySQL query to crash with a syntax error if the user ignores the field altogether when doing a search i.e if the field is left as null.

We need the query to work even if the field isn't filled in by the user. The field is the '_Location' field in the code below.

<?php
//include ('manager/special_offers/inc/dbconnect.php'); 
echo '<h2>Search for Special Offers</h2><br>'; 
//Include the PS_Pagination class 
//include('manager/special_offers/inc/ps_pagination.php'); 
//Connect to mysql db 
$show_form=true; //used later on
if(isset($_POST['sortform'])) {
  $conn = mysql_connect('localhost', 'login', 'password'); 
  mysql_select_db('specialoffers_db',$conn); 
  
  $yoursafe_post_location = $_POST['_Location']; 
  $yoursafe_post_price1 = $_POST['_Price1']; 
  $yoursafe_post_price2 = $_POST['_Price2']; 
  $yoursafe_post_category = $_POST['_Category']; 
  $yoursafe_post_company = $_POST['_Company']; 
/* check variables being set */ 
//echo '<p>Values:</p>'; 
//echo 'Location: ' . $yoursafe_post_location . '<br />'; 
//echo 'Price1: ' . $yoursafe_post_price1. '<br />'; 
//echo 'Price2: ' . $yoursafe_post_price2. '<br />'; 
//echo 'Category: ' . $yoursafe_post_category. '<br />'; 
  
  $query = "SELECT * FROM specialofferstable WHERE "; 
  $and = "0"; 
  
  if(trim($yoursafe_post_location)!= '') { 
  $and = "0"; 
  $query .= "location LIKE '%$yoursafe_post_location%'"; 
  } 
  
  if(trim($yoursafe_post_price1)!= '' && trim($yoursafe_post_price2)!= '') { 
  $and = "1"; 
  if(trim($and)!= '') {$query .= " AND ";} 
  $query .= "price BETWEEN $yoursafe_post_price1 AND $yoursafe_post_price2"; 
  } 
  
  if(trim($yoursafe_post_category)!= '') { 
  $and = "1"; 
  if(trim($and)!= '') {$query .= " AND ";} 
  $query .= "category LIKE '$yoursafe_post_category'"; 
  } 
if(trim($yoursafe_post_company)!= '') { 
  $and = "1"; 
  if(trim($and)!= '') {$query .= " AND ";} 
  $query .= "company_hotel LIKE '%$yoursafe_post_company%'"; 
  } 
  
  $query .= " ORDER BY price"; 

The mySQL error it causes is (for example):

Could not execute query:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND price BETWEEN 0 AND 80 AND category LIKE 'hotel' AND company_hotel LIKE '%Co' at line 1

Any ideas how we can fix this?

[TheMadScientist]

It looks like the AND is the problem...
The quickest, easiest fix is probably something like this:

$query = "SELECT * FROM specialofferstable WHERE ";
$and = "";

if(trim($yoursafe_post_location)!= '') {
$and = "1";
$query .= "location LIKE '%$yoursafe_post_location%'";
}

if(trim($yoursafe_post_price1)!= '' && trim($yoursafe_post_price2)!= '') {
if(trim($and)!= '') {$query .= " AND ";}
$query .= "price BETWEEN $yoursafe_post_price1 AND $yoursafe_post_price2";
$and = "1";
}

if(trim($yoursafe_post_category)!= '') {
if(trim($and)!= '') {$query .= " AND ";}
$query .= "category LIKE '$yoursafe_post_category'";
$and = "1";
}

if(trim($yoursafe_post_company)!= '') {
if(trim($and)!= '') {$query .= " AND ";}
$query .= "company_hotel LIKE '%$yoursafe_post_company%'";
}

$query .= " ORDER BY price";