Member Avatar for ghosh22

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.
[IMG]http://i56.tinypic.com/2dceg4p.jpg[/IMG]
Now, If I select England from this MySQL table, then I want MySQL to display me all the corresponding cities for England.
Like this:
[IMG]http://i56.tinypic.com/1zbyoep.jpg[/IMG]
Not this:
[IMG]http://i51.tinypic.com/2lvntsm.jpg[/IMG]
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..

Australia	Sydney 	122
	Melbourne	522
	Canberra	452
England	London	552
	Exeter	235
	Leeds	445
India	Delhi	545
	Kolkata	542
	Mumbai	485
England	Taunton	225
	Bristol	654
	Plymouth	585
  • 2 Contributors
  • 4 Replies
  • 303 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by ghosh22
Member Avatar for urtrivedi

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

Australia,Sydney,122
Australia,Melbourne,522
Australia,Canberra,452
England,London,552
England,Exeter,235
England,Leeds,445
India,Delhi,545
India,Kolkata,542
India,Mumbai,485
England,Taunton,225
England,Bristol,654
England,Plymouth,585
Member Avatar for ghosh22

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

Member Avatar for urtrivedi

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
Member Avatar for ghosh22

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

Edited by ghosh22 because: n/a
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.