Sorting/searching values - text input or select (ASC/DESC)

If you go to: http://www.nhusedautosales.com/browse.php

I am trying to create a simple search to refine results and then have the option to sort the fields by ASC/DESC.

I have googled some tutorials but they weren't very clear.

I know the basis would check if the form is submitted use this query and use the LIKE clause for my text input elements, but I am confused on how to throw it all together? This has been stumping me for awhile.

Any ideas on what I can do?

Thanks,


Ryan
<?php
$domain             = "?p=browse";
$current_page       = 1;  // default page
$entries_per_page   = 10;  // default entries per page
$num_of_pages_links = 6; // current and 2 either side, but you can change this
$extra_url_values   = '';  // add extra values to the urls if parameters were specified in the current one
include_once ("php_lib/php_lib.php");
$con = connectDB();
error_reporting(E_ALL);
?>
<form action="browse.php" method="get" id="action">
	<label class="browse">Make: </label><input type="text" size="20" name="make" /><br />
	<label class="browse">Model: </label><input type="text" size="20" name="model" /><br />
	<label class="browse">Year: </label><input type="text" size="20" name="year" /><br />
	<label class="browse">Price: </label>
	<select name="price">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
	<label class="browse">Year: </label>
	<select name="year">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
	<label class="browse">Sort Make: </label>
	<select name="smake">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
	<label class="browse">Sort Model: </label>
	<select name="smodel">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
	<label class="browse">Sort Mileage: </label>
	<select name="smileage">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
	<input type="submit" value="Sort" />
	<div id="page">
	<label class="browse">Page:</label>
</form>
 
<?php
if(!isset($_GET['page'])){
      $page = 1;
}else{
      $page = $_GET['page'];
}
if (isset($_GET["page"])) $perpage = $_GET["page"];
    else $perpage = 100;
 
	$max_results = 100;
	$from = (($page * $max_results) - $max_results);
 	if ($perpage == 0) $perpage = 100;
	$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM autos"),0);
	$total_pages = intval($total_results / $max_results);
	if ($total_results % $perpage > 0) $total_pages++;
	
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"$domain&amp;page=$prev\" class=\"pagenum\">Previous</a>\n ";
}
for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
    }else{
		echo "<a href=\"$domain&amp;page=$i\" class=\"pagenum\">$i</a>\n ";
    }
}
 
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"$domain&amp;page=$next\" class=\"pagenum\">Next</a>\n</div>\n";
}
  	$sql    = "SELECT * FROM autos LIMIT $from,$max_results";	
	$result = mysql_query($sql) or die("Bad Query: " . mysql_error());
	$cnt    = 0;
	print "
	<table id=\"myTable\" width=\"80%\">
		<thead> 
			<tr>
			<th>Click to view</th> 
			<th>Make</th>
			<th>Model</th>
			<th>Year</th>
			<th>Mileage</th>
			<th>Color</th> 
			<th>Short Description</th>
			<th>VIN</th> 
			<th>Price</th>
			</tr> 
		</thead>
		<tbody>
		";
		while ($row = mysql_fetch_array($result)){
			print "<tr>";
			print "<td><a href=\"details.php?id=".$row["id"]."\">View Car</a></td>";
			print "<td>".$row["make"]."</td>\n"; 
			print "<td>".$row["model"]."</td>\n"; 
			print "<td>".$row["year"]."</td>\n"; 
			print "<td>".number_format($row["mileage"], 0, '.', ',')."</td>\n";
			print "<td>".$row["color"]."</td>\n"; 
			print "<td>".$row["short_desc"]."</td>\n";
			print "<td>".$row["vin"]."</td>\n"; 
			print "<td>$".number_format($row["price"], 0, '.', ',')."</td>\n";
			print "</tr>\n";
			$cnt++;
		}	
		print "</tbody>\n";
        print "</table>\n";
?>

Open in new window

LVL 1
catonthecouchproductionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Beverley PortlockCommented:
You need to build an order by clause, but it is complicated by the fact that you have to assign a priority. Are you going to sort by make then model then year or by year then make then model?

Once you decide this then it is just a matter of getting the sequences and building an ORDER BY clause for your query. For example

$ordClause = "";
$seq  = ( $_POST['smake'] == "desc" ) ? " smake desc ," : "";
$seq .= ( $_POST['smodel'] == "desc" ) ? " smodel desc ," : "";

if ( $seq != "" ) {
    $seq = substr( $seq, 0, -1 );
    $ordClause = "order by $seq";
}


......


        $sql    = "SELECT * FROM autos $ordClause LIMIT $from,$max_results";      
....

0
Beverley PortlockCommented:
I've just noticed that your column names are slightly different fom the field names, so amending the above code.... I've also enforced ascending order if no selections are made


$ordClause = "";
$seq  = ( $_POST['smake'] == "desc" ) ? " make desc ," : " make ,";
$seq .= ( $_POST['smodel'] == "desc" ) ? " model desc ," : " model ";

if ( $seq != "" ) {
    $seq = substr( $seq, 0, -1 );
    $ordClause = "order by $seq";
}


......


        $sql    = "SELECT * FROM autos $ordClause LIMIT $from,$max_results";      
....

0
catonthecouchproductionsAuthor Commented:
Ahh I see now! I am guessing at first then at going to fill out the text inputs, then use the sort? Does that help you?

And for all of these:

$seq  = ( $_POST['smake'] == "desc" ) ? " smake desc ," : "";

I copy/paste for each of my SORT fields correct? Where you have "desc" can I leave that as that? Correct?

And then what would i use for my text input?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

