homepage Welcome to WebmasterWorld Guest from 54.198.224.121
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Accredited PayPal World Seller

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Update Table1 From Table2
IntegrityWebDev




msg:4228503
 4:04 pm on Nov 9, 2010 (gmt 0)

I have 2 tables of identical structure in MySQL:
Table1 (the live table)
Table2 (the backup)
fields = id, a, b, c, d, e

Using PHP some fields in Table1 are editable by the end user. However I want to add a RESTORE button so that if they screw something up they can go back to the original data.

So I need a way (for example) to replace the entire row id=4 of Table1 with the original data from Table2

What is the best way to do this?

Thanks!

 

Demaestro




msg:4228556
 6:59 pm on Nov 9, 2010 (gmt 0)

How I would do it is record all changes and display the last change to the user. If they "screw up" then simply delete that row and the one before it is restored by default.

Then if you want to keep things clean you can write a script that runs once a month and deletes changes that are 11 changes old or you could do it on time and delete anything older than X months... it depends on your data.

But basically it goes like this.

Table1 (The reference)
fields = rid, and all fields not editable

Table2 (The lookup)
fields = uid, rid, a, b, c, d, e, user_id (of person who made changes), timestamp (datetime of entry)

When someone makes an update you simply insert a new row into Table2

When you grab the data for viewing you just grab the most recent row from Table2

Something like.....

select tbl1.* tbl2.a, tbl2.b, tbl2.c, tbl2.d, tbl2.e
from
Table1 tbl1 left join Table2 tbl2 on (tbl1.rid = tbl2.rid and tbl2.uid = (select uid from Table2 where rid = THE_ID order by timestamp desc limit 1)

IntegrityWebDev




msg:4228566
 7:23 pm on Nov 9, 2010 (gmt 0)

What I'm wanting to do is actually bit simpler (granted its lest robust but for what I need I dont need the revisions.) I just need to brute force overwrite one row of a table with a row from another table. I was wondering if there was a simple query for that.

Demaestro




msg:4228572
 7:42 pm on Nov 9, 2010 (gmt 0)

What DB are you using?

I think MySql doesn't support update/selects

but what you want is

UPDATE Table1 tbl1, Table2 tbl2
SET tbl1.a = tbl2.a, tbl1.b = tbl2.b, and so on

WHERE tbl1.id = tbl2.id

IntegrityWebDev




msg:4228573
 7:49 pm on Nov 9, 2010 (gmt 0)

I am using MySQL

Demaestro




msg:4228584
 8:24 pm on Nov 9, 2010 (gmt 0)

Does that syntax work?

IntegrityWebDev




msg:4228585
 8:26 pm on Nov 9, 2010 (gmt 0)

I couldn't get that to work...I ended up with just using 2 SQL queries in my PHP...one to pull out of the backup table, one to put that info in the "live" table and that worked. Thanks!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved