hELLO,
Here is an image of a menu with an additional dropdown menu where a user can select the order by which the menu items will be displayed
Here is the code which gets the user selected order code
$menu_order = get_menu_order($user_id);
var_dump($menu_order);
Here is the result of var_dump()
C:\wamp64\www\AHopefully_order\pages\instrument.inc.php:6:string ‘3’ (length=1)
there are 4 options for the order, 1, 2, 3, and 4
If the user makes no selection the default option is 1
Here is the function
function get_menu_order($user_id)
{
global $db;
try
{
$sql = "SELECT `menu_order`
FROM `order_m`
WHERE `user_id` = :user_id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$stmt->execute();
if($stmt->rowCount() == 0){
return 1;
}else{
$row = $stmt->fetchColumn();
return $row;
}
}
catch(Exception $e)
{
return false;
}
}//End function
Here is the function I coded to get the items for main menu
function get_all_couples($user_id, $start_date, $end_date, $hour_start, $hour_end,
$days, $menu_order)
{
global $db;
try
{
$sql = "SELECT DISTINCT `item`, SUM(`profit`) AS profit
FROM `data`
WHERE DATE(`o_time`) BETWEEN :start_date AND :end_date
AND TIME(`o_time`) BETWEEN :hour_start AND :hour_end
AND WEEKDAY(`o_time`) IN ($days)
AND `user_id` = :user_id
GROUP BY item
ORDER BY
CASE WHEN @menu_order = 1 THEN item ASC
CASE WHEN @menu_order = 2 THEN item DESC
CASE WHEN @menu_order = 3 THEN profit ASC
CASE WHEN @menu_order = 4 THEN profit DESC";
$stmt = $db->prepare($sql);
$stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
$stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);
$stmt->bindParam(':hour_start', $hour_start, PDO::PARAM_STR);
$stmt->bindParam(':hour_end', $hour_end, PDO::PARAM_STR);
$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$stmt->bindParam(':menu_order', $menu_order, PDO::PARAM_INT);
$stmt->execute();
if($stmt->rowCount() == 0)
return 0;
else
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
catch(Exception $e)
{
echo $e;
die();
return false;
}
}
and Here is the error message :
PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\wamp64\www\AHopefully_order\includes\fetch.php:199 Stack trace: #0 C:\wamp64\www\AHopefully_order\includes\fetch.php(199): PDOStatement->execute() #1 C:\wamp64\www\AHopefully_order\pages\instrument.inc.php(10): get_all_couples(‘30’, ‘2017-08-14’, ‘2023-03-07’, ‘00:00:00’, ‘23:59:59’, ‘0,1,2,3,4,5,6’, 1) #2 C:\wamp64\www\AHopefully_order\instrument.php(4): require(‘C:\wamp64\www\A…’) #3 {main}
What is the right syntax for SQL case of the ORDER BY in this query ?
Thanks
