MySQL pulling results twice

[Adamhumbug]

Hi all,

I am pulling info from my database but for some reason the results are being populated twice.

The code i have is below:

$jid = $_SESSION['current_job_id'];

$sql = "SELECT * FROM ssm_menu_order where job_id = $jid";
	$result = mysqli_query($conn, $sql);
		if(mysqli_num_rows($result)>0){

$sql = "SELECT  menu_name, menu_price, menu_item_name, menu_item_catagory, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty
FROM ssm_menu a
INNER JOIN
ssm_menu_connection b
on a.menu_id = b.menu_id
INNER JOIN
ssm_menu_items c
on b.menu_item_id = c.menu_item_id
left join 
ssm_menu_order d 
on c.menu_item_id = d.menu_item_id
WHERE d.menu_id = ?
ORDER BY (CASE c.menu_item_catagory
   WHEN 'Starter' 	 THEN 1
   WHEN 'Main' 	 THEN 2
   WHEN 'Dessert' 	 THEN 3
   ELSE 100 END) ASC, c.menu_item_name ASC";




$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $_GET['q']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($mname, $mprice, $mitname, $mitcat, $miid, $mid, $jid, $qty);

echo"<form action='actions/update-menu-action.php' method='post'>";
echo "<button type='submit' name='select_menu_update_button' class='btn btn-primary float-right mb-2'>Update</button>";

echo "<table class='table table-striped table-hover table-bordered mt-3'>";
$first = true;
while($stmt->fetch()){
    if($first) {

echo "<tr>";
echo "<th class='text-center' colspan='5'><div>".$mname." - £".$mprice."</div></th>";
echo "</tr>";
echo "<tr class='text-center'><th>Course</th><th>Dish</th><th>Notes</th><th>Quantity</th></tr>";
	$first = NULL;
}
	echo "<tr>";
	echo "<td class=''>" . $mitcat . "</td>";
	echo "<td class=''>" . $mitname . "</td>";
	echo "<td><input type='text' /></td>";
	echo "<input type='hidden' name='menuItemId[]' value='".$miid."'>";
	echo "<input name='menuId' type='hidden' value='".$mid."'/>";
	echo "<td><input class='text-center' name='menuItemQty[]' value='".$qty."' /></td>";
	echo "</tr>";
}

echo "</table>";
echo "</form>";


$stmt->close();
		
		} else {

			$sql = "SELECT  menu_name, menu_price, menu_item_name, menu_item_catagory, b.menu_item_id, a.menu_id
FROM ssm_menu a
INNER JOIN
ssm_menu_connection b
on a.menu_id = b.menu_id
INNER JOIN
ssm_menu_items c
on b.menu_item_id = c.menu_item_id
WHERE a.menu_id = ?
ORDER BY (CASE c.menu_item_catagory
   WHEN 'Starter' 	 THEN 1
   WHEN 'Main' 	 THEN 2
   WHEN 'Dessert' 	 THEN 3
   ELSE 100 END) ASC, c.menu_item_name ASC";




$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $_GET['q']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($mname, $mprice, $mitname, $mitcat, $miid, $mid);

echo"<form action='actions/submit-menu-action.php' method='post'>";
echo "<button type='submit' name='select_menu_submit_button' class='btn btn-primary float-right mb-2'>Submit</button>";

echo "<table class='table table-striped table-hover table-bordered mt-3'>";
$first = true;
while($stmt->fetch()){
    if($first) {

echo "<tr>";
echo "<th class='text-center' colspan='5'><div>".$mname." - £".$mprice."</div></th>";
echo "</tr>";
echo "<tr class='text-center'><th>Course</th><th>Dish</th><th>Notes</th><th>Quantity</th></tr>";
	$first = NULL;
}
	echo "<tr>";
	echo "<td class=''>" . $mitcat . "</td>";
	echo "<td class=''>" . $mitname . "</td>";
	echo "<td><input type='text' /></td>";
	echo "<input type='hidden' name='menuItemId[]' value='".$miid."'>";
	echo "<input name='menuId' type='hidden' value='".$mid."'/>";
	echo "<td><input name='menuItemQty[]' /></td>";
	echo "</tr>";
}

echo "</table>";
echo "</form>";


$stmt->close();

		}

