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.
Now, If I select England from this MySQL table, then I want MySQL to display me all the corresponding cities for England.
Like this:
Not this:
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. I have attached my .txt 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..

Before you upload you file, you must complete the record, you can not pass blank country. Human can read file and understand that to which country that blank city belong after looking the top row of city group. But computer can not understand that.

Try to attach following file with comma as delimiter

hi..my text file is really big..I think I need to write a code to fill them up..

instead of using load file, you directly read file using php and insert records directly to mysql.

while reading file in code, if you find blank country, then you may set country to country of previous record.

commented: Good advice. Need to pre-process data with procedural language, such as php or Perl. +9

ok bro..I'll try this or think something else..thanks for your help..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.