Please help!!!!

phpstudent111 · March 24, 2009

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?

Here is the code I have...

$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 =???;

Just something like . (in english)

if $mymodelIS NOT NULL

then $where="$mymodel=model.id"

and then repeat that for each variable until the WHERE clause has only the "$mymodel=model.id AND $mymanufacturer=model.manufacturer_id AND $myyear=car.year", etc. But Only for the variables that are not null I hope this makes sense, please help, if you need more information please just ask.

rhodesa · March 24, 2009

<?php
$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 = array();
if(!empty($_POST['mymodel']))
  $where[] = sprintf("model.id='%s'",mysql_real_escape_string($_POST['mymodel']));
if(!empty($_POST['mymanufacturer']))
  $where[] = sprintf("model.manufacturer='%s'",mysql_real_escape_string($_POST['mymanufacturer']));
if(!empty($_POST['myyear']))
  $where[] = sprintf("car.year='%s'",mysql_real_escape_string($_POST['myyear']));
if(!empty($_POST['mydoors']))
  $where[] = sprintf("car.doors='%s'",mysql_real_escape_string($_POST['mydoors']));
if(!empty($_POST['mydealercost']))
  $where[] = sprintf("car.dealercost='%s'",mysql_real_escape_string($_POST['mydealercost']));
if(!empty($_POST['myretailprice']))
  $where[] = sprintf("car.retailprice='%s'",mysql_real_escape_string($_POST['myretailprice']));
if(!empty($_POST['mycolor']))
  $where[] = sprintf("color.color_name='%s'",mysql_real_escape_string($_POST['mycolor']));
if(!empty($_POST['myinteriorcolor']))
  $where[] = sprintf("interior_color.interior_color='%s'",mysql_real_escape_string($_POST['myinteriorcolor']));

if(count($where))
  $query .= ' WHERE '.implode(' AND ',$where);
  
print $query;
?>

phpstudent111 · March 24, 2009

Thank you for your quick response. i added your code, I got the following error message

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/j/w/a/jwalkow111/html/search.php on line 50

The WHERE clause was omitted from the sql query in favor of the variable $where...could that be why? i'm still a newbie and my system will be complete if I can only get this code to work...thanks again for your help it is much much appreciated

rhodesa · March 24, 2009

did you close the string for $query?

phpstudent111 · March 24, 2009

I cut and pasted your code. I'm not sure what you mean by closing the string. The whole thing now looks like this...

mysql_connect("$host", "$username", "$password", 1, 131072)or die("Unable to Connect");

mysql_select_db("$db_name")or die("Cannot Select Database");

//defining variables

$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 = array();

if(!empty($_POST['mymodel']))

$where[] = sprintf("model.id='%s'",mysql_real_escape_string($_POST['mymodel']));

if(!empty($_POST['mymanufacturer']))

$where[] = sprintf("model.manufacturer='%s'",mysql_real_escape_string($_POST['mymanufacturer']));

if(!empty($_POST['myyear']))

$where[] = sprintf("car.year='%s'",mysql_real_escape_string($_POST['myyear']));

if(!empty($_POST['mydoors']))

$where[] = sprintf("car.doors='%s'",mysql_real_escape_string($_POST['mydoors']));

if(!empty($_POST['mydealercost']))

$where[] = sprintf("car.dealercost='%s'",mysql_real_escape_string($_POST['mydealercost']));

if(!empty($_POST['myretailprice']))

$where[] = sprintf("car.retailprice='%s'",mysql_real_escape_string($_POST['myretailprice']));

if(!empty($_POST['mycolor']))

$where[] = sprintf("color.color_name='%s'",mysql_real_escape_string($_POST['mycolor']));

if(!empty($_POST['myinteriorcolor']))

$where[] = sprintf("interior_color.interior_color='%s'",mysql_real_escape_string($_POST['myinteriorcolor']));

if(count($where))

$query .= ' WHERE '.implode(' AND ',$where);

print $query;

$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();

?>

</body>

</html>

mike12255 · March 24, 2009

please start typing :

['code] ['/code]

around your code but dont include the ' i just inserted that to show the tags

it'll make it easier for us to read.

rhodesa · March 24, 2009

next time...use [ code ][ /code ] tags (without the spaces)...makes code easier to read. this is what i mean...the line i edited is commented:

