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.

Solution 1

Give a look to the mysql_insert_id() function.

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

Solution 2

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.

Solution 3

Yes You can using Shell command <BR> mysql -user -p -h database -e ' SQL STATEMENT 1; SQL STATEMENT 2; SQL STATEMENT 3; .......; '

Php / MYSQL Programmer

Solution 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()) ;

Solution 5

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

http://php.net/mysql_query

View more solutions

Comments

[djmzfKnm]

"Note: The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries. " this is written in the link what you have given me. Still is it OK to use mysql_insert_id() ?

[sqram]

This really is the best answer, and only way to do it.

[Maiku Mori]

Very bad idea on production website. MySQL errors can contain some sensitive data. The best solution is to log em.