CSV File query in PHP
Posted: Fri Feb 10, 2006 11:25 am
I have a large .csv file (1 GB) containing information pertaining to different coordinate positions. The user inputs a pair of coordinates on a page and then presses "submit". This sends the coordinates to a page that gets the info from the .csv pertaining to the coordinates they entered.
However, there is no guarantee that the exact coordinates they entered will be in the .csv file, so I need to choose the closest coordinates. To do this, I would like to pull all coordinate pairs that are like the input coordinates and place them into an array (i.e. if the coordinates are entered to 6 significant figures, I would truncate it to 4 sig figs and find all coords that share those first sig figs.)
Up until now I have been doing this with mySQL. I imported the .csv file into mySQL and had a huge table in my database. Well, now I have about 30 other tables just like it (oh, sweet growth!) that I need to use as well. I don't want to have a huge mySQL database, so I'd prefer to put the .csv files into a folder on the server and just pull from them instead. Here is a basic summary of how I pull the data from mySQL:
Basically what I need is some help with a script that can pull entire rows for a range of field values from a .csv file, then store them in an array in PHP. Once I get the array of potential rows, I can write a script to choose the closest value. My only issue is getting teh array built efficiently. Any help would be appreciated.
However, there is no guarantee that the exact coordinates they entered will be in the .csv file, so I need to choose the closest coordinates. To do this, I would like to pull all coordinate pairs that are like the input coordinates and place them into an array (i.e. if the coordinates are entered to 6 significant figures, I would truncate it to 4 sig figs and find all coords that share those first sig figs.)
Up until now I have been doing this with mySQL. I imported the .csv file into mySQL and had a huge table in my database. Well, now I have about 30 other tables just like it (oh, sweet growth!) that I need to use as well. I don't want to have a huge mySQL database, so I'd prefer to put the .csv files into a folder on the server and just pull from them instead. Here is a basic summary of how I pull the data from mySQL:
Code: Select all
$lat=$_GET['lat'];
$lon=$_GET['lon'];
$latRound=round($lat,1);
$lonRound=round($lon,0);
$sql="SELECT * FROM coordData WHERE latitude LIKE '$latRound%' AND longitude LIKE '$lonRound%'";
$result=mysql_query($sql,$conn);