mysql：IF'some columns value'是DISTINCT THEN的反转布尔值

It seems that I'm asking a simple question but I haven't been able to find an answer that works for me. I hope you can help!

I'm writing a php script that takes some form data from an html-file (name, artist, song) and put it into a table with the columns (name, artist, song, queue, newcomer). Now, the column newcomer is a boolean; and I want this boolean value to change from the default of false or 0, to true, or 1, if the name value of the incoming form data is unique.

This is my try:

$newcomer = mysql_query(
                'IF NOT EXISTS (SELECT DISTINCT name FROM tbl_queue) 
                 THEN UPDATE tbl_queue newcomer=1'
            );

but obviously, it doesn't work...

Thanks in advance!!

Hi and thanks for the answer! HOwever it still doesn't work for me =( THis is the code from the php:

$newcomer = mysql_query("UPDATE tbl_queue SET newcomer=1 WHERE name='" . $_POST[name] . "' AND NOT EXISTS (SELECT DISTINCT name FROM tbl_queue WHERE name='" . $_POST[name] . "')" );

I would be tempted to do your initial insert like this:-

INSERT INTO tbl_queue (name, artist, song, queue, newcomer)
SELECT DISTINCT a.name, a.artist, a.song, a.queue, CASE WHEN b.newcomer IS NULL THEN 0 ELSE 1 END as newcomer
FROM (SELECT '$name' AS name, '$artist' AS artist, '$song' AS song, '$queue' AS queue) a
LEFT OUTER JOIN tbl_queue b
ON a.name = b.name

Have a dummy select of the values you want to insert, and left join that against the existing table based on name, using a CASE statement to check if a matching row was found or not.

EDIT - Below is some php showing the var assignments and escaping the values. I have used mysql_ functions here as you have in your original post, but really should be using mysqli_ functions to future proof things.

<?php

$name = mysql_real_escape_string($_POST['name']);
$artist = mysql_real_escape_string($_POST['artist']);
$song = mysql_real_escape_string($_POST['song']);
$fcfs_queue = mysql_real_escape_string($_POST['fcfs_queue']);

$sql = ("INSERT INTO tbl_queue (name, artist, song, fcfs_queue, newcomer)
SELECT DISTINCT a.name, a.artist, a.song, a.fcfs_queue, CASE WHEN b.newcomer IS NULL  THEN 0 ELSE 1 END as newcomer
FROM (SELECT '$name' AS name, '$artist' AS artist, '$song' AS song, '$fcfs_queue' AS  fcfs_queue) a
LEFT OUTER JOIN tbl_queue b
ON a.name = b.name");


if (!mysql_query($sql))
{
    die('Error: ' . mysql_error());
}
echo "success";


?>

"UPDATE tbl_queue SET newcomer=1
    WHERE   name = '" . $name_variable . "' AND
            NOT EXISTS (SELECT DISTINCT name
                            FROM tbl_queue
                            WHERE name='" . $name_variable . "')"

Is that what you are trying to do? Obviously replace $name_variable with the correct variable.

Edit: reformatted SQL

I wanted this to be a comment to Kickstart but it doesn't fit within the limits of a comment, so.. i hope it's ok I answer here?

Thank you! However that didn't work either... It returns this error: Warning: mysql_error() expects parameter 1 to be resource (regarding this row: die('Error: ' . mysql_error($sql)); in the if statement)

My code is:

$sql = ("INSERT INTO tbl_queue (name, artist, song, fcfs_queue, newcomer)
SELECT DISTINCT a.name, a.artist, a.song, a.fcfs_queue, CASE WHEN b.newcomer IS NULL  THEN 0 ELSE 1 END as newcomer
FROM SELECT '$name' AS name, '$artist' AS artist, '$song' AS song, '$fcfs_queue' AS  fcfs_queue) a
LEFT OUTER JOIN tbl_queue b
ON a.name = b.name");


if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error($sql));
  }
echo "success";

I would use a trigger for that, as it is a "fixed" requirement for your table:

DROP TRIGGER IF EXISTS `newComerCheck`//
CREATE TRIGGER `newComerCheck` BEFORE INSERT ON `songs`
 FOR EACH ROW BEGIN
    DECLARE c integer;
    SELECT COUNT(name) FROM `songs` WHERE `name`=NEW.name INTO c;

    IF (c >= 1) THEN
      SET NEW.newcomer=0;
    ELSE 
      SET NEW.newcomer=1;
    END IF;
  END
//

See fiddle: http://sqlfiddle.com/#!2/3e73b5/1