What is easiest way to copy one table to another table?

IceRegent

I have a need to copy one table selection into another table, based upon the user selection. The query first selects everything from the table rows based upon the user response from a form, and then needs to copy that set of fields into another table based upon the user band name. Here is what I have so far:

$band = $_SESSION['band'];	
$theme = $_POST['theme'];
	if ($_SESSION['oldtheme'] <> $theme)
		{
		$query = "UPDATE band SET theme='$theme' WHERE band = '$band' LIMIT 1";
		$result = mysql_query($query) or exit(mysql_error());
		}
	if ($_POST['resttheme'] == "yes")
		{
			$query = "SELECT * FROM theme WHERE theme='$theme' LIMIT 1";
			$result = mysql_query($query) or exit(mysql_error());

			$


	}

Thanks;
Ice

laserlight

Instead of copying, insert rows into another table such that you map user ids with the primary keys of those selected rows.

IceRegent

The table rows already exist, I do not need to insert rows. I need to 'copy rows' to overwrite what is currently in the existing rows with what is read from the other table rows.

laserlight

IceRegent wrote:
The table rows already exist, I do not need to insert rows.

If the table rows already exist, then you do not need to copy anything either.

IceRegent wrote:
I need to 'copy rows' to overwrite what is currently in the existing rows with what is read from the other tbale rows.

So, you have table A and table B. Table B has some rows that were copied from table A. The rows in table A have some changes to their content. You now want to update those rows in table B? That would be rather pointless, since you should not have the duplication in the first place by using my suggestion.

IceRegent

Ok, let me put it to you a little clearer. The rows I need to copy the data to, already have earlier data in them. Now, I get information from a form submitted by the user, with an indication of the new data-set they wish to utilize. I have the new data-set in another holding table, which never changes, but, rather holds the information for updating the user fields. Now, I need to copy the fields from the holding table, over to the EXISTING fields of the user-table, over-witing the existing user fields with the data copied from the HOLDING fields table.

laserlight

IceRegent wrote:
I have the new data-set in another holding table, which never changes, but, rather holds the information for updating the user fields.

Ah, so this holding table serves as a kind of template for what should be filled in in the actual table?

IceRegent wrote:
Now, I need to copy the fields from the holding table, over to the EXISTING fields of the user-table, over-witing the existing user fields with the data copied from the HOLDING fields table.

Does the user table store data for multiple users?

What I am getting at here is that if you can just change keys rather than data, then you should do so.

IceRegent

The holding table, yes, contains templates for the users. The users table has fields that contain the current template information. When the user selects a new template, the default values from that newly selected template are then transferred over the the users table fields. Yes, the users table contains multiple users, each with their own set of values from the templates. They are also able to individually change each field of their template info to suit their own tastes, however, when they choose to select a new template and say they want to reset the fields, the newly selected template needs to be copied into their fields, thusly overwriting what is currently in their template fields with the holding table template values.

IceRegent

The script as it now stand should do the trick, however, this seems really inefficient. Surely there is a different way, such as with a foreach statement or something?

<?php
session_start();
include $_SERVER['DOCUMENT_ROOT'] . '/include/config.php';

// name = theme, name = restheme - two variables from form

foreach ($_POST as $key => $value)
	{
    $value = trim($value);
    if ($value != '') {$value = "'" . mysql_real_escape_string($value) . "'";}
     else {$value = 'NULL';}
    $$key = $value;
	}
$band = $_SESSION['band'];	
$theme = $_POST['theme'];
	if ($_SESSION['oldtheme'] <> $theme)
		{
		$query = "UPDATE band SET theme='$theme' WHERE band = '$band' LIMIT 1";
		$result = mysql_query($query) or exit(mysql_error());
		}
	if ($_POST['resttheme'] == "yes")
		{
			$query = "SELECT * FROM theme WHERE theme='$theme' LIMIT 1";
			$result = mysql_query($query) or exit(mysql_error());

			$body = $row['body'];
			$headerh1 = $row['headerh1'];
			$menufore = $row['menufore'];
			$menuback = $row['menuback'];
			$menua = $row['menua'];
			$hover = $row['hover'];
			$hoverback = $row['hoverback'];
			$leftback = $row['leftback'];
			$leftfore = $row['leftfore'];
			$centerback = $row['centerback'];
			$centerfore = $row['centerfore'];
			$rightback = $row['rightback'];
			$rightfore = $row['rightfore'];
			$footer = $row['footer'];

		$query = "UPDATE colors SET body='$body', headerh1='$headerh1', menufore='$menufore', menuback='$menuback', menua='$menua', hover='$hover', hoverback='$hoverback', leftback='$leftback', leftfore='$leftfore', centerback='$centerback', centerfore='$centerfore', rightback='$rightback', rightfore='$rightfore', footer='$footer' WHERE band = '$band' LIMIT 1"; 
$result = mysql_query($query) or exit(mysql_error());

	}

$_GET['select'] = "theme";
include 'index.php';
?>

laserlight

Ah, so my idea cannot apply.

One possible solution is this: delete from the user table all rows that belong to the given user, and whose template ids are not in the set of selected templates that are not to be reset. Then, insert into the user table all rows that correspond to the selected templates other than those that are not to be reset.

In both cases, you can hold an array of template ids and use PHP to group them into the two subsets (those to be left alone versus those to be deleted, and then those that were previously selected and not to be reset and those to be newly selected) for use in an IN clause in the SQL statement.