如何在表中创建插入，其中部分数据来自一个表并且其余部分已填充

Question

I have table messages where I am trying to create new messages to users. Algorythm looks like:

1) Get all user e-mails (they act as user id and is primary key in table user)

2) For each e-mail insert message (same to all), flag is read (false) and time (NOW())

How to do this? I wrote next, but I am sure that such line is wrong.

INSERT INTO `messages` (`email`, `message`,`isread`,`createdat`) SELECT DISTINCT `email` FROM `users`, ?, false, NOW()

P.S. I am doing this via PHP, so ? will be message text. For messages primary key is id that is AUTO_INCREMENT. Database: MySQL

Answer 1

Add your literal value to the select clause as shown below.

Take a look at this sqlFiddle

INSERT INTO `messages` (`email`, `message`,`isread`,`createdat`) 
SELECT DISTINCT `email`, '?', false, NOW() 
FROM `users`

And the samething, but in PHP

<?php

  $myMessage ="...";

  $sql = sprintf("
                  INSERT INTO `messages` (`email`, `message`,`isread`,`createdat`) 
                  SELECT DISTINCT `email`, '%s', false, NOW() 
                  FROM `users`", 
                  $myMessage);