Why the sql could not execute the insert query?

Question

I am trying to execute below code but I keep receiving below error :

"Could not execute the insert query."

It seem like the insert into Employees isn't working.

I am not sure what is missing.

Below is my code:

if(isset($_POST['submit'])) {
    $fname = $_POST['fname'];
    $minitial = $_POST['minitial'];
    $lname = $_POST['lname'];
    $gender = $_POST['gender'];
    $phone = $_POST['phone'];
    $dob = $_POST['dob'];
    $ssn = $_POST['ssn'];
    $address = $_POST['address'];
    $city = $_POST['city'];
    $state = $_POST['state'];
    $zip = $_POST['zip'];
    $email = $_POST['email'];
    $username = $_POST['username'];
    $password = $_POST['password'];

    if($fname == "" || $minitial == "" || $lname == "" || $gender == "" || 
            $phone == "" || $dob == "" || $ssn == "" || $address == "" || 
            $city == "" || $state == "" || $zip == "" || $email == "" || 
            $username == "" || $password == "") {
        echo "All fields should be filled. Either one or many fields are empty.";
        echo "<br/>";
        echo "<a href='register.php'>Go back</a>";} 
    else {
        mysqli_query($mysqli, "INSERT INTO 'Employees'('fname', 'minitial', 'lname', 'gender', 'phone', 'dob', 'ssn', 'address', 'city', 'state', 'zip', 'email', 'username', 'password') VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))")
        or die("Could not execute the insert query.");

            echo "Registration successfully";
            echo "<br/>";
            echo "<a href='login.php'>Login</a>";
        }

Myqli is built for the sole use of binding params, unlike mysql_, so please bind your params before running a query otherwise you're in for a world of hurt. — , Dec 08 '16 at 09:06
I would advice u to start using prepared statements and use `password_hash()` and `password_verify()` for secure passwords — Masivuye Cokile, Dec 08 '16 at 09:23
and where do you actually close the if statement that execute the entire script? — Masivuye Cokile, Dec 08 '16 at 09:29

Rahul · Answer 1 · 2016-12-08T09:26:02.817

3

That's cause you are quoting the table name as pointed below. Don't single quote column names else it's treated as string literal rather a actual table/column name

INSERT INTO 'Employees'

You actually meant to escape it like

INSERT INTO `Employees`

Re-write your INSERT statement to be like

"INSERT INTO `Employees`(`fname`, `minitial`, `lname`, `gender`, `phone`, `dob`, `ssn`, `address`, `city`, `state`, `zip`, `email`, `username`, `password`) VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))"

edited Dec 08 '16 at 09:26

answered Dec 08 '16 at 09:06

Rahul

76,197
13
71
125

It still not working even after I tried this. mysqli_query($mysqli, "INSERT INTO `Employees`(`fname`, `minitial`, `lname`, `gender`, `phone`, `dob`, `ssn`, `address`, `city`, `state`, `zip`, `email`, `username`, `password`) VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))") or die("Could not execute the insert query."); – Jackie Dec 08 '16 at 09:15
@Jackie, what doesn't work? that's not enough ... post the error statement/msg you get. (OR) what happened when you tried? – Rahul Dec 08 '16 at 09:21
I got this message when I ran the code "Could not execute the insert query." it seem that my Insert Into Employees doesn't work and I don't see why that was case. – Jackie Dec 08 '16 at 09:26
@Jackie, last but not least, see edit in answer and try the edited insert statement and see – Rahul Dec 08 '16 at 09:27
I got this an red underlined , echo "Registration successfully"; in my code after I copied and pasted your line of code, It seem like we are heading in the right direction. – Jackie Dec 08 '16 at 09:36
I got a Syntax error unexpected: echo after: "" – Jackie Dec 08 '16 at 09:43
mysqli_query($mysqli, "INSERT INTO `Employees`(`fname`, `minitial`, `lname`, `gender`, `phone`, `dob`, `ssn`, `address`, `city`, `state`, `zip`, `email`, `username`, `password`) VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))") or die("Could not execute the insert query."); echo "Registration successfully"; echo "
"; – Jackie Dec 08 '16 at 09:47

Devsi Odedra · Answer 2 · 2016-12-08T09:11:03.673

0

try this query.

Because you put Table name and field name in single quote.

mysqli_query($mysqli, "INSERT INTO Employees(fname, minitial, lname, gender, phone, dob, ssn, address, city, state, zip, email, username, password) VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))")

edited Dec 08 '16 at 09:11

answered Dec 08 '16 at 09:07

Devsi Odedra

5,244
1
23
37

You really should put a note on this answer other than `try this` also, you should warn them that not binding their params can cause trouble, in case someone google and finds this answer. – Dec 08 '16 at 09:09
I have tried this mysqli_query($mysqli, "INSERT INTO Employees(fname, minitial, lname, gender, phone, dob, ssn, address, city, state, zip, email, username, password) VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))") or die("Could not execute the insert query."); – Jackie Dec 08 '16 at 09:19
than echo your query. and directly run in the phpmyadmin query. – Devsi Odedra Dec 08 '16 at 09:29

score 0 · Answer 3 · answered Dec 08 '16 at 09:08

0

Replace ' into ` or remove ' in filed list in insert query.

So you query will be

mysqli_query($mysqli, "INSERT INTO `Employees`(`fname`, `minitial`, `lname`, `gender`, `phone`, `dob`, `ssn`, `address`, `city`, `state`, `zip`, `email`, `username`, `password`) VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))")

answered Dec 08 '16 at 09:08

Md. Sahadat Hossain

3,210
4
32
55

I have tried with ` and without `, and I still got the same result. – Jackie Dec 08 '16 at 09:23

score 0 · Answer 4 · answered Dec 08 '16 at 09:21

0

The mysqli_error() won't solve your problem, but it will at least tell you where it is. Replace:

mysqli_query($mysqli, "INSERT INTO 'Employees'('fname', 'minitial', 'lname', 'gender', 'phone', 'dob', 'ssn', 'address', 'city', 'state', 'zip', 'email', 'username', 'password') VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))")
or die("Could not execute the insert query.");

with:

mysqli_query($mysqli, "INSERT INTO 'Employees'('fname', 'minitial', 'lname', 'gender', 'phone', 'dob', 'ssn', 'address', 'city', 'state', 'zip', 'email', 'username', 'password') VALUES('$fname', '$minitial', '$lname', '$gender', '$phone', '$dob', '$ssn', '$address', '$city', '$state', '$zip', '$email', '$username', md5('$password'))")
or die(mysqli_error($mysqli));

answered Dec 08 '16 at 09:21

Ben Hillier

2,126
1
10
15

I received: Duplicate entry '' for key 'PRIMARY' – Jackie Dec 08 '16 at 10:00
@Jackie That does what is says on the tin. Your table contains a primary key that is contstrained to always be unique. You're trying to write a value to that column when another row in the table already has that value. Do you know what the primary key is in the table? – Ben Hillier Dec 08 '16 at 10:23
Thank you, table Employees primary key is eID, and it also a foreign key for tables Departments and Manges. – Jackie Dec 08 '16 at 10:42
You don't try to set eID at all. My guess is you haven't defined it to be `AUTOINCREMENT`, so it tries to set it to 0 every time. You have two choices: 1) Make it an `AUTOINCREMENT` field; or 2) Set a value each time! – Ben Hillier Dec 08 '16 at 10:45
Thank you Ben, I'm in the process of adding Autoincrement to my tables now. – Jackie Dec 08 '16 at 11:14
@Jackie Did that solve the problem? – Ben Hillier Dec 09 '16 at 08:02

Why the sql could not execute the insert query?

4 Answers4