我是编程新手,请耐心等待…我正在使用MySQL/PHP创建一个应用程序,其中一部分允许用户从两个下拉菜单中选择位置和房间。有三张桌子,房间、位置和房间位置。我创建了允许用户添加房间到房间表的表单和允许用户添加位置到位置表的表单,这两种表单都很好,因为输入只是一个文本字段,用户可以提交他们喜欢的任何文本。我的问题是第三个表单/表(dropdown_form.php)——这个表有三个字段,id|room_name|location_name。
我试图通过room_location表实现的是:用户将获得一个网页,该网页具有用于选择location_name(源自位置表)的下拉菜单和用于选择room_name(源自房间表)的另一个下拉菜单。从两个下拉列表中选择后,用户将提交房间和位置的组合,并将一个新条目添加到数据库中的room_location表中。
房间位置表示例
ID |位置名称|房间名称
1|医院1|房间1
2|医院1|房间2
3|医院1|房间3
4|医院2|房间1
5|医院2|房间2etc
到目前为止,我已经有了显示两个下拉菜单的表格,但当我选择并提交时,我收到了一个错误,上面写着"错误:键"PRIMARY"的重复条目"。我包含了一些用于测试目的的回声语句,以查看发生了什么,我现在得到了:
输入的位置和房间名称为
阵列([select_location]=>[select_room]=>)
错误:键"PRIMARY"的条目"重复(重复的条目显示为NULL?所以不要认为实际有任何东西被传递到process.php)
看起来(我认为)应该将数据传递给process.php的select有问题,process.php正在处理$_POST语句。我已经包含了我的代码副本,如果能提供任何帮助,我们将不胜感激。
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)
?>
***根据建议进行的更新谢谢你到目前为止的建议,我;感谢大家,我取得了一些进展。使用下面的代码,我现在可以将select_location输出为location_id,将select_room输出为room_name_id,而不是location_name和room_name——你们知道我做错了什么吗?
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>'n";
}
?>
</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>'n";
}
?>
</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)
?>
除了其他人所说的,
在SELECTS上的dropdown_form.php中,要获得下拉列表,请不要为表单中的id值检索id。
SELECT location_name FROM location ORDER BY location
make it
SELECT id,location_name FROM location ORDER BY location desc
然后你可以访问它像:
echo "<option value='"".$row['id']."'">".$row['location_name']."</option>'n";
注意,我也正确格式化了
对第二个select执行相同操作。
在将新行插入数据库时,请确保id是主键并自动递增。查询:
$sql='INSERT INTO room_location (`id`,`location_name`, `room_name`) VALUES ("","'.mysql_real_escape_string($_POST['select_location']).'", "'.mysql_real_escape_string($_POST['select_room']).'")';
请注意,因为我假设您在这篇文章中出于快速目的删除了清理,但请确保您正在根据SQL注入对$_POST
变量进行清理。
话虽如此,试着用以下内容替换您的查询:
$sql="INSERT INTO room_location (location_name, room_name) VALUES (('".$_POST['select_location']."'), ('".$_POST['select_room']."'))";
编辑:
似乎我在这方面有点晚了,因为Blender已经注意到了。
终于自己破解了这个。。。不是最优雅的解决方案,但它有效:D谢谢你的指点。
*更新到process.php**
<?php
//This section retrieves the room name selected on dropdown_form.php page
$room_id = $_POST['select_room']; //temp variable to hold room_name_id
$query = mysql_query("SELECT room_name FROM room_name WHERE room_name_id = $room_id");
while ($row = mysql_fetch_array($query))
{
$room_name = $row['room_name'];
}
//This section retrieves the location name selected on add_room_location_form.php page
$location_id = $_POST['select_location']; //temp variable to hold location_name_id
$query = mysql_query("SELECT location_name FROM location WHERE location_id = $location_id");
while ($row = mysql_fetch_array($query))
{
$location_name = $row['location_name'];
}
//Insert room & location data held in $location_name & $room_name into room_location table
$sql="INSERT INTO room_location (location_name, room_name) VALUES (('$location_name'), ('$room_name'))";
echo "Location and Room name entered was ", $location_name, " & ", $room_name;
//I tried using this INSERT below but it inserts the array indexes instead of elements to location_name, room_name.
//$sql="INSERT INTO room_location (location_name, room_name) VALUES (('".$_POST['select_location']."'), ('".$_POST['select_room']."'))";
?>
尝试在数据库中设置id字段的自动递增