I'm working on a search system very outdated in PHP+MySQL, now... The owner asked me to introduce two buttons to apply filters to the main query. First button should find only users with same first_name and last_name, the second one should find only users with same last_name. Now ATM the query uses different joins (inner and left do get other informations from other tables)
$query = "
SELECT s1.*,s2.first_name,s2.last_name,s2.email,s2.id as subscriber_id,s2.phone,s2.info,a.id as agency_id,a.info as agency_info,a.invoice as agency_invoice
FROM subscription as s1
LEFT JOIN agency as a
ON s1.agency=a.id
INNER JOIN subscriber as s2
ON s1.subscriber=s2.id
WHERE $matches AND s1.deleted=0
ORDER BY s1.id DESC
LIMIT $maxid,$limit
";
Everything works properbly but if I add clauses to group users by same full_name it returns only one result per fullname...
So if I have a table like this
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Foo | Bar |
| Foo | Bar |
| Bar | Foo |
| Bar | Foo |
| John | Doe |
+------------+-----------+
It returns
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Foo | Bar |
| Bar | Foo |
+------------+-----------+
Now... the clauses that I add into query are these (count and groupby)
$query = "
SELECT s1.*,s2.first_name,s2.last_name,s2.email,s2.id as subscriber_id,s2.phone,s2.info,a.id as agency_id,a.info as agency_info,a.invoice as agency_invoice, Count(s2.*) AS Cnt
FROM subscription as s1
LEFT JOIN agency as a
ON s1.agency=a.id
INNER JOIN subscriber as s2
ON s1.subscriber=s2.id
WHERE $matches AND s1.deleted=0
GROUP BY s2.first_name, s2.last_name HAVING Cnt>1
ORDER BY s1.id DESC
LIMIT $maxid,$limit
";
But as I've already said... it doesn't work and I don't know why... where am I wrong? Does it depend from joining? Is there a way to find a solution or with that query I need to find another solution? Please help me, I've tried other solutions but no way! :D
