php select from two fields as value from db union all error

Question

I have created the following script that works in phpadmin but not as a php script. Does anyone know why this produces the following parse error. Parse error: syntax error, unexpected 'draw_id' (T_STRING), expecting ',' or ')' for the first line. I am using wamp with php 7.2 and mysql 5.7.

SELECT * FROM (SELECT draw_id, picknum01 as n_value FROM joker union all 
SELECT draw_id, picknum02 as n_value FROM joker union all 
SELECT draw_id, picknum03 as n_value FROM joker union all 
SELECT draw_id, picknum04 as n_value FROM joker union all 
SELECT draw_id, picknum05 as n_value FROM joker) as temp;

SELECT n1, n2, n3, count(*) as total
FROM (
    SELECT up1.n_value as n1, up2.n_value as n2, up3.n_value as n3
    FROM unpivot up1
    JOIN unpivot up2
    ON up1.`id` = up2.`id`        
    AND up1.n_value < up2.n_value  
    JOIN unpivot up3
    ON up2.`id` = up3.`id`        
    AND up2.n_value < up3.n_value  
    ) Temp
GROUP BY n1, n2, n3
ORDER BY total desc
LIMIT 3;

Specifically what error do you get? Please paste it into your question. And you'll need to show us how you're executing it in PHP if that's the only scenario in which it goes wrong. — ADyson, Jul 08 '21 at 12:38
P.s. it looks like these are actually two separate SQL statements. By any chance are you trying to execute them in a single PHP command? Because that won't work. — ADyson, Jul 08 '21 at 12:39
Hello ADyson, the above is a small part of the file. The error is Parse error: syntax error, unexpected 'draw_id' (T_STRING), expecting ',' or ')' for the first line. This is strange as it works in phpadmin sql using the table. — Peter, Jul 08 '21 at 12:42
Please paste that into your question, not the comments. And also as I requested please show this code in the context of the nearby PHP. The error message is a PHP error not a SQL error — ADyson, Jul 08 '21 at 12:52
This may help you to solve it yourself anyway: [PHP parse/syntax errors; and how to solve them](https://stackoverflow.com/questions/18050071/php-parse-syntax-errors-and-how-to-solve-them) — ADyson, Jul 08 '21 at 12:53

score 0 · Answer 1 · edited Jul 08 '21 at 15:12

I realized the answer after seeing the comments.

Here is the correct version:

$unionselect = "
    SELECT * FROM (
    SELECT draw_id, picknum01 as n_value FROM joker union all 
    SELECT draw_id, picknum02 as n_value FROM joker union all 
    SELECT draw_id, picknum03 as n_value FROM joker union all 
    SELECT draw_id, picknum04 as n_value FROM joker union all 
    SELECT draw_id, picknum05 as n_value FROM joker) as temp";

    $unionselect2 = "
    SELECT n1, n2, n3, count(*) as total
    FROM (
        SELECT up1.n_value as n1, up2.n_value as n2, up3.n_value as n3
        FROM unpivot up1
        JOIN unpivot up2
        ON up1.`id` = up2.`id`        
        AND up1.n_value < up2.n_value  
        JOIN unpivot up3
        ON up2.`id` = up3.`id`        
        AND up2.n_value < up3.n_value  
        ) Temp
    GROUP BY n1, n2, n3
    ORDER BY total desc
    LIMIT 3";

php select from two fields as value from db union all error

1 Answers1