Member Avatar for joeltonnberg

Hey,
I have the following query which I would like to insert into another table which I guess would demand some kind of loop or such.

The query:

$colname_GetName = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Name = $_SESSION['MM_Username'];
}
mysql_select_db("db_name");
$query_Recordset = sprintf("SELECT * FROM Table WHERE UserName = %s", GetSQLValueString($colname_GetName, "text"));
$Recordset = mysql_query($query_Recordset) or die(mysql_error());
$row_Recordset = mysql_fetch_assoc($Recordset);
$totalRows_Recordset = mysql_num_rows($Recordset);

I would like to loop through and insert all values for $row_Recordset, $row_Recordset etc into a new table.

  • 2 Contributors
  • 2 Replies
  • 97 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Designer_101

Dani AI

Generated

posted a SELECT and asked how to copy those rows into another table; @Designer101 correctly pointed out that iterating the resultset will work. Two approaches are worth considering depending on intent and volume: (A) do the copy in one SQL statement on the server (fast and simple when you just want the same columns copied), or (B) fetch rows and INSERT them with a prepared statement (useful when you must transform values or skip rows). Prefer PDO or mysqli over the old mysql* functions shown in the thread — those were removed from PHP and lack modern safety features.

A single-statement copy (recommended for straight copies; list columns explicitly and test the SELECT first):

INSERT INTO target_table (col_a, col_b, col_c)
SELECT col_a, col_b, col_c
FROM source_table
WHERE username = 'someuser';

If values need per-row processing (or you need to bind parameters), use a prepared INSERT inside a transaction. Prepare the INSERT once, loop over fetched rows, bind the transformed values, execute, then commit. Using a transaction reduces I/O and keeps the copy atomic.

Do not forget:

  • Always specify column lists on both INSERT and SELECT to avoid schema mismatches.
  • If the target has unique keys and you want to skip/merge duplicates, consider INSERT IGNORE or ON DUPLICATE KEY UPDATE.
  • For large data sets, prefer INSERT...SELECT or batch commits rather than single-row autocommits.
  • Backup data first and test on a copy before running against production.

For reference on the server-side copy pattern and prepared statements, see the MySQL docs on INSERT ... SELECT (https://dev.mysql.com/doc/refman/8.0/en/insert-select.html) and the PHP manual on PDO prepared statements (https://www.php.net/manual/en/pdo.prepared-statements.php).

Member Avatar for Designer_101

Hi
Is this what your looking for?
Obviously replacing the '####' with the column name of the data you want to repeat. If you want everything to be printed. Simply create a variable outside the while loop and then increment it after the process is done (just before the end of the while loop). Put this as the array value.

$colname_GetName = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Name = $_SESSION['MM_Username'];
}
mysql_select_db("db_name");
$query_Recordset = sprintf("SELECT * FROM Table WHERE UserName = %s", GetSQLValueString($colname_GetName, "text"));
$Recordset = mysql_query($query_Recordset) or die(mysql_error());
$totalRows_Recordset = mysql_num_rows($Recordset);
while ($row_Recordset = mysql_fetch_assoc($Recordset)) {
echo "<table>";
echo "<tr>";
echo "<td>";
echo "$row_Recordset['#####']";
echo "</td>";
echo "</tr>";
echo "</table>";
}
echo "<br><br>There are ".$totalRows_Recordset." row(s)";

If you need any help, just post
:)

Member Avatar for Designer_101

Sorry I got the wrong idea to what you wanted after re-reading.
The same method applies though. Just in a different context.
Hope this helps still

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.