[SOLVED] .txt to database problem

[Stooney]

I can't seem to find the problem here.  I am taking data from data.txt, which is all delimited by | and storing it a database.  I've output the queries to make sure the syntax is right.  I copied the output directly into phpmyadmin and each query runs fine one by one.  But when I try to run the query from the script, I get the following error:

 

Unknown column 'city' in 'field list'

 

but 'city' is a field, and the query runs fine with ran via phpmyadmin.  Here's the code and a sample of data.txt:

 

<?php
include("dbc.php");
$file="data.txt";
$data=file_get_contents($file);

$edata=explode("|", $data);
$count=count($edata);
for($i=0; $i<=$count; $i+=13){
$query="INSERT INTO `stauctioneer_users` (`biddernum`, `lname`, `fname`, `address`, `city`, `state`, `zip`) VALUES ('".$edata[$i]."', '".$edata[$i+1]."', '".$edata[$i+2]."', '".$edata[$i+4]."', '".$edata[$i+5]."', '".$edata[$i+6]."', '".$edata[$i+7]."')";
$do=mysql_query($query);
echo mysql_error();
echo $query;
echo "<br>"; 
}
?>

 

 

data.txt

2|LNAME|Don|1|11340 Tom Ulzoas|El Paso|TX|79936|9154911591||0|0||
3|LNAME|Erika|1|6305 Aztec|El Paso|TX|79925|9152035120||0|0||
4|LNAME|Esther|1|6513 Escondido Suite A|El Paso|TX|79912|9156130024||1|0||
5|LNAME|Paul|1|917 Cortijo|El Paso|TX|79912|9155810070||0|0||
6|LNAME|Michelle|1|5609 Burning Tree|El Paso|TX|79912|9156033590||1|0||
7|LNAME|Ron|1|3408 Dialrock Ln.|El Paso|TX|79935|9152032487||0|0||
8|LNAME|Alfonso|1|7345 Royal Arms|El Paso|TX|79912|9157606901||0|0||
9|LNAME|Roger|1|3209 Killarney|El Paso|TX|79925|9155919745||0|0||
10|LNAME|ROXy|1|13233 widhorse|clint|tx|79836|||0|0||
11|LNAME|John|1|6530 River Road| El Paso|tx|79925|9155553535||1|0||
12|LNAME|thomas|1|13233 wildhorse|clint|tx|79836|9158514513||1|0||
13|LNAME|Donna|1|6017 Bella Bonna Circle|El Paso|tx|79924|9157570144||0|0||
14|LNAME|Donna|1|6017 Bella Bonna Circle|El Paso|tx|79924|9157570144||0|0||
15|LNAME|RAUL|1|3011 orkney|el paso|tx|79925|9218947256||0|0||
16|LNAME|Dennis|1|5001 Dearborne|El Paso|tx|79924|9158223871||0|0||
17|LNAME|Beatrice|1|1280 Bowie St.|Beaumont|tx|77701|4098131000||0|0||

 

It goes on for about 3500 users.  Thanks in advance for help. *edited out names for obvious reasons*

[Crew-Portal]

It looks as if the query worked properly so who cares if you get an error message? since its only for your private use there should be no problem?

[Stooney]

Well the script doesn't put the info in the database due to the error.  I dont have to time to put it in query by query in phpmyadmin, I need the script to take care of it.

[SirChick]

have you checked city in your database is not: "City" rather than "city" ?

 

Capitals matter.

[SirChick]

