MySQL output corresponding rows - Webmaster Forums - Webmaster forum for HTML, PHP, ASP, CSS and more

ghosh24 · 07-21-2011, 11:02 PM

HI friends..I have a tab delimited text file with country, city, Participants field.. Now, when I search for a particular entry for eg. Country England, then I want it to show all the cities associated with it Inot just one). My actual table looks like this--
Please check the Screenshot from my first attachment.
Now, If I select England from this MySQL table, then I want MySQL to display me all the corresponding cities for England.
Like this:
Please check the Screenshot from my second attachment.
Not this:
Please check the Screenshot from my third attachment.
One thing, all the rows with city names of the corresponding country is fixed to three.
I loaded the data using LOAD DATA LOCAL INFILE command to load them from my tab delimited text file.
Table created using VARCHAR with Default null..
Could you please tell me the appropriate command for this (like if there's any appropriate LIMIT command or something like that)
Thanks for your help..

Rukbat · 09-16-2011, 04:08 PM

You have 2 cities, London and Taunton, with a country of England. You have 4 cities, Exeter, Leeds, Bristol and Plymouth (along with Melbourne, Kolkata and Mumbai) that have no countries. Your data is corrupt. Add the proper country to each row.

Then your select statement will be

"SELECT Cities FROM test2 WHERE Country = 'England';"

If you also want the country, make that

"SELECT Country, Cities FROM test2 WHERE Country = 'England';"

You can't select a blank country from a record that has a non-blank country, and you can't select based on a country if the record's Country field is blank.

If you want it displayed your way you'll have to write code (PHP if it's a website), to display the country name every third line. (You could also write some fairly complex SQL to do that.)