<?php
mysql_connect("$host", "$username", "$password", 1, 131072)or die("Unable to Connect");
mysql_select_db("$db_name")or die("Cannot Select Database");
//defining variables
$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)"; //HERE
$where = array();
if(!empty($_POST['mymodel']))
  $where[] = sprintf("model.id='%s'",mysql_real_escape_string($_POST['mymodel']));
if(!empty($_POST['mymanufacturer']))
  $where[] = sprintf("model.manufacturer='%s'",mysql_real_escape_string($_POST['mymanufacturer']));
if(!empty($_POST['myyear']))
  $where[] = sprintf("car.year='%s'",mysql_real_escape_string($_POST['myyear']));
if(!empty($_POST['mydoors']))
  $where[] = sprintf("car.doors='%s'",mysql_real_escape_string($_POST['mydoors']));
if(!empty($_POST['mydealercost']))
  $where[] = sprintf("car.dealercost='%s'",mysql_real_escape_string($_POST['mydealercost']));
if(!empty($_POST['myretailprice']))
  $where[] = sprintf("car.retailprice='%s'",mysql_real_escape_string($_POST['myretailprice']));
if(!empty($_POST['mycolor']))
  $where[] = sprintf("color.color_name='%s'",mysql_real_escape_string($_POST['mycolor']));
if(!empty($_POST['myinteriorcolor']))
  $where[] = sprintf("interior_color.interior_color='%s'",mysql_real_escape_string($_POST['myinteriorcolor']));
if(count($where))
  $query .= ' WHERE '.implode(' AND ',$where);
print $query;
$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();
?>
</body>
</html>

phpstudent111 · March 24, 2009

Thanks so much. I can tell that is so close to working 2 questions.

1.. In my results now on the front end, it prints

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 model.id='NULL' AND model.manufacturer='200' AND car.year='NULL' AND car.doors='NULL' AND color.color_name='NULL' AND interior_color.interior_color='NULL'

and then my table...so maybe an extra quote?

and

