Geek Answers Handbook

Is it possible to insert an INSERT and then a SELECT inserted row one by one?

I execute two queries one after another : one is INSERT, the other is SELECT, which selects the inserted row. Although the row was inserted successfully (I see it in db), the select query does not return the row.

When I execute the SELECT query again, it returns the correct result.

Insert:

$stmt = $pdo->prepare('INSERT INTO user (id ,name, lastname ,birthday, social_type, social_id) VALUES(NULL, :name, :lastname, :birthday, :social_type, :social_id)'); $success=$stmt->execute(array( ':name' => $user['name'], ':lastname' => $user['lastname'], ':birthday' => $user['birthday'], ':social_type' => $user['social_type'], ':social_id' => $user['social_id'] ));

Choose

  $stmt = $pdo->prepare('SELECT * FROM user WHERE social_id = :social_id AND social_type = :social_type LIMIT 1'); $stmt->execute(array( 'social_id' => $user['social_id'], 'social_type' => $user['social_type'] )); $result = $stmt->fetch(PDO::FETCH_ASSOC);
+4
source share
1 answer

IF YOU USE INNODB:

If you use INNODB since you confirmed that the row was inserted, it should have been returned with SELECT, while SELECT asks for the key of the actual row that was inserted. (Are you sure that you are not using a function such as INSERT DELAYED? May interfere with the return of the string.)

IF YOU USE MYISAM:

Since MyISAM does not support transactions, SELECT should return the insert, but I cannot find anything that claims it is actually guaranteed.

NOTE. The first URL below indicates that you are using MYISAM (by default from this link) INSERTS will lock the table. However, the second URL states that the lock placed by the insert is a readable lock, so it should not prevent the table from reading.

http://www.sitepoint.com/mysql-mistakes-php-developers/

http://aarklondatabasetrivia.blogspot.com/2009/04/how-to-lock-and-unlock-tables-in-mysql.html

IF YOU USE INNODB (CONT'D):

  • If AUTOCOMMIT is used on your system (I'm not sure), you should have seen the selected row (this question indicates that the inserted row was checked as having been added to the database).

  • If a transaction is used, the transaction must be completed (this question indicates that the inserted row was verified as being added to the database).

Are you sure the SELECT query that is executed the first time is the same as the second time?

Are you sure $user['social_id'] is the same value after INSERT and during SELECT?

  • If you are referring to a row inserted in another transaction, instead of in the session that is performing the insert, then this URL:

http://blogs.innodb.com/wp/2011/04/get-started-with-innodb-memcached-daemon-plugin/

state "you will need to do" read uncommitted "select to find the lines just inserted:"

those. the established session PROTECTION TRANSACTION ISOLATION LEVEL, read without taking into account,

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

(This function may depend on the version of MYSQL used)

  • If for some reason you use INSERT DELAYED, the string cannot be returned

NOTES: According to this URL, if you started a transaction, the selected rows are shown in the following SELECT statement (not in PHP):

http://zetcode.com/databases/mysqltutorial/transactions/

This statement implies that if you start a transaction, you do not need to set AUTOCOMMIT:

"MySQL also automatically compiles statements that are not part of the transaction."

This URL describes how to start a transaction in PHP:

PHP + MySQL transaction examples

+2
source

More articles:


All Articles