Insert a text file into a column

merlinti

Hi all,

I have a database: streetguide
I have a table: streets
I have a text file with some zipcodes: zip.txt (with no commas)
ex:
89103
89146
89235

The table "streetguide" has 8 columns , 7 that are filled with data already.

The last column is "zip". I'm trying to fill it with the data from zip.txt.

I have been able to add it to the "zip" column, but it appends it to the end of the table by adding records instead of filling the empty spaces in the "zip" column.

Here is what I have:

LOAD DATA INFILE 'G:/My Documents/~~Web Files/StreetGuide/Database/zip01.txt'
INTO TABLE streets (zip);

I'm using phpMyAdmin.

Thanks for any help

daphreeek

I assume you have an ID field with an autonumber (or atleast a unique ID)

If its an autonumber then make a php file like this:

<?php
---include database connect here---

$zip = file("G:/My Documents/~~Web Files/StreetGuide/Database/zip01.txt");

$result = @mysql_query("SELECT COUNT(id) FROM streets",$db);
$myrow = @mysql_fetch_array($result);
$numrecords = $myrow["COUNT(id)"];

for ($id=1; $id<=$numrecords; $id++)
   mysql_query("UPDATE streets SET zip='".$zip[$id-1]."' WHERE id='$id'");

?>

That isnt tested so I dont know if it will work - its just quickly thrown together 🙂

Hope it helps
- Matt

merlinti

Hi daphreeek,

Thanks for the response.
This is what I tried, but it's not working.
My Unique ID field is: "StreetID"

<?php

include ("../mysqlinfo.php");

$zip = file("zip01.txt");

$result = @("SELECT COUNT(StreetID) FROM streets",$DBName);
$myrow = @mysql_fetch_array($result);
$numrecords = $myrow["COUNT(StreetID)"];

for ($StreetID=1; $StreetID<=$numrecords; $StreetID++)
mysql_query("UPDATE streets SET zip='".$zip[$StreetID-1]."' WHERE StreetID='$StreetID'");

I'm not sure if I should have substituted the variables or not.

thanks

daphreeek

as i said it wasnt tested. you'll have to play with it a bit.

sorry bout that.
- Matt

kevinsequeira

is the order of the zip codes in the zip file the same as the order of the records ??
eg zip file
89103 - > go to first row in table ??
89230 - > go to 2nd row in table ??
89211 - > go to 3rd row in table ??

and is the primary key StreetID sequential ie 1,2,3,4,5,6,7,8 .....

if thats the case try this

<?php
$handle = fopen ("G:/My Documents/~~Web Files/StreetGuide/Database/zip01.txt", "r");
//set up database connections
//starting streetID
$x = 1;
while (!feof ($handle)){
    $buffer = fgets($handle, 4096);
    echo $buffer;
	$query = "UPDATE streets SET zip='".$buffer."' WHERE StreetID = $x";
	mysql_query($query);
	$x++;
}
fclose ($handle);
?>

reg
kevin

merlinti

Thanks for the help, I tried your code but all I got was a list off all the zip codes across the screen like this:

89106 89005 89121 89117 89142 89128 89117 89117 89134 89142 89142 89052 89119 89110 89131 89014 89120 89156 89106 89014 89115 89134 89145 89107 89014 89107 89145 89015 89031 89147 89115 89115 89122 89101 89110 89122 89012 89031 89031 89102 89031 89145 89142 89147 89121 89015 89108 89156 89156 89131 89131 89131 89143 89014 89118 89145 89147 89015

It did not write it to the table.

daphreeek

What is StreetID? is it an autonumber? if so - what does it start at? If it isnt an autonumber or doesnt start at 1 then neither of the above solutions would work.

If it is an auto number then both these solutions should work.

The other alternative is to do this:

<?php
//---database connect here---

// read file
$zip = file("G:/My Documents/~~Web Files/StreetGuide/Database/zip01.txt");

// get all the StreetId's from streets
$result = @mysql_query("SELECT StreetID FROM streets",$db);
$x = 0;

// loop through the StreetID's (add keys to each)
while ($myrow = @mysql_fetch_array($result)) {
   // insert the zip code for each
   mysql_query("UPDATE streets SET zip='".$zip[$x]."' WHERE id='".$myrow['StreetID']."'");
   $x++;
}

?>

That way you know it is the correct ID. The above should work if its autonumber or not.

Hopefully that works 🙂
- Matt

merlinti

Yes it's auto-increment

I'll give it a try. Thanks