2..with the if(!empty....etc -- do I pass a "NULL" for null variables or a "0" ?

Thanks again so much.

rhodesa · March 24, 2009

first, remove this line:

print $query;

...i just put that in there for debugging

as for NULL/0...that depends on how you want it to work. if the form element is empty, what does that mean? i assumed that meant "don't filter on that field"

phpstudent111 · March 24, 2009

Thanks. I have drop down menu's for the HTML form, passing the ID's that need to be either included or omitted from the WHERE clause in the mysql based on whether or not they are empty, null, or zero.

I can pass whichever values work. i've tried NULL and empty and return no results with either... Any ideas?

rhodesa · March 24, 2009

i'm still confused...what does one of your SELECTs look like?

phpstudent111 · March 24, 2009

It works perfectly. I just had to modify some of the calls to the database, ie car.model...which you couldn't have possibly known.

You are a genius, you are the man. Thank you so much I really appreciate it.

If you want to get really crazy...You can help me to modify my WHERE clause to make the dealercost and retailprice search between the two values inserted.

rhodesa · March 24, 2009

what do the form fields look like for these?

edit: and what does the data in the table look like for these columns? ie are they INTs?

phpstudent111 · March 24, 2009

I'd really like to have them populate directly from the database, but this is my first application i'm building so I kept it fairly simple. Thanks again for getting that search to work. I really appreciate it.

<h2 align="center"> Inventory Management System</h2>

<table id="inventory align="center">
<form name="search" method="post" action="search.php">
<br><br><p><strong>Please Search Below:</strong></p>
<tr><td>Manufacturer:</td>
<td><select name="mymanufacturer" size="1">
<option value=0>Manufacturer</option>
<option value="200">Acura</option>
<option value="201">Infiniti</option>
<option value="202">Porsche</option>
<option value="203">Maserati</option>
<option value="204">Ford</option>
<option value="205">GM</option>
<option value="206">Dodge</option>
<option value="207">Chrysler</option>
<option value="208">Mercedes</option>
<option value="209">Chevrolet</option>
<option value="210">Honda</option>
<option value="211">Toyota</option>
<option value="212">Subaru</option>
<option value="213">Hyundai</option>
<option value="214">Saturn</option>
<option value="215">Land Rover</option>
<option value="216">Lotus</option>
<option value="217">Nissan</option>
<option value="218">Lincoln</option>
</td></tr>
<tr><td>Model:</td>
<td><select name="mymodel" size="1"> 
<option value=0>Model</option>
<option value="1">TL</option>
<option value="2">Integra</option>
<option value="3">TSX</option>
<option value="4">RSX</option>
<option value="5">Altima</option>
<option value="6">Sentra</option>
<option value="7">Maxima</option>
<option value="8">S230</option>
<option value="9">C330</option>
<option value="10">Benz</option>
<option value="11">Quatroporte</option>
<option value="12">Boxster</option>
<option value="13">Carrera</option>
<option value="14">Mustang</option>
<option value="15">Taurus</option>
<option value="16">Envoy</option>
<option value="17">Towncar</option>
<option value="18">Sky</option>
<option value="19">Ram</option>
<option value="20">Challenger</option>
<option value="21">G35</option>
<option value="22">Corvette</option>
<option value="23">F150</option>
<option value="24">Accord</option> 
</td></tr><tr> <td>

<tr><td>Doors:</td>
<td><select name="mydoors" size="1"> 
<option value=0>Doors</option>
<option>2</option>
<option>4</option>
<option>5</option></td></tr>
<tr><td>
Dealer Cost:  </td><td> <input name="mydealercost" type="text" name="mydealercost" /></br></td>
</tr><tr><td>
Retail Price:</td><td> <input name="myretailprice" type="text" id="myretailprice" /></td></br>
</tr>
<td>
<tr><td>Year:</td>
<td><select name="myyear" size="1"> 
<option value=0>Year</option>
<option>1980</option>
<option>1981</option>
<option>1982</option>
<option>1983</option>
<option>1984</option><option>1985</option><option>1986</option><option>1987</option>
<option>1988</option><option>1999</option><option>2000</option><option>2001</option>
<option>2002</option><option>2003</option><option>2004</option><option>2005</option>
<option>2006</option><option>2007</option><option>2008</option><option>2009</option><option>2010</option>
</td></tr><tr> <td>
<tr><td>Color:</td>
<td><select name="mycolor" size="1"> 
<option value=0>Color</option>
<option value="1">Black</option>
<option value="2">White</option>
<option value="3">Red</option>
<option value="4">Orange</option>
<option value="5">Yellow</option>
<option value="6">Violet</option>
<option value="7">Blue</option>
<option value="8">Green</option>
<option value="9">Silver</option> 
</td></tr>
<tr><td>Interior Color:</td>
<td><select name="myinteriorcolor" size="1"> 
<option value=0>Interior Color</option>
<option value="1">Black Leather</option>
<option value="2">White Leather</option>
<option value="3">Blue Leather</option>
<option value="4">Red Leather</option>
<option value="5">Silver Leather</option>
<option value="6">Black Cloth</option>
<option value="7">White Cloth</option>
<option value="8">Blue Cloth</option>
<option value="9">Red Cloth</option>
<option value="10">Silver Cloth</option> 

</td></tr>
<tr><td><br><br>
<input type="submit" name="submit" value="Search" /></td></tr></br>
</form> </div>



</table>
</body>
</html>

rhodesa · March 24, 2009

ok...so:

Dealer Cost:  </td><td> <input name="mydealercost" type="text" name="mydealercost" /></br></td>
Retail Price:</td><td> <input name="myretailprice" type="text" id="myretailprice" /></td></br>

what do you expect someone to type in this field?

phpstudent111 · March 24, 2009

Something like - "40000.00" - But I can easily change them to dropdown values incrementing by 10,000 - if we are able to select cars from within the 2 price ranges. I would make them

Retail Cost within:

and 2 dropdowns to select from....This is just a learning project for me. It's never going to be used..thanks again!

rhodesa · March 24, 2009

yeah...you need to change this to either:

-1 drop down with ranges...ie 10,000 - 15,000 / 15,000 - 20,000 / etc

or

-2 inputs (either text or dropdown) with a min and a max

once you have that, you can do:

if(!empty($_POST['mydealercostmin']))

$where[] = sprintf("car.dealercost >= '%d'",mysql_real_escape_string($_POST['mydealercostmin']));

if(!empty($_POST['mydealercostmax']))

$where[] = sprintf("car.dealercost <= '%d'",mysql_real_escape_string($_POST['mydealercostmax']));

the %d will force it to an integer

phpstudent111 · March 24, 2009

Wow. Thanks again, it works perfectly.

Sign In

Please help!!!!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information