I am working on a CMS for a restaurant guide. The admin section will let users change/add normal restaurant details such as name, phone, description etc... The problem I'm having is figuring out the best way to list a group of check boxes that let's the user select the restaurant category. For example: A restaurant can have multiple categories such as: American food, Chinese, Cafe, Burgers, steaks, fast food etc... I have no problem setting up the database to get this right. The problem I'm running into is what is the best way to query the database and populate the check boxes to be checked or un-checked for a particular restaurant. Hope this makes sense.
I have a table called restaurant_types that has two fields (restaurant_id and category_id). so basically I make the quert of SELECT * FROM restaurant_types WHERE restaurant_id = (whatever the id of the current restaurant I'm on is such as 4) Let's say the query came up with two results of category_id = 9 and category_id = 8 the check box area should end up looking something like this (note that the categories that came up in the query are checked and the categoris are in alphabetical order): <input type="checkbox" name="type[]" value="9" checked="checked" /> American <input type="checkbox" name="type[]" value="12" /> Bakery <input type="checkbox" name="type[]" value="8" checked="checked" /> BBQ <input type="checkbox" name="type[]" value="18" /> Breakfast <input type="checkbox" name="type[]" value="21" /> Brewery <input type="checkbox" name="type[]" value="1" /> Chinese <input type="checkbox" name="type[]" value="13" /> Coffee/Tea <input type="checkbox" name="type[]" value="3" /> Fast Food <input type="checkbox" name="type[]" value="22" /> Fine Dining <input type="checkbox" name="type[]" value="17" /> German Code (markup): Hope this explains it a little better.
This type of table relationship should have 3 tables involved (many-many relationship). So... 'restaurant' table -> 1:many -> 'restaurantcat' table -> many:1 -> categories the middle table restaurantcat will simply contain 2 fields (id from 'restaurant', id from 'categories') the primary key for this table should be set on BOTH fields. Once this is established, you can query the database using a left join (categories => restaurantcat) to show all categories and a calculated field returning true/false, 0/1 or simliar to indicate whether restaurant belongs to particular category. You're looking to return a result set similar to this: catid: 1 | category: american food | belongs: yes catid: 2 | category: chinese | belongs: no catid: 3 | category: cafe | belongs: yes then iterate the result set outputing checked and unchecked boxes where appropriate. When user updates restaurant, execute a delete from restaurantcat where restaurantid = '[restaurantid]' to delete all entries in restaurantcat for particular restaurant. Then use insert statement to insert all checked categories into restarurantcat using category.id and restaurantcat.id .
Ah. I see your 2nd post now. You are on the right track. Where in particular are you having trouble? The left join should be the key to retrieving the data properly.
I have the following three tables: restaurant(id, name,phone,etc...) restaurant_category(id,name) restaurant_types(restaurant_id and category_id) my current query looks like this: $restaurant_query = mysql_query("SELECT * FROM `restaurant` WHERE approved = 'yes' AND id = $_GET[id]"); Code (markup): would I alter this query or ad a new query within the results of this query?
Try this: SELECT `restaurant_category`.`id`, `restaurant_category`.`name`, if(max(`restaurant_types`.`category_id`)is null,0,1) as belongs FROM `restaurant_category` LEFT OUTER JOIN `restaurant_types` ON (`restaurant_category`.`id` = `restaurant_types`.`category_id`) WHERE (`restaurant_types`.`restaurant_id` = '[replace w/ restaurant id]' or `restaurant_types`.`restaurant_id` is null) GROUP BY `restaurant_category`.`id` Code (markup): replace the condition [replace w/ restaurant id] with actual id of restaurant. This will return ALL restaurant types with a 1 if restaurant is member or 0 if not. You can replace the 0,1 with whatever you'd like. 'checked', 'unchecked' might streamline things a bit. Hope this helps.
Mike, Try this instead. Previous stmt had bug in it. Plus, this ones cleaner. SELECT `restaurant_category`.`name`, `restaurant_category`.`id`, if(restaurant_types.category_id IN (Select restaurant_types.category_id from restaurant_types where restaurant_types.restaurant_id= '[insert actual restaurant id here]'),1,0) FROM `restaurant_category` LEFT OUTER JOIN `restaurant_types` ON (`restaurant_category`.`id` = `restaurant_types`.`category_id`) GROUP BY `restaurant_category`.`id` Code (markup):
This is what I have: <?php if($_GET['id'] != ''){ $category_query = mysql_query("SELECT `restaurant_category`.`name`, `restaurant_category`.`id`, if(restaurant_types.category_id IN (Select restaurant_types.category_id from restaurant_types where restaurant_types.restaurant_id= '$_GET[id]'),1,0) FROM `restaurant_category` LEFT OUTER JOIN `restaurant_types` ON (`restaurant_category`.`id` = `restaurant_types`.`category_id`) GROUP BY `restaurant_category`.`id`"); while($category_results = mysql_fetch_assoc($category_query)){ print "<input type=\"checkbox\" name=\"type[]\" value=\"$category_results[id]\" $checked /> $category_results[name]"; } } ?> Code (markup): I'm just getting the (Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource) error. I'm not sure if I'm putting the code in the right spot or not.
Hi Mike, There are several things that need to be changed. However, I'm running late for an appointment. I'll post the corrected code later tonight. Grant
if($_GET['id'] != '') { $id = $_GET['id']; $category_query = mysql_query("SELECT `restaurant_category`.`name`, `restaurant_category`.`id`, if(restaurant_types.category_id IN (Select restaurant_types.category_id from restaurant_types where restaurant_types.restaurant_id= '$id'),1,0) as ischecked FROM `restaurant_category` LEFT OUTER JOIN `restaurant_types` ON (`restaurant_category`.`id` = `restaurant_types`.`category_id`) GROUP BY `restaurant_category`.`id` ORDER BY `restaurant_category`.`name` ASC"); while($category_results = mysql_fetch_assoc($category_query)) { $checked = $category_results['ischecked']==1 ? "checked = \"checked\"":""; $typeid = $category_results['id']; $restid = $id; print "<input type=\"checkbox\" name=\"posteddata[$typeid][ischecked]\" $checked />". $category_results['name']; print "<input type=\"hidden\" name=\"posteddata[$typeid][typeid]\" value='$typeid'>"; print "<input type=\"hidden\" name=\"posteddata[$typeid][restid]\" value='$restid'>"; } } Code (markup): You'll still need to submit and process the form. I suggest you print_r the $posteddata array to see structure of submitted data. You should be able to iterate the array and perform the necessary inserts. Don't forget to first delete the entries for the restaurant_id before inserting.
Still getting: "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in test2.php on line 26" here's the whole page: <?php $description="$_POST[desc]"; require_once('../constants.php'); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>TDN.com » Local Restaurant Guide</title> <style type="text/css"> </style> </head> <body> <div style="width:780px; margin:5px auto;"> <?php if($_GET['id'] != '') { $id = $_GET['id']; print "SELECT `restaurant_category`.`name`, `restaurant_category`.`id`, if(restaurant_types.category_id IN (Select restaurant_types.category_id from restaurant_types where restaurant_types.restaurant_id= '$id'),1,0) as ischecked FROM `restaurant_category` LEFT OUTER JOIN `restaurant_types` ON (`restaurant_category`.`id` = `restaurant_types`.`category_id`) GROUP BY `restaurant_category`.`id` ORDER BY `restaurant_category`.`name` ASC"; $category_query = mysql_query("SELECT `restaurant_category`.`name`, `restaurant_category`.`id`, if(restaurant_types.category_id IN (Select restaurant_types.category_id from restaurant_types where restaurant_types.restaurant_id= '$id'),1,0) as ischecked FROM `restaurant_category` LEFT OUTER JOIN `restaurant_types` ON (`restaurant_category`.`id` = `restaurant_types`.`category_id`) GROUP BY `restaurant_category`.`id` ORDER BY `restaurant_category`.`name` ASC"); while($category_results = mysql_fetch_assoc($category_query)) { $checked = $category_results['ischecked']==1 ? "checked = \"checked\"":""; $typeid = $category_results['id']; $restid = $id; print "<input type=\"checkbox\" name=\"posteddata[$typeid][ischecked]\" $checked />". $category_results['name']; print "<input type=\"hidden\" name=\"posteddata[$typeid][typeid]\" value='$typeid'>"; print "<input type=\"hidden\" name=\"posteddata[$typeid][restid]\" value='$restid'>"; } } ?> </div> </body> </html> Code (markup): Here's the error I get when I try running the SQL query in PHPMyAdmin" "#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 'Select restaurant_types.category_id from restaurant_types WHERE" I'm very lost with your query, waaaaay above my head!
the query works for me using similar table structure I put together for a client. Double check your table names and field names and ensure they are correct in your query. I can't do this for you as I don't have access to your db What version of mysql are you using? It is possible (although unlikely) the version your using doesn't fully support the subquery syntax (embedded select). I believe version 4.1+ fully support subqueries.
I got the following to work for me with the desired results: <?php if($_GET['id'] != ''){ $id = $_GET['id']; $restaurant_query = mysql_query("SELECT * FROM restaurant WHERE approved = 'yes' AND id = $id"); $num_rows = mysql_num_rows($restaurant_query); if($num_rows == 0){ print "We don't currently have any restaurants that match your criteria."; }else{ while($restaurant_results = mysql_fetch_assoc($restaurant_query)){ //SELECT patients.name, meds.name, meds.dose FROM patients LEFT JOIN meds ON patients.med_id = meds.med_id $category_query = mysql_query("SELECT * FROM restaurant_category ORDER BY name ASC"); while($category_results = mysql_fetch_assoc($category_query)){ $checked = ""; $check_query = mysql_query("SELECT * FROM restaurant_types WHERE category_id=$category_results[id] AND restaurant_id = $id"); while($check_results = mysql_fetch_assoc($check_query)){ $checked = "checked=\"checked\""; } print "<input type=\"checkbox\" name=\"type[]\" value=\"$category_results[id]\" $checked /> $category_results[name]"; } } } } ?> Code (markup): You can see final product here:http://tdn.com/restaurant/admin/test3.php?id=15
Excellent! glad to see you got er done Looks like maybe mysql was choking on the subquery. Good work splitting it into two queries.