Solved

Copy records from one table into another one.

Posted on 2011-09-06
4
297 Views
Last Modified: 2012-05-12
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
Comment
Question by:Zado
  • 3
4 Comments
 
LVL 8

Expert Comment

by:stalhw
ID: 36491030
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
 
LVL 8

Expert Comment

by:stalhw
ID: 36491082
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
 
LVL 8

Accepted Solution

by:
stalhw earned 500 total points
ID: 36491091
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
 
LVL 8

Author Closing Comment

by:Zado
ID: 36501559
Thanks.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question