SELECT和INSERT不选择

Question

When I execute

INSERT INTO `survey`
  (`id`,`name`,`date_start`,`date_end`)
  values
  (:id,:name,NULL,DATE_ADD(NOW(), INTERVAL 1 MINUTE))
  on duplicate key UPDATE `name`=:name;
  SELECT coalesce(:id,LAST_INSERT_ID()) as 'id'

it inserts a new data fine, but doesn't select the id (which is needed later on in my php code)

I've tried this suggestion

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

but this SQL throws errors (due to duplicate parameters)

SELECT ASCII(substr(`perm` FROM floor(:i/8)+1))&(1<<(:i%8))>0 as perm FROM `user` WHERE `id`=:id

I'm in a lose-lose situation, re-writing all my SQL code to not have duplicate parameters would be very messy, doing a separate select straight after inserting may not return the id I want. Any suggestions would be great

Answer 1

You cannot run two queries at the same time, only one at the time.

If you want to do the whole thing at once then create a stored procedure.

Same goes for complex queries, when it gets complicated you want to have your logic in the database.

Here is an example:

DELIMITER //
 CREATE PROCEDURE sp_insert_survey(IN `p_id`, 
                                   IN `p_name`, 
                                   IN `p_date_start`, 
                                   IN `p_date_end`)
   BEGIN
   INSERT INTO `survey`(`id`,`name`,`date_start`,`date_end`)
   VALUES (p_id, p_name, p_date_start, p_date_end);

   SELECT `id`,`name`,`date_start`,`date_end` 
   FROM survey WHERE `id` =LAST_INSERT_ID();
   END //
 DELIMITER ;

Call the sp from PDO:

$stmt = $db->prepare('CALL sp_insert_survey(?, ?, ?, ?)');

then fetch the data as a SELECT query.