Error with ORDER BY

[HDFilmMaker2112]

Semi-Urgent... site is active, not very large site though.

 

Trying to add Sort By and and Number of Products options to my site... the below is returning:

 

Problem with the query: SELECT * FROM products WHERE product_category='emf meters' LIMIT 0, 20 ORDER BY product_id

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 'ORDER BY product_id' at line 1

 

if(isset($_POST['num_products'])){
$num_product_per_page=$_POST['num_products'];
$num_product_per_page = stripslashes($num_product_per_page);
$num_product_per_page = mysql_real_escape_string($num_product_per_page);
}
else{
$num_products_per_page="20";
}
$num_products='';

if(isset($_POST['sort_by'])){
$sort_by_selected=$_POST['sort_by'];
$sort_by_selected = stripslashes($sort_by_selected);
$sort_by_selected = mysql_real_escape_string($sort_by_selected);
}
else{
$sort_by_selected="product_id";
}
$sort_by='';

// How many adjacent pages should be shown on each side?
$adjacents = 3;

/* 
First get total number of rows in data table. 
If you have a WHERE clause in your query, make sure you mirror it here.
*/

$query5 = "SELECT COUNT(*) as num FROM $tbl_name WHERE product_category='$cat'";
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages[num];

/* Setup vars for query. */
$targetpage = "store.php?cat=".$cat; 	//your file name  (the name of this file)
$limit = 20; 								//how many items to show per page
$page = $_GET['page'];
if($page){
$start = ($page - 1) * $limit; 			//first item to display on this page
}
else{
$start = 0;								//if no page var is given, set start to 0
}

/* Get data. */
$sql30 = "SELECT * FROM $tbl_name WHERE product_category='$cat' LIMIT $start, $limit ORDER BY $sort_by_selected";
$result30 = mysql_query($sql30) or die("Problem with the query: $sql30<br>" . mysql_error());

[monkeytooth]

I would first start by checking if $sort_by_selected is actually returning a value (echo it out) also try putting the LIMIT part after the ORDER BY part

[Pikachu2000]

The LIMIT clause should come after the ORDER BY.

[HDFilmMaker2112]

Well if the error is printing out "product_id" wouldn't that mean it's echoing out?

 

Anyway, moved the ORDER BY before the LIMIT and it's working now... doesn't make much sense to me. Thought it really didn't matter about the order so long as the query started with a SELECT, INSERT, or DELETE statement.

 

Thanks for the help.

[monkeytooth]

Im not sure if I am correct on this statement I am about to make. But from my understanding of the LIMIT clause it is if used like an end of line concept. Once a LIMIT is imposed within your statement it thinks its the end of the query statement if theres more in the query then it errors, again thats my take on it from my own experiences. I'm sure theres a better way to state that that's more technical but hopefully I conveyed the point enough?