0

Yes, there are similar questions (i.e. PDO mysql: How to know if insert was successful and $stmt->execute() : How to know if db insert was successful?), however, the supplied answers don't seem to work for me.

Given the below query, $stmt->execute(array($doc_id,$id,$sites_id)) returns true, however, a record is not inserted.

How do I determine whether PDO inserted a record?

EDIT. Please don't just say to use PDO::lastInsertId, but if doing so is necessary, give a reason why it is necessary.

INSERT INTO docx_priv_projects (documents_id,projects_id)
SELECT 2972614382,t.id
FROM projects AS t INNER JOIN entities AS e ON e.id=t.id
WHERE t.id=1379519490 AND e.record_status='active' AND e.sites_id=2416619273;
Community
  • 1
  • 1
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • 3
    true just means the query didn't fail. a query which doesn't accomplish anything is NOT a failure. it's just a successful query that happened to produce an empty result set, e.g. `select * from foo where 1=0` would be just such a "successful" query. you need to check things like `affected_rows()`, which may not be particularly useful when you're doing an `insert .. select from`. – Marc B Feb 09 '16 at 20:06
  • @MarcB Okay, makes sense. Was confused by the other answers. So ignore them and use `lastInsertID`? – user1032531 Feb 09 '16 at 20:08
  • 1
    If lastInsertId gives you an id, i.e a number, then the query has created a new row; otherwise, it will be 0. – Dan Sebastian Feb 09 '16 at 20:11
  • that'll work, but only for the LAST record inserted. if the select finds more than one record, there's no way to retrieve the first 1->(n-1) insert IDs. – Marc B Feb 09 '16 at 20:11
  • 2
    `$stmt->rowCount();` maybe??? – AbraCadaver Feb 09 '16 at 20:13
  • @ Dan and Marc. Fortunately, for my case, it will only insert one row. – user1032531 Feb 09 '16 at 20:13
  • 2
    maybe interesting? [PDO were rows affected during execute statement](http://stackoverflow.com/questions/10522520/pdo-were-rows-affected-during-execute-statement). Also the _Linked_ questions in the right hand column on this page? – Ryan Vincent Feb 09 '16 at 20:28
  • Actually, `lastInsertID` is not a method to know if ‘PDO inserted a record’, but to retrieve the last inserted primary ID. Performing a `INSERT` query to a table without primary ID, it return indeed 0. – fusion3k Feb 09 '16 at 20:33
  • If you don't have a primary key, then perhaps you can count the rows on that table and save it to (memcache, redis, another table, etc). Then after inserting, count again and compare with the last count (and update cache) to check if it's inserted. It's better to create a primary key for this, though... – peixotorms Feb 09 '16 at 20:42
  • After testing, `PDO::lastInsertId()` returns zero even though a record was inserted. I do have a PK, however, it is a composite PK. – user1032531 Feb 10 '16 at 15:06

2 Answers2

1

PDO::lastInsertId appears to only work on auto incremented PKs, however, this requirement does not seem to be documented very well at http://php.net/manual/en/pdo.lastinsertid.php. Furthermore, it does not appear to work with composite keys.

pdo::rowCount() is required. See http://php.net/manual/en/pdostatement.rowcount.php. Thanks given to AbraCadaver and Ryan Vincent.

$sql='INSERT INTO docx_priv_projects (documents_id,projects_id)
SELECT :doc_id,t.id
FROM projects AS t INNER JOIN entities AS e ON e.id=t.id
WHERE t.id=:id AND e.record_status='active' AND e.sites_id=:sites_id';
$stmt = $conn->prepare($sql);
$stmt->execute(array('id'=>1379519490 ,'sites_id'=>2416619273,'doc_id'=>2972614382));
$x=$stmt->rowCount();  //Will be `1` if added
user1032531
  • 24,767
  • 68
  • 217
  • 387
0

You can test if there was a last inserted id in two ways:

Using the PDO API:

$stmt = $db->prepare("...");
$stmt->execute();
$id = $db->lastInsertId();

Using SQL instead of the PDO API:

$stmt = $db->query("SELECT LAST_INSERT_ID()");
$lastId = $stmt->fetch(PDO::FETCH_NUM);
$lastId = $lastId[0];

Then test for example is, lastid or id is int > 1 or do your own validation.

http://php.net/manual/en/pdo.lastinsertid.php
peixotorms
  • 1,246
  • 1
  • 10
  • 21