CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 MOVE SOME RECORDS FROM TWO TABLES

Print topic Send  topic

Author Message
Cris

Posts: 76
Posted: 08/27/2015, 2:27 AM

I guys,


I have to implement one of my projects.

I have two tables , the first is called news and the second is archive . I have to pass some records from news table after the deadline date into archive table .

Do you have any suggestion on how to do that ?

Thank youhor your help
_________________
------
Cristiano
Skype: cris-do

View profile  Send private message
eratech


Posts: 513
Posted: 08/28/2015, 5:56 AM

Cris,

I tend to leave things like that in the same table and set an archived flag or similar, but that's not important.

I think the aim is to automatically trigger the movement (execution of SQL) and there are several options I can think of:

* (if I was on linux) I would run a cron job to run a web page or SQL command. Not sure if you can do something similar on windows scheduler (I know you can schedule stuff, just unsure if you can load a webpage)

* If running MySQL (or MS SQL etc) you should be able to set an event to run the SQL (on MySQL it is 'Events', and in MSSQL you have Jobs that can run SQL)

* put the sql query in a Before Show of a page (like the list of articles or a menu, something reasonably often used) so that when it is opened, the move is done (it's not fully automatic but it would work if nothing else is available)

As for the SQL if you haven't got it already: something like this for Mysql
START TRANSACTION;  
 INSERT INTO archive (<field names>) SELECT <fields Names> FROM news WHERE deadline < CURDATE();  
DELETE FROM news WHERE deadline < CURDATE();  
COMMIT;

This can be put in a single SQL Query and executed at once.

Cheers

Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
Cris

Posts: 76
Posted: 08/28/2015, 6:34 AM

Quote eratech:
Cris,

I tend to leave things like that in the same table and set an archived flag or similar, but that's not important.

I think the aim is to automatically trigger the movement (execution of SQL) and there are several options I can think of:

* (if I was on linux) I would run a cron job to run a web page or SQL command. Not sure if you can do something similar on windows scheduler (I know you can schedule stuff, just unsure if you can load a webpage)

* If running MySQL (or MS SQL etc) you should be able to set an event to run the SQL (on MySQL it is 'Events', and in MSSQL you have Jobs that can run SQL)

* put the sql query in a Before Show of a page (like the list of articles or a menu, something reasonably often used) so that when it is opened, the move is done (it's not fully automatic but it would work if nothing else is available)

As for the SQL if you haven't got it already: something like this for Mysql
START TRANSACTION;  
 INSERT INTO archive (<field names>) SELECT <fields Names> FROM news WHERE deadline < CURDATE();  
DELETE FROM news WHERE deadline < CURDATE();  
COMMIT;

This can be put in a single SQL Query and executed at once.

Cheers

Eric


Hi Eric,

Thanks, excellent as always your suggestions on the other hand

_________________
------
Cristiano
Skype: cris-do

View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.