Using a MySQL table-populated PHP dropdown to insert selection into another table

Question

I'm making a web app to help determine whose turn it is to make tea in my office, giving me a focus to learn the use of PHP/MySQL. Apologies for newbie ignorance.

For new users signing up I need to populate the user table with their selections from a dropdown, which is itself populated from a separate table. So when a user signs up, I wan them to select the name of their favourite drink from the dropdown/drinks table and I want the ID of that drink saved in the defaultdrink field of the user table. I also understand this should be done using POST, not GET.

Have so far successfully made a form that populates the DB and have made a dropdown populated from the DB - but no success yet in doing both.

The form page is...

   <?php 
require "insert_dropdown.php";
?>

<table width="300" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td><form name="form1" method="post" action="insert_ac.php">
<table width="100%" border="0" cellspacing="1" cellpadding="3">
<tr>
<td colspan="3"><strong>Sign up to the Tea App</strong></td>
</tr>

<tr>
<td width="71">Name</td>
<td width="6">:</td>
<td width="301"><input name="name" type="text" id="name"></td>
</tr>


<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>

</table>
</form>
</td>
</tr>
</table>

<?php
$dropdown = "<select name='drinkname'>";
while($row = mysql_fetch_assoc($dresult)) {
  $dropdown .= "\r\n<option value='{$row['drinkname']}'>{$row['drinkname']}</option>";
}
$dropdown .= "\r\n</select>";
echo $dropdown;
?>

The form actions are led by insert_ac.php...

<?php

$host=""; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="tea"; // Database name 
$tbl_name="users"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form 
$name=$_POST['name'];
$pref=$_POST['pref']; // Drink preference


// Insert data into mysql 
$sql="INSERT INTO $tbl_name(name, pref)VALUES('$name', '$pref')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful". 
if($result){
echo "Successful";
}

else {
echo "ERROR";
}

// close connection 
mysql_close();
?>

And I'm populating the dropdown using insert_dropdown.php...

<?php

$host=""; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 


// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Write out our query.
$dquery = "SELECT drinkname FROM drinks";
// Execute it, or return the error message if there's a problem.
$dresult = mysql_query($dquery) or die(mysql_error());

// if successfully insert data into database, displays message "Successful". 
if($dresult){
echo "Drink Successful";
echo "<BR />";
}

else {
echo "ERROR";
}

// close connection 
mysql_close();
?>

Am I beyond saving?

Cheers,

Alex

Answer 1

Do not close mysql connection.
Or - even better - store the actual db rows into array and use that array to populate drop-down.
and put your select box inside of the form.

Well if you want yo learn something useful yet simple

config.php

<?php
$host=""; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="tea"; // Database name 

// Connect to server and select database.
mysql_connect($host, $username, $password); 
mysql_select_db($db_name);

// A function! greatest invention since wheel.
function dbgetarr($query){
  $a   = array();
  $res = mysql_query($query);
  if (!$res) {
    trigger_error("dbget: ".mysql_error()." in ".$query);
  } else {
    while($row = mysql_fetch_assoc($res)) $a[]=$row;
  }
  return $a;
}

main page.

<?php
include 'config.php';
$data = dbGetArr("SELECT drinkname FROM drinks");
$tpl = 'tea.tpl.php';
include 'main.tpl.php';

main site template main.tpl.php

<html>
<body>
<?php include $tpl ?>
</body>
</html>

tea page template tea.tpl.php

<table width="300" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td><form name="form1" method="post" action="insert_ac.php">
<table width="100%" border="0" cellspacing="1" cellpadding="3">
<tr>
<td colspan="3"><strong>Sign up to the Tea App</strong></td>
</tr>

<tr>
<td width="71">Name</td>
<td width="6">:</td>
<td width="301"><input name="name" type="text" id="name"></td>
</tr>
<tr>
  <td width="71">Name</td>
  <td width="6">:</td>
  <td width="301"><input name="drink" type="text" id="name">
    <select name="drinkname">
<?php foreach($data as $row)): ?>
      <option value="<?=$row['drinkname']?>"><?=$row['drinkname']?></option>
<?php endforeach ?>
    </select>
  </td>
</tr>
<tr>
  <td colspan="3" align="center">
    <input type="submit" name="Submit" value="Submit">
  </td>
</tr>
</table>
</form>
</td>
</tr>
</table>

insert_ac.php

<?php
include 'config.php';
$tbl_name="users"; // Table name
// Get values from form and formatting them as SQL strings
$name = mysql_real_escape_string($_POST['name']);
$pref = mysql_real_escape_string($_POST['pref']); // Drink preference

// Insert data into mysql 
$sql="INSERT INTO `$tbl_name` (name, pref) VALUES('$name', '$pref')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful". 
if($result){
echo "Successful";
}else {
echo "ERROR";
}