3

I am using mysqli_stmt_bind_param() to create an INSERT statement. For some reason I am getting an error. I used mysqli_error() to see the error message, but it's not especially useful.

Is there a way to just see what query is actually being executed?

the resulting error:

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 'desc,date,expdate,mintix,maxtix,contactname,contactemail,contactphone) VALUES (?' at line 1

burger
  • 5,683
  • 9
  • 40
  • 63
  • 3
    Please show us your code. It's difficult to help you otherwise. – Lotus Notes Mar 28 '10 at 00:13
  • In addition to what byronh commented, you should be able to store your query statement in a variable and then {log, echo, save} the query statement along with the error code. – bdl Mar 28 '10 at 00:14
  • 1
    @bdl I couldn't find a way to convert a prepared statement back to a string (I think what bigmac is asking for). – Brendan Long Mar 28 '10 at 00:15
  • I think Brendan is right, but I added the code. Maybe I just can't see an extra comma or something. – burger Mar 28 '10 at 16:15

2 Answers2

2

According to this answer, it is indeed impossible to get the final generated statement (which is horrible!), but maybe mysqli_report as shown in this question can help you debug your query.

Community
  • 1
  • 1
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
2

Prepared statements created by mysqli_prepare() are server-side prepared statements.
When you execute such a prepared statement only the statement id and the parameters are transferred, not some query string as if you would replace the placeholders by the actual parameters (on the client-side, i.e. your php script).
But you can see the result in the general log of the MySQL server, see Prepared Statement Logging

edit: in your case the preparation of the statement fails because desc is a reserved keyword.
For a list of keywords and how to use them as identifiers (if necessary) see http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

$q = '
  INSERT INTO
    `event`
    (
      `cityid`, `name`, `desc`, `date`,
      `expdate`, `mintix`, `maxtix`,
      `contactname`, `contactemail`, `contactphone`
    )
  VALUES
    (
      ?,?,?,?,
      ?,?,?,
      ?,?,?
    )
';

if ( false===($stmt=mysqli_prepare($dblink, $q)) ) {
  /* 
    in production-code you might not want to reveal
    the error string to each and every user
    ...but for this example and for debugging purposes:
  */
  die('mysqli_prepare failed: '.htmlspecialchars(mysqli_error($dblink)));
}

$rc = mysqli_stmt_bind_param(
  $stmt,
  "issssiisss",
  $city,$name,$desc,$date,
  $expdate,$mintix,$maxtix,
  $contactname,$contactemail,$contactphone
);
if ( false===$rc ) {
  die('mysqli_stmt_bind_param failed: '.htmlspecialchars(mysqli_stmt_error($stmt)));
}


if ( false===mysqli_stmt_execute($stmt) ) {
  die('mysqli_stmt_execute failed: '.htmlspecialchars(mysqli_stmt_error($stmt)));
}

mysqli_stmt_close($stmt);
VolkerK
  • 95,432
  • 20
  • 163
  • 226
  • Ahh, so *that's* the reason why neither mysql nor PDO support this properly. Thanks. – Pekka Mar 28 '10 at 17:06
  • PDO can do both server-side and client-side (emulated) prepared statements (PDO::ATTR_EMULATE_PREPARES). In the latter case it _would be_ possible to print a query string though it doesn't seem to be exposed. – VolkerK Mar 28 '10 at 17:47
  • I thought I've used desc as column name before, but I was wrong. I am surprised phpMyAdmin let me use it. I just tried changing it and phpMyAdmin displayed an error. It still managed to change it and now everything is working. Thanks for all those ways to catch errors. That's really useful. Didn't realize all those were possible. – burger Mar 28 '10 at 18:03