I am new to php and MySql and am trying to write a php script to query a MySQL table. I have spent hours searching stackoverflow but don't quite understand some of the answers from similar questions like How to select unique values from a table? and MYSQL + Select 2 columns - 1 being Unique
I have a table with a 'location' column and an 'address' column and some other columns with other information about the location etc. The location is a name of, for example, a shop. The address column holds the street address of that shop.
A shop sometimes has multiple outlets at different addresses and I need to have a query that shows all data from the table with the location only being displayed multiple times if the address is unique.
I have the php connection to the database and selection of the database working but need help with the MySql query.
I have used a GROUP BY function on the location, but all addresses get put under the same location.
This is what I have used but only unique locations get displayed.
<?php
mysql_connect("localhost","username","password");
mysql_select_db("dbname");
$sql=mysql_query("SELECT * from table GROUP BY location");
while($row=mysql_fetch_assoc($sql))
$output[]=$row;
print(json_encode($output));
mysql_close();
?>
How do I go about making a query the allows the location to be displayed multiple times if the address is unique?
Thanking you in advance..
Not sure how to post table structure but this is what I could get from myphpadmin
**Column** **Type** **NULL** **Comments**
_id int(11) No auto_increment
location text No Shop Name
address text No Shop Address
suburb varchar(30) No
state varchar(3) No
longitude float No longitude
latitude float No latitude
date date No
time time No
What I am after is all locations with a unique address. Some locations will have the same name and same address, but some locations will have the same name but a different address. For example.. If table looked like
**Location** **Address**
Target Pacific Fair
Kmart Pacific Fair
Kmart Robina
Kmart Southport
Target Pacific Fair
Target Tweed City
Kmart Pacific Fair
Best and Less Pacific Fair
I want the results to show
**Location** **Address**
Target Pacific Fair
Target Tweed City
Kmart Pacific Fair
Kmart Robina
Kmart Southport
Best and Less Pacific Fair
If I understand your question correctly, you need to group by both location and address:
SELECT location, address FROM table GROUP BY location, address
or you can use DISTINCT:
SELECT DISTINCT location, address FROM table
I'm assuming you could have more cases than you show in the data. You could have NULLs in col1, you could have names that occur only in col1 or only in col2, etc.
SELECT a.name, c1.`count`, c2.`count`
FROM (SELECT col1 AS name FROM `Table` UNION SELECT col2 FROM `Table`) a
LEFT JOIN (SELECT col1, COUNT(*) AS `count` FROM `Table` GROUP BY col1) c1
ON a.name = c1.col1
LEFT JOIN (SELECT col2, COUNT(*) AS `count` FROM `Table` GROUP BY col2) c2
ON a.name = c2.col2;
Explanation: The derived table a is a union of all names that appear in either column. Then make two more derived tables, one with each each name from col1 and the count of how many times it occurs, and then another similar derived tale for names in col2.