Getting the following results from my code:
pl: 0
ph: 999999
new_pre: p
hull_material: f
make: any
query: SELECT * FROM boats WHERE price > 0 AND price < 999999AND new_pre = 'pre-owned'AND hull_material = 'fiberglass'AND make LIKE '%' ORDER BY price DESC
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 'new_pre = 'pre-owned'AND hull_material = 'fiberglass'AND make LIKE '%' ORDER B' at line 3
So I know the default price function is now working, but he SQL is not picking up my where clause for the new_pre value. If I throw the query directly into the database, it will work if I add a space after pice and before the AND for new_pre. Can't figure out where I'm wrong in my code.
SELECT * FROM boats WHERE price > 0 AND price < 999999
AND new_pre = 'pre-owned'AND hull_material = 'fiberglass'AND make LIKE '%' ORDER BY price DESC
Here's all the code:
<?php
$image_dir = '/test/images/inventory/';
$pl = trim($_GET['pl']);
$ph = trim($_GET['ph']);
if(empty($ph))
$ph = 999999;
else
$ph = (int)$ph; //make integer
if(empty($pl))
$pl = 0;
else
$pl = (int)$pl; //make integer
echo "pl: $pl <br/>";
echo "ph: $ph <br/>";
$new_pre = $_GET['new_pre'];
echo "new_pre: $new_pre <br/>";
$hull_material = $_GET['hull_material'];
echo "hull_material: $hull_material <br/>";
$make = $_GET['make'];
echo "make: $make <br/>";
//$where_condition = "WHERE condition = '$new'
//AND condition = '$pre-owned'";
switch ($new_pre) {
case "b":
$where_new_pre = "AND new_pre LIKE '%'";
break;
case "n":
$where_new_pre = "AND new_pre = 'new'";
break;
case "p":
$where_new_pre = "AND new_pre = 'pre-owned'";
break;
}
switch ($hull_material) {
case "a":
$where_hull_material = "AND hull_material = 'aluminum'";
break;
case "f":
$where_hull_material = "AND hull_material = 'fiberglass'";
break;
case "b":
$where_hull_material = "AND hull_material LIKE '%'";
break;
}
switch ($make) {
case "any":
$where_make = "AND make LIKE '%'";
break;
case "contender":
$where_make = "AND make = 'contender'";
break;
case "century":
$where_make = "AND make = 'century'";
break;
case "hydra-sports":
$where_make = "AND make = 'hydra-sports'";
break;
case "alweld":
$where_make = "AND make = 'alweld'";
break;
case "hewes":
$where_make = "AND make = 'hewes'";
break;
case "cobia":
$where_make = "AND make = 'cobia'";
break;
case "maverick":
$where_make = "AND make = 'maverick'";
break;
case "pathfinder":
$where_make = "AND make = 'pathfinder'";
break;
case "twin vee":
$where_make = "AND make = 'twin vee'";
break;
}
$where_price = "WHERE price > $pl
AND price < $ph";
$where = $where_price . $where_new_pre . $where_hull_material . $where_make;
$simple = TRUE;
if ($pl <> 0) $simple = FALSE;
if ($make <> '') $simple = FALSE;
if ($new_pre <> '') $simple = FALSE;
if ($hull_material <> '') $simple = FALSE;
$query_simple = "SELECT * FROM boats ORDER BY price DESC LIMIT 5";
$query_search = "SELECT * FROM boats
$where
ORDER BY price DESC";
if ($simple) $query = $query_simple;
else $query = $query_search;
echo "query: $query <br/>";
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Database Test</title>
</head>
<body>
<?php
// Connects to your Database
require_once('inc/dbstuff.php');
$data = mysql_query($query)
or die(mysql_error());
Echo "<table border cellpadding=3>";
while($row = mysql_fetch_array( $data ))
{
Echo "<tr>";
Echo "<th>Photo:</th> <td><img src=$image_dir{$row['pix']}></td>";
Echo "<th>Year:</th> <td>".$row['year'] . "</td> ";
Echo "<th>Make:</th> <td>".$row['make'] . "</td> ";
Echo "<th>Model:</th> <td>".$row['model'] . "</td> ";
Echo "<th>Location:</th> <td>".$row['location'] . "</td> ";
Echo "<th>Condition:</th> <td>".$row['new_pre'] . "</td> ";
Echo "<th>Engine:</th> <td>".$row['engine_make'] . "</td> ";
Echo "<th>Price:</th> <td>".$row['price'] . " </td></tr>\n";
}
Echo "</table>";
?>
<form id="search_form" form action="dbtest.php" method="get" enctype="multipart/form-data" name="search_form">
<ul>
<h2>Condition:</h2>
<li>
<label for="new_pre">
<select name="new_pre">
<option value="b" selected="selected">New & Pre-Owned</option>
<option value="n">New</option>
<option value="p">Pre-Owned</option>
</select></label>
<!-- <label for="new_pre">
<input type="checkbox" name="CheckboxGroup1" value="n" id="new" />
New</label>
<br />
<label for="condtion">
<input type="checkbox" name="CheckboxGroup1" value="p" id="pre-owned" />
Pre-Owned</label> -->
</li></ul>
<ul>
<h2>Hull Material:</h2>
<li>
<label for="hull_material">
<select name="hull_material">
<option value="f" selected="selected">Fiberglass</option>
<option value="a">Aluminum</option>
<option value="b">Both</option>
</select></label>
</li></ul>
<ul>
<h2>Manufacturer:</h2>
<li>
<label for="make">
<select name="make">
<option value="any" selected="selected">Any</option>
<option value="contender">Contender</option>
<option value="century">Century</option>
<option value="hydra-sports">Hydra-Sports</option>
<option value="alweld">Alweld</option>
<option value="hewes">Hewes</option>
<option value="cobia">Cobia</option>
<option value="maverick">Maverick</option>
<option value="pathfinder">Pathfinder</option>
<option value="twin vee">Twin Vee</option>
</select></label>
</li></ul>
<ul>
<h2>Price:</h2>
<li><label = "price">
<input type="text" style="width: 50px;" onKeyPress="submitOnEnter(event)" id="fromPrice" name="pl">
to <input type="text" style="width: 50px;" onKeyPress="submitOnEnter(event)" id="toPrice" name="ph"></label>
</li>
</ul>
<ul><li>
<input name="submit" type="submit" value="Search"/>
</li></ul>
<br />
</form>
</body>
</html>
