case when in where clause?

[bbmak]

Instead of writing 2 separated query, are there anyway to put a case statement when in the where clause of the query? 

$category_id will be null if on front page and not null on category pages.  So, I want to put a statement on the highlighted part. However, it seems not working. Can someone help with my syntax.

 

case when $category_id is Null THEN
""
when $category_id is not Null THEN
ci.cat_id = ?

		function next24($category_id, $lastItemID) {

			if(!empty($lastItemID)) {
				
				
				if($next24item = $this->mysqli->prepare("
				SELECT
				   ci.id as item_id,
				   ci.cat_id as cat_id,
				   ci.item_name,
				   ci.item_description,
				   ci.item_note,
				   ci.item_price,
				   ci.item_discount,
				   ci.item_url,
				   ci.date_created,
				   ci.date_updated,
				   ci.date_expired,
				   cii.item_id as cii_item_id,
				   cii.item_image_filename,
				   cm.merchant_name,
				   cm.merchant_url,
				   cm.merchant_description,
				   cm.merchant_logo_thumb 
				From
				   core_item ci 
				   LEFT JOIN
					  core_merchant cm 
					  on ci.merchant_id = cm.id 
				   LEFT JOIN
					  core_item_image cii 
					  on cii.item_id = ci.id 
				WHERE
				   ci.id < ? 
				 
				AND
                                   case when $category_id is Null THEN
                                   ""
                                        when $category_id is not Null THEN
                                   ci.cat_id = ?

				ORDER BY
				   ci.id DESC LIMIT 24
				   "))
					
					{
						$next24item->bind_param("ii", $lastItemID,$category_id);
						$next24item->execute();
						$next24results = $next24item->get_result();
						return $next24results->fetch_all(MYSQLI_BOTH);
						
						
						
					}
			
				} 	 else {
					
					break;
					
				}
				
		}

Thanks

Edited March 5, 2017 by bbmak

[kicken]

No, but by binding your parameter multiple times you can add a second condition that is always true.

 

WHERE
   ci.id < ?
   AND (? IS NULL OR ci.cat_id = ?)

$next24item->bind_param('iii', $lastItemID, $category_id, $category_id);

If $category_id is null then the first condition ? IS NULL will always be true and cause the second condition to be ignored. If $category_id is not null then ? IS NULL will always be false and the second condition will be evaluated.

[bbmak]

Thanks

This seems even better than putting a case when statement in the query for my case.