Create dropdown list from another table into my update/delete/add table

Question

Im a newbie and working on a project for school

I have a website that lists foods.

I have an update table that allows me to change and add data.

For the food group field I have it cross reference another table called food_group which has the food_group(name) and an id.

When you view the food data you can see the name that it pulls instead of the ID. On the update page I would like a drop down to be in the place of the ID. So you can see the "friendly" name instead of the ID number, but it has to store the ID not the friendly name in the food table.

Website can be found at http://web.nmsu.edu/~jrortiz/ICT458/FINAL/

The code I have is:

<html>
<head>
</head>
<body>
<?php
$con = mysqli_connect("localhost","user","pw","db");
if (!$con){
die("Can not connect: " . mysql_error());
}


if(isset($_POST['update'])){
$UpdateQuery = "UPDATE food SET food_group='$_POST[Food_group]', food='$_POST[Food]',     ph='$_POST[PH]' WHERE food='$_POST[hidden]'";               
mysql_query($UpdateQuery, $con);
};

if(isset($_POST['delete'])){
$DeleteQuery = "DELETE FROM food WHERE Food='$_POST[hidden]'";          
mysql_query($DeleteQuery, $con);
};

if(isset($_POST['add'])){
$AddQuery = "INSERT INTO food (Food_group, Food, PH) VALUES     ('$_POST[addGroup]','$_POST[addFood]','$_POST[addPH]')";         
mysql_query($AddQuery, $con);
};



$sql = "SELECT * FROM food";
$myData = mysqli_query($con,$sql);
echo "<table border=1>
<tr>
<th>Food Group</th>
<th>Food</th>
<th>PH</th>
<th>Update/Add</th>
<th>Delete</th>
</tr>";
while($record = mysqli_fetch_array($myData)){
echo "<form action=updateFood.php method=post>";
echo "<tr>";
echo "<td><input type='text' name='Food_group' value='$record[food_group]'/></td>";
echo "<td><input type='text' name='Food' value='$record[food]'/></td>";
echo "<td><input type='text' name='PH' value='$record[ph]'/></td>";
echo "<td><input type='submit' name='update' value='update'/></td>";
echo "<td><input type='submit' name='delete' value='delete'/></td>";
echo "<td><input type='hidden' name='hidden' value='$record[food]'/></td>";
echo "</tr>";
echo "</form>";
}
echo "<form action=updateFood.php method=post>";
echo "<tr>";
echo "<td><input type='text' name='addGroup'></td>";
echo "<td><input type='text' name='addFood'></td>";
echo "<td><input type='text' name='addPH'></td>";
echo "<td><input type='submit' name='add' value='add'/></td>";
echo "</tr>";
echo "</form>";
echo "</table>";
mysql_close($con);
?>

</body>
</html>

____________ Update 12/2/13 10:30pm ___________________ Ok so if I create a new php page like the following it will work. However, I have no idea how to combine it into the original above... Can anyone help?

<html>
<head>
</head>
<body>
<?php

// Connect to the database server
$con = mysql_connect("localhost","user","pw");
if (!$con){
die("Can not connect: " . mysql_error());
}
mysql_select_db("db",$con);

$sql2="SELECT id, food_group FROM food_group"; 
$result = mysql_query($sql2,$con) or die(mysql_error());
while ($row = mysql_fetch_array($result)) { 
$type=$row["food_group"];
$options.= '<option value="'.$row['id'].'">'.$row['food_group'].'</option>';
};?>

<SELECT NAME=Food_group>
<OPTION VALUE=0>Choose</OPTION>
<?php echo $options; ?>
</SELECT>
</body>
</html>

Thank you for all your help! Jason

You are open to SQL injection. Please, escape the input data before you put it into the query. — user4035
– user4035, Commented Dec 1, 2013 at 22:42

Drixson Oseña · Accepted Answer · 2013-12-01 22:48:35Z

1

Your script is nice but I just want to point the following:

There's no need to concatenate this

"<td>" . "<input type=text name=Food_group value=" . $record['food_group'] . "         </td>";

you can type it like this:

echo "<td><input type=text name=Food_group value='$record[food_group]'</td>";

also you missed to close your input tag

echo "<td><input type=text name=Food_group value='$record[food_group]' /></td>";

and another is you need to quote your attribute values , see below

echo "<td><input type='text' name='Food_group' value='$record[food_group]'</td>";

Last thing is that you're open to SQL injection, so you should start learning mysqli and prepared statement

answered Dec 1, 2013 at 22:48

Drixson Oseña

3,6313 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

JasonOrtiz Over a year ago

Thank you for those corrections. This is going to make things much better. By using mysqli is there easy ways to make the dropdowns work now?

JasonOrtiz Over a year ago

BTW. I edited my original code with your suggestions. Thank you.

Drixson Oseña Over a year ago

What does dropdown accomplish for? to select a method add/edit/delete?

JasonOrtiz Over a year ago

For example> select (select food_group from food_group where food_group.id = food.food_group) aa, food, ph from food;"; For example the food_group for 'Alfalfa Grass' is id# '16' from the food group table. So on my edit page it shows the number '16' instead of 'Vegetables' which is defined in the food_group table. So I was hoping I could have a drop down menu that would list the friendly name of 'Vegetables' while the database uses the id number to reference the two tables.

JasonOrtiz Over a year ago

If you goto web.nmsu.edu/~jrortiz/ICT458/FINAL You will see links on the left hand side. Food is the list of food whereas update/add foods give your raw data. I would like my update/add foods page to look "more like" my foods page with the friendly names.So I would like to see a dropdown menu for the Food Group option that cross references the user friendly name from the other table but saves the id# instead

|

Collectives™ on Stack Overflow

Create dropdown list from another table into my update/delete/add table

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related