php - 使用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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@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 CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */

;

最佳答案

首先，使用准备好的语句来防止SQL注入。
现在您的错误：

表Employer没有列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。

而且由于loyeeId是一个int而不是一个字符字段，因此您不需要使用单引号将其引起来。

以下select语句中存在相同的错误。

然后，将mysqli_* API和mysql_*（mysql_errr()）混合在一起。将其更改为mysqli_error