...

View Full Version : MySQL results order by " " how ??



chakytori
05-18-2011, 08:11 PM
Am doing a website to sell cars, you can see it here:
http://www.autosromo.com

And i would like to be able to order the results based on price (ascending for starters), miles, year, etc.,

The code its already done am just trying to improve it the results are shown via this function in the index.php file:



<!--SEARCH-->
<?php
foreach (Search () as $row) {
?>


and in a file here it is the search function:



function Search () {
global $rows_limit;
$results = array();
// CONDICIONES
$query = "SELECT au.*, mk.name AS marca, mo.name AS modelo FROM autos AS au, make AS mk, model AS mo ";
$query .= (!empty($_GET['make']) && $_GET['make']>1) ? 'WHERE make = \''.$_GET['make'].'\'' : '';
$query.= (!empty($_GET['model']) && $_GET['model']>0) ?" AND model =". $_GET['model'] : '';
$query.= ($_GET['year'] > 0 && $_GET['make']=="1" && $_GET['model'] == "0" && $_GET['cilinders'] >= "0" && $_GET['transmision'] >= "0" && $_GET['traccion'] >= "0") ? " WHERE year LIKE '".$_GET['year']."'" : "";
$query.= ($_GET['year'] > 0) ? " AND year LIKE '".$_GET['year']."'" : "";
// QUERY CILINDROS
$query.= ($_GET['cilinders'] > 0 and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['transmision'] >= "0" and $_GET['traccion'] >= "0") ? " WHERE cilinders =". $_GET['cilinders'] : "";
$query.= ($_GET['cilinders'] > 0) ? " AND cilinders =". $_GET['cilinders'] : "";
// QUERY TRANSMISION
$query.= ($_GET['transmision'] > 0 and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['cilinders'] == "0" and $_GET['traccion'] >= "0") ? " WHERE transmision =". $_GET['transmision'] : "";
$query.= ($_GET['transmision'] > 0) ? " AND transmision =". $_GET['transmision'] : "";
// QUERY TRACCION
$query.= ($_GET['traccion'] > 0 and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['cilinders'] == "0" and $_GET['transmision'] == "0") ? " WHERE traccion =". $_GET['traccion'] : "";
$query.= ($_GET['traccion'] > 0) ? " AND traccion =". $_GET['traccion'] : "";
$query.= (!empty($_GET['make']) && $_GET['make']>1) ? " AND au.make=mk.id AND au.model=mo.id" : "WHERE au.make=mk.id AND au.model=mo.id";

// LIMIT
$per_page = $rows_limit;
$start = (empty($_GET['page'])) ? 0 : ($_GET['page']-1)*$per_page;
$limit = " LIMIT $start,$per_page";

$sql= mysql_query($query.$limit);
while ($row = mysql_fetch_array ($sql)) {
$results[] = $row;
}
return $results;
}


What and where should i made the modifications on the code to order the results by price for example... thanks a lot for the help.

Fumigator
05-18-2011, 10:54 PM
Since your code is building the query one piece at a time, you can easily add an ORDER BY piece to the query. Add an element to your query string so you get a $_GET['sortcolumn'] coming into your script, and then add $query .= "ORDER BY {$_GET['sortcolumn']}"; to the query, after the WHERE clause and before the LIMIT clause.

chakytori
05-18-2011, 11:39 PM
Since your code is building the query one piece at a time, you can easily add an ORDER BY piece to the query. Add an element to your query string so you get a $_GET['sortcolumn'] coming into your script, and then add $query .= "ORDER BY {$_GET['sortcolumn']}"; to the query, after the WHERE clause and before the LIMIT clause.

I added the //order part in the script but it still shows an error this change i made to the script (only the //order part i change):



// QUERY TRACCION
$query.= ($_GET['traccion'] > 0 and $_GET['make']=="1" and $_GET['model'] == "0" and $_GET['year'] == "0" and $_GET['cilinders'] == "0" and $_GET['transmision'] == "0") ? " WHERE traccion =". $_GET['traccion'] : "";
$query.= ($_GET['traccion'] > 0) ? " AND traccion =". $_GET['traccion'] : "";
$query.= (!empty($_GET['make']) && $_GET['make']>1) ? " AND au.make=mk.id AND au.model=mo.id" : "WHERE au.make=mk.id AND au.model=mo.id";

// ORDER
$order = (empty($_GET['price'])) ? 0 : ($_GET['price']);
$query.= " ORDER BY $order";


// LIMIT
$per_page = $rows_limit;
$start = (empty($_GET['page'])) ? 0 : ($_GET['page']-1)*$per_page;
$limit = " LIMIT $start,$per_page";

$sql= mysql_query($query.$limit);
while ($row = mysql_fetch_array ($sql)) {
$results[] = $row;
}
return $results;
}


It shows this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/autosrom/public_html/funcionesp.php on line 69

where line 69 is:
while ($row = mysql_fetch_array ($sql)) {

shadowmaniac
05-19-2011, 02:26 AM
On line 68 or so,


echo $query.$limit;
exit;

and verify that the query is properly formed.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum