为什么我的MySQL查询返回语法错误？

Question

I can't for the life of me figure out why my query is returning an error. I am generating it with PHP.

$column = "display_name";
$criteria = "Steven";
$query = "
                SELECT
                    m.user_id AS id, m.email_address, m.display_name, m.status, UNIX_TIMESTAMP(m.join_date) AS join_date,
                    l.listing_id, COUNT(l.member_id) AS total_listings,
                    g.group_id AS group_id, g.title AS group_title
                FROM users AS m
                LEFT JOIN listings as l on m.user_id = l.member_id
                LEFT JOIN groups AS g on m.group_id = g.group_id";

            if($column == "display_name"){

                $query .= '
                WHERE m.display_name LIKE \'%'.$criteria.'\'%';

            } else {

                $query = "
                WHERE m.".$column." = '$criteria'";

            }

            $query .="
                GROUP BY m.user_id";

The above code produces the following:

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 '% GROUP BY m.user_id' at line 8

SELECT m.user_id AS id, m.email_address, m.display_name, m.status, UNIX_TIMESTAMP(m.join_date) AS join_date, l.listing_id, COUNT(l.member_id) AS total_listings, g.group_id AS group_id, g.title AS group_title FROM users AS m LEFT JOIN listings as l on m.user_id = l.member_id LEFT JOIN groups AS g on m.group_id = g.group_id WHERE m.display_name LIKE '%Steven'% GROUP BY m.user_id

I've tried using LIKE %$criteria% and LIKE '%$criteria%' and I have no idea why neither are working. Any ideas?

Answer 1

Because your query is being interpreted like this

WHERE m.display_name LIKE '%text'%

And here you are using single quote before % sign. It should be '%text%' instead of '%text'% there. Fix it like this

$query .= '
            WHERE m.display_name LIKE \'%'.$criteria.'%\'';

Better to use double quotes outside to remove all this confusions. Like this:

$query .= "
            WHERE m.display_name LIKE '%$criteria%'";