How to execute SELECT and INSERT in single query with PHP/MYSQL?

Question

I have a table user_name with 3 fields, id, Name, Email (id is auto_increment field). I want to execute the following query in PHP, but its not returning any result.

INSERT INTO user_name (Name, Email) VALUES ('Example', '[email protected]'); 
SELECT LAST_INSERT_ID() AS 'userid';

When I am executing the above query in PHP as below then its not returning anything.

$_SQL="INSERT INTO user_name (Name,Email) VALUES ('Example', '[email protected]'); 
SELECT LAST_INSERT_ID() AS 'userid';";

$result_last_id = @mysql_query($_SQL);
$rs_insert = mysql_fetch_array($result_last_id);

$new_userid = $rs_insert['userid'];

Can anyone please tell me how to execute both queries into one.

Answer 1

Give a look to the mysql_insert_id() function.

mysql_query($insertStatementOnly);
$new_userid = mysql_insert_id();

Answer 2

Simple answer really: You just can't do it.

http://php.net/mysql_query

Answer 3

It appears you don't need to execute multiple queries, but I included how to do it below. What you want is the last inserted id, which you get from mysql_insert_id.

To execute multiple queries

From comments on documentation of mysql_query:

The documentation claims that "multiple queries are not supported".

However, multiple queries seem to be supported. You just have to pass flag 65536 as mysql_connect's 5 parameter (client_flags). This value is defined in /usr/include/mysql/mysql_com.h: #define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt support */

Executed with multiple queries at once, the mysql_query function will return a result only for the first query. The other queries will be executed as well, but you won't have a result for them.

Alternatively, have a look at the mysqli library, which has a multi_query method.

Answer 4

May I also suggest you avoid the error-suppression operator '@' in mysql_query as you may not be made aware of any mysql errors. At the very least do

mysql_query($sql) or die("error: " . mysql_error()) ;

Answer 5

If you are using the Zend Framework with a PDO defined MySQL database, you would just use:

$database=Zend_Db::factory('PDO_MySQL',Array('hostname'=>'localhost','username'=>'x','password'=>'y','dbname'=>'z');
$connectionHandle=$database->getConnection();
$rowsInserted=$connectionHandle->insert('database_name','INSERT INTO x (a,b) VALUES (c,d)');
if ($rowsInserted>0) {
 $autoIncrementValue=$connectionHandle->lastInsertId();
}