I'm new to programming so please bear with me...I'm using MySQL/PHP to create an application where part of it allows users to select a location and room from two drop down menus. There are three tables, room, location and room_location. I have created forms that allow user to add a room to room table and a form to allow them to add a location to location table - these both work fine as the input is just a text field and the user can submit any text they like. My problem is with the third form/table (dropdown_form.php)- this table has three fields, id | room_name | location_name.
What I am trying to achieve with the room_location table is this: The user will get a webpage that has a dropdown menu for selecting a location_name (which is derived from the location table) and another dropdown menu for selecting the room_name (which is derived from the room table). After selecting from both dropdowns the user will submit this combination of room and location and a new entry will be added to the room_location table in the database.
example of room_location table
ID | location_name | room_name
1 | Hospital1 | room1
2 | Hospital1 | room2
3 | Hospital1 | room3
4 | Hospital2 | room1
5 | Hospital2 | room2 etc
So far I've got the form for this to display both of the dropdown menus but when I select and submit I get an error saying "Error: Duplicate entry '' for key 'PRIMARY' " I included some echo statements for testing purposes to see what is happening, I'm now getting:
Location and Room name entered was
Array ( [select_location] => [select_room] => )
Error: Duplicate entry '' for key 'PRIMARY' (duplicate entry appears to be NULL? so don't think anything is actually being passed to process.php)
It appears that there is something wrong (I think) with the selects that are supposed to be passing the data to process.php which is handling the $_POST statements. I've included a copy of my code, any help at all with this would be greatly appreciated.
dropdown_form.php
<html>
<head>
<title>Testing Dropdown form</title>
</head>
<body>
<?php
//make connection to the database
mysql_connect ("localhost", "root", "password") or die ('Database connection ERROR: ' . mysql_error());
mysql_select_db ("my_database");
?>
<fieldset style="width:30%" align="center"><legend><b>Add New Location/Room Combination</b></legend>
<table align="center" cellspacing="0" cellpadding="10" border="0">
<tr>
<td align="center" valign="middle">
<b> Location Name: </b>
</td>
<td align="center" valign="middle">
<b> Room Name: </b>
</td>
</tr>
<tr>
<td align="center" valign="middle">
<!-- create form to submit data process.php -->
<form name="room_location_form" action="process.php" method="post">
<select name="select_location">
<?php
//create and run a query that selects all the locations to create an options list
$loc_query = "SELECT location_name FROM location ORDER BY location_name";
$loc_result = mysql_query($loc_query);
while ($row = mysql_fetch_array($loc_result, MYSQL_ASSOC))
{
echo "<option value=$row[id]>$row[location_name]</option>";
}
?>
</select>
</td>
<td>
<?php
//create and run a query that selects all the rooms to create an options list
$room_query = "SELECT room_name FROM room_name ORDER BY room_name";
$room_result = mysql_query($room_query);
?>
<select name="select_room">
<?php
while ($row = mysql_fetch_array($room_result, MYSQL_ASSOC))
{
echo "<option value=$row[id]>$row[room_name]</option>";
}
?>
</select>
</td>
<!--Add a submit button -->
<td align="center" valign="middle">
<input type="submit" value="Add Location/Room">
</td>
</tr>
</form>
</table>
</fieldset>
<br>
<br>
<hr width=50%>
<br>
<table border=1 align=center cellspacing=1>
<tr>
<th>Location Name</th>
<th>Room Name</th>
</tr>
<?php
//build query to display a list of all current locations and rooms
$query = mysql_query("select * from room_location ORDER BY location_name, room_name");
//return the array and loop through each row
while ($row = mysql_fetch_array($query)) {
$location_name = $row['location_name'];
$room_name = $row['room_name'];
?>
<tr>
<th><?php echo $location_name;?></th>
<th><?php echo $room_name;?></th>
</tr>
<?php } //this ends the loop
?>
process.php
<?php
//make connection
$conn = mysql_connect ("localhost", "root", "password");
if (!$conn)
{
die ('Database connection ERROR: ' . mysql_error());
}
$db = mysql_select_db ("my_database");
//Add new Location and Room Name combination - sent from form
$sql="INSERT INTO room_location (location_name, room_name) VALUES (('$_POST[select_location]'), ('$_POST[select_room]'))";
echo "Location and Room name entered was ", $_POST['select_location'], " & ", $_POST['select_room'];
?>
<br> <!-- Line break just to see what is being output when testing -->
<?php
print_r($_POST); //added for testing purposes to see what is being POSTed
?>
<br> <!-- Line break just to see what is being output when testing -->
<?php
if (!mysql_query($sql,$conn))
{
die ('Error: ' . mysql_error());
}
echo "Location and Room name entered was ", $_POST['select_location'], " & ", $_POST['select_room'];
mysql_close($conn)
?>
***UPDATES MADE AS SUGGESTED** Thanks for the suggestions so far, I;ve made some progess thanks to you all. Using code below I now get select_location outputting as location_id and select_room outputting as room_name_id instead of the location_name and room_name - any ideas what I'm doing wrong guys?
dropdown_form.php
<?php
//make connection to the database
mysql_connect ("localhost", "root", "password") or die ('Database connection ERROR: ' . mysql_error());
mysql_select_db ("my_database");
?>
<fieldset style="width:30%" align="center"><legend><b>Add New Location/Room Combination</b></legend>
<table align="center" cellspacing="0" cellpadding="10" border="0">
<tr>
<td align="center" valign="middle">
<b> Location Name: </b>
</td>
<td align="center" valign="middle">
<b> Room Name: </b>
</td>
</tr>
<tr>
<td align="center" valign="middle">
<!-- create form to submit data process.php -->
<form name="room_location_form" action="process.php" method="post">
<select name="select_location">
<?php
//create and run a query that selects all the locations to create an options list
$loc_query = "SELECT location_id,location_name FROM location ORDER BY location_name";
$loc_result = mysql_query($loc_query);
while ($row = mysql_fetch_array($loc_result, MYSQL_ASSOC))
{
echo "<option value=\"".$row['location_id']."\">".$row['location_name']."</option>
";
}
?>
</select>
</td>
<td>
<?php
//create and run a query that selects all the rooms to create an options list
$room_query = "SELECT room_name_id,room_name FROM room_name ORDER BY room_name";
$room_result = mysql_query($room_query);
?>
<select name="select_room">
<?php
while ($row = mysql_fetch_array($room_result, MYSQL_ASSOC))
{
echo "<option value=\"".$row['room_name_id']."\">".$row['room_name']."</option>
";
}
?>
</select>
</td>
<!--Add a submit button -->
<td align="center" valign="middle">
<input type="submit" value="Add Location/Room">
</td>
</tr>
</form>
</table>
</fieldset>
<br>
<br>
<hr width=50%>
<br>
<table border=1 align=center cellspacing=1>
<tr>
<th>Location Name</th>
<th>Room Name</th>
</tr>
<?php
//build query to display a list of all current locations and rooms
$query = mysql_query("select * from room_location ORDER BY location_name, room_name");
//return the array and loop through each row
while ($row = mysql_fetch_array($query)) {
$location_name = $row['location_name'];
$room_name = $row['room_name'];
?>
<tr>
<th><?php echo $location_name;?></th>
<th><?php echo $room_name;?></th>
</tr>
<?php } //this ends the loop
?>
process.php
<?php
//make connection
$conn = mysql_connect ("localhost", "root", "password");
if (!$conn)
{
die ('Database connection ERROR: ' . mysql_error());
}
$db = mysql_select_db ("my_database");
//Add new Location and Room Name combination - sent from form
$sql="INSERT INTO room_location (location_name, room_name) VALUES (('".$_POST['select_location']."'), ('".$_POST['select_room']."'))";
?>
<br> <!-- Line break just to see what is being output when testing -->
<?php
print_r($_POST); //added for testing purposes to see what is being POSTed
?>
<br> <!-- Line break just to see what is being output when testing -->
<?php
if (!mysql_query($sql,$conn))
{
die ('Error: ' . mysql_error());
}
echo "Location and Room name entered was ", [$_POST['select_location']], " & ", $_POST['select_room'];
mysql_close($conn)
?>
