douxiangbiao1899
2013-05-07 12:44
浏览 75
已采纳

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

图片转代码服务由CSDN问答提供 功能建议

我似乎在问一个简单的问题,但我找不到适合的答案 我。 我希望你能提供帮助!

我正在编写一个PHP脚本,它从html文件(名称,艺术家,歌曲)中获取一些表单数据并放入 它进入一个包含列(名称,艺术家,歌曲,队列,新手)的表格。 现在,列 newcomer 是一个布尔值; 如果传入表单数据的名称值是唯一的,我希望此布尔值从默认值false或0更改为true或1。

这是我的尝试:

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

但很明显,它不起作用......

提前致谢!!


嗨,谢谢你的回答! 它仍然不适用于我=(

$ newcomer = mysql_query(“更新tbl_queue SET newcomer = 1 WHERE name =' _POST [name]。“'AND NOT EXISTS(SELECT DISTINCT name FROM tbl_queue WHERE name ='”。$ _POST [name]。“')”);

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • douyi3767 2013-05-07 13:06
    已采纳

    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";
    
    
    ?>
    
    打赏 评论
  • doufei2194 2013-05-07 12:51
    "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

    打赏 评论
  • dpjj4763 2013-05-10 08:11

    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";
    
    打赏 评论
  • duanaidang6197 2013-05-10 08:29

    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

    打赏 评论

相关推荐 更多相似问题