How can I make sure my query inserts a new row?

Question

Here is my query:

$db->query("INSERT INTO mytable(col1, col2) VALUES('val1', 'val2')");

I'm trying to understand that query inserts a new row or not? How can I determine it?

Note: I'm not using execute() in this case.

http://stackoverflow.com/questions/1661863/pdo-mysql-how-to-know-if-insert-was-successful and http://stackoverflow.com/questions/13833499/syntax-for-if-else-statement-if-insert-was-successful-in-a-pdo-prepared-statemen — Funk Forty Niner, Jul 04 '16 at 14:59
@Fred-ii- Both of them are about `execute()` function *(which returns `true` on success)*, bud as I mentioned, I don't use `execute()` function in this case. I'm using `query()`. — Martin AJ, Jul 04 '16 at 15:00
@Fred-ii- Do you mean `query()` function also returns *true* on success? — Martin AJ, Jul 04 '16 at 15:02

score 1 · Answer 1 · edited May 23 '17 at 10:28

Taken directly from w3schools.com/php/php_mysql_insert...

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

You're looking for this: if ($conn->query($sql) === TRUE)

Edit: Sorry, took wrong example as @Drew pointed out!

Below is correct snippet, it does make use of execute, as I think it should (correct me if I'm wrong!)

This link might be of more use: pdostatement.execute

try {
     $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
     // set the PDO error mode to exception
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $sql = "INSERT INTO MyGuests (firstname, lastname, email)
     VALUES ('John', 'Doe', 'john@example.com')";
     // use exec() because no results are returned
     $conn->exec($sql);
     echo "New record created successfully";
    }
catch(PDOException $e)
    {
      echo $sql . "<br>" . $e->getMessage();
    }

and

<?php 
function customExecute(PDOStatement &$sth, array $params = array()) { 
    if (empty($params)) 
        return $sth->execute(); 
    return $sth->execute($params); 
} 
?>

Hope this helps!

Juan, at the bottom there is PDO. Even still, it would return a `PDOStatement`, no? Am I wrong, I might be. Please tweak your answer so I can turn my DV into an upvote my friend. — Drew, Jul 04 '16 at 16:43

score 0 · Answer 2 · answered Jul 04 '16 at 15:06

PDO query function will return a PDOStatement

You can check for the error code in the PDOStatement. It should be 0 if everything was executed succesfully. Another option, is checking to rowCount function result - if new row was inserted, it should be 1.

score 0 · Answer 3 · answered Jul 04 '16 at 15:06

0

You typically perform execute() call on a PDOStatement object, returned via prepare() call.

Here, the return value of your query() call would also be PDOStatement object on which, you can apply your rowCount().

PDO::query() returns a PDOStatement object, or FALSE on failure.

answered Jul 04 '16 at 15:06

hjpotter92

78,589
36
144
183

Thank you .. upvote. May you please tell me what's a `PDOStatement` look like? – Martin AJ Jul 04 '16 at 15:11
It looks like something ready to execute, or bind parameters into (yea). – Drew Jul 04 '16 at 15:12
@Drew Is that like a query? Like `INSERT INTO ...` ? – Martin AJ Jul 04 '16 at 15:12
It can be, sure, just like yours. – Drew Jul 04 '16 at 15:13
In a properly configured application PDO::query() does not return FALSE on failure. – Your Common Sense Jul 04 '16 at 16:17
@YourCommonSense Yes, I know. Which is why it was not mentioned in my answer, except for the quote where the PHP docs says so. – hjpotter92 Jul 04 '16 at 18:19

How can I make sure my query inserts a new row?

3 Answers3