Forum Moderators: coopster
How does one make a pre-filled drop-down? I've been struggling with this for some time now and am still lost.
Here's the code on my "add_recipe" page:
// create sql statement
// make vendor pull-down menus from recipe_vendor table
$query = "SELECT * FROM recipe_vendor ORDER BY vendor ASC";
$result = @mysql_query ($query);
$pulldown = '<option>0</option>';
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
$pulldown .= "<option value=\"{$row['vendorID']}\">{$row['vendor']}</option>\n";
}
?><tr>
<td align="right">Vendor 1:</td>
<td><select name="vendor1"><?php echo $pulldown;?></select></td>
</tr>
<tr>
<td align="right">Vendor 2:</td>
<td><select name="vendor2"><?php echo $pulldown;?></select>
(optional)</td>
</tr>
<tr>
<td align="right">Vendor 3:</td>
<td><select name="vendor3"><?php echo $pulldown;?></select>
(optional)</td>
</tr>
But on my edit page, I don't know how to create the pre-filled dropdowns that list what, if anything, has been selected on the form.
According to my "PHP & MySql" book, "To preselect a pull-down menu, use selected="selected" (and the example given is this):
<select name="year">
<option value="2002">2002</option>
<option value="2003"> selected=<"selected">2003</option>
</select>
I just don't know how to connect the dots here and make a pre-filled dropdown base on the example given in the book.
It's not coming from a post, it's an edit page that results when a link is clicked that passes the recipe id.
Anyone? Thanks for any help...
<?php
$list_item = (isset($_POST['list_item']))? $_POST['list_item'] : '';
// execute database query and build list:
$select_list = '';
$sql = "SELECT list_item FROM table";
$rows = mysql_query($sql);
if (mysql_num_rows($rows) <> 0) {
while ($row = mysql_fetch_assoc($rows)) {
$select_list .= "<option value=\"" . htmlentities($row['list_item']) . '"';
if ($list_item == $row['list_item']) $select_list .= ' selected="selected"';
$select_list .= '>' . htmlentities($row['list_item']). '</option>';
}
}
mysql_free_result($rows);
?>
<html>
<head>
...
<form>
<select id="list_item" name="list_item">
<option value="0"
<?php if (!$list_item) print ' selected="selected"';?>
>All</option><?php print $select_list . "\n";?>
</select>
...
</html>
the "SELECTED" attribute goes right inside the <option> tag.
This is the method you need:
$selectedone=$_GET['whatever'];
while ($row = mysql_fetch_array ($result)) {
$pulldown .= "<option value='".$row['vendorID']."'";
if ($row['vendorID']=$selectedone){
$pulldown .= " SELECTED";
}
$pulldown .= ">";
$pulldown .= "{$row['vendor']}</option>\n";
}
The output you want looks like this (for example, if option 3 is preselected):
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3' SELECTED>3</option>
<option value='4'>4</option>
<option value='5'>5</option>
I hope that puts you on the right path.
good luck!
There are 3 dropdowns each for vendor and category. The table structure is like this:
The recipe_titles table:
titleID
imagepath
title
ingredients
directions
contributor
approved
date_submitted
The other tables are:
recipes_vendor_link
titleID
vendorID
recipe_vendor
vendorID
vendor
recipes_category_link
titleID
categoryID
recipe_category
categoryID
category
and, for example, a recipe in 3 categories would be listed like this in the recipes_category_link table:
titleID categoryID
1 4
2 6
3 2
3 5
3 6
So, in the example above, the recipe with the title ID of 3 is in three categories, 2, 5, and 6 (and the actual name of the category, like vegetarian, is stored in the category field of the recipes_categories table).
Would I need to somehow loop through the recipes_category_link table to get the categoryID's that are associated with a titleID, and then use what is returned to somehow enter the values in the dropdown on the edit page?
Sorry to be so confused...
Thanks,
kpd
You should only need 3 tables. Vendors, Categories, and Recipes.
I take it Recipes can have one or more vendors and belong to one or more categories.
So, in your "recipes" table, have a column named "vendor" and another named "category".
For example, if the recipe belongs in "desserts", then its category is "15". if it belongs in "desserts" and "apertifs" then it's "15,22". With more categories, you can store the category comma-delimited numbers, an array, such as "15,22,45".
Same goes for the vendors. If a recipe has mutliple vendors, then store them as comma-separated numbers.
It's very easy to split apart a few numbers stored as an array, whereas linked weirdness between tables with a lot of extra primary keys - that gets confusing.
So here's my advice. Shuffle your data around, and get rid of any tables that aren't necessary. Then you won't get so confused when writing your SELECT queries and select options.
Would this be correct, then?
In the recipes table I'd have:
recipeID
vendor
category
imagepath
title
ingredients
directions
contributor
approved
date_submitted
and in the vendor table:
vendorID
vendor
and in the category table:
categoryID
category
Would I need recipeID to be in the category and vendor table? Would that structure be easy to search by vendor or category?
And how would I insert the values from the dropdown as comma-delimited numbers? I originally thought this code (based on PHP and MySql book) would do that, but it doesn't:
// insert values into recipes_vendor_link table
if ($tid > 0) { // if it ran ok// build the query
$query = 'INSERT INTO recipes_vendor_link (titleID, vendorID) VALUES ';
if ($_POST['vendor1'] > 0) {
$query .= "('$tid', '$vendor1'), ";
}
if ($_POST['vendor2'] > 0) {
$query .= "('$tid', '$vendor2'), ";
}
if ($_POST['vendor3'] > 0) {
$query .= "('$tid', '$vendor3'), ";
}
$query = substr ($query, 0, -2); // Chop off last comma and space
Thanks again!
Think of each table as being intelligent and "knowing" certain things and "not knowing" other things. Your recipe knows its vendors and categories. The vendors don't know anything but their own names. The categories don't know anything except their own names.
Because the recipe table is all-knowing, neither the vendors nor categories NEED to know more than they do, and it's actually better that they don't. If you ever want to change the relationship between a recipe and a category, you only have to change it in one place, not many.
To search for recipes by vendor, you ask any recipes to step forward that know the vendor in question.
SELECT * FROM recipes WHERE vendor LIKE '%".$vendorID."%'
// the results for "2" will match
// "2", "12", "23", "11,42", etc
while (loop through results){
// $row is the fetch_array
$vendors=implode(",",$row['vendor']);
// that's turned your comma-separated
// string into an array
if in_array($vendorID,$vendors){
// then here you have a match,
// and you can do something with
// $row['vendor']
}
}
There are probably better methods using mySQL functions for finding numbers in a comma-separated list, but I don't know them offhand and frankly the method above works and it's easy to understand.
This method does not use the database efficiently. But by making the data structures less complicated, the logic is a little easier to follow, and might help your situation.
