Syntax for if/else statement if insert was successful in a PDO prepared statement

Question

I'm trying to switch from mySql statements to PDO prepared statements, but I'm having trouble figuring out the correct syntax for the if/else statements that I have to use if the insert was successful (which were previously if($result) {...}).

I know that $stmt->execute(); returns true on success or false on failure, but I haven't been able to determine how to set the statement up to act on that.

The new code (PDO prepared statement)

$gender = $_POST['gender'];  
if ($gender==="female" ) {
try {      
   $stmt = $conn->prepare('INSERT INTO customer_info (fname...) VALUES(:fname...)');
   $stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
   $stmt->execute();   
    } catch(PDOException $e) {
  echo $e->getMessage();
}

This is the rest of the original if ($gender==="female") function

$result = @mysql_query($qry);    
    if($result) {          
      $qry="SELECT * FROM customer_info WHERE user_name='$_POST['user_name']' AND password='$_POST['password']'";
      $result=mysql_query($qry);          
if($result) {
    if(mysql_num_rows($result) == 1) {
        //user_name Successful
        session_regenerate_id();
        $member = mysql_fetch_assoc($result);
        $_SESSION['SESS_USER_ID'] = $member['user_id'];
        session_write_close();
        header("location: flatter_iframe.html");
        exit();
    }else {        
        header("location: login_failed.html");
        exit();
    }

I've deleted most of the variables in order to simplify things (since the code is the same)

Jonathan Spiller · Accepted Answer · 2012-12-19T07:24:34.363

6

There are a number of ways you can check whether an INSERT worked correctly.

1. Return value from $stmt->execute()

As you said, $stmt->execute(); returns true on success or false on failure. So you can use:

$result = $stmt->execute();
if ($result) {...}

PDO Execute documentation here

2. Row Count

rowCount will return the number of rows afffected by a query. After a successful insert, this should be 1.

$stmt->execute();
$affected_rows = $stmt->rowCount();
if ($affected_rows == 1) {...}

PDO rowCount documentation here

3. Last Inserted Id

If your table has an ID column, you can return the ID of the last inserted row using lastInsertId().

$stmt->execute();
$newCustomerInfoId = $pdo->lastInsertId();
if ($newCustomerInfoId) {...}

Note: You must call lastInsertId on the PDO object, not the $stmt.

PDO lastInsertId documentation here

edited Dec 19 '12 at 07:24

answered Dec 12 '12 at 11:39

Jonathan Spiller

1,885
16
25

Thank you for laying it out so clearly :-) It's amazing how it can be even more confusing to figure this out then it was to first learn php ;-) Just to make sure I understood you correctly, I changed both instances of "$result=mysql_query($qry);" to "$result=$stmt->execute();" and changed "if(mysql_num_rows($result) == 1)" to "$affected_rows = $stmt->rowCount(); if ($affected_rows == 1)". Did I get that right? Also, can I leave the rest of the code as is? I know it can be tough to read code in these comments, so I also put the new code in a fiddle :-) http://jsfiddle.net/chayacooper/ujV6T/1/ – Chaya Cooper Dec 12 '12 at 18:25
Also, in general, you should break your code down into simple segments, and check the output of each segment before adding them together. var_dump is useful for this, so is firePHP for console debugging. – Jonathan Spiller Dec 12 '12 at 23:20
Thank you so much!!! I'd love to see the code, but there's an internal server error for that link. Would you mind reloading it when you get a chance? – Chaya Cooper Dec 12 '12 at 23:49
It took me a little while to get everything working, but it looks like it is :-D And I think I’m getting the hang of which statements need to be rewritten to use with PDO :-) It was inserting every entry twice and I discovered that changing $selectResult = $stmt->execute(); if($selectResult) {…} to if($qry) {…}, so just wanted to make sure that was ok; and I ran into a little difficulty with a mail() function but I think I got that right. Here’s my new code: http://snipt.org/ygigi6 – Chaya Cooper Dec 19 '12 at 00:47
The thing that’s still confusing me is how to reuse bindParam statements, names and values (in this case I have a separate INSERT statement if $gender is male). I’m assuming that there’s a way to do that, but when I tried moving the bindParam statements to before the $gender if/else statement it stopped working. – Chaya Cooper Dec 19 '12 at 00:53

Syntax for if/else statement if insert was successful in a PDO prepared statement

1 Answers1

1. Return value from $stmt->execute()

2. Row Count

3. Last Inserted Id

Linked