Dynamic Form Help for PHP and MySQL

Question

The code below works as far as inserting records from a file into MySQL, but it only does so properly if the columns in the file are already ordered the same way as in the database. I would like for the user to be able to select the drop down the corresponds to each column in their file to match it up with the columns in the database (the database has email address, first name, last name). I am not sure how to accomplish this. Any ideas?

    <?php

$lines =file('book1.csv');

foreach($lines as $data)
{
list($col1[],$col2[],$col3[])
= explode(',',$data);
}

$i = count($col1);

if (isset($_POST['submitted'])) {

DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', 'password');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'csvimport');

// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);



for($d=1; $d<$i; $d++) {


$q = "INSERT into contacts (email, first, last) VALUES ('$col3[$d]', '$col1[$d]', '$col2[$d]')";
$r = @mysqli_query ($dbc, $q);

}
}

echo "<form action =\"handle2.php\" method=\"post\">Email<br />
<select name =\"email\">
<option value='col1'>$col1[0]</option>
<option value='col2'>$col2[0]</option>
<option value='col3'>$col3[0]</option>
</select><br /><br />
First Name <br />
<select name=\"field2\">
<option value='col1'>$col1[0]</option>
<option value='col2'>$col2[0]</option>
<option value='col3'>$col3[0]</option>
</select><br /><br />
Last Name <br />
<select name=\"field3\">
<option value='col1'>$col1[0]</option>
<option value='col2'>$col2[0]</option>
<option value='col3'>$col3[0]</option>
</select><br /><br />
<input type=\"submit\" name=\"submit\" value=\"Submit\" />

<input type=\"hidden\" name=\"submitted\" value=\"TRUE\" />

</form>";


?>

Answer 1

Going from what you already have, you'll probably want to do something like this...

$table_name = "contacts";

// You'll want the array to look like this:
$cols_in_csv_file = array(
   'col_header_1',
   'col_header_2',
   'col_header_3',
);

$csv_select_menu = '<select name="csv_cols[]">';
foreach ($cols_in_csv_file as $col) {
   $csv_select_menu .= '<option value="' . htmlentities($col) . '">' . $col . '</option>';
}
$csv_select_menu .= '</select>';

$result = mysqli_query("SHOW COLUMNS FROM " . $table_name, $q);
$fields = array();
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
   $fields[] = $row['Field'];
}

echo '<table><tr>';
foreach ($fields as $field) {
   echo '<td>' . $field . $csv_select_menu . '</td>';
}
echo '</tr></table>';