?
Solved

Copy records from one table into another one.

Posted on 2011-09-06
4
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

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…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

762 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