[SOLVED] which loop to use?

[tgavin]

I'm not sure which loop to use in this situation. I need to get all of the records in the users table and move all of the data in certain fields into another table. This is where I'm currently stuck

 

$count = mysql_result(mysql_query("SELECT COUNT(*) as total FROM users"),0);
for($i=0; $i<=$count; $i++) {
   // do stuff
   // do more stuff
}

[adam291086]

while($row = mysql_fetch_array($result))
{
//your code
}

[chigley]

<?php

$query = mysql_query("SELECT * FROM users") or die(mysql_error());

while($result = mysql_fetch_assoc($query)) {
$field1 = $result["field1"];
$field2 = $result["field2"];

$query2 = mysql_query("INSERT INTO table2 VALUES('{$field1}', '{$field2}')") or die(mysql_error());
echo "Inserted {$field1} and {$field2} into table2<br />\n";
}

?>

 

Like that?

 

That reads field1 and field2 from the users table, and inserts them into table2.

[tgavin]

thanks for the replies. When I do a while loop, I get the following error: 'Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource' after the records are processed. That's why I was looking into the for, or foreach. I think I need a way to count the loops to make it stop when all of the records are processed.

 

$sql = mysql_query("SELECT * FROM ".SUB_DATABASE.".".SUB_TABLE."") or die(mysql_error());
while($row = mysql_fetch_assoc($sql)) {
select_db();
$sql = mysql_query("INSERT INTO subscribers_data (sd_sub_id,cust1,cust2,cust3,cust4,cust5,added_by,is_friend,subscribed,bounced,bounce_count,bounced_id,dont_bounce,ref_url,ip_address,date_added,activated) SELECT ".SUB_ID.",".SUB_CUST1.",".SUB_CUST2.",".SUB_CUST3.",".SUB_CUST4.",".SUB_CUST5.",".SUB_ADDED_BY.",".SUB_IS_FRIEND.",".SUB_SUBSCRIBED.",".SUB_BOUNCED.",".SUB_BOUNCE_COUNT.",".SUB_BOUNCED_ID.",".SUB_DONT_BOUNCE.",".SUB_REF_URL.",INET_NTOA(".SUB_IP_ADDRESS.") AS ip_address,".SUB_DATE_ADDED.",".SUB_ACTIVATED." FROM ".SUB_DATABASE.".".SUB_TABLE."") or die(mysql_error());

mysql_select_db($database, $conn) or die(mysql_error());
$sql = mysql_query("ALTER TABLE list_subscribers DROP cust1,cust2,cust3,cust4,cust5,added_by,is_friend,subscribed,bounced,bounce_count,bounced_id,dont_bounce,ref_url,ip_address,date_added,activated") or die(mysql_error());
}

[chigley]

OK you can't use the same variable name ($sql) for all of your queries. Otherwise it ruins the while() command after one run-through.

 

You do not need to use a for(), as while() only runs for as many records there are.

[tgavin]

Makes sense

 

Thanks!