Dynamically adding columns to insert into...

fbx

I currently have 2 tables that work together. When a row is put into "sites", a column is created in "vehicles". Then, on the php page the form dynamically changes to request the user what they want in the new column:

$sites = mysql_query("SELECT * FROM sites");

$row_count = mysql_num_rows($sites);
if ($row_count == 0) {
    echo "No Sites Exist!";
} else {

// printing table rows
while ($row = mysql_fetch_array($sites)) {
echo "<tr><td><font style=\"font-size: 8pt; font-family:verdana\">" . $row['site_number'] . "-" . $row['site_name'] . ":</font><td width=\"450\"><select style=\"font-size: 8pt; font-family:verdana\" name=\"site_" . $row['site_id'] . "\">";
echo "<option value=\"0\">No</option>";
echo "<option value=\"1\">Yes</option>";
echo "</select></td></tr>";
} 

mysql_free_result($sites);

}

Now, I just need the page to be able to update the INSERT INTO statements correctly. When the column is created in the vehicles table, it is named "site_#" and # is the site_id from the sites table. Here is the current insert statement for the vehicles:

$query = "INSERT INTO vehicles (vehicle_id, key_number, vehicles_number, department, limit, authorization, check_digit, lockout_status, pin, begin_odometer, last_odometer, total_fuel, max_fuelings_day, total_fuelings) VALUES ('', '$key_number', '$vehicles_number', '$department', '$limit', '$authorization', '$check_digit', '$lockout_status', '$pin', '$begin_odometer', '$last_odometer', '$total_fuel', '$max_fuelings_day', '$total_fuelings')";

So what is the best way to add the new columns onto the end of the insert into statement?

Krik

Use [man]mysql_fetch_field[/man] to retrieve all the column name in a given table.

fbx

Ok, I used mysql_fetch_field to pull the special column names and put them into an array. What would be the best way to put them into the "insert into" statement?

Krik

The example on the [man]mysql_fetch_field[/man] page on php.net has the answer in the while loop.

This is the same thing I just use a for loop (it looks cleaner), and I took out all the other unneeded info, and instead of echoing it I place the values in a string.

$columnstring = '';
for ($i = 0; $i < mysql_num_fields($result); $i++ ) {

$meta = mysql_fetch_field($result, $i);

if ($i != 0) {
    $columnstring .= ", ";
}

$columnstring .= "`" . $meta->name . "`";

}

Then place "$columnstring" in your query