4 Replies - 549 Views - Last Post: 26 June 2012 - 06:17 PM Rate Topic: -----

#1 sonalmac  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 83
  • Joined: 24-June 11

Why this update prepared statement is not working?

Posted 26 June 2012 - 12:17 PM

I have following update statement in one of the script, and it is not working. I tried to debug as much as I can without success. What can be the problem?

$rating = 4;
$q = "UPDATE user_book_trn  SET book_rating = ? WHERE user_id = 1 AND member_id=24 AND book_id=120";
    $r = mysqli_prepare($dbc, $q) or trigger_error("Query: $q\n<br />" . mysqli_error($dbc));
    mysqli_bind_param($r, 'i', $br);
    
    $br = (int) $rating;
    //echo "\n rating : " . $br;
    mysqli_stmt_execute($r);
    if(mysqli_stmt_affected_rows($r)==1) {
        echo "Thanks for submitting your rating for the book. We appreciate it!";
    } else {
        echo "error";
        echo '<p>' . mysqli_stmt_error($r) . '</p>';
    }


The query runs OK in phpmyAdmin with the given values and also for other values in tables. But this script always prints "error" message, but never shows any mysqli_stmt_error.

This post has been edited by sonalmac: 26 June 2012 - 12:29 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Why this update prepared statement is not working?

#2 mccabec123  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 233
  • Joined: 03-March 11

Re: Why this update prepared statement is not working?

Posted 26 June 2012 - 12:41 PM

Before you even begin to solve this, stop using mysqli() and start using PHP data objects for database queries etc.

Reasons:

-Queries are cached
-Enhanced protection against SQL Injection
-Prepared statements
-Many more functions
-Database independent

Here's a tutorial for you:

PDO Tutorial

This post has been edited by mccabec123: 26 June 2012 - 12:43 PM

Was This Post Helpful? 1
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 793
  • View blog
  • Posts: 1,679
  • Joined: 30-January 09

Re: Why this update prepared statement is not working?

Posted 26 June 2012 - 03:45 PM

It looks like he's using the PDO component to mysqli, so it should be fine in terms of security. I haven't seen those functions before though, so it might be a little abstracted.

@the OP, You've tried to bind a value to a parameter before instantiating the value. Notice lines 4-6?
mysqli_bind_param($r, 'i', $br);
$br = (int) $rating;



Try this instead:
mysqli_bind_param($r, 'i', (int)$rating);


Was This Post Helpful? 0
  • +
  • -

#4 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Why this update prepared statement is not working?

Posted 26 June 2012 - 04:16 PM

View Poste_i_pi, on 26 June 2012 - 06:45 PM, said:

It looks like he's using the PDO component to mysqli, so it should be fine in terms of security. I haven't seen those functions before though, so it might be a little abstracted.

"PDO component to mysqli?" Huh? He's just using the procedural syntax to the MySQLi extension. It's the same extension as the object-oriented version and has exactly the same capabilities, it just looks a bit more...baroque.

And as you said, it's perfectly safe. If it's working for you, there is absolutely no reason to dump it in favor of PDO.

View Postsonalmac, on 26 June 2012 - 03:17 PM, said:

But this script always prints "error" message, but never shows any mysqli_stmt_error.

Well, there probably is no error. I tried that exact same code on a test table and it worked perfectly. So your code is fine.

The problem is most likely with your data. If the query succeeds, but doesn't update any rows (e.g. because no rows matched the WHERE clause), then mysqli_stmt_affected_rows() will return zero. The way you've coded it, that will cause your script to print "error".
Was This Post Helpful? 1
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 793
  • View blog
  • Posts: 1,679
  • Joined: 30-January 09

Re: Why this update prepared statement is not working?

Posted 26 June 2012 - 06:17 PM

View PostAdaHacker, on 27 June 2012 - 10:16 AM, said:

"PDO component to mysqli?" Huh? He's just using the procedural syntax to the MySQLi extension. It's the same extension as the object-oriented version and has exactly the same capabilities, it just looks a bit more...baroque.

Right you are [tip_of_the_hat]. These 12 hour days and 7am starts are not doing my brain much good :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1