Error when deleting or editing form data from database using PHP and MySQL -
everything in code seems work fine, except when try edit/delete queried data.
delete nothing... , edit gives me error shown below:
current code: (login/database information missing privacy reasons):
// create connection $db = new mysqli($servername, $username, $password, $dbname); // check connection if ($db->connect_error) { die("connection failed: " . $cdb->connect_error); } echo "connected <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"> (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 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 employer eid='$eid'"; echo mysql_error(); if ($db->query ($sql) == true) { echo "record deleted <br>"; } } if (isset($_post['btnedit'])) { $sql = "select * employer 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'> (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' eid='$eid'") or die ("query has failed us" . mysql_error()); echo "record updated <br>"; mysql_close($link); $sql = "update 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' eid='$eid'"); echo "record updated 2 <br>"; } } $sql = "select employerid, compname, address, phone, email, postitle, description, location, skill1, experience1, skill2, experience2, skill3, experience3, need 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(); ?> (end of code)
if please view code , give me feedback, appreciated!
(employer.sql) - (data redacted, personal information null):
-- phpmyadmin sql dump -- version 4.0.9deb1.lucid~ppa.1 -- http://www.phpmyadmin.net -- -- host: localhost -- generation time: mar 30, 2017 @ 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 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 table `employer` -- insert `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 */ ;
first of all, use prepared statements prevent sql injection. error:
the table employer has no column 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; the column name refering named employerid.
so statement must delete employer employerid=$eid.
and because employerid int not character field not need single quotes arround.
the same error in following select statement.
then mix mysqli_* api , mysql_* (mysql_errr()). change mysqli_error
Comments
Post a Comment