[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Copy records from one table into another one.

Posted on 2011-09-06
4
Medium Priority
?
301 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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 count occurrences of each item in an array.
Suggested Courses

656 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