URGENT HELP! - PHP while loop

[jatpatel1]

Hi there,
I have been scratching my head for a few days now! with this problem:
I am trying to echo out some data from a mysql table.
I have a column name Country which has the following Countries: India, Malaysia Austaralia etc.
I also have a column name Town which has the following Towns: Varkala, Cochin, Kula Lumpar, Sydney, Cairns etc.

Each town is linked with the accosiated countries eg:
India - Varkala
India - Cochin
Malaysia - Kula Lumpar
Australia - Sydney
Australia - Cairns

In php I can echo out each row using while loop, but the problem is I want to group the countries so it will display the country once and all the towns related underneath. For example:
India
- Varkala
- Cochin
Malaysia
- Kula Lumpar
Australia
- Sydney
- Cairns

Please have a look at the problem by visiting the following site:
http://www.jatinpatel.co.uk/blog
If you look on the right hand side where it says "Other Blogs".

If you can help I will be very greatful!

Thank you
Jatin

[chrishea]

OK, so your MySQL Select should be ordering by country and then town and you probably need some while loops to detect when the town changes and when the country changes. You will have to get beyond just echoing what is in the database and write some code to determine when a break occurs and do the appropriate thing. You need to write the code and if you have difficulty making it work, someone here may be willing to help you. If you're lucky, someone may have a code snippet that does something similar that they can give you. If you don't have the PHP / programming knowledge to do it, then you have a problem and you need to do some learning or you need to find someone with the skills who can do it for you.

[jatpatel1]

pls ignore this - can't delete this post

[jatpatel1]

OK, so your MySQL Select should be ordering by country and then town and you probably need some while loops to detect when the town changes and when the country changes. You will have to get beyond just echoing what is in the database and write some code to determine when a break occurs and do the appropriate thing. You need to write the code and if you have difficulty making it work, someone here may be willing to help you. If you're lucky, someone may have a code snippet that does something similar that they can give you. If you don't have the PHP / programming knowledge to do it, then you have a problem and you need to do some learning or you need to find someone with the skills who can do it for you.

Chris thank you for your reply!
I have a good programming knowledge but cant seem to be working this one out.
I have tried using GROUP BY in the SQL Query but still not what I want.
Im preety sure its simple enough to do but if someone can point me in the right directions then I will take it from there.

This is what my code looks like:

<?php
$sql_list = mysql_query("SELECT * FROM tblblogs");
while($row_list=mysql_fetch_array($sql_list)){
	echo $row_list['BCountry'];

echo'<li><a href="?blog=' . $row_list['BId'] . '">' . $row_list['BTown'] . '</a></li>';

}
?>

As you can see that it will echo out the country and town from each row which is not what I'm looking for. I want it to echo out the town from each row but the country only once. So basically I want to group the countries and display the towns foe that country.

I hope that this is a better insight of the problem.

Thanks Jat

[CFROG]

There may be a better solution to this but a really quick way to get around it (I think) would be to nest one loop inside of the other (wouldn't be my first choice).

<?php

$sql_list = mysql_query("SELECT DISTINCT BCountry FROM tblblogs");

while($row_list=mysql_fetch_array($sql_list)){
	echo $row_list['BCountry'];

$get_town = mysql_query("SELECT DISTINCT BTown FROM tblblogs WHERE BCountry = ''.$row_list['BCountry'].'' ");

while($town=mysql_fetch_array($get_town))
{
echo'<li><a href="?blog=' . $town['BId'] . '">' . $town['BTown'] . '</a></li>';
}
}

?>

It's worth a shot anyhow

[jatpatel1]

Thanks CFROG! your a genius lol
Although I was on the right lines. I tried doing that but I put the second SELECT query outside the while loop and not inside. I have learnt somthing new today! :D
I thought if you put a while into a while it does it four times.
I completley understand your code but just one thing. What does DISTINCT do in the query?

Thanks again!

Jat

[CFROG]

In so many words DISTINCT eliminates the retrieval of duplicated data.

If your table had entries like

USA
USA
India
Canada
India
Canada
USA

DISTINCT would return

USA
India
Canada

You had multiple entries for the same country and DISTINCT ensured that only one was returned. Without it your results will not be what you expect. Remove it from the query to see what it does. 8-)

[ali mebratu]

write a c++program that displays mid exam,final exam of 100 students and also displays total mark,average,maximum mark and name of students by ascending order depending on there total mark

[kingredyard]

i am also having same problem. Please hekp me out if any once know the solution.

I want to display the output like
India
- Varkala
- Cochin
Malaysia
- Kula Lumpar
Australia
- Sydney
- Cairns

but it shoe like this

India
- Varkala
- Cochin
- Kula Lumpar
- Sydney
- Cairns
Malaysia
- Varkala
- Cochin
- Kula Lumpar
- Sydney
- Cairns
Australia
- Varkala
- Cochin
- Kula Lumpar
- Sydney
- Cairns