SELECT and INSERT not selecting

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

score 1 · Accepted Answer · answered Oct 19 '14 at 17:04

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.

This answer actually looks like it would make my code easier to read, thank you for this — Isaac, Oct 19 '14 at 17:31
@Isaac indeed, cleaner to wrap queries into a stored procedure, it also gives you advantage to modify it without touching PDO code — meda, Oct 19 '14 at 17:32

score 0 · Answer 2 · edited May 23 '17 at 12:20

Upon typing this up, one of the similar questions that came up on the right getting last inserted row id with PDO (not suitable result) gave a suitable answer in the question itself, although I'm a little dubious considering the method is being questioned itself.

$db->lastInsertId();

Seems to work for me, but in the question linked it isn't working as desired for the questioner, so I'm not entirely settled with this answer. It does seem to be a bit of a hack.

SELECT and INSERT not selecting

2 Answers2