for($i=0; $i<=$count; $i+=13){

 

 

are you meant to only have one =  ? cos that will set $i always to 0 if your meant to do for $i equal to 0 then it needs to have two "==" rather than "="

 

Just thought I'd check.

[Stooney]

the for loop is fine, works right.  I have no clue what I'm missing

 

Here's the table structure straight from phpmyadmin:

 

CREATE TABLE `stauctioneer_users` (
  `userid` int(6) NOT NULL auto_increment,
  `biddernum` int(6) NOT NULL,
  `username` varchar(32) collate latin1_german2_ci NOT NULL,
  `password` varchar(32) collate latin1_german2_ci NOT NULL,
  `fname` varchar(32) collate latin1_german2_ci NOT NULL,
  `lname` varchar(32) collate latin1_german2_ci NOT NULL,
  `cphone` varchar(32) collate latin1_german2_ci NOT NULL,
  `hphone` varchar(32) collate latin1_german2_ci NOT NULL,
  `wphone` varchar(32) collate latin1_german2_ci NOT NULL,
  `address` tinytext collate latin1_german2_ci NOT NULL,
  `email` tinytext collate latin1_german2_ci NOT NULL,
  `level` int(2) NOT NULL default '0',
  `city` varchar(32) collate latin1_german2_ci NOT NULL,
  `state` varchar(32) collate latin1_german2_ci NOT NULL,
  `zip` smallint(5) NOT NULL,
  PRIMARY KEY  (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=7 ;

[slapdashgrim]

actually the 'initial expression' ($i = 0) is only run once when the for loop is first called. so that is right.

im not quite sure why it is plus 13 tho every time. explain please.

[Stooney]

well, there's 13 variables per record in data.txt.  so when i read it all to a string, then explode(), every 13 parts of the array = 1 query.  So everytime to for loop loops, I want to move on to the next 13 variables.  Hope that makes sense.  Maybe there's an easier way to go about it?  That's just how I've usually gone about it.

[slapdashgrim]

that makes sense ill keep thinking

 

and lol you seem to know some about explode i need help with that i have a topic for help here http://www.phpfreaks.com/forums/index.php/topic,171657.0.html

[PFMaBiSmAd]

Your posted code is echoing the mysql_error() and the query string. Is this error occurring on the first pass through the loop? Also, what is the query string that causes the error (xxxxx out any sensitive information, but leave all punctuation.)

[Stooney]

The error is displayed as many times as the loop loops.  I had it echo both the query string and mysql_error() per loop for debugging.  I'll copy+paste a bit of the output of $query:

 

INSERT INTO `stauctioneer_users` (`biddernum`, `lname`, `fname`, `address`, `city`, `state`, `zip`) VALUES ('2', 'LNAME', 'Don', '5555 Tom Ulzoas', 'El Paso', 'TX', '79936')
INSERT INTO `stauctioneer_users` (`biddernum`, `lname`, `fname`, `address`, `city`, `state`, `zip`) VALUES (' 3', 'LNAME', 'Erika', '5555 Aztec', 'El Paso', 'TX', '79925')
INSERT INTO `stauctioneer_users` (`biddernum`, `lname`, `fname`, `address`, `city`, `state`, `zip`) VALUES (' 4', 'LNAME', 'Esther', '5555 Escondido Suite A', 'El Paso', 'TX', '79912')
INSERT INTO `stauctioneer_users` (`biddernum`, `lname`, `fname`, `address`, `city`, `state`, `zip`) VALUES (' 5', 'LNAME', 'Paul', '5555 Cortijo', 'El Paso', 'TX', '79912')
INSERT INTO `stauctioneer_users` (`biddernum`, `lname`, `fname`, `address`, `city`, `state`, `zip`) VALUES (' 6', 'LNAME', 'Michelle', '5555 Burning Tree', 'El Paso', 'TX', '79912')

 

That's copied straight from the browser when only outputting the query string per loop.  That's 5 of 3500ish.

[rab]

<?php
include("dbc.php");
$file="data.txt";
$data=file_get_contents($file);
$data=explode("\n", $data);

foreach($data as $qstring) {
    $qstring = explode("|", $qstring);
    foreach($qstring as $k => $v)
        $qstring[$k] = "'$v'";
    $qstring = implode(",", $qstring);

    $query="INSERT INTO `stauctioneer_users` (`biddernum`, `lname`, `fname`, `address`, `city`, `state`, `zip`) VALUES ($qstring)";
$do=mysql_query($query);
if( !$do ) mysql_error();
echo $query;
echo "<br>"; 
}
?>

 

Try it out.

[Stooney]

Working it out as we speak rab.  I only need 7 items from each row of data (which has about 14 items).  Once I get that it should work. 

[PFMaBiSmAd]

I would remove and retype the `city` portion of the query statement. It might have some non-printing character as part of it or the back-ticks are not really back-ticks `

 

Back-ticks are only necessary when a table or column name contains characters that are not normally permitted or if it is a reserved mysql keyword, so removing them would not hurt in this case.

[Stooney]

well, I re exported the access database with only the info I needed.  Re typed and took out the ` from the query.  back to the old error:

 

Unknown column 'city' in 'field list

 

I'm going to remake the column, then the table if it persists.

[Stooney]

Alright I finally got the data in the database.  All I did was use the script to echo the query string, copied all and pasted into the query box in phpmyadmin, and it executed all the queries just fine.  So all the info is in the database now, but the script never did work.  No matter though, it's done and thank you all for helping.