looping inserts

[jools]

In a vain attempt to stop me banging my head against the wall, does anyone have any insights why I cant (go and do something less boring instead...) use a for loop (or any loop for that matter) to get some data from one table and insert it into another?

 

the loop in question:

 

FOR($i=1;$i<$number;$i++){

$sqlquery = "SELECT colA FROM $source_table WHERE row_id = $i";

$result = mysql_query($sqlquery);

 

$fielda = mysql_result($result,0,"colA");

 

$row = "INSERT INTO $table (fieldb) VALUES ('$fielda')";

$insert_row = mysql_query($row);

 

echo "field A is=" . urlencode($fielda);

 

}

 

the echo happily presents all of fielda from the source_table but it only inserts the first record...???

 

Is it my insert, have I got my fyntax suddled, did I not pay the piper this week, will I sleep tonight?

 

answers on a postcard or here

 

fank u very much.

 

Jools

[per1os]

<?php
for($i=1;$i<$number;$i++){
$sqlquery = "SELECT colA FROM $source_table WHERE row_id = $i";
$result = mysql_query($sqlquery);

$fielda = mysql_result($result,0,"colA");

$row = "INSERT INTO $table (fieldb) VALUES ('$fielda')";
mysql_query($row) or DIE(mysql_error());

echo "field A is=" . urlencode($fielda);

}
?>

 

Try that, if that does not work, post more code as you are only giving us a little bit. For instance what is the value of $number? Where does it get assigned etc.

[jools]

hhmmm had tried this before when the query was outside the loop but this time I got a 'Duplicate entry '' for key 1' from the mysql_error?

 

I did have a auto-increment in the $table but removed this when testing this.

 

The $number is assigned outside the loop as 901, there are 900 rows, I was getting this from the query intially but in my quest for answers have removed that.

[per1os]

If there is no auto-increment field than that is why. You need to something to distinguish one row from the other.

 

Try adding that auto-increment primary key field back in, see what happens.

[jools]

tried that, got 'Duplicate entry '' for key 2' ??

 

appreciate your time.

 

I may be just approaching this task wrong, but I have been given an old table that we want to utilise the data into a new set of tables and so I have imported the old table (was a right mess) into a generic table and just want to chery pick data into the new tables. Originally typed away a script to do the lot and have now been reduced to trying to get a single field into one new table...

 

I would have thought this was a common task...

[per1os]

$row = "INSERT INTO $table (id,fieldb) VALUES ('', '$fielda')";

 

See if that makes a difference or not.

 

If not is fieldb set to be Unique? If so that would be why it is throwing the error.

[jools]

hahaa very good, there was one other field set to be unique, hadn't spotted that, as it wasn't being populated it was apearing to duplicate ' ' hence the vague duplicate error.

 

Ta very much, I will sleep tonight after all.