generate checkbox from mysql and write checked value in mysql

[eltonpiko]

hi i trying to work on a lunch monthly order form.what i want to do is create a table just like a calendar with 30 cells label 1 to 30 days and in each cell the same food item with checkbox next to it generated from mysql.

the user put their username and checked their desired food in each cell for the 30days and the data is stored in another table in the database

so there will be a table with all food item which will be update and will use these value to generate the checkbox in each date cell on my table

another table to store the food choice of each user

can anyone tell me how i can do this.how do i generate checkbox from the database?

[Albert Pinto]

Even I posted a similar query few days back........ And I'm still stuck with it...

Some people advised me to store the checked value in only one column.... I mean as a string using the implode function.... and while retreiving data use SELECT to recover it and then explode that string and echo it.......

[Albert Pinto]

I don't know wether external site links are allowed in this forum but still this may help....

http://corpocrat.com/2009/05/24/how-to-store-and-retreive-checkbox-value-in-mysql-with-php/

[eltonpiko]

my bigest problem is how should i design my database to hold the name and value of each checkboxitem and echo the name and checkbox into my table.

[eltonpiko]

ok i got this from a search which is supose to generatecheck box from mysql but for some resone im getting an error

<?
include("includes/db.php");

MYSQL_CONNECT(HOST,USER,PASS) OR DIE("Unable to connect to database"); 
@mysql_select_db(DB) or die( "Unable to select database");

//$query=("select * from categories");
$query=("select * from categories order by category, category desc");

$result=mysql_query($query) or die ("Unable to Make the Query:" . mysql_error() ); 

while($row=mysql_fetch_array($result)){ 
$category = @$row["category"];
$cid = @$row["cid"];

echo "<input type=\"checkbox\" name=\"$category\" value=\"$cid\"> $category:";
echo "<br>";
}
?>

First of all get your db sorted. Will the same foods be available every day or will they rotate? Say bananas every Mon, Tue, Thu and oranges every Wed, Fri?

TABLE: food
food_id (PK, int)
category_id (FK, tinyint) - optional don't know whether you need this
food_label (varchar 30) - checkbox label
price - optional
available (int, 3) - optional - bits of days, e.g. Sun=1,Mon=2,Tue=4,Wed=8 etc, so 11 = Sun,Mon,Wed
(or if req'd you could always set up separate day fields)

OPTIONAL TABLE: category
category_id (PK,tinyint)
category_label (varchar, 20)
category_order (tinyint, 2) - optional - for ordering display purposes only

TABLE: users
user_id (PK, int)
username (etc all the usual user table stuff)

TABLE: orders
order_id (PK, int)
user_id (FK, int)
for_date (int) - unix timestamp OR for_date (date) - unix date
ordered_on (int) or (date)
(you could have optional cost, tax, total etc here if required - or even a dedicated set of tables for pricing)

TABLE: orderitems
order_id (FK,int)
food_id (FK,int)
quantity (tinyint, 2)

OPTIONAL TABLE: closeddates
closed_id (PK, id)
from_date (int or date)
to_date (int or date)

OK, once data is sorted, you can start designing forms around it.
Your calendar idea sounds fine in theory, but it could become a monster if you have 28-31 x whole menus on display at any given time.
An easier solution may be to have a popup menu or better still a div area displaying a form which changes as you select/click a date from say a datepicker or html table calendar.

You could have just a simple vanilla send form to server / follow calendar link to reload page every time OR
you can use js/ajax to easy some of the server load / user wait time. jQuery would be perfect for this.

Anyway, few ideas.

As for creating a checkbox list:

$ordered_array = array(); //just temporary to avoid error on checking
$checks = ''; //assume all orders for the day are in $ordered_array
while($data =  mysql_fetch_assoc($result)){
    $checked = (in_array($data['food_id'],$ordered_array)) ? ' checked="checked"' : '';
    $checks .= "\n<input type=\"checkbox\" id = \"food_{$data['food_id']}\" name=\"food[{$data['food_id']}]\"$checked /> <label for=\"food_{$data['food_id']}\">{$data['food_label']}</label>";
}
//then at the appropriate place:
echo $checks;