For some reason, the reasults are being echo'd twice.

 

 

[requinix]

Code seems fine, so that means the problem is that your query is returning duplicates. You've got a few joins in there, and you're using menu_item_id in multiple places which is suspicious.

What's the source data and what results are you getting?

[Adamhumbug]

Table A - ssm_menu

This is where the menu name and price are defined

menu_id      menu_name      menu_price

      1                     menu 1               22.50

      2                    menu 2                 51.50

 

Table B - ssm_menu_connection

This is where the menu items are connected to a menu

menu_id      menu_item_id       surrogate_id

      1                        1                               1

       1                        4                                2

       2                         1                                3

 

Table C - ssm_menu_item

This is where the individual items for each menu are defined and given a catagory

menu_item_id         menu_item_name         menu_item_catagory

            1                                   soup                              starter

            2                                   pudding                           desert

 

Table D - ssm_menu_order

When the menu items are given a qantity, this is the table that they go into for later use.

job_id        menu_id        menu_item_id         menu_item_qty       surrogate_id

    1                  1                       1                                    100                            1

    1                   1                     2                                      100                           2

 

This is the basic layout of all of the tables that are used in the query.

 

If the user selects menu one and submits the form with quantities for some of the items, when the page reloads i want it to display all items in the menu that the user select but also show the quantities that they submitted.  This will mean that some items have quantities and some do not.  Either way all menu items for that menu should be shown.

I hope this helps.

 

Kind Regards

[Adamhumbug]

Just as an fyi, when i run this in mysql this is what i see.  I changed the ? in the query to be 1.

 

[Adamhumbug]

I have just realised, that i dont have job id in that query.  I need to only select items that have the job id that has been set.  In the current case, i only have one job id in the table but i dont use this variable in the second query

$jid = $_SESSION[current_job_id] 

 

[Barand]

On 4/11/2019 at 11:49 PM, Adamhumbug said:

I am pulling info from my database but for some reason the results are being populated twice.

In your sample output above every row is different, so what do you mean by "results being populated twice"?

[mac_gyver]

the reason you are getting the wrong result is because you are only joining on the menu_item_id for the table D LEFT JOIN condition, but both the menu_id and the menu_item_id are what associates a menu/item with its' quantity in the D table (you probably have the same menu_item_id in more than one menu.) you should also be using a.menu_id in the WHERE clause in both queries.

you should actually have an auto-increment id column in the B table. this will define a menu/item id. you would use this id in the D table.  this will also simplify the form fields since there's only one id involved with each quantity.

next, you don't need two sets of code/queries. the LEFT JOIN with the D table will give the quantity if there is one, or a null (which php will treat as a zero) if there isn't a row for an item.

[Barand]

Youe should be storing the category_id (and not the category name) in the menu_item table. That would also save you from having to do this

CASE c.menu_item_category
   WHEN 'Starter'      THEN 1
   WHEN 'Main'         THEN 2
   WHEN 'Dessert'      THEN 3

 

[Adamhumbug]

Thanks so much for your help.  I will for sure take your suggestions on board and have a look at how i am doing things.  Thanks again Barand, i will take yours on board also.

6 hours ago, mac_gyver said:

next, you don't need two sets of code/queries

How would i go about this without having two sets of code?  Would i have another document with an include?

[mac_gyver]

just use the LEFT JOIN query, with corrected join condition and where clause.

if you fetch all the data from the query into a php array variable and use var_dump(), you will be able to see what result the LEFT JOIN produces when there are and are not corresponding row(s) in the D table.