dynamic drop down - multiple colums

[missingcolor123]

I am using the following code to generate a dynamic drop down menu from a column in table1(name) - and then inserting the selected option into table2.

What I am trying to do is, select data from 2 columns from table1(name,age) and then store and insert both of those values into table2

 

I am hoping someone can help me out or at least point me in the rigth direction. ..I would like to insert into table 2 the age that matches the person selected by the drop down menu from table1.

 

For example - if from the drop down menu I choose John - when the form is sumbitted i want to insert into table2 John's name and age (based on what is stored in table1, in this case John,22)

 

...Any ideas???

 

table1

id|name|age|score

01-john-22-1547

02-jane-22-1245

 

table2

id|county|name|age

 

 

the dynamic drop down is generated from table1 (name) with following code

 

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

<table width="100%" border="0" cellpadding="5" cellspacing="0">



<?php

require_once('sql.php');

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);



$query="SELECT name FROM table1  ORDER BY name ASC";

$result = mysqli_query ($dbc,$query) or die(mysqli_error());



$dropdown = "<select name='name'>";

while($row = mysqli_fetch_array($result)) {

$dropdown .= "\r\n<option value='{$row['name']}'>{$row['name']}</option>";

}

$dropdown .= "\r\n</select>";

echo $dropdown;

?>



     	<tr>

          		<td height="50" align="right">

               		<label for="scheduled_time">Country</label><br/>

	</td>

          		<td align="left">

		<input type="text" id="country" name="country" class="input" maxlength="30"" />

                </td>

      		</table>

      		<p></p>

    	<input type="submit" value="Add Info" name="submit" />

</form>

 

I am inserting into table2 with the following code

 

<?php
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

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

$county = mysqli_real_escape_string($dbc, trim($_POST['country']));

$name = mysqli_real_escape_string($dbc, trim($_POST['name']));

$query = "INSERT INTO table2 (country, name) VALUES ('$country', '$name'')";
$result = mysqli_query($dbc, $query);

if (!$result) {

printf("query error : <br/> %s\n", mysqli_error($dbc));

}



if ($result) {

echo 'Success';

}



      // close dbc

      mysqli_close($dbc);

      exit();

      }

?>

 

 

 

 

 

 

[trq]

Why on earth would you want to duplicate your data in the database?

[missingcolor123]

the data that is stored in table1 - will be changing on a regular basis

when i create the record in table2 - i want it to store what table1 was showing at the time

 

... not sure if that makes sense, but just playing around. what would be a better/proper way of doing it? i am very new to php