swapping columns in different tables

johnskywalker99

Hello, I'm really stuck on how to achieve the following!

User selects a column of from 'table 1' and moves it to (replaces)a nominated column in 'table 2'.

'table 1' is a temporary table of data
'table 2' is a live data table

I can only think how to do it if both tables have the same number of records in, Obviously if the number of records in 'table 1' exceeded the number of records in 'table 2' the script would need to recognise this and create the appropriate number of records in 'table 2'.

*Overall objective : a customer can upload data to a mysql table via a tab deliniated text file to 'table1' they then select column(s) from 'table1' to replace nominated columns in 'table2'.

*table1, table2 structure :
tmp (auto inc)
RecRow
RecTable
Col000
Col001
Col002
Col003
Col036
Col124
Col208
Col344
Col376
Col392
Col410
Col578
Col826
Col682
Col752
Col756
Col840
Col978
Col004
Col005
Col006
Col007
Col008
Col009
Col010
Col011

I've been struggling with this for a few days now. Can someone please tell me how to achive this?

Many thanks

John

paulnaj

Hi John,

*Overall objective : a customer can upload data to a mysql table via a tab deliniated text file to 'table1' they then select column(s) from 'table1' to replace nominated columns in 'table2'.

With your goal in mind, you don't actually need to create the temporary table1 ...

1) User uploads file.
2) PHP parses the file and displays it in columns.
3) User chooses source columns and target columns from table2.
4) PHP parses the file again to update table2, according to the user choice in step (3).

Hope that helps!

Paul 🙂

johnskywalker99

Hello Paulnaj, Its the query to update a specified column across all the records that I'm struggling with.

Example: The user wants to replace Col004 with the data they have just uploaded. How do I add the data across all of the different records? and what if there are more / less records than what the user is adding ie. The user wants to add 6 new values to Col004 but at the moment theres only 2 records. So I would need to make the MySQL query know when to update and when to create.

Database:
1 . Col001 . Col002 . Col003 . Col004 . Col005
2 . Col001 . Col002 . Col003 . Col004 . Col005

New Values:
. . . Col004 .
. . . Col004 .
. . . Col004 .
. . . Col004 .
. . . Col004 .

just can't get my head round it so if you can help that would be great.

Cheers

paulnaj

Hi John,

Sorry mate, but I don't quite get what you're trying to do ...

The user wants to replace Col004 with the data they have just uploaded

... doesn't really make sense. Do you mean:

1) Get rid of all existing records, then add new ones.
2) Update existing records with new data, and add completely new records.

Give us the full description of your tables (the CREATE statement) so there's no doubt over the structure, and also some sample data to be uploaded by the user.

Paul 🙂

johnskywalker99

I'm working towards number (2). The main product database can be updated from selective entries in the temp database. So the user can choose to update complete product records or all the values in a specified column.

Construction temp:
Thanks for coming back to the thread. The table is created as follows:

MySQL_Query ("CREATE TABLE temptable (
tmp mediumint(4) auto_increment not null,
RecTable varchar(8),
RecRow mediumint(4),
Col000 text,
Col001 text,
Col002 text,
Col003 text,

Col036 text,
Col124 text,
Col208 text,
Col344 text,
Col376 text,
Col392 text,
Col410 text,
Col578 text,
Col826 text,
Col682 text,
Col752 text,
Col756 text,
Col840 text,
Col978 text,

Col005 text,
Col006 text,
Col007 text,
Col008 text,
Col009 text,
Col010 text,
Col011 text,

PRIMARY KEY (tmp),
UNIQUE id (tmp))
") or die( "Database create error: " . mysql_error() );

there all text values so theres lots of flexibility for the user to organise their data online - the 'livetable' would be optimised to specific lengths.

Typical data would be:

pftest1 speed touch 5301 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest2 speed touch 5302 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest3 speed touch 5303 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest4 speed touch 5304 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest5 speed touch 5305 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest6 speed touch 5306 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest7 speed touch 5307 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest8 speed touch 5308 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest9 speed touch 5309 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard modems 450 st530.gif st530f.gif online
pftest10 speed touch 53010 test the system test 256 chars 45.55 45.55 45.55 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 45.99 50+ standard router 450 st530.gif st530f.gif online

I hope that gives you a better idea of what I mean. To be honest I'm leaning towards a script that only allows them to update complete records rather than a specific column across multiple records!

Thanks again for coming back to the thread!