Mysql Insert query with values and select as subquery

Question

I have tried numerous solutions and am not able to get my query to work.

I am trying to create a like system, which in the code will check that the item being liked exists, and has not already been liked.

I think I am close in my query, however I keep getting an error.

The variable "$this_is_liked" is predefined to the value "Yes", the variable "$liked_user_id" is predefined to the value of the logged in user (1 in this example), and then "$liked_item_id" is being pulled from the pms_inventory table, while I'm trying to have it check that the item id exists, and that it has not already been liked.

Below is my code, and then the error.

        if(isset($_POST['type'], $_POST['itemid'], $_POST['liked']))  {
            include_once('connectdb.php');
            include_once('global.php');
            $liked_item_id = (int)$_POST['itemid']; // Liked Item ID
            $liked_user_id = (int)$_SESSION['id']; // User ID
            $this_is_liked = $_POST['liked']; // Value of "Yes"
        mysql_query("
        INSERT INTO pms_inventory_likes 
            (
            liked,
            user_id, 
            item_id, 
            )
            VALUES
            (
            SELECT 
            {$this_is_liked}, {$liked_user_id}, {$liked_item_id} 
            FROM pms_inventory
            WHERE EXISTS (
                SELECT id 
                FROM pms_inventory
                WHERE id = {$liked_item_id})
            AND NOT EXISTS (
                SELECT id 
                FROM pms_inventory_likes 
                WHERE user_id = {$liked_user_id} 
                AND item_id = {$liked_item_id} 
                AND liked = 'Yes')  
            )
            LIMIT 1
        ") or die(mysql_error());
    }

Here is the error:

    <span>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
            VALUES
            (
            SELECT 
            Yes, 1, 40 
            FROM pms_inventory
            ' at line 6</span>

I am not sure if I am nesting "SELECT" correctly, or if I am labeling the values properly.

Please let me know if more info is needed!

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

Try this It May Help You ...

              INSERT INTO pms_inventory_likes 
                (liked,
                user_id, 
                item_id) 

               SELECT {$this_is_liked}, {$liked_user_id}, {$liked_item_id} FROM pms_inventory

                WHERE EXISTS (
                    SELECT id 
                    FROM pms_inventory
                    WHERE id = {$liked_item_id})

                AND NOT EXISTS (
                    SELECT id 
                    FROM pms_inventory_likes 
                    WHERE user_id = {$liked_user_id} 
                    AND item_id = {$liked_item_id} 
                    AND liked = "Yes")  
                LIMIT 1

For More Reference refer below links :

Oracle insert if not exists statement

http://www.techonthenet.com/oracle/insert.php

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/exists-and-not-exists-subqueries.html

edited May 23 '17 at 11:49

Community

1
1

answered Sep 18 '14 at 05:30

K.Raj.

643
6
22

This returns the error: Unknown column 'Yes' in 'field list' Just as reference, $this_is_liked has a value of 'Yes'. Not sure if that helps? – vsdesign Sep 18 '14 at 05:50
Try using different quotes for "yes" as the identifier quote character is the backtick (“`”). Otherwise MySQL "thinks" that you point to a column named "yes". reference : http://dev.mysql.com/doc/refman/5.0/en/identifiers.html make your functional quotes '' instead of "" so it will not conflict with your inner quotes – K.Raj. Sep 18 '14 at 06:07
PLEASE REFER THIS LINK: http://stackoverflow.com/questions/1346209/unknown-column-in-field-list-error-on-mysql-update-query – K.Raj. Sep 18 '14 at 06:12
This new one gives me this error now: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}, {$liked_user_id}, {$liked_item_id} FROM pms_inventory WHER' at line 6 – vsdesign Sep 18 '14 at 07:07
Can you show the sample database or more smaple code ? so I can test it and can give you a perfect solution. – K.Raj. Sep 18 '14 at 10:43
Thanks for your follow up! My database has the columns of id, user_id, item_id and liked. When an item is liked, the result auto increments the id column, inserts the id of the user who liked the item, inserts the id of the item liked, and inserts "Yes" into the liked field. (There are other columns in this db but are unrelated to this Liking system) – vsdesign Sep 19 '14 at 17:21

score 0 · Accepted Answer · answered Sep 18 '14 at 07:28

With the help of the two contributors above, I was able to get to the final root of the problem... Along with removing "VALUES" part of the statement and adding in single quotes around '{$this_is_liked}' (thank you Senk and K.Raj), as usual, the tiny syntax error that we all seemed to miss here which finally got it working:

    INSERT INTO pms_inventory_likes 
        (
        liked,
        user_id, 
        **item_id,** 
        )

There was a comma after the item_id which kept the code from running. For those of you who would like to use this code to make a simple "Like" system for your site, the final, working version of the code is this:

    if(isset($_POST['type'], $_POST['itemid'], $_POST['liked']))  {
        include_once('connectdb.php');
        include_once('global.php');
        $liked_item_id = (int)$_POST['itemid']; // Liked Item ID
        $liked_user_id = (int)$_SESSION['id']; // User ID
        $this_is_liked = $_POST['liked']; // Value of "Yes"
    mysql_query("
    INSERT INTO pms_inventory_likes 
        (
        liked,
        user_id, 
        item_id
        )
        SELECT 
        '{$this_is_liked}', {$liked_user_id}, {$liked_item_id} 
        FROM pms_inventory
        WHERE EXISTS (
            SELECT id 
            FROM pms_inventory
            WHERE id = {$liked_item_id})
        AND NOT EXISTS (
            SELECT id 
            FROM pms_inventory_likes 
            WHERE user_id = {$liked_user_id} 
            AND item_id = {$liked_item_id} 
            AND liked = 'Yes')  
        LIMIT 1
    ") or die(mysql_error());
}

And the tables in your database will need to be id, user_id, item_id and liked.

I hope this helps someone!

Mysql Insert query with values and select as subquery

2 Answers2