Solved

Copy records from one table into another one.

Posted on 2011-09-06
4
294 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now