looping results to insert in table

Anon

ok...since my last question was obviously too hard, so here's a better one...

I know how to select data from a table, and I know how to loop it using a while statement for display purposes.

So my question is this, I want to select all data from one table and insert it into another without displaying it at all. How can this be accomplished?

Here's some code to reflect on, but it only snags the first record:

$sql = "SELECT * FROM table1 ORDER BY id ASC";

$connection = mysql_connect($default->db_host, $default->db_user, $default->db_pass)
or die ("Couldn't connect to server.");

$db = mysql_select_db($default->db, $connection)
or die ("Couldn't select database.");

$sql_result = mysql_query($sql)
or die("Couldn't execute query.");

if (!$sql_result) {

echo "Could not snag content for migration! Contact the Administrator!";
} else {
if (mysql_num_rows ($sql_result) > 0 ) {
while ($row = mysql_fetch_array($sql_result)) {
$data1 = $row["data1"];
$data2 = $row["data2"];
$data3 = $row["data3"];
}
$sql = "INSERT INTO table2 VALUES ( NULL, '$data1', '$data2', '$data3')";

$connection = mysql_connect($default->db_host, $default->db_user, $default->db_pass)
or die ("Couldn't connect to server.");

$db = mysql_select_db($default->db, $connection)
or die ("Couldn't select database.");

$sql_result = mysql_query($sql)
or die("Couldn't execute query.");
if (!$sql_result) {

echo "Couldn't write to table! Please contact the administrator!";
} else {
echo "
<table width=$default->table_width border=\"0\" cellspacing=\"2\" cellpadding=\"2\" align=\"center\">
<tr align=\"left\">
<td>You have successfully migrated the data from the staging area to the live page.</td>
</tr>
</table>";

merkinmuffley

disclaimer: I don't use mySql and don't know those functions.

just by what you wrote though, I suspect that the looping is not complete, you're not doing one INSERT per row selected.

your code is something like this:

$sql_result = result set from some query;
if (1 or more rows)
{
while (each row)
$data = data from row;

insert new row;

}

I think you merely have to move the code that inserts the new row, into the while loop.

merkinmuffley

maybe this is a dumb question, but can you get result set meta-data -- the names of the columns, specifically -- with the mySql functions? if so i suggest a nicer way to go about this, one you can generalize to any 2 tables since you won't have to write column names in your code.

example:

$rs = result set from some query;
while ($record = next record in $rs)
{

// get the column names of the result set
$columnNames = ??

// build the columns and values parts of
// the insert statement
$cols = $values = "";
for ($i=0; $i < count($columnNames); $i++)
{
$cols .= $columnNames[$i].", ";
$vals .= "'".$record[$i]."', ";
}

// strip trailing commas
$cols = substr($cols, 0, -2);
$vals = substr($vals, 0, -2);

$stmt = "INSERT INTO Table ($cols) VALUES ($vals)";

et cetera

}

Anon

At least you decided to take a stab at it, and that's more than most who have viewed this dilema. I attempted all sorts of manifestations of the while loop, and without much success. If anyone has any additional comments to add, please do.

Anon

I don't know about your specific database, but in Postgresql you can do:

select * into new_table from old_table;

They have to be in the same database, but other than that it should work.

Anon

INSERT INTO TABLE ... SELECT * FROM OTHER TABLE;

Chris

Anon

yeah... thanks for all your help guys. The command that worked was:

insert into tabl2 tbl1.item1, tbl1.item2 from tbl2;

just make sure you fields from one table to the next correspond.