使用PHP和MySQL从数据库中删除或编辑表单数据时出错
问题描述：
我的代码中的所有内容似乎都能正常工作，除非我尝试编辑/删除已查询的数据。使用PHP和MySQL从数据库中删除或编辑表单数据时出错
删除什么也不做......和编辑给了我如下的错误：
当前代码：（登录/数据库信息丢失隐私的原因）：
// Create connection
$db = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $cdb->connect_error);
}
echo "Connected successfully <br>";
$thisPHP = $_SERVER['PHP_SELF'];
if (!isset($_POST['btnEdit'])) {
echo <<<EOT
<form action="$thisPHP" method="POST"><br>
Employer ID: <input type="text" name="eid"><br>
Company Name: <input type="text" name="compname"><br>
Address: <input type="text" name="address"><br>
Phone: <input type="text" name="phone"><br>
Email: <input type="text" name="email"><br>
Position: <input type="text" name="position"> Description: <input type="text" name="description">
Location: <select name="state" id="state">
<option value="">--</option>
<option value="AL">AL</option>
<option value="AK">AK</option>
<option value="AZ">AZ</option>
<option value="AR">AR</option>
<option value="CA">CA</option>
<option value="CO">CO</option>
<option value="CT">CT</option>
<option value="DE">DE</option>
<option value="FL">FL</option>
<option value="GA">GA</option>
<option value="HI">HI</option>
<option value="ID">ID</option>
<option value="IL">IL</option>
<option value="IN">IN</option>
<option value="IA">IA</option>
<option value="KS">KS</option>
<option value="KY">KY</option>
<option value="LA">LA</option>
<option value="ME">ME</option>
<option value="MD">MD</option>
<option value="MA">MA</option>
<option value="MI">MI</option>
<option value="MN">MN</option>
<option value="MS">MS</option>
<option value="MO">MO</option>
<option value="MT">MT</option>
<option value="NE">NE</option>
<option value="NV">NV</option>
<option value="NH">NH</option>
<option value="NJ">NJ</option>
<option value="NM">NM</option>
<option value="NY">NY</option>
<option value="NC">NC</option>
<option value="ND">ND</option>
<option value="OH">OH</option>
<option value="OK">OK</option>
<option value="OR">OR</option>
<option value="PA">PA</option>
<option value="RI">RI</option>
<option value="SC">SC</option>
<option value="SD">SD</option>
<option value="TN">TN</option>
<option value="TX">TX</option>
<option value="UT">UT</option>
<option value="VT">VT</option>
<option value="WA">WA</option>
<option value="WV">WV</option>
<option value="WI">WI</option>
<option value="WY">WY</option>
<option value="DC">DC</option>
</select> <br>
Skill: <select name="skill1">
<option value="Skill1">Skill 1</option>
<option value="Skill2">Skill 2</option>
<option value="Skill3">Skill 3</option>
</select>
Experience: <select name="experience1">
<option value="0">0</option>
<option value="1">1-3</option>
<option value="3">3-5</option>
<option value="5">5-10</option>
<option value="10">10+</option>
</select><br>
Skill: <select name="skill2">
<option value="Skill1">Skill 1</option>
<option value="Skill2">Skill 2</option>
<option value="Skill3">Skill 3</option>
</select>
Experience: <select name="experience2">
<option value="0">0</option>
<option value="1">1-3</option>
<option value="3">3-5</option>
<option value="5">5-10</option>
<option value="10">10+</option>
</select><br>
Skill: <select name="skill3">
<option value="Skill1">Skill 1</option>
<option value="Skill2">Skill 2</option>
<option value="Skill3">Skill 3</option>
</select>
Experience: <select name="experience3">
<option value="0">0</option>
<option value="1">1-3</option>
<option value="3">3-5</option>
<option value="5">5-10</option>
<option value="10">10+</option>
</select><br>
Need: <input type="radio" name="need" value="Urgent" checked> Urgent (<1 Month) <br>
<input type="radio" name="need" value="Soon"> Soon (1-3 Months) <br>
<input type="radio" name="need" value="Upcoming"> Upcoming (3+ Month) <br>
<input type="submit" name="btnAdd" value="Add"> <br>
<hr>
</form>
EOT;
}
$eid = $_POST["eid"];
$compname = $_POST["compname"];
$address = $_POST["address"];
$phone = $_POST["phone"];
$email = $_POST["email"];
$position = $_POST["position"];
$description = $_POST["description"];
$location = $_POST["state"];
$skill1 = $_POST["skill1"];
$experience1 = $_POST["experience1"];
$skill2 = $_POST["skill2"];
$experience2 = $_POST["experience2"];
$skill3 = $_POST["skill3"];
$experience3 = $_POST["experience3"];
$need = $_POST["need"];
if (isset($_POST['btnAdd'])){
if (!empty($eid)){
// Form sql string
$location = $_POST["state"];
$sql = "insert into Employer (EmployerID, CompName, Address, Phone, Email, PosTitle, Description, Location, Skill1, Experience1, Skill2, Experience2, Skill3, Experience3, Need) values ('$eid', '$compname', '$address', '$phone', '$email', '$position', '$description', '$location', '$skill1', '$experience1', '$skill2', '$experience2', '$skill3', '$experience3', '$need')";
if ($db->query ($sql) == TRUE) {
echo "Record added <br>";
}
}
}
if (isset($_POST['btnDelete'])) {
$eid= $_POST['eid'];
$sql = "delete from Employer where eid='$eid'";
echo mysql_error();
if ($db->query ($sql) == TRUE)
{
echo "Record deleted <br>";
}
}
if (isset($_POST['btnEdit'])) {
$sql = "select * from Employer where eid='$eid'";
if (($result = $db->query ($sql)) == TRUE)
{
while($row = $result->fetch_assoc()) {
$eid = $row["EmployerID"];
$compname = $row["CompName"];
$address = $row["Address"];
$phone = $row["Phone"];
$email = $row["Email"];
$position = $row["PosTitle"];
$description = $row["Description"];
$location = $row["Location"];
$skill1 = $row["Skill1"];
$experience1 = $row["Experience1"];
$skill2 = $row["Skill2"];
$experience2 = $row["Experience2"];
$skill3 = $row["Skill3"];
$experience3 = $row["Experience3"];
$need = $row["Need"];
}
}
echo <<<EOE
<form action="$thisPHP" method="POST">
Employer ID: <input type="text" name="eid" value='$eid'>
Company Name: <input type="text" name="compname" value='$compname'>
Address: <input type="text" name="address" value='$address'>
Phone: <input type="text" name="phone" value='$phone'>
Email: <input type="text" name="email" value='$email'><br>
Position: <input type="text" name="position" value='$position'>
Description: <input type="text" name="description" value='$description'>
Location: <select name="state" id="state" value='$location'>
<option value="">--</option>
<option value="AL">AL</option>
<option value="AK">AK</option>
<option value="AZ">AZ</option>
<option value="AR">AR</option>
<option value="CA">CA</option>
<option value="CO">CO</option>
<option value="CT">CT</option>
<option value="DE">DE</option>
<option value="FL">FL</option>
<option value="GA">GA</option>
<option value="HI">HI</option>
<option value="ID">ID</option>
<option value="IL">IL</option>
<option value="IN">IN</option>
<option value="IA">IA</option>
<option value="KS">KS</option>
<option value="KY">KY</option>
<option value="LA">LA</option>
<option value="ME">ME</option>
<option value="MD">MD</option>
<option value="MA">MA</option>
<option value="MI">MI</option>
<option value="MN">MN</option>
<option value="MS">MS</option>
<option value="MO">MO</option>
<option value="MT">MT</option>
<option value="NE">NE</option>
<option value="NV">NV</option>
<option value="NH">NH</option>
<option value="NJ">NJ</option>
<option value="NM">NM</option>
<option value="NY">NY</option>
<option value="NC">NC</option>
<option value="ND">ND</option>
<option value="OH">OH</option>
<option value="OK">OK</option>
<option value="OR">OR</option>
<option value="PA">PA</option>
<option value="RI">RI</option>
<option value="SC">SC</option>
<option value="SD">SD</option>
<option value="TN">TN</option>
<option value="TX">TX</option>
<option value="UT">UT</option>
<option value="VT">VT</option>
<option value="WA">WA</option>
<option value="WV">WV</option>
<option value="WI">WI</option>
<option value="WY">WY</option>
<option value="DC">DC</option>
</select> <br>
Skill: <select name="skill1" value='$skill1'>
<option value="Skill1">Skill 1</option>
<option value="Skill2">Skill 2</option>
<option value="Skill3">Skill 3</option>
</select>
Experience: <select name="experience1" value='$experience1'>
<option value="0">0</option>
<option value="1">1-3</option>
<option value="3">3-5</option>
<option value="5">5-10</option>
<option value="10">10+</option>
</select><br>
Skill: <select name="skill2" value='$skill2'>
<option value="Skill1">Skill 1</option>
<option value="Skill2">Skill 2</option>
<option value="Skill3">Skill 3</option>
</select>
Experience: <select name="experience2" value='$experience'>
<option value="0">0</option>
<option value="1">1-3</option>
<option value="3">3-5</option>
<option value="5">5-10</option>
<option value="10">10+</option>
</select><br>
Skill: <select name="skill3" value='$skill3'>
<option value="Skill1">Skill 1</option>
<option value="Skill2">Skill 2</option>
<option value="Skill3">Skill 3</option>
</select>
Experience: <select name="experience3" value='$experience3'>
<option value="0">0</option>
<option value="1">1-3</option>
<option value="3">3-5</option>
<option value="5">5-10</option>
<option value="10">10+</option>
</select><br>
Need: <input type="radio" name="need" value="Urgent" checked value='$name'> Urgent (<1 Month) <br>
<input type="radio" name="need" value="Soon" value='$name'> Soon (1-3 Months) <br>
<input type="radio" name="need" value="Upcoming" value='$name'> Upcoming (3+ Month) <br>
<input type="submit" name="Update" value="Update"> <br>
<hr>
</form>
EOE;
}
if (isset($_POST['Update'])) {
$link = mysql_connect("0.0.0.0", $username, $password) or die (mysql_error());
$db_selected = mysql_select_db('tschauss', $link);
if (!$db_selected) {
die ('Can\'t use foo : ' . mysql_error());
}
mysql_query(" UPDATE Employer SET eid='$eid' , compname='$compname' , address='$address' , phone='$phone' , email='$email' , position='$position' , description= '$description' , location= '$state' , skill1= '$skill1' , experience1= '$experience1' , skill2= '$skill2' , experience2= '$experience2' , skill3= '$skill3' , experience3= '$experience3' , need= '$need' WHERE eid='$eid'") or die ("Query has failed us" . mysql_error());
echo "Record updated <br>";
mysql_close($link);
$sql = "update into Employer (EmployerID, CompName, Address, Phone, Email, PosTitle, Description, Location, Skill1, Experience1, Skill2, Experience2, Skill3, Experience3, Need) values ('$eid', '$compname', '$address', '$phone', '$email', '$position', '$description', '$location', '$skill1', '$experience1', '$skill2', '$experience2', '$skill3', '$experience3', '$need')";
if ($db->query ($sql) == TRUE)
{
mysql_query(" UPDATE Employer SET eid='$eid' , compname='$compname' , address='$address' , phone='$phone' , email='$email' , position=$'position' , description= '$description' , location= '$state' , skill1= '$skill1' , experience1= '$experience1' , skill2= '$skill2' , experience2= '$experience2' , skill3= '$skill3' , experience3= '$experience3' , need= '$need' WHERE eid='$eid'");
echo "Record updated 2 <br>";
}
}
$sql = "SELECT EmployerID, CompName, Address, Phone, Email, PosTitle, Description, Location, Skill1, Experience1, Skill2, Experience2, Skill3, Experience3, Need FROM Employer";
$result = $db->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "EmployerID: " . $row["EmployerID"] . " - Company Name: " . $row["CompName"] . " - Address: " . $row["Address"] . " - Phone: " . $row["Phone"] . " - Email: " . $row["Email"] . "<br>" . "Position Title: " . $row["PosTitle"] . " - Description: " . $row["Description"] . " - Location: " . $row["Location"] . " - Skill 1: " . $row["Skill1"] . " - Experience 1: " . $row["Experience1"] . " - Skill 2: " . $row["Skill2"] . " - Experience 2: " . $row["Experience2"] . " - Skill 3: " . $row["Skill3"] . " - Experience 3: " . $row["Experience3"] . " - Need: " . $row["Need"];
echo " <form action=\"$thisPHP\" method='post' style=\"display:inline\" >";
echo "<input type='hidden' name='eid' value='$eid'>";
echo "<input type='submit' name='btnEdit' value='Edit'> ";
echo "<input type='submit' name='btnDelete' value='Delete'> </form>" . "<br>";
}
} else {
echo "0 results";
}
$db->close();
?>
（代码结束）
如果你可以请查看我的代码并给我任何反馈，非常感谢！
（employer.sql） - （现节录数据，个人信息是NULL）：
所有的-- phpMyAdmin SQL Dump
-- version 4.0.9deb1.lucid~ppa.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 30, 2017 at 01:31 PM
-- Server version: 5.5.52-0ubuntu0.12.04.1-log
-- PHP Version: 5.3.10-1ubuntu3.25
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `NULL`
--
-- --------------------------------------------------------
--
-- Table structure for table `Employer`
--
CREATE TABLE IF NOT EXISTS `Employer` (
`EmployerID` int(60) NOT NULL,
`CompName` varchar(60) NOT NULL,
`Address` varchar(20) NOT NULL,
`Phone` int(10) NOT NULL,
`Email` varchar(30) NOT NULL,
`PosTitle` varchar(30) NOT NULL,
`Description` varchar(100) NOT NULL,
`Location` varchar(35) NOT NULL,
`Skill1` varchar(20) NOT NULL,
`Experience1` int(10) NOT NULL,
`Skill2` varchar(20) NOT NULL,
`Experience2` int(11) NOT NULL,
`Skill3` varchar(20) NOT NULL,
`Experience3` int(11) NOT NULL,
`Need` varchar(15) NOT NULL,
PRIMARY KEY (`EmployerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `Employer`
--
INSERT INTO `Employer` (`EmployerID`, `CompName`, `Address`, `Phone`, `Email`, `PosTitle`, `Description`, `Location`, `Skill1`, `Experience1`, `Skill2`, `Experience2`, `Skill3`, `Experience3`, `Need`) VALUES
(0, '', '', 0, '', '', '', '', '', 0, '', 0, '', 0, '');
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */
;
答
首先，使用准备好的语句，防止SQL注入。 我们您的错误：
表用人单位有没有列EID：
CREATE TABLE IF NOT EXISTS `Employer` (
`EmployerID` int(60) NOT NULL,
`CompName` varchar(60) NOT NULL,
`Address` varchar(20) NOT NULL,
`Phone` int(10) NOT NULL,
`Email` varchar(30) NOT NULL,
`PosTitle` varchar(30) NOT NULL,
`Description` varchar(100) NOT NULL,
`Location` varchar(35) NOT NULL,
`Skill1` varchar(20) NOT NULL,
`Experience1` int(10) NOT NULL,
`Skill2` varchar(20) NOT NULL,
`Experience2` int(11) NOT NULL,
`Skill3` varchar(20) NOT NULL,
`Experience3` int(11) NOT NULL,
`Need` varchar(15) NOT NULL,
PRIMARY KEY (`EmployerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
你指的列名被命名为EmployerID。
所以你的发言必须是delete from Employer where EmployerID=$eid。
而且由于employerId是一个int而不是字符字段，所以不需要单引号。
同样的错误是在以下select声明。
然后你混合mysqli_* API和mysql_*（mysql_errr()）。将其更改为mysqli_error
+0
啊，是的，我现在看到了那个错误。谢谢。我现在能够删除一条记录，之后没有任何反应。我还更新了编辑函数：// $ sql =“select * from Employer where EmployerID ='$ eid'”; //我假设这是由于我如何编写代码。 – Null
该问题自行解释，您的表中没有“eid”列。 – hassan
错误是不言自明的。该列不存在于您的表格中 – Akintunde007
不要只将您的整个应用程序转储到此处。也请**请**了解'for'循环和数组。你已经重复了至少两次状态选择器。这可以用正确的方法清理成四行代码。 – tadman