[SOLVED] Query Problem

[JakeSilver]

Hi There,

 

I am trying to edit a script so that i can order it by date. I know its a cubecart script but Mods plese dont move as its a problem with a query and i really need it fixed ASAP. Thanks

 

The code is as follows:

   $productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE .$like";
   
} elseif($_GET['catId']=="saleItems" && $config['saleMode']>0) {
   
   $productListQuery = "SELECT ".$glob['dbprefix']."CubeCart_cats_idx.cat_id, ".$glob['dbprefix']."CubeCart_cats_idx.productId, productCode, quantity, description, image, price, name, popularity, sale_price, stock_level, useStockLevel FROM ".$glob['dbprefix']."CubeCart_cats_idx INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON ".$glob['dbprefix']."CubeCart_cats_idx.productId = ".$glob['dbprefix']."CubeCart_inventory.productId WHERE sale_price > 0 GROUP BY ".$glob['dbprefix']."CubeCart_inventory.productId";

} else {
   
   $productListQuery = "SELECT ".$glob['dbprefix']."CubeCart_cats_idx.cat_id, ".$glob['dbprefix']."CubeCart_cats_idx.productId, productCode, quantity, description, image, price, name, popularity, sale_price, stock_level, useStockLevel FROM ".$glob['dbprefix']."CubeCart_cats_idx INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON ".$glob['dbprefix']."CubeCart_cats_idx.productId = ".$glob['dbprefix']."CubeCart_inventory.productId WHERE ".$glob['dbprefix']."CubeCart_cats_idx.cat_id = ".$db->mySQLSafe($_GET['catId']);
   
}

Basically how can i add

 

ORDER BY `date` ASC

into each of the 3 instances of the query? As i have had difficulty figureing it out.

 

Many Thanks

[GingerRobot]

At the end of the query? How about showing us what you actually tried? And tell us what happened when you tried that.

[solon]

Hey Jake,

 

Have you tried to put the  ORDER BY `date` ASC at the end of each query?

 

Something like:

 

   $productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE .$like, ORDER BY `date` ASC";
   
} elseif($_GET['catId']=="saleItems" && $config['saleMode']>0) {
   
   $productListQuery = "SELECT ".$glob['dbprefix']."CubeCart_cats_idx.cat_id, ".$glob['dbprefix']."CubeCart_cats_idx.productId, productCode, quantity, description, image, price, name, popularity, sale_price, stock_level, useStockLevel FROM ".$glob['dbprefix']."CubeCart_cats_idx INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON ".$glob['dbprefix']."CubeCart_cats_idx.productId = ".$glob['dbprefix']."CubeCart_inventory.productId WHERE sale_price > 0 GROUP BY ".$glob['dbprefix']."CubeCart_inventory.productId, ORDER BY `date` ASC";

} else {
   
   $productListQuery = "SELECT ".$glob['dbprefix']."CubeCart_cats_idx.cat_id, ".$glob['dbprefix']."CubeCart_cats_idx.productId, productCode, quantity, description, image, price, name, popularity, sale_price, stock_level, useStockLevel FROM ".$glob['dbprefix']."CubeCart_cats_idx INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON ".$glob['dbprefix']."CubeCart_cats_idx.productId = ".$glob['dbprefix']."CubeCart_inventory.productId WHERE ".$glob['dbprefix']."CubeCart_cats_idx.cat_id = ".$db->mySQLSafe($_GET['catId']).",ORDER BY `date` ASC";
   
}

[JakeSilver]

The first query I have done

 

$productListQuery = "SELECT * FROM ".$glob['dbprefix']."CubeCart_inventory WHERE .$like ORDER BY date ASC";

 

Second query i have done

	$productListQuery = "SELECT ".$glob['dbprefix']."CubeCart_cats_idx.cat_id, ".$glob['dbprefix']."CubeCart_cats_idx.productId, productCode, quantity, description, image, price, name, popularity, sale_price, stock_level, useStockLevel FROM ".$glob['dbprefix']."CubeCart_cats_idx INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON ".$glob['dbprefix']."CubeCart_cats_idx.productId = ".$glob['dbprefix']."CubeCart_inventory.productId WHERE sale_price > 0 ORDER BY ".$glob['dbprefix']."CubeCart_inventory.date ASC";

 

Its the last of the three. I have tried eveything and keep getting the error

Parse error: syntax error, unexpected T_STRING in /home/adrian/public_html/shop/includes/content/viewCat.inc.php on line 162

 

Line 162 is below:

		$productListQuery = "SELECT ".$glob['dbprefix']."CubeCart_cats_idx.cat_id, ".$glob['dbprefix']."CubeCart_cats_idx.productId, productCode, quantity, description, image, price, name, popularity, sale_price, stock_level, useStockLevel FROM ".$glob['dbprefix']."CubeCart_cats_idx INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON ".$glob['dbprefix']."CubeCart_cats_idx.productId = ".$glob['dbprefix']."CubeCart_inventory.productId WHERE ".$glob['dbprefix']."CubeCart_cats_idx.cat_id = ".$db->mySQLSafe($_GET['catId']) ORDER BY ".$glob['dbprefix']."CubeCart_Inventory.date ASC;

 

Solon :- I have tried your query and recieve the following error

 

MySQL Error Occured

1064: 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 `date` ASC LIMIT 0, 10' at line 1

 

QUERY = SELECT CubeCart_cats_idx.cat_id, CubeCart_cats_idx.productId, productCode, quantity, description, image, price, name, popularity, sale_price, stock_level, useStockLevel FROM CubeCart_cats_idx INNER JOIN CubeCart_inventory ON CubeCart_cats_idx.productId = CubeCart_inventory.productId WHERE CubeCart_cats_idx.cat_id = '12',ORDER BY `date` ASC

 

 

[GingerRobot]

You don't need a comma to separate the ORDER BY clause.

[JakeSilver]

Thanks Solon & GingerRoot

 

Problem solved!

[solon]

try to use ORDER BY `table_name`.`date` ASC without the comma (,) Ginger is right!