Hi,
I am trying to insert some data from one mysql table into another table but I am only able to insert data which is included in the post. How do I include other data from the original table which I used to create a drop down list. For example I have selected student_id from the student table and this is inserted fine into the new table, but I also want to insert first_name and last_name into the new table. Is there any way I can include this in the POST? My code is below. I would appreciate any help I can get. Thanks
<?php
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to
MySQL: ' . mysqli_connect_error());
$query = "SELECT student_id, first_name, last_name FROM student WHERE trainer_id = '" . $_SESSION['trainer_id'] . "'";
$data = mysqli_query($dbc, $query);
if (isset($_POST['submit'])) {
// Grab the profile data from the POST
$student_id = mysqli_real_escape_string($dbc, trim($_POST['student_id']));
$first_name = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
$last_name = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
$date = mysqli_real_escape_string($dbc, trim($_POST['date']));
$break = mysqli_real_escape_string($dbc, trim($_POST['break']));
$m_tea = mysqli_real_escape_string($dbc, trim($_POST['m_tea']));
$lunch = mysqli_real_escape_string($dbc, trim($_POST['lunch']));
$a_tea = mysqli_real_escape_string($dbc, trim($_POST['a_tea']));
$la_tea = mysqli_real_escape_string($dbc, trim($_POST['la_tea']));
$dinner = mysqli_real_escape_string($dbc, trim($_POST['dinner']));
}
if (!empty($date)){
$query = "INSERT INTO eating (student_id, first_name, last_name, date, break, m_tea, lunch, a_tea, la_tea, dinner)".
"VALUES ('$student_id', '$first_name', '$last_name', '$date', '$break', '$m_tea', '$lunch', '$a_tea', '$la_tea', '$dinner')";
mysqli_query($dbc, $query)
or die('error querying database');
}
echo '<form method="post" action="' . $_SERVER['PHP_SELF'] . '">';
echo '<fieldset><legend>Food Intake</legend>';
echo '<select name="student_id" select id = "multiple">';
// printing the list box select command
while($nt=mysqli_fetch_array($data)){//Array or records stored in $nt
echo "<option value='$nt[student_id]'>$nt[first_name] $nt[last_name]</option>";
/ Option values are added by looping through the array /
}
echo '</select><br />';// Closing of list box
?>
<label for="date">Date:</label>
<input type="text" id="date" name="date" value="<?php if (!empty($date)) echo $date; else echo 'YYYY-MM-DD'; ?>" /><br />
<div id = "eating">
<h2>EATING HABITS</h2>
<label for="break">Breakfast:</label>
<select id="break" name="break">
<option value="" <?php if (!empty($break) &&$break == 'N') echo 'selected = "selected"'; ?>></option>
<option value="W" <?php if (!empty($break) &&$break == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
<option value="L" <?php if (!empty($break) &&$break == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
</select><br />
<label for="m_tea">Morning Tea:</label>
<select id="m_tea" name="m_tea">
<option value="W" <?php if (!empty($m_tea) &&$m_tea == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
<option value="L" <?php if (!empty($m_tea) &&$m_tea == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
<option value="N" <?php if (!empty($m_tea) &&$m_tea == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>
</select><br />
<label for="lunch">Lunch:</label>
<select id="lunch" name="lunch">
<option value="W" <?php if (!empty($lunch) &&$lunch == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
<option value="L" <?php if (!empty($lunch) &&$lunch == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
<option value="N" <?php if (!empty($lunch) &&$lunch == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>
</select><br />
<label for="a_tea">Afternoon Tea:</label>
<select id="a_tea" name="a_tea">
<option value="W" <?php if (!empty($a_tea) &&$a_tea == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
<option value="L" <?php if (!empty($a_tea) &&$a_tea == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
<option value="N" <?php if (!empty($a_tea) &&$a_tea == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>
</select><br />
<label for="la_tea">Late Afternoon Tea:</label>
<select id="la_tea" name="la_tea">
<option value="W" <?php if (!empty($la_tea) &&$la_tea == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
<option value="L" <?php if (!empty($la_tea) &&$la_tea == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
<option value="N" <?php if (!empty($la_tea) &&$la_tea == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>
</select><br />
<label for="dinner">Dinner:</label>
<select id="dinner" name="dinner">
<option value="W" <?php if (!empty($dinner) &&$dinner == 'W') echo 'selected = "selected"'; ?>>Ate Well</option>
<option value="L" <?php if (!empty($dinner) &&$dinner == 'L') echo 'selected = "selected"'; ?>>Ate Little</option>
<option value="N" <?php if (!empty($dinner) &&$dinner == 'N') echo 'selected = "selected"'; ?>>Did Not Eat</option>
</select><br />
</div>
<?php
echo '</fieldset>';
echo '<input type="submit" value="Save Eating Data" name="submit" />';
echo '</form>';
mysqli_close($dbc);
?>
</div>
<?php
// Insert the page footer
require_once('footer.php');
?>
