dynamic mySQL select statment

[jardane]

I have a program where i have my text box's where the user can enter search terms. The user is only required to enter at least one but has the option to enter up to 14. I tried this:

$q = "SELECT `name`, `site_address`, `site_city`, `state`, `zip_code`, `telephone`, `mailing_address`, `transporter`, `desginated_facility`, `us_dot`, `quantity`, `wast_code`,`offerers_name`, `manifests` FROM `companies` WHERE " . $search;

I  build $search to include what i want searched for depending on what the user wants to search for. But this is not working and i know for sure that it's the select statement. Can someone give me a hand or tell me another way of doing this?

[the182guy]

echo out the SQL statement and post it here so we can see whats going wrong. You'll need to post the code that builds the $search aswell, otherwise nobody is going to be able to help.

[jardane]

The echo of the select statement is:

SELECT `name`, `site_address`, `site_city`, `state`, `zip_code`, `telephone`, `mailing_address`, `transporter`, `desginated_facility`, `us_dot`, `quantity`, `wast_code`,`offerers_name`, `manifests` FROM `companies` WHERE quantity='$qu' 

 

$search is made with this script:

for ($i = 0; $i < $count; $i++) {
		$search = $search . $find[$i];
	}

 

And the $find array is set using:

if (!empty($_POST['quantity'])) {
	if ($count == 0){
		$find[$count] = " quantity='\$qu'";
		$qu = mysqli_real_escape_string($dbc, trim($_POST['quantity']));
		$count ++;
			}else{
			$find[$count] = " AND quantity='\$qu'";
			$qu = mysqli_real_escape_string($dbc, trim($_POST['quantity']));
			$count ++;
	}
}

There is one of these for each search option.

[DavidAM]

Your SELECT statement contains the literal '$qu' NOT the value of the variable named $qu.  It looks like you are trying to bind the PHP variable to the SELECT statement.  I don't do binding (don't even know how or if it is possible).  Change that way you setup your $find array to put the value into the string:

 

if (!empty($_POST['quantity'])) {
  $qu = mysqli_real_escape_string($dbc, trim($_POST['quantity']));
  if ($count == 0){
    $find[$count] = " quantity='" . $qu . "'";
    $count ++;
  }else{
    $find[$count] = " AND quantity='" . $qu . "'";
    $count ++;
  }
}

 

[the182guy]

The first problem is you have put a backslash before the $qu variable, the result of this is literally '$qu' gets inserted into the SQL statement, intead of the value of $qu

 

Second problem is it looks like you haven't set $qu to the value of the posted variable here (on both lines)

$find[$count] = " quantity='\$qu'";

 

Move the $qu =  line to above the $find[$count] line, if you don't then $qu will not be set to the value of the posted quantity

[jardane]

DavidAM your code worked great thanks a ton!!!