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

Question

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] . "')" );

Answer 1

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";


?>