Grouping and sorting results

Posted on 16th Feb 2014 07:03 pm by admin

Please help with this query.

I have 2 tables in a database, countires and cities. states looks like this

countries

id -----------country
1 ------------uk
2 ------------us
3 ------------japan

cities
id ---country ----city
1 ----1 ----------London
2 ----1 ----------Manchester
3 ----2 ----------New York
4 ----2 ----------Washington
5 ----2 ----------San Francisco
6 ----2 ----------Las Vegas
7 ----2 ----------New Orleans
8 ----3 ----------Tokyo


I have queried the database as follows:

///////////////////////// Make the country query //////////////////////////////
$q_country = "SELECT * FROM countries";
$result_country = mysqli_query($dbc, $q_country);
$row_country = mysqli_fetch_array($result_country);

///////////////////////// Make the city query //////////////////////////////
$q_city = "SELECT * FROM cities";
$result_city = mysqli_query($dbc, $q_city);
$row_name = mysqli_fetch_array($result_name);

I want to display the data as follows:

Country - UK
Cities - London, Manchester

Country - US
Cities - New York, Washinton, San Francisco, Las Vegas, New Orleans

Country - Japan
Cities - Tokyo

I'm new to PHP and just can't work it out. Please can somebody point me in the right direction?!!!! PLEASE!!!!!

Your Answer:

Login to answer

Other forums

Select Lists into MYSQL
Hello All, I am new to PHP @ 1 week. So borrowing code anywhere I can. I am making progress, but h

Warning message
I've put a website that I was doing live and I'm getting this warning message when I try to add a ne

Using loop to count number of entries
I'm writing a program that must ask user to type in numbers. After each entry, the program has to re

PHP Error
On my .php page I have a drop down box that has several names in it. When a user clicks the name &am

Mail from PHP form not always ending up in my inbox?
Hi guys,

I'm a real newbie to PHP and have managed to put together a simple mail form that se

Post PHP form to non-existing page
The company that I work for host their PHP code on IIS on a Windows server, no Apache, so no mod_rew

PHP page loading in Firefox but not in IE
hello,

i am currently doing a project in PHP and i have a problem. If I view the project in M

Line break?
Hi, I'm new to the forum and new to php. I'm not sure if I'm using the correct terminology so here i

try catch error
I have been trying try catch but, I can't get it to work. The code opens a non existing file called

Is there a quick way to do this?
Hi all,

I need to create a mysql table from php that goes a little something like this