Need help writing data from a mySQL query to a new table
I am trying to read data from a few mySQL database tables. Each table is just one column of data. A single value from each table will be selected via a pull-down menu. There are 3 tables. The 3 selected values from the 3 tables will then be written collectively to a new table containing 3 columns + 1 additional column for a 'quantity' attribute.
Currently I have been able to read the 3 tables and have the data be listed in separate pull-down menus. I don't have the quantity attribute added yet.This is where I need some help.
Example:
database: objects
table1: color: red, green, blue, yellow
table2: state: california, wyoming, oklahoma, florida
table3: transportation: car, truck, moped, skateboard
So, if I query the database and select 'red' and 'florida' and 'car' from my 3 pull-down menus which resulted from the 'objects' database query, how do I do the following:
1. Write these selected values to a new table called table4 - assuming table4 exists, to get:
table4:
color: red
state: florida
transportation: car
2. I'll need for the user to manually enter a number in a 'quantity' field in a form of some kind. I'm not sure how to combine this function next to the pull-down menus.
So, I would end up with something like this in table4:
table4:
quantity: 25
color: red
state: florida
transportation: car
If you guys can help me that would be great.
Here is what I've got so far. I had to use 'distinct' in my query since there may actually be duplicates in each table.
Thanks,
Angus
-------------------------------------------------------------------------------
$db_host = "localhost";
$db_username = "supervisor";
$db_pass = "test";
$db_name = "objects";
$db_table = "table1";
$db_table2 = "table2";
$db_table3 = "table3";
$db_table4 = "table4";
mysql_connect("$db_host","$db_username","$db_pass") or die ("Could not connect to mySQL");
echo "successful connection to mysql <br />";
@mysql_select_db($db_name) or die ("No database");
echo "connected to database: $db_name <br /><br />";
$sql = "SELECT distinct color FROM table1";
$result = mysql_query($sql);
echo "<select name='color'>";
while ($row = mysql_fetch_array($result)) {
echo "<option value='" . $row['color'] . "'>" . $row['color'] . "</option>";
}
echo "</select>";
$sql = "SELECT distinct state FROM table2";
$result = mysql_query($sql);
echo "<select name='state'>";
while ($row = mysql_fetch_array($result)) {
echo "<option value='" . $row['state'] . "'>" . $row['state'] . "</option>";
}
echo "</select>";
$sql = "SELECT distinct transportation FROM table3";
$result = mysql_query($sql);
echo "<select name='transportation'>";
while ($row = mysql_fetch_array($result)) {
echo "<option value='" . $row['transportation'] . "'>" . $row['transportation'] . "</option>";
}
echo "</select>";
?>