Product Sorting Trouble.

JustinD2011 · October 24, 2011

Hello all! I am new to the forums and this is my first post. I've tried to search for an answer but have come up empty handed so I thought I'd just ask outright.

I am building a website for a local car dealership and they want to have a "Vehicles" page where it shows a list of all the vehicles in inventory and the consumer can sort the inventory by Make, Model, Year, Color, and/or Price. I'm working with php and MySQL and I am a newbie coder trying to learn on my own. I know how to sort querys but I am completely lost when it comes to sorting with dynamic/multiple variable chosen by the user. Please help. I know my code sucks but any advise will be SUPER helpful.

<?php
include "storescripts/connect_to_mysql.php";
if(isset($_POST['Year'])){
$where = "WHERE";
if(isset($_POST['Color'])) {
	$and = "AND";
} else {
	$and = "";
}
if(isset($_POST['Model'])) {
	$and = "AND";
} else {
	$and = "";
}
$yearArr = "'" . implode("','", $_POST['Year']) . "'";

$yearSort = "Year IN($yearArr) $and";	
	if(isset($_POST['Color'])){
		$colorArr = "'" . implode("','", $_POST['Color']) . "'";
		$colorSort = "Color IN($colorArr)";

	} else {
		$colorArr = "";
		$colorSort = "";
			}
	if(isset($_POST['Model'])){
		$modelArr = "'" . implode("','", $_POST['Model']) . "'";
		$modelSort = "Model IN($modelArr)";	
	} else {
		$modelArr = "";
		$modelSort = "";
			}
} else {
$yearArr = "";
$yearSort = "";
$where = "";
}
if(isset($_POST['Color'])){
$where = "WHERE";
if(isset($_POST['Year'])) {
	$and = "AND";
} else {
	$and = "";
}
if(isset($_POST['Model'])) {
	$and = "AND";
} else {
	$and = "";
}
$colorArr = "'" . implode("','", $_POST['Color']) . "'";

$colorSort = "Color IN($colorArr) $and";	
	if(isset($_POST['Year'])){
		$yearArr = "'" . implode("','", $_POST['Year']) . "'";
		$yearSort = "Year IN($yearArr)";

	} else {
		$yearArr = "";
		$yearSort = "";
			}
	if(isset($_POST['Model'])){
		$modelArr = "'" . implode("','", $_POST['Model']) . "'";
		$modelSort = "Model IN($modelArr)";	
	} else {
		$modelArr = "";
		$modelSort = "";
			}
} else {
$colorArr = "";
$colorSort = "";
$where = "";
}
if(isset($_POST['Model'])){
$where = "WHERE";
if(isset($_POST['Color'])) {
	$and = "AND";
} else {
	$and = "";
}
if(isset($_POST['Year'])) {
	$and = "AND";
} else {
	$and = "";
}
$modelArr = "'" . implode("','", $_POST['Model']) . "'";

$modelSort = "Model IN($modelArr) $and";	
	if(isset($_POST['Color'])){
		$colorArr = "'" . implode("','", $_POST['Color']) . "'";
		$colorSort = "Color IN($colorArr)";

	} else {
		$colorArr = "";
		$colorSort = "";
			}
	if(isset($_POST['Year'])){
		$yearArr = "'" . implode("','", $_POST['Year']) . "'";
		$yearSort = "Year IN($yearArr)";	
	} else {
		$yearArr = "";
		$yearSort = "";
			}
} else {
$modelArr = "";
$modelSort = "";
$where = "";
}
$querySort = "$yearSort $colorSort $modelSort";
$query = "SELECT * FROM vehicles $where $querySort $sort";  // create the query object
$sql = mysql_query($query);
// Pagination
// Logic
$nr = mysql_num_rows($sql);// Get total number of rows
if(isset($_GET['pn'])) {
 $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']);
} else {
$pn = 1;
}
$itemsPerPage = 10;
$lastPage = ceil($nr / $itemsPerPage);
if ($pn < 1) {
$pn = 1;
} else if ($pn > $lastPage) {
$pn = $lastPage;
}
$centerPages = ""; // Initialize Variable
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
} else if ($pn == $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
}$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage;
$sql2 = mysql_query("$query");
// End Logic
// Pagination Display
$paginationDisplay = "";
if ($lastPage !="1") {
$paginationDisplay .='Page <strong>' . $pn . '</strong> of ' . $lastPage . ' <img src="images/clearImage.gif" width="48" height="1" alt="Spacer" />';
if ($pn != 1) {
	$previous = $pn - 1;
	$paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
}
$paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
if ($pn != $lastPage) {
	$nextPage = $pn + 1;
	$paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
}
}
$sql2 = mysql_query("$query");
$dynamicList = '<table border="0" cellpadding="10">';
$productCount = mysql_num_rows($sql2); // count the output amount
if ($productCount > 0) {
while($row = mysql_fetch_array($sql2)){
		 $id = $row["id"];
		 $stockNumber = $row["StockNumber"];
		 $status = $row["Status"];
		 $gasType = $row["GasType"];
		 $year = $row["Year"];
		 $make = $row["Make"];
		 $model = $row["Model"];
		 $category = $row["Category"];
		 $transmission = $row["Transmission"];
		 $engineSize = $row["EngineSize"];
		 $color = $row["Color"];
		 $internetPrice = $row["InternetPrice"];
		 $driveTrain = $row["DriveTrain"];
		 $msrp = $row["MSRP"];
		 $internetSpecial = $row["InternetSpecial"];
		 $dateCreated = strftime("%b %d, %Y", strtotime($row["DateCreated"]));
		 $comments = $row["VehicleComments"];
		 $options = $row["Options"];
		 $shortComments = substr($options, 0, 50);
		 $detailSummary = '' . $stockNumber . ', ' . $color . ', ' . $gasType . ', ' . $driveTrain . ', ' . $shortComments . '';
		 $dynamicList .= '<tr><td>' . $year . ' ' . $make . ' ' . $model . ' ' . $detailSummary . ' $' . $internetPrice . '</td>';
}
} else {
$dynamicList = "We don't have any items in our store yet";
}
$dynamicTable .= '</tr></table></form>';
mysql_close();
?>
<?php
include 'storescripts/connect_to_mysql.php';
$sql = mysql_query("SELECT Color FROM vehicles GROUP BY Color");
$i = 0;
$dynamicTable = '<table border="0" cellpadding="10"><strong>Color</strong>';
while($row = mysql_fetch_array($sql)){

$id = $row["id"];
$color = $row["Color"];

if($i % 2 == 0) {
	$dynamicTable .= "<tr><td><input name='Color[]' type='checkbox' value='" . $color . "' />" . $color . "</td>";
} else {
	$dynamicTable .= "<td><input name='Color[]' type='checkbox' value='" . $color . "' />" . $color . "</td>";
}
$i++;
}
$dynamicTable .= '</tr></table>';
?>
<?php
include 'storescripts/connect_to_mysql.php';
$sql = mysql_query("SELECT Model FROM vehicles GROUP BY Model");
$i = 0;
$dynamicModel = '<table border="0" cellpadding="10"><br /><strong>Model</strong>';
while($row = mysql_fetch_array($sql)){

$id = $row["id"];
$model = $row["Model"];

if($i % 2 == 0) {
	$dynamicModel .= '<tr><td><input name="Model[]" type="checkbox" value="' . $model . '" />' . $model . '</td>';
} else {
	$dynamicModel .= '<td><input name="Model[]" type="checkbox" value="' . $model . '" />' . $model . '</td>';
}
$i++;
}
$dynamicModel .= '</tr></table>';
?>
<?php
include 'storescripts/connect_to_mysql.php';
$sql = mysql_query("SELECT Year FROM vehicles GROUP BY Year");
$i = 0;
$dynamicYear = '<table border="0" cellpadding="10"><strong>Year</strong>';
while($row = mysql_fetch_array($sql)){

$id = $row["id"];
$year = $row["Year"];
if(isset($_POST['Year'])); {

	if($_POST['Year'] == $year) {
		$check = 'checked';	
	} else {
		$check = '';
	}
}

if($i % 2 == 0) {
	$dynamicYear .= "<tr><td><input id='Year[]' name='Year[]' type='checkbox' " . $check . " value='" . $year . "' />" . $year . "</td>";
} else {
	$dynamicYear .= "<td><input id='Year[]' name='Year[]' type='checkbox' " . $check . " value='" . $year . "' />" . $year . "</td>";
}
$i++;
}
$dynamicYear .= '</tr></table>';
?>
<!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>Jay GMC Commercial - <?php echo $Year; ?><?php echo $Make; ?><?php echo $Model; ?> in Columbus, GA</title>
</head>

