如何向所有用户发送评论（在1个表中）（指另一个表）？

Question

Right now I have 2 tables. A comment table and a user table. Short and simple, I wanna sent a comment to all users. I need to know the sql command for it and how to display on PHP.

Comment table

1. pk
2. comment
3. sender (refers to username)
4. receiver (refers to username)

User table

1. pk
2. username
3. etc.....

I tried using this:

$provider = $_REQUEST['provider'];

$cert = $_REQUEST['cert'];

$valid = $_REQUEST['valid'];

$lprice = $_REQUEST['lprice'];

$trial = $_REQUEST['trial'];

$cost = $_REQUEST['cost'];

echo $cert;

$sql=mysql_query("SELECT * FROM sslcert WHERE CertificateName='$cert'",$connect);
 if(mysql_num_rows($sql)>0)
   {
    echo"SSL already exists.";
    echo "<br><a href=addssl.php>Back</a>";
   }
 else
    {
      $insert = mysql_query("INSERT INTO sslcert (Provider, CertificateName, Validation,                    
      ListPrice, Trial, Cost) VALUES ('$provider', '$cert', '$valid', '$lprice', 
     '$trial',     '$cost')",$connect);


          $result77=mysql_query("SELECT username FROM user");
     while($row77=mysql_fetch_array($result77))
     {
       $insert1 = mysql_query("INSERT INTO notification (msg, sender, receiver,   notification) VALUES ('A new product is now available, $cert by $provider', '$username',   '$row77['username']', 1)");
       if(!$insert1){

            die("There's little problem: ".mysql_error());
            echo "<br><a href=quotesmanage1.php>Back</a>";
        }
     }

    if(!$insert){

       die("There's little problem: ".mysql_error());
       echo "<br><a href=addssl.php>Back</a>";
    }

    echo "<br>You have add a new Product <br><a href=adminhome.php>Back to Home</a>";

    }   
    mysql_close($connect);
}

the call for username is somewhere else on the page. It came out with a blank page (error on php), any ideas?

Answer 1

I believe you want a INSERT...SELECT query.

INSERT INTO `Comment`(`Comment`.`comment`, `Comment`.`sender`, `Comment`.`receiver`)
SELECT 'Message as text or variable substitution', `User`.`pk`, '1'
FROM `User`

You can substitute any ID instead of 1 at the end of the SELECT line, I just figured your administrative user is probably 1 (but you can create a new user for sending these mass messages and just use whatever number ID corresponds.

By the way, in your comment table you should probably reference User.pk, not User.username. That's sort of why you have the key in the first place :-)