Please Help!!!

[phpstudent111]

Hi,

 

Any help would be greatly appreciated as I've been beating my head against the wall with this one.  I have 7 variables and I am trying to populate the WHERE clause of a mysql_query based on whether or not these variables are NULL.  Is this possible?

 

Just something like . (in english)

 

if $myvariable IS NOT NULL

then $newvariable="$myvariable=table1.column"

 

and then repeat that for each variable until the WHERE clause has only the "$myvariable=table1.column" portion for the variables that are not null.  I hope this makes sense, please help, if you need more information please just ask.

 

Thank you!!

 

[ILMV]

if(!is_null($var1) {
   if($where=='') {
      $where=' WHERE field = '.$var1;
   } else {
      $where=' AND field = '.$var1;
   }
}

 

Try something like that, I haven't tested it, but something along those lines should be fine.

[phpstudent111]

Thanks so much, I will definitely try it...I am fairly new to PHP so I have a few questions.  Below is my code for your review...can I stick your code right in the WHERE clause or does it need to be before/after?  Is there any way you can re-write with these variables to make it a bit more clear? Thanks so much for your help.

 

$mymodel=$_POST['mymodel'];

$mymanufacturer=$_POST['mymanufacturer'];

$myyear=$_POST['myyear'];

$mydoors=$_POST['mydoors'];

$mydealercost=$_POST['mydealercost'];

$myretailprice=$_POST['myretailprice'];

$mycolor=$_POST['mycolor'];

$myinteriorcolor=$_POST['myinteriorcolor'];

$query="SELECT car.id

, model.manufacturer

, model.modelname

, car.year

, color.color_name

, interior_color.interior_color

, car.doors

, car.retailprice

, car.dealercost

, transmission_type.transmission_type

FROM car

INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id)

WHERE xxxxxx;

$result=mysql_query($query) or die (mysql_error());

$fields_num=mysql_num_fields($result);

echo "<table border='1'><tr>";

//printing talbe headers

for($i=0; $i<$fields_num; $i++)

{

$field=mysql_fetch_field($result);

echo "<td>{$field->name}</td>";

}

echo "</tr>\n";

//print table rows

while($row=mysql_fetch_row($result))

{

echo "<tr>";

foreach($row as $cell)

echo "<td>$cell</td>";

echo "</tr>\n";

}

mysql_free_result($result);

ob_end_flush();

?>

[AdRock]

$query="SELECT car.id
, model.manufacturer
, model.modelname
, car.year
, color.color_name
, interior_color.interior_color
, car.doors
, car.retailprice
, car.dealercost
, transmission_type.transmission_type
FROM car
INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id) ";

if(!is_null($var1) {
   if($query=='') {
      $query.=' WHERE field = '.$var1;
   } else {
      $query.=' AND field = '.$var1;
   }
}

[ILMV]

You will need to copy the code I have given you once for every field in your where clause... this needs to go before the sql statement.

 

When you execute the sql, just use $where in your statement.

[ILMV]

$query="SELECT car.id
, model.manufacturer
, model.modelname
, car.year
, color.color_name
, interior_color.interior_color
, car.doors
, car.retailprice
, car.dealercost
, transmission_type.transmission_type
FROM car
INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id) ";

if(!is_null($var1) {
   if($query=='') {
      $query.=' WHERE field = '.$var1;
   } else {
      $query.=' AND field = '.$var1;
   }
}

 

Nice try, but that won't work. As it check to see if $query is empty (if it's the first where clause)... but you have already started to construct your $query variable it will look like this...

 

SELECT * FROM table AND name = 'jim'

[phpstudent111]

Thanks so much.  I'm just not sure how your variables relate to mine.

 

Your code:

 

f(!is_null($var1) {

  if($where=='') {

      $where=' WHERE field = '.$var1;

  } else {

      $where=' AND field = '.$var1;

  }

}

 

As I understand it -  I need the WHERE clause to update with each non-null variable - basically if '$mymodel' is not null then I need "$mymodel=model.id" placed in the WHERE clause....then if $mymanufacturer is not null, I need it updated to "$mymodel=model.id AND $mymanufacturer=model.manufacturer_id" - and so on for each clause...can you please explain a little bit more