<body>

<table width="100%" border="0" cellpadding="10">
  <tr>
    <td width="64%"><h3><?php echo $query; ?> <br />PhP Grid Layout <br /><?php echo $yearSort; ?><?php echo $yearArr; ?></h3> </td>
    <td width="36%" align="right">Sort By:<form id="order" name="form1" method="get" action="">
      <label for="order"></label>
      <select name="order" id="order">
        <option value="ORDER BY Year ASC" <?php echo $selYa; ?>>Year (Low - High)</option>
        <option value="ORDER BY Year DESC" <?php echo $selYd; ?>>Year (High - Low)</option>
        <option value="ORDER BY Model ASC" <?php echo $selMa; ?>>Model (A - Z)</option>
        <option value="ORDER BY Model DESC" <?php echo $selMd; ?>>Model (Z - A)</option>
      </select>
      <input name="Submit" type="submit" value="Submit" />
    </form></td>
  </tr>
</table>
<form action="sorttest.php" method="post" name="sort">
  <table width="100%" border="1" cellpadding="10">
    <tr align="left" valign="top">
      <td width="24%"><input name="submit1" type="submit" />
        <br />
        <br />
        <?php echo $dynamicTable; ?><br />
        <?php echo $dynamicModel; ?><br />
        <?php echo $dynamicYear; ?><br /></td>
      <td width="76%"><?php echo $dynamicList; ?></td>
    </tr>
  </table>
</form>
</body>
</html>

Thanks again!

JustinD2011 · October 24, 2011

BTW, http://columbuscommercialtrucks.com/sorttest.php is the site that uses the above code. Thanks again!

Sign In

Product Sorting Trouble.

Recommended Posts

JustinD2011

Link to comment

Share on other sites

JustinD2011

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information