catonthecouchproductionsAuthor Commented:
I jsut got the post that you posted now, so should I be using the newest one you posted? Because my column names are different than the input names?

0
catonthecouchproductionsAuthor Commented:
Below is my table layout if it will make this process easier.
CREATE TABLE `autos` (
  `id` int(11) NOT NULL auto_increment,
  `make` varchar(120) default NULL,
  `short_desc` varchar(240) default NULL,
  `long_desc` text,
  `active` int(11) default NULL,
  `vin` varchar(120) default NULL,
  `price` float default NULL,
  `model` varchar(120) default NULL,
  `year` year(4) default NULL,
  `image` int(11) default NULL,
  `mileage` bigint(20) default NULL,
  `color` varchar(60) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Open in new window

0
Beverley PortlockCommented:
Catonthecouch said "so should I be using the newest one you posted? Because my column names are different than the input names?"

Yes. For now just get it working with make and model. It should then only be a matter of extending it for other fields. Also I noticed a missing comma which I have corrected. Sorry for the sloppy typing, but it's late.....



$ordClause = "";
$seq  = ( $_POST['smake'] == "desc" ) ? " make desc ," : " make ,";
$seq .= ( $_POST['smodel'] == "desc" ) ? " model desc ," : " model ,";

if ( $seq != "" ) {
    $seq = substr( $seq, 0, -1 );
    $ordClause = "order by $seq";
}


......


        $sql    = "SELECT * FROM autos $ordClause LIMIT $from,$max_results";      
....
0
catonthecouchproductionsAuthor Commented:
Alrighty! Thats fine we all get sloppy after awhile, haha. I made those changes and tested it and doesnt seem to be working? This is for the sorting on:

Make
Model

Correct?

http://www.nhusedautosales.com/browse.php

I uploaded my latest files. Any ideas why it is doing that? Doesnt seem to be working.
0
Beverley PortlockCommented:
I've used $_POST but I've noticed your form is a $_GET. Change my POSTs to GETs and try it. If it doesn't work, let me know and I'll look at it in the morning.

0
catonthecouchproductionsAuthor Commented:
It works thank you!

http://www.nhusedautosales.com/browse.php

1. Anything i can do in the mean time until you come back tomorrow?

2. To work with the LIKE clause for the text input?

3. And can you explain this line, just for my own sake:

      $seq  = ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";


4. I know the GET part, but I am confsued on the other stuff.

5 I would use this same syntax for the mileage and year correct?

Thank you so much for your help!

0
catonthecouchproductionsAuthor Commented:
Just a little update, I tried doing the year, price, and mileage and I cant seem to get it, I was going off of this basis:

      $seq .= ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";

      $seq .= ( $_GET['syear'] == "desc" ) - the SYEAR is the name of the field

       year desc ," : " year ,"; - Is the column name "year" sort by DESC and then the column name?

I am not sure why didnt work for me.
0
Beverley PortlockCommented:

2. To work with the LIKE clause for the text input?

Can you explain what you mean by this?


3. And can you explain this line, just for my own sake:

      $seq  = ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";


Sure - this is a shorthand for an if-then-else statement. The "if" is in the brackets, the "then" is between the "?" and the :" and the "else" is the last bit. It is referred to as a "ternery" statement and it is functionally equivalent to saying


     if ( $_GET['smake'] == "desc )
          $seq = " make desc,";
     else
          $seq = " make,";




4. I know the GET part, but I am confsued on the other stuff.

Other stuff?  Which stuff? Or do you mean the ternery statement?



5 I would use this same syntax for the mileage and year correct?

Yes - it should work OK. Now you mentioned that when you tried the year that it did not seem to work. This could be due to "over sorting". You can only do so much sorting and have it make sense. For instance, look at these 3 columns

Make  Model   Year
Ford   Fiesta   2000
Opel  Korsa    1999

If I am sorting in Make (asc) / Model (Asc) / Year (desc) it looks as shown. If I then sort in Make (asc) / Model (asc)  / Year (asc) it will look the same because the make and model have the highest sorting priority. To see a difference I would need records that have the first two elements the same, like

Make (asc) / Model (Asc) / Year (asc)

Make  Model   Year
Ford   Fiesta   2000
Opel  Korsa    1999
Opel  Korsa    2000

Doing this in  Make (asc) / Model (Asc) / Year (desc) would make it look like this

Make  Model   Year
Ford   Fiesta   2000
Opel  Korsa    2000
Opel  Korsa    1999

because the third element is the only differentiator for records 2 and 3

To check if your code is working, make YEAR the primary sort key. That way its effect will be easily seen

$ordClause = "";
$seq  = ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";
$seq .= ( $_POST['smake'] == "desc" ) ? " make desc ," : " make ,";
$seq .= ( $_POST['smodel'] == "desc" ) ? " model desc ," : " model ,";

if ( $seq != "" ) {
    $seq = substr( $seq, 0, -1 );
    $ordClause = "order by $seq";
}

0
catonthecouchproductionsAuthor Commented:
2. By LIKE I mean, I am trying to use LIKE for my text input to search? I would use LIKE there right?

3. Thank you! I see how this works now!

4. And by other stuff I did mean the rest of the syntax on the if else shorthand

5. Ahhh..I seee! On the over sorting bit.

I tried the new code to sort and no luck..any idea why? I tried just the year and nothing. My new code is below.
<?php
$domain             = "?p=browse";
include_once ("php_lib/php_lib.php");
$con = connectDB();
error_reporting(E_ALL);
?>
<p>
<form action="browse.php" method="get" id="action">
<table border="0" bordercolor="" width="" bgcolor="">
<tr>
<td>
	<label class="browse">Make: </label><input type="text" size="20" name="make" /><br />
 
	<label class="browse">Model: </label><input type="text" size="20" name="model" /><br />
 
	<label class="browse">Year: </label><input type="text" size="20" name="year" /><br />
 
	<label class="browse">Price: </label>
	<select name="sprice">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
</td>
<td class="leftSpacing">
	<label class="browse">Year: </label>
	<select name="syear">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
	<label class="browse">Sort Make: </label>
	<select name="smake">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
	<label class="browse">Sort Model: </label>
	<select name="smodel">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
	<label class="browse">Sort Mileage: </label>
	<select name="smileage">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
</td>
</tr>
<tr><td><input type="submit" value="Sort" /></td>
</table>
 
 
	<div id="page">
	<label class="browse">Page:</label>
</form>
 
<?php
	if(!isset($_GET['page'])){
	      $page = 1;
	}else{
	      $page = $_GET['page'];
	}
	if (isset($_GET["page"])) $perpage = $_GET["page"];
	    else $perpage = 100;
	
		$max_results = 100;
		$from = (($page * $max_results) - $max_results);
		if ($perpage == 0) $perpage = 100;
		$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM autos"),0);
		$total_pages = intval($total_results / $max_results);
		if ($total_results % $perpage > 0) $total_pages++;
		
	if($page > 1){
	    $prev = ($page - 1);
	    echo "<a href=\"$domain&amp;page=$prev\" class=\"pagenum\">Previous</a>\n ";
	}
	for($i = 1; $i <= $total_pages; $i++){
	    if(($page) == $i){
		echo "$i ";
	    }else{
			echo "<a href=\"$domain&amp;page=$i\" class=\"pagenum\">$i</a>\n ";
	    }
	}
	
	if($page < $total_pages){
	    $next = ($page + 1);
	    echo "<a href=\"$domain&amp;page=$next\" class=\"pagenum\">Next</a>\n</div>\n";
	}
	
		// start sorting and new SQL here
 
 
	$ordClause = "";
	$seq  = ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";
	$seq .= ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";
	$seq .= ( $_GET['smodel'] == "desc" ) ? " model desc ," : " model ,";
	
	if ( $seq != "" ) {
	    $seq = substr( $seq, 0, -1 );
	    $ordClause = "order by $seq";
	}
	
	$sql    = "SELECT * FROM autos $ordClause LIMIT $from,$max_results";
	$result = mysql_query($sql) or die("Bad Query: " . mysql_error());
	$cnt    = 0;
	
	print "
	<table id=\"myTable\" width=\"80%\">
		<thead> 
			<tr>
			<th>Details</th> 
			<th>Make</th>
			<th>Model</th>
			<th>Year</th>
			<th>Mileage</th>
			<th>Color</th> 
			<th>Description</th>
			<th>VIN</th> 
			<th>Price</th>
			</tr> 
		</thead>
		<tbody>
		";
		while ($row = mysql_fetch_array($result)){
			print "<tr>";
			print "<td><a href=\"details.php?id=".$row["id"]."\">Details</a></td>";
			print "<td>".$row["make"]."</td>\n"; 
			print "<td>".$row["model"]."</td>\n"; 
			print "<td>".$row["year"]."</td>\n"; 
			print "<td>".number_format($row["mileage"], 0, '.', ',')."</td>\n";
			print "<td>".$row["color"]."</td>\n"; 
			print "<td>".$row["short_desc"]."</td>\n";
			print "<td>".$row["vin"]."</td>\n"; 
			print "<td>$".number_format($row["price"], 0, '.', ',')."</td>\n";
			print "</tr>\n";
			$cnt++;
		}	
		print "</tbody>\n";
        print "</table>\n";
?></p>

Open in new window

0
catonthecouchproductionsAuthor Commented:
It seems that the year will sort, but the others wont even after resetting back to default.
<?php
$domain             = "?p=browse";
include_once ("php_lib/php_lib.php");
$con = connectDB();
error_reporting(E_ALL);
?>
<p>
<form action="browse.php" method="get" id="action">
<table border="0" bordercolor="" width="" bgcolor="">
<tr>
<td>
	<label class="browse">Make: </label><input type="text" size="20" name="make" /><br />
 
	<label class="browse">Model: </label><input type="text" size="20" name="model" /><br />
 
	<label class="browse">Year: </label><input type="text" size="20" name="year" /><br />
 
	<label class="browse">Price: </label>
	<select name="sprice">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
</td>
<td class="leftSpacing">
	<label class="browse">Year: </label>
	<select name="syear">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
	<label class="browse">Sort Make: </label>
	<select name="smake">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
	<label class="browse">Sort Model: </label>
	<select name="smodel">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
	<label class="browse">Sort Mileage: </label>
	<select name="smileage">
		<option value="asc">Ascending</option>
		<option value="desc">Descending</option>
	</select><br />
 
</td>
</tr>
<tr><td><input type="submit" value="Sort" /></td>
</table>
 
 
	<div id="page">
	<label class="browse">Page:</label>
</form>
 
<?php
	if(!isset($_GET['page'])){
	      $page = 1;
	}else{
	      $page = $_GET['page'];
	}
	if (isset($_GET["page"])) $perpage = $_GET["page"];
	    else $perpage = 100;
	
		$max_results = 100;
		$from = (($page * $max_results) - $max_results);
		if ($perpage == 0) $perpage = 100;
		$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM autos"),0);
		$total_pages = intval($total_results / $max_results);
		if ($total_results % $perpage > 0) $total_pages++;
		
	if($page > 1){
	    $prev = ($page - 1);
	    echo "<a href=\"$domain&amp;page=$prev\" class=\"pagenum\">Previous</a>\n ";
	}
	for($i = 1; $i <= $total_pages; $i++){
	    if(($page) == $i){
		echo "$i ";
	    }else{
			echo "<a href=\"$domain&amp;page=$i\" class=\"pagenum\">$i</a>\n ";
	    }
	}
	
	if($page < $total_pages){
	    $next = ($page + 1);
	    echo "<a href=\"$domain&amp;page=$next\" class=\"pagenum\">Next</a>\n</div>\n";
	}
	
		// start sorting and new SQL here
 
 
	$ordClause = "";
	$seq  = ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";
	$seq .= ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";
	$seq .= ( $_GET['smodel'] == "desc" ) ? " model desc ," : " model ,";
	
	if ( $seq != "" ) {
	    $seq = substr( $seq, 0, -1 );
	    $ordClause = "order by $seq";
	}
	
	$sql    = "SELECT * FROM autos $ordClause LIMIT $from,$max_results";
	$result = mysql_query($sql) or die("Bad Query: " . mysql_error());
	$cnt    = 0;
	
	print "
	<table id=\"myTable\" width=\"80%\">
		<thead> 
			<tr>
			<th>Details</th> 
			<th>Make</th>
			<th>Model</th>
			<th>Year</th>
			<th>Mileage</th>
			<th>Color</th> 
			<th>Description</th>
			<th>VIN</th> 
			<th>Price</th>
			</tr> 
		</thead>
		<tbody>
		";
		while ($row = mysql_fetch_array($result)){
			print "<tr>";
			print "<td><a href=\"details.php?id=".$row["id"]."\">Details</a></td>";
			print "<td>".$row["make"]."</td>\n"; 
			print "<td>".$row["model"]."</td>\n"; 
			print "<td>".$row["year"]."</td>\n"; 
			print "<td>".number_format($row["mileage"], 0, '.', ',')."</td>\n";
			print "<td>".$row["color"]."</td>\n"; 
			print "<td>".$row["short_desc"]."</td>\n";
			print "<td>".$row["vin"]."</td>\n"; 
			print "<td>$".number_format($row["price"], 0, '.', ',')."</td>\n";
			print "</tr>\n";
			$cnt++;
		}	
		print "</tbody>\n";
        print "</table>\n";
?></p>

Open in new window

0
Beverley PortlockCommented:
Insert a 1974 chevy camaro in to the database please - same spelling and capitalisation as the existing one. It will assist testing
0
catonthecouchproductionsAuthor Commented:
Done: http://www.nhusedautosales.com/NHUsedAutos.php

Anything else I can do?
0
Beverley PortlockCommented:
I'm very confused! Your script works here. Look at http://dev.gembiz.co.uk/cotc.php
0
catonthecouchproductionsAuthor Commented:
Sorry! It cant seem to be able to sort Make/Model?
0
Beverley PortlockCommented:
Code attached


-- Server version: 5.0.18
-- PHP Version: 5.1.2
-- 
-- Database: `aaa`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `autos`
-- 
 
DROP TABLE IF EXISTS `autos`;
CREATE TABLE `autos` (
  `id` int(11) NOT NULL auto_increment,
  `make` varchar(120) default NULL,
  `short_desc` varchar(240) default NULL,
  `long_desc` text,
  `active` int(11) default NULL,
  `vin` varchar(120) default NULL,
  `price` float default NULL,
  `model` varchar(120) default NULL,
  `year` year(4) default NULL,
  `image` int(11) default NULL,
  `mileage` bigint(20) default NULL,
  `color` varchar(60) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
-- 
-- Dumping data for table `autos`
-- 
 
INSERT INTO `autos` VALUES (2, 'Jeep', NULL, NULL, NULL, NULL, 50000, 'Cherokee', 1988, NULL, NULL, NULL);
INSERT INTO `autos` VALUES (3, 'Chevy', NULL, NULL, NULL, NULL, 200000, 'Camaro', 1976, NULL, NULL, NULL);
INSERT INTO `autos` VALUES (4, 'Chevy', NULL, NULL, NULL, NULL, 500000, 'Camaro', 1974, NULL, NULL, NULL);
        
 
 
(Note: The form action has changed on the attached script)
 
 
<?php
$domain             = "?p=browse";
include_once ("php_lib/php_lib.php");
$con = connectDB();
 
 
?>
<p>
<form action="cotc.php" method="get" id="action">
<table border="0" bordercolor="" width="" bgcolor="">
<tr>
<td>
     <label class="browse">Make: </label><input type="text" size="20" name="make" /><br />
 
     <label class="browse">Model: </label><input type="text" size="20" name="model" /><br />
 
     <label class="browse">Year: </label><input type="text" size="20" name="year" /><br />
 
     <label class="browse">Price: </label>
     <select name="sprice">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
</td>
<td class="leftSpacing">
     <label class="browse">Year: </label>
     <select name="syear">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
     <label class="browse">Sort Make: </label>
     <select name="smake">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
     <label class="browse">Sort Model: </label>
     <select name="smodel">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
     <label class="browse">Sort Mileage: </label>
     <select name="smileage">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
</td>
</tr>
<tr><td><input type="submit" value="Sort" /></td>
</table>
 
 
     <div id="page">
     <label class="browse">Page:</label>
</form>
 
<?php
     if(!isset($_GET['page'])){
           $page = 1;
     }else{
           $page = $_GET['page'];
     }
     if (isset($_GET["page"])) $perpage = $_GET["page"];
         else $perpage = 100;
     
          $max_results = 100;
          $from = (($page * $max_results) - $max_results);
          if ($perpage == 0) $perpage = 100;
          $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM autos"),0);
          $total_pages = intval($total_results / $max_results);
          if ($total_results % $perpage > 0) $total_pages++;
          
     if($page > 1){
         $prev = ($page - 1);
         echo "<a href=\"$domain&amp;page=$prev\" class=\"pagenum\">Previous</a>\n ";
     }
     for($i = 1; $i <= $total_pages; $i++){
         if(($page) == $i){
          echo "$i ";
         }else{
               echo "<a href=\"$domain&amp;page=$i\" class=\"pagenum\">$i</a>\n ";
         }
     }
     
     if($page < $total_pages){
         $next = ($page + 1);
         echo "<a href=\"$domain&amp;page=$next\" class=\"pagenum\">Next</a>\n</div>\n";
     }
     
          // start sorting and new SQL here
 
 
     $ordClause = "";
     $seq  = ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";
     $seq .= ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";
     $seq .= ( $_GET['smodel'] == "desc" ) ? " model desc ," : " model ,";
     
     if ( $seq != "" ) {
         $seq = substr( $seq, 0, -1 );
         $ordClause = "order by $seq";
     }
     
     $sql    = "SELECT * FROM autos $ordClause LIMIT $from,$max_results";
     echo $sql;
     $result = mysql_query($sql) or die("Bad Query: " . mysql_error());
     $cnt    = 0;
     
     print "
     <table id=\"myTable\" width=\"80%\">
          <thead> 
               <tr>
               <th>Details</th> 
               <th>Make</th>
               <th>Model</th>
               <th>Year</th>
               <th>Mileage</th>
               <th>Color</th> 
               <th>Description</th>
               <th>VIN</th> 
               <th>Price</th>
               </tr> 
          </thead>
          <tbody>
          ";
          while ($row = mysql_fetch_array($result)){
               print "<tr>";
               print "<td><a href=\"details.php?id=".$row["id"]."\">Details</a></td>";
               print "<td>".$row["make"]."</td>\n"; 
               print "<td>".$row["model"]."</td>\n"; 
               print "<td>".$row["year"]."</td>\n"; 
               print "<td>".number_format($row["mileage"], 0, '.', ',')."</td>\n";
               print "<td>".$row["color"]."</td>\n"; 
               print "<td>".$row["short_desc"]."</td>\n";
               print "<td>".$row["vin"]."</td>\n"; 
               print "<td>$".number_format($row["price"], 0, '.', ',')."</td>\n";
               print "</tr>\n";
               $cnt++;
          }    
          print "</tbody>\n";
        print "</table>\n";
?></p>

Open in new window

0
catonthecouchproductionsAuthor Commented:
Thank you, year/make/model all seem to work now. Any idea on how I can work in the input? So a user can type in "Chevy" or the year in the boxes?
0
catonthecouchproductionsAuthor Commented:
i added in mileage/price and they work ifne, only part left is the LIKE query.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>New Hampshire Used Cars, NH Used Cars, NH Used Car Dealer and NH Used Autos</title>
<meta name="Resource-Type" content="document">
<meta name="keywords" content="New Hampshire used cars, NH used cars, NH preowned cars, New Hampshire used car lot, NH used autos">
<meta name="description" content="NH Used Cars: For the best in NH used cars, visit Priceline Auto with two convenient locations in Concord, NH. We have a fantastic selection of cars, a free warranty offer, and can help you obtain financing, right on the spot. Click here for more details">
<meta name="Classification" content="NH Used Cars">
<meta name="robots" content="INDEX, FOLLOW">
<meta name="revisit-after" content="30 days">
<meta name="robots" content="all">
<meta name="document-class" content="Completed">
<meta name="document-distribution" content="Global">
<meta name="cache-control" content="Public">
<meta name="language" content="English">
<meta name="Abstract" content="NH Used Cars: For the best in NH used cars, visit Priceline Auto with two convenient locations in Concord, NH. We have a fantastic selection of cars, a free warranty offer, and can help you obtain financing, right on the spot. Click here for more details">
<META NAME="AUTHOR" CONTENT="George C. Jobel, PO Box 6184, Penacook, NH 03303-6184 - 603.369.1471, Lead Copywriter: Jen Lahey, Lead Designer: Yulia Dubinchik, Programmer: Paul Jarvis">
 
<style type="text/css">
	A.navLink:hover{
	TEXT-DECORATION: none; color: #03153F;}
	A.navTellAmerica:hover{
	TEXT-DECORATION: none; color: #DFE9FA;}
</style>
<link rel="stylesheet" type="text/css" href="Main.css">
</head>
 
<body topMargin="20" bottomMargin="20">
<table cellSpacing="0" cellPadding="0" width="752" border="0" align="center" bgcolor="white">
	<tr>
		<td height="1" bgcolor="white" colspan="1"></td>
	</tr>
	<tr>
		<td align="center">
			<table cellSpacing="0" cellPadding="0" width="750" border="0" align="center">
				<tr>
					<td>
						<table WIDTH="750" BORDER="0" CELLPADDING="0" CELLSPACING="0">
							<tr>
								<td>
									<TABLE WIDTH=750 BORDER=0 CELLPADDING=0 CELLSPACING=0>
										<TR>
											<TD><IMG SRC="images/top_01.gif" WIDTH=250 HEIGHT=40 ALT="Priceline Auto Sales & Repairs"></TD>
											<TD><IMG SRC="images/top_02.gif" WIDTH=250 HEIGHT=40 ALT="Priceline Auto Sales & Repairs"></TD>
											<TD><IMG SRC="images/top_03.gif" WIDTH=83 HEIGHT=40 ALT="Priceline Auto Sales & Repairs"></TD>
											<TD><a href="index.html"><IMG SRC="images/top_04.gif" WIDTH=74 HEIGHT=40 ALT="Home" border="0"></a></TD>
											<TD><a href="UsedCarsForNH.htm"><IMG SRC="images/top_05.gif" WIDTH=93 HEIGHT=40 ALT="Contact Us" border="0"></a></TD>
										</TR>
										<TR>
											<TD ROWSPAN=2><IMG SRC="images/top_06.gif" WIDTH=250 HEIGHT=93 ALT="Priceline Auto Sales & Repairs"></TD>
											<TD ROWSPAN=2><IMG SRC="images/top_07.gif" WIDTH=250 HEIGHT=93 ALT="Priceline Auto Sales & Repairs"></TD>
											<TD COLSPAN=3 background="images/top_08.gif" WIDTH=250 HEIGHT=37 align="right"><font color="white">NH Used Cars&nbsp;&nbsp;&nbsp;</font></TD>
										</TR>
										<TR>
											<TD COLSPAN=3><IMG SRC="images/top_09.gif" WIDTH=250 HEIGHT=56 ALT="Priceline Auto Sales & Repairs"></TD>
										</TR>
										<TR>
											<TD><IMG SRC="images/top_10.gif" WIDTH=250 HEIGHT=117 ALT="Priceline Auto Sales & Repairs"></TD>
											<TD><IMG SRC="images/top_11.gif" WIDTH=250 HEIGHT=117 ALT="Priceline Auto Sales & Repairs"></TD>
											<TD COLSPAN=3><IMG SRC="images/top_12.gif" WIDTH=250 HEIGHT=117 ALT="Priceline Auto Sales & Repairs"></TD>
										</TR>
									</TABLE>
								</td>
							</tr>		
						</table>
					
						<table WIDTH="750" BORDER="0" CELLPADDING="0" CELLSPACING="0">
							<tr>
								<td width="550" align="center" valign="top">
									<table WIDTH="510" BORDER="0" CELLPADDING="0" CELLSPACING="0">
										<tr>
											<td><img src="images/title_cars.gif" alt="Cars Available" WIDTH="300" HEIGHT="40"></td>
										</tr>
										<tr>
											<td align="right">
												<script type="text/javascript">
												var now = new Date();
												var days = new Array('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday');
												var months = new Array('January','February','March','April','May','June','July','August','September','October','November','December');
												var date = ((now.getDate()<10) ? "0" : "")+ now.getDate();
												function fourdigits(number)	{
												return (number < 1000) ? number + 1900 : number;}
												today =  days[now.getDay()] + ", " +
												months[now.getMonth()] + " " +
												date + ", " +
												(fourdigits(now.getYear())) ;
												document.write("<font face='Helvetica,Arial' style='font-size: 11px; color: #324C89;'>" + today + "</font>");
												</script>&nbsp;&nbsp;
											</td>
										</tr>
										
										<tr>
											<td height="20"></td>
										</tr>
										<tr>
											<td>
<?php
$domain             = "?p=browse";
include_once ("php_lib/php_lib.php");
$con = connectDB();
 
 
?>
<p>
<form action="NHUsedAutos.php" method="get" id="action">
<table border="0" bordercolor="" width="" bgcolor="">
<tr>
<td>
     <label class="browse">Make: </label><input type="text" size="20" name="make" /><br />
 
     <label class="browse">Model: </label><input type="text" size="20" name="model" /><br />
 
     <label class="browse">Year: </label><input type="text" size="20" name="year" /><br />
 
     <label class="browse">Price: </label>
     <select name="sprice">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
</td>
<td class="leftSpacing">
     <label class="browse">Year: </label>
     <select name="syear">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
     <label class="browse">Sort Make: </label>
     <select name="smake">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
     <label class="browse">Sort Model: </label>
     <select name="smodel">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
     <label class="browse">Sort Mileage: </label>
     <select name="smileage">
          <option value="asc">Ascending</option>
          <option value="desc">Descending</option>
     </select><br />
 
</td>
</tr>
<tr><td><input type="submit" value="Sort" /></td>
</table>
 
 
     <div id="page">
     <label class="browse">Page:</label>
</form>
 
<?php
     if(!isset($_GET['page'])){
           $page = 1;
     }else{
           $page = $_GET['page'];
     }
     if (isset($_GET["page"])) $perpage = $_GET["page"];
         else $perpage = 100;
     
          $max_results = 100;
          $from = (($page * $max_results) - $max_results);
          if ($perpage == 0) $perpage = 100;
          $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM autos"),0);
          $total_pages = intval($total_results / $max_results);
          if ($total_results % $perpage > 0) $total_pages++;
          
     if($page > 1){
         $prev = ($page - 1);
         echo "<a href=\"$domain&amp;page=$prev\" class=\"pagenum\">Previous</a>\n ";
     }
     for($i = 1; $i <= $total_pages; $i++){
         if(($page) == $i){
          echo "$i ";
         }else{
               echo "<a href=\"$domain&amp;page=$i\" class=\"pagenum\">$i</a>\n ";
         }
     }
     
     if($page < $total_pages){
         $next = ($page + 1);
         echo "<a href=\"$domain&amp;page=$next\" class=\"pagenum\">Next</a>\n</div>\n";
     }
     
          // start sorting and new SQL here
 
 
     $ordClause = "";
     $seq  = ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";
     $seq .= ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";
     $seq .= ( $_GET['smodel'] == "desc" ) ? " model desc ," : " model ,";
     $seq .= ( $_GET['smileage'] == "desc" ) ? " mileage desc ," : "mileage ,";
     $seq .= ( $_GET['sprice'] == "desc" ) ? " price desc ," : "price ,";
 
 
     
     if ( $seq != "" ) {
         $seq = substr( $seq, 0, -1 );
         $ordClause = "order by $seq";
     }
     
     $sql    = "SELECT * FROM autos $ordClause LIMIT $from,$max_results";
     echo $sql;
     $result = mysql_query($sql) or die("Bad Query: " . mysql_error());
     $cnt    = 0;
     
     print "
     <table id=\"myTable\" width=\"80%\">
          <thead> 
               <tr>
               <th>Details</th> 
               <th>Make</th>
               <th>Model</th>
               <th>Year</th>
               <th>Mileage</th>
               <th>Color</th> 
               <th>Description</th>
               <th>VIN</th> 
               <th>Price</th>
               </tr> 
          </thead>
          <tbody>
          ";
          while ($row = mysql_fetch_array($result)){
               print "<tr>";
               print "<td><a href=\"details.php?id=".$row["id"]."\">Details</a></td>";
               print "<td>".$row["make"]."</td>\n"; 
               print "<td>".$row["model"]."</td>\n"; 
               print "<td>".$row["year"]."</td>\n"; 
               print "<td>".number_format($row["mileage"], 0, '.', ',')."</td>\n";
               print "<td>".$row["color"]."</td>\n"; 
               print "<td>".$row["short_desc"]."</td>\n";
               print "<td>".$row["vin"]."</td>\n"; 
               print "<td>$".number_format($row["price"], 0, '.', ',')."</td>\n";
               print "</tr>\n";
               $cnt++;
          }    
          print "</tbody>\n";
        print "</table>\n";
?></p>
											</td>
										</tr>
										<tr>
											<td height="170"></td>
										</tr>
										<tr>
											<td align="center"><font color="#324C89" size="1"> | <a href="index.html" class="navLink"> Home</a> | <a href="NewHampshireUsedCarsAndUsedAutos.htm" class="navLink"> Why Us</a> | <a href="NHUsedAutos.htm" class="navLink"> Cars Available</a> | <a href="NHUsedCarsAndAutoRepairs.htm" class="navLink"> Services Offered</a> | <a href="NHUsedAndPreownedCars.htm" class="navLink"> Two Locations</a> | <br> | <a href="NHUsedCarWarrantees.htm" class="navLink"> Vehicle Warrantees</a> | <a href="NHUsedCarLots.htm" class="navLink"> Directions</a> | <a href="UsedCarsForNH.htm" class="navLink"> Contact Us</a> | <a href="NHWebSiteDevelopment.htm" class="navLink"> Credits</a> | </font></td>
										</tr>
										<tr>
											<td height="30"></td>
										</tr>
									</table>
								</td>
								<td width="200" valign="top">
									<TABLE WIDTH=200 BORDER=0 CELLPADDING=0 CELLSPACING=0>
										<TR>
											<TD><A HREF="index.html"><IMG SRC="images/menu_01.gif" WIDTH=200 HEIGHT=42 BORDER=0 ALT="Home"></A></TD>
										</TR>
										<TR>
											<TD><A HREF="NewHampshireUsedCarsAndUsedAutos.htm"><IMG SRC="images/menu_02.gif" WIDTH=200 HEIGHT=36 BORDER=0 ALT="Why Us"></A></TD>
										</TR>
										<TR>
											<TD><A HREF="NHUsedAutos.htm"><IMG SRC="images/menu_03.gif" WIDTH=200 HEIGHT=34 BORDER=0 ALT="Cars Available"></A></TD>
										</TR>
										<TR>
											<TD><A HREF="NHUsedCarsAndAutoRepairs.htm"><IMG SRC="images/menu_04.gif" WIDTH=200 HEIGHT=33 BORDER=0 ALT="Services Offered"></A></TD>
										</TR>
										<TR>
											<TD><A HREF="NHUsedAndPreownedCars.htm"><IMG SRC="images/menu_05.gif" WIDTH=200 HEIGHT=33 BORDER=0 ALT="Two Locations"></A></TD>
										</TR>
										<TR>
											<TD><A HREF="NHUsedCarWarrantees.htm"><IMG SRC="images/menu_06.gif" WIDTH=200 HEIGHT=34 BORDER=0 ALT="Vehicle Warrantees"></A></TD>
										</TR>
										<TR>
											<TD><A HREF="NHUsedCarLots.htm"><IMG SRC="images/menu_07.gif" WIDTH=200 HEIGHT=35 BORDER=0 ALT="Directions"></A></TD>
										</TR>
										<TR>
											<TD><A HREF="UsedCarsForNH.htm"><IMG SRC="images/menu_08.gif" WIDTH=200 HEIGHT=32 BORDER=0 ALT="Contact Us"></A></TD>
										</TR>
										<TR>
											<TD><IMG SRC="images/menu_09.gif" WIDTH=200 HEIGHT=21 ALT="Priceline Auto Sales & Repairs"></TD>
										</TR>
									</TABLE>
								</td>
							</tr>
						</table>
						
						<table WIDTH="750" BORDER="0" CELLPADDING="0" CELLSPACING="0">
							<tr>
								<td colspan="2"><img src="images/bottom.gif" alt="Priceline Auto Sales & Repairs" WIDTH="750" HEIGHT="61"></td>
							</tr>
							<tr>
								<td height="25" colspan="2" bgcolor="#324C89"></td>
							</tr>
							<tr>
								<td width="550" align="right" bgcolor="#324C89"><font color="white" size="1">&nbsp;Copyright</font> 
									<script type="text/javascript">
										var now = new Date();
										var date = ((now.getDate()<10) ? "0" : "")+ now.getDate();
										function fourdigits(number)	{
										return (number < 1000) ? number + 1900 : number;}
										today = (fourdigits(now.getYear())) ;
										document.write("<font face='Helvetica,Arial' style='font-size: 10px; color: white;'>" + today + "</font>");
									</script>
									<font color="white" size="1">Priceline Auto Sales & Repairs - All Rights Reserved.</font>&nbsp;&nbsp;&nbsp;
								</td>
								<td width="200" align="left" bgcolor="#324C89"><a href="http://www.nh-web-site-designer.com" target="_blank" class="navTellAmerica">Site by George C. Jobel</a></td>
							</tr>	
							<tr>
								<td bgcolor="#324C89" align="center" colspan="2">
									<font color="white" size="1">
										Priceline Auto Sales and Repairs is NOT affiliated with priceline.com, an online travel site.
									</font>
								</td> 
							</tr>	
							<tr>
								<td height="25" colspan="2" bgcolor="#324C89"></td>
							</tr>	
						</table>
					</td>
				</tr>
			</table>
		</td>
	</tr>
	<tr>
		<td height="1" bgcolor="white" colspan="1"></td>
	</tr>
</table>		
		
	
</body>
</html>

Open in new window

0
Beverley PortlockCommented:
cotc said "Sorry! It cant seem to be able to sort Make/Model?"

It is because of sort ordering. At present it sorts by year then model then make. Because the years are all different, sorting the year is sufficient to sort everything.

I have flipped the code to read

     $seq  = ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";
     $seq .= ( $_GET['smodel'] == "desc" ) ? " model desc ," : " model ,";
     $seq .= ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";


and now it sorts by make / model. This relates back to what I said about sorting in point number 5 several posts ago.
0
catonthecouchproductionsAuthor Commented:
Alrighty thank you for that! I will make those changes right now. Any ideas on the post i just posted above this? I am not sure if you saw it so that is why I said it, didnt mean to be pushy.
0
Beverley PortlockCommented:
"didnt mean to be pushy."

You pushy devil!!!!   ;-)  (only joking)

Similar sort of code, try using the segment below.  Extend it by adding more tests - the section before the where clause build.

 
 
     $whereClause = "";
     $wItems = array();
     
     // If an selection was made, add it to the array for the where clause build
     //
     if ( isset($_GET['make']) )
          if ( strlen($_GET['make'] ) > 0 )
               $wItems [] = " make LIKE '%".mysql_real_escape_string($_GET['make'])."%' ";
          
     if ( isset($_GET['model']) )
          if ( strlen($_GET['model'] ) > 0 )
             $wItems [] = " model LIKE '%".mysql_real_escape_string($_GET['model'])."%' ";
          
     if ( isset($_GET['year']) )
          if ( strlen($_GET['year'] ) > 0 )
               $wItems [] = " year LIKE '%".mysql_real_escape_string($_GET['year'])."%' ";
          
          
     // If any selections are in the array, build the where clause with AND opertaors
     //          
     if ( count( $wItems ) > 0 )
          $whereClause = " where " . implode( " and ", $wItems );
 
          
     $ordClause = "";
     $seq  = ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";
     $seq .= ( $_GET['smodel'] == "desc" ) ? " model desc ," : " model ,";
     $seq .= ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";
     
     if ( $seq != "" ) {
         $seq = substr( $seq, 0, -1 );
         $ordClause = "order by $seq";
     }
     
     $sql    = "SELECT * FROM autos $whereClause $ordClause LIMIT $from,$max_results";
     

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
catonthecouchproductionsAuthor Commented:
Would we use something along the lines of what we used previously?

     $ordClause = "";
     $seq  = ( $_GET['syear'] == "desc" ) ? " year desc ," : " year ,";
     $seq .= ( $_GET['smake'] == "desc" ) ? " make desc ," : " make ,";
     $seq .= ( $_GET['smodel'] == "desc" ) ? " model desc ," : " model ,";
     $seq .= ( $_GET['smileage'] == "desc" ) ? " mileage desc ," : "mileage ,";
     $seq .= ( $_GET['sprice'] == "desc" ) ? " price desc ," : "price ,";


     
     if ( $seq != "" ) {
         $seq = substr( $seq, 0, -1 );
         $ordClause = "order by $seq";
     }

But Add say $whereClause and place that in:

     $sql    = "SELECT * FROM autos $whereClause $ordClause  LIMIT $from,$max_results";

something like that?
0
Beverley PortlockCommented:
I think we've crossed posts! We both posted at 4:28 GMT
0
catonthecouchproductionsAuthor Commented:
So we have! Haha..ironic! Thank you for that code, I was looking it over and now is see how that is working to get the clause and making a segment for each search fields.

Any way to get it if t her is none then display "No records" Is that using mysql_num_rows = 0 then DISPLAY?
0
Beverley PortlockCommented:
Your supposition about mysql_num_rows is correct.

More info here http://www.php.net/mysql_num_rows

Regards....
0
catonthecouchproductionsAuthor Commented:
Thank you for your help!

Ryan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.