• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Copy records from one table into another one.

Hi,


I've got a table:

TABLE_NO_1:
File | User
------------
filen1 | Admin
filen2 | Admin
filen3 | Regular
filen4 | Admin

And I want to copy each File value into another table, but I want to insert all values into same row, but each value into different column, example:

TABLE_NO_2:
User | File1 | File2 | File3 | File4
------------------------------------
Admin | filen1 | filen2 | filen4 | NULL

I started with this:
<?php
mysql_connect('localhost','user','pass');
mysql_select_db('database');

$select=mysql_query("SELECT * FROM TABLE_NO_1 WHERE User='Admin'");
while($row=mysql_fetch_array($select)) {
	$Filename=($row['File']);
}
/*Now I probably need to process those filenames somehow and insert into another table*/
$copy = mysql_query("INSERT INTO TABLE_NO_2 (File1, File2, File3, File4) VALUES (/*I need help here*/)");
/*After all I want to delete values from first table, so I used this script:*/
$delete = mysql_query("DELETE FROM TABLE_NO_1 WHERE User='Admin'");
?>

Open in new window

You could suggest some different way to do this, I just provided my idea, but it doesn't have to be done this way.


Thanks for any help.
0
Zado
Asked:
Zado
  • 3
1 Solution
 
stalhwCommented:
You know you have a max of 4 files?

INSERT INTO TABLE2 (user, file1, file2, file3, file4)
SELECT T1.user, T1.file, T2,file, T3,file, T4.file
FROM TABLE1 T1
LEFT JOIN TABLE1 T2 ON T1.user=T2.user AND T2.file>T1.file
LEFT JOIN TABLE1 T3 ON T2.user=T3.user AND T3.file>T2.file
LEFT JOIN TABLE1 T4 ON T3.user=T4.user AND T4.file>T3.file

I'm using T4.file>T3.file to have them in order...

I'm pretty sure it can also be done with a PIVOT query, but I'm not an expert on that and if you know you got 4 files max, then my query shoudl work.
0
 
stalhwCommented:
If your SQL is 2005 or later, look at PIVOT:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

and if you want to do it user by user using your php:

<?php
mysql_connect('localhost','user','pass');
mysql_select_db('database');

$Filename=array('Null','Null','Null','Null');
$idx=0;
$select=mysql_query("SELECT * FROM TABLE_NO_1 WHERE User='Admin'");
while($row=mysql_fetch_array($select)) {
	$Filename[$idx]=($row['File']);
	$idx++;	
}


$sqlcopy="INSERT INTO TABLE_NO_2 (User, File1, File2, File3, File4) VALUES ('Admin'";
for ($idx=0; $idx<4; $idx++)
	$sqlcopy.=",'{$Filename[$idx]}'";
$sqlcopy.=")";

$copy = mysql_query($sqlcopy);

/*After all I want to delete values from first table, so I used this script:*/
$delete = mysql_query("DELETE FROM TABLE_NO_1 WHERE User='Admin'");
?>

Open in new window

0
 
stalhwCommented:
ops made a mistake here...
corrected code:

<?php
mysql_connect('localhost','user','pass');
mysql_select_db('database');

$Filename=array('Null','Null','Null','Null');
$idx=0;
$select=mysql_query("SELECT * FROM TABLE_NO_1 WHERE User='Admin'");
while($row=mysql_fetch_array($select)) {
	$Filename[$idx]="'{$row['File']}'";
	$idx++;	
}


$sqlcopy="INSERT INTO TABLE_NO_2 (User, File1, File2, File3, File4) VALUES ('Admin'";
for ($idx=0; $idx<4; $idx++)
	$sqlcopy.=",{$Filename[$idx]}";
$sqlcopy.=")";

$copy = mysql_query($sqlcopy);

/*After all I want to delete values from first table, so I used this script:*/
$delete = mysql_query("DELETE FROM TABLE_NO_1 WHERE User='Admin'");
?>

Open in new window

0
 
ZadoAuthor Commented:
Thanks.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now