I am having some trouble understanding how to get my 1 to many PHP query to run correctly.
Currently I have 2 Tables in MYSQL Database
I have a MYSQL query like this:
$sql_deli_meat="SELECT * FROM deli_items,deli_items_quantity WHERE ingredient_type = 'deli_meat' ORDER BY sort ASC";
PHP Query
<?php while($rows=mysql_fetch_array($result_deli_meat)){ ?>
<input type="checkbox" name="deli_meat[]" id="<? echo $rows['itemname']; ?>" value="<? echo $rows['label']; ?>"/>
<select name="deli_meat[]">
<option value="<? echo $rows['qty']; ?>"><? echo $rows['qty']; ?></option>
</select>
<?php } ?>
I'm trying to get each check box input returned from DELI_ITEMS to show the quantity items from DELI_ITEMS_QUANTITY in a select box. Both have a common field called "Ingredient_Type"
How can I tweek my MYSQL Query to show this?
My initial idea was to do something like below, but didn't work
$sql_deli_meat="SELECT * FROM deli_items JOIN deli_items_quantity ON deli_items.ingredient_type=deli_items_quantity.ingredient_type WHERE enabled = 1 AND deli_items.ingredient_type = 'deli_meat' ORDER BY sort ASC";
I think you misunderstand mysql joins.
The syntax is:
**SELECT** {YOUR COLUMNS LISTED WITH ,} **FROM** {YOUR BASE TABLE (the ONE in your one to many) **INNER JOIN** {YOUR OTHER TABLE} **ON** {BASETABLENAME.column = OTHERTABLE.column}
So in your example, it seems you don't have any primary keys and foreign keys. Lets create some, in-fact lets make the entire example better.
DELI_ITEMS [id, item_name]
INGREDIENTS [id, name]
DELI_ITEMS_INGREDIENTS [deli_item_id, ingredients_id, quantity]
Now you have done some normalisation. Your base list of items and ingredients are in separates lists and you have a link table where you can select ingredients in your items. The quantity is stored in the link table.
Lets look at the SQL for a select.
SELECT di.name 'item', i.name 'ingredient', dii.quantity
FROM deli_items di
INNER JOIN deli_items_ingredients dii ON di.id= dii.deli_item_id
INNER JOIN ingredients i ON i.id = dii.ingredients_id
This will give you the list of all deli items and the ingredients as:
item, ingredient, quantity
To explain the above, the letters after the table names are alias' to make the query easier to read. INNER JOIN means we must have a record in each table to show results. The ON statement tells SQL what are the identifying columns to join on. The quotes in the select line are alias's for the columns which makes it easier as two tables use "name" in this example.
Now to manage ingredients and quantities you only update one table and it makes your data much easier to manage.
--
There are lots of tutorials on this online and this is only a very basic example. I suggest you stop, google and read a few watch youtube about SQL tables, normalisation and querying.
That and look into "PDO" and "PARAMETRISED QUERIES" and if your accepting user input to your queries "SQL INJECTION" before you go any further.
Try this query
SELECT *
FROM deli_items AS i
JOIN deli_items_quantity AS q ON i.ingredient_type=q.ingredient_type
WHERE enabled = 1 AND i.ingredient_type = 'deli_meat'
ORDER BY i.ingredient_type ASC
Anyway, is not so clear how the tables look, due you dont specify the columns your are using like enabled = 1...
Also would be nice to test the query direcrly in MySql Workbench or dump what php is geting.