Error using case function in sql query with php

Question

I am trying to display user ID, username and the count of subscriptions they purchased every year. My database contain two tables namely subscriptions and users as follows:

users

user_id	name	phone_number
1	xyz	4567890
2	abc	4578003

subscriptions

subc_id	user_id	type	start_time
1	2	week	2019-07-30 17::00:19
2	4	year	2020-05-12 22:38:24

SQL query i am using in my php code is

SELECT subscriptions.user_id, users.name, COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2018" AND subscriptions.type = "year" THEN 1 END) AS "2018",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2019" AND subscriptions.type = "year" THEN 1 END) AS "2019",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2020" AND subscriptions.type = "year" THEN 1 END) AS "2020",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2021" AND subscriptions.type = "year" THEN 1 END) AS "2021" FROM subscriptions, users WHERE subscriptions.user_id = users.user_id

Problem is it's working fine in my phpmyadmin SQL query interface but when i use it with php mysqli it gives me following error Parse error: syntax error, unexpected '2018' (T_LNUMBER)

Kindly tell me what is wrong with my code. My php code is

<?php
        $sql = "SELECT subscriptions.user_id, users.name, COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2018" AND subscriptions.type = "year" THEN 1 END) AS "2018",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2019" AND subscriptions.type = "year" THEN 1 END) AS "2019",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2020" AND subscriptions.type = "year" THEN 1 END) AS "2020",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2021" AND subscriptions.type = "year" THEN 1 END) AS "2021" FROM subscriptions, users WHERE subscriptions.user_id = users.user_id";
        if ($res = mysqli_query($conn, $sql)) {
            if (mysqli_num_rows($res) > 0) {
                echo "<table>";
                echo "<tr>";
                echo "<th>user_id</th>";
                echo "<th>Name</th>";
                echo "<th>2018</th>";
                echo "<th>2019</th>";
                echo "<th>2020</th>";
                echo "<th>2021</th>";
                echo "<tr>";
                while($row = mysqli_fetch_array($res))
                {
                    echo "<tr>";
                    echo "<td>" . $row['user_id'] . "</td>";
                    echo "<td>" . $row['name'] . "</td>";
                    echo "<td>" . $row['2018'] . "</td>";
                    echo "<td>" . $row['2019'] . "</td>";
                    echo "<td>" . $row['2020'] . "</td>";
                    echo "<td>" . $row['2021'] . "</td>";
                    echo "</tr>";
                }
                echo "<table>";
            }
            else {
                echo "No Records";
            }
        }
        ?>

Please show the PHP code, it looks like it's in a string which is in `"`'s — Nigel Ren, May 02 '21 at 16:04
The first thing wrong with your code is that you don't use [Prepared Statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) which is leaving you open to SQL injections. — Jaquarh, May 02 '21 at 16:08
if it is not executable then how it would be vulnerable to SQL injection. — Ayesha Javed, May 02 '21 at 16:14
Good point! The ultimate way to protect against SQL injection is `DEL CE3.php` (I am joking, please do not delete your code file). — Bill Karwin, May 02 '21 at 16:14

forpas · Answer 1 · 2021-05-02T16:36:00.633

I don't believe that your query works in phpmyadmin neither, although it is syntactically correct, because it is missing a GROUP BY clause.

Also, you are not using properly the ".
In a line were you use double quotes to assign a string value to a variable like this:

$sql = "SELECT subscriptions.user_id, users.name, COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2018" ...........";

it is wrong to use double quotes again inside to enclose string values and also identifiers like column names.
For string values use single quotes and for identifiers use backticks.

Also, use proper JOINs with an ON clause and not that outdated comma syntax.

Finally add the GROUP BY clause:

SELECT u.user_id, users.name, 
       COUNT(CASE WHEN YEAR(s.start_time) = '2018' AND s.type = 'year' THEN 1 END) AS `2018`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2019' AND s.type = 'year' THEN 1 END) AS `2019`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2020' AND s.type = 'year' THEN 1 END) AS `2020`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2021' AND s.type = 'year' THEN 1 END) AS `2021` 
FROM subscriptions s INNER JOIN users u 
ON s.user_id = u.user_id
GROUP BY u.user_id, users.name

You could also use a WHERE clause, that would be better for performance and shorten the code,since you are counting only rows with type = 'year':

SELECT u.user_id, users.name, 
       COUNT(CASE WHEN YEAR(s.start_time) = '2018' THEN 1 END) AS `2018`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2019' THEN 1 END) AS `2019`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2020' THEN 1 END) AS `2020`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2021' THEN 1 END) AS `2021` 
FROM subscriptions s INNER JOIN users u 
ON s.user_id = u.user_id
WHERE s.type = 'year'
GROUP BY u.user_id, users.name

There is another way to write this query if you take advantage of MySql's evaluation of Boolean expressions to 1 for true and 0 for false, with SUM() instead of COUNT():

SELECT u.user_id, users.name, 
       SUM(YEAR(s.start_time) = '2018') AS `2018`,
       SUM(YEAR(s.start_time) = '2019') AS `2019`,
       SUM(YEAR(s.start_time) = '2020') AS `2020`,
       SUM(YEAR(s.start_time) = '2021') AS `2021` 
FROM subscriptions s INNER JOIN users u 
ON s.user_id = u.user_id
WHERE s.type = 'year'
GROUP BY u.user_id, users.name

Thanks for pointing out. I am new to sql and i had no idea about different between using double quotes and single quotes. Thanks! — Ayesha Javed, May 02 '21 at 16:22
@AyeshaJaved if this answer solved your problem consider accepting it by clicking on the checkmark. — forpas, May 16 '21 at 14:57

Error using case function in sql query with php

1 Answers1