Mysqli "Prepare statement" is not creating the Object

Question

I'm a but unused to Mysqli, and I am having a problem with the following code..

$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
     if (mysqli_connect_errno()) {
        printf("DB error: %s", mysqli_connect_error());
        exit();
     }

     $query = "INSERT INTO employee(id, name, age, address, phone, email, department,
         designation, joindt, terminate, salary, deduction, tds, pf)
         VALUES (:id, :name, :age, :address, :phone, :email, :department,
         :designation, :joindt, :terminate, :salary, :deduction, :tds, :pf)";

     $ins = $mysql->prepare($query);
     if(!ins){
            echo "prepare failed\n";
      echo "error: ", $mysql->error, "\n";
      echo "OBJECT NOT CREATED";
      return;

     }

Upon running this code, I get the following errors in my browser :

( ! ) Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' in C:\wamp\www\payroll\new_backend.php on line 40

( ! ) mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join, terminate, salary, deduction, tds, pf) VALUES (:id, :name, :age, ' at line 2 in C:\wamp \www\payroll\new_backend.php on line 40

I guess the PREPARE statement is not creating the $ins object. Any help ?

I assume you know that the SQL syntax error is the reason behind the object not being created... :-? — Álvaro González, Apr 06 '11 at 11:03
@Alvaro - I'm using the syntax from this tutorial - http://net.tutsplus.com/tutorials/databases/sql-for-beginners/ Can you tell me the correct syntax please ? Or a page where I can learn ? — Hrishikesh Choudhari, Apr 06 '11 at 11:05
Did you get the code from the tutorial you link? It doesn't even mention the mysqli extension. — Álvaro González, Apr 06 '11 at 11:11

Pekka · Accepted Answer · 2011-04-06T11:12:43.497

5

join is a reserved word in mySQL. You will either need to enclose it in backticks:

`join`

or - better - change the column's name.

Additionally, it looks like mysqli doesn't support PDO-style :fieldname bindings. Check out the example in the manual on prepare().

I can't test this right now but the correct syntax should go something like this (abbreviated):

$id = 10;
$name = "John Doe";

$query = "INSERT INTO employee(id, name) values (?, ?)";
$query->bind_param("i", $id);
$query->bind_param("s", $name);

edited Apr 06 '11 at 11:12

answered Apr 06 '11 at 10:56

Pekka

442,112
142
972
1,088

yeah.. I changed it to joindt just now.. with the same errors. – Hrishikesh Choudhari Apr 06 '11 at 11:00
@Hrishikesh Is `:fieldname` valid in mysqli in the first place? That is PDO notation, no idea whether mysqli supports it too. Where are you getting the values from? – Pekka Apr 06 '11 at 11:03
Oh Yeah..! Thanks a lot.. So I was mixing up Mysqli and PDO notations.. hehe.. Thanks.. I solved it with your help.. Below is the solution.. – Hrishikesh Choudhari Apr 06 '11 at 11:19
@Pekka: Just 1 last thing.. When I'm binding the DATE param, which datatype should I use ? 'i' or 's' ? – Hrishikesh Choudhari Apr 06 '11 at 11:28
@Hrisikesh according to this: http://stackoverflow.com/questions/805828/using-mysqli-bind-param-with-date-and-time-columns, it's `s` – Pekka Apr 06 '11 at 11:29
@Hrisikesh can you show the date you are using? It'll need to be in mySQL format: `YYYY-MM-DD` – Pekka Apr 06 '11 at 11:40
@Pekka: On the client side, I used the jQuery UI's DateTime picker, and it returns the date in the form of "04/26/2011".. Hmm.. So I got to convert this to the MySQL format.. – Hrishikesh Choudhari Apr 06 '11 at 11:44
1

@Hrishikesh yup. IIRC, the date picker can convert the date in your desired format automatically. – Pekka Apr 06 '11 at 11:45

score 1 · Answer 2 · answered Apr 06 '11 at 11:10

1

Look at the manual page for mysqli::prepare():

http://es.php.net/mysqli_prepare

Prepared statements expect ? as place-holders, rather than :foo.

answered Apr 06 '11 at 11:10

Álvaro González

142,137
41
261
360

score 1 · Answer 3 · edited Apr 06 '11 at 11:38

Hereis the solution I arrived at, after Pekka's help.. :)

 $mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
     if (mysqli_connect_errno()) {
        printf("DB error: %s", mysqli_connect_error());
        exit();
     }

     $query = "INSERT INTO employee(name, age, address, phone, email, department,
         designation, joindt, terminate, salary, deduction, tds, pf)
         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";

     $ins = $mysql->prepare($query);
     if(!$ins){
            echo "prepare failed\n";
      echo "error: ", $mysql->error, "\n";
      return;

     }

     $ins->bind_param('sisisssiiiiii', $data['name'], $data['age'], $data['address'],
             $data['phone'], $data['email'], $data['department'], $data['designation'],
             $data['joindate'], $data['terminationdate'], $data['salary'], $data['leave_deduction'], $data['tds'], $data['pf']);

     $ins->execute();

I'm glad you didn't need to read the manual :-P — Please use the `{}` toolbar button to format source code. I've done it for you this time. — Álvaro González, Apr 06 '11 at 11:39

Mysqli "Prepare statement" is not creating the Object

3 Answers3