[RESOLVED] refresh database

rikmoncur

I've looked but can't find the answer to this...

I've got a demo site where folks can edit my original data in a database or add new entries.
At the end of each day I can run a cronjob and delete everything from the table.
No problem so far.

Here's the question:
I'd like to then reupload my original data, to have a fresh database, ready for a new day.
How would I do that?
I did think that on the end of my delete script, I'd just write an insert to write the data but that seemed like a longish way to do it?

Is there a way, with a cronjob, to upload a sql file to mysql or maybe transfer data from one table to another?

Any tutorials anyone knows of?

Sxooter

In postgresql you can do this:

drop database maindatabase
create database maindatabase with template='maindbtemplate';

To do that. I'm sure there's a functional equivalent for MySQL, but you might have to run command line tools to make parts of it happen.

rikmoncur

thanx for replying!

I've found this on another forum: (transfer data from one table to another)

SELECT *
INTO new_table
FROM my_table

It doesn't seem to work yet - I'm still fiddling with it. I'm good at deleting stuff (sometimes too good!)

Sxooter

Ahhh! well, if the schema structure won't be changing much you could write a simple script to do what you want:

truncate table1;
truncate table2;
...
insert into table1 select from table1_bak;
insert into table2 select from table2_bak;

and so on.

Just the dropdb / createdb is two easy steps is all. But it's not a big difference to do it one way or another.

csn

I would probably just create a dump of the original file with "clean" commands, then exec "psql -f dump.sql db".

rikmoncur

It worked! Thanx chaps - now, who can teach my wife to cook?


<?php

/* set to run as a cronjob */

/* Dreamweaver's code - delete all from original table */
mysql_select_db($database_events_users, $events_users);
$query_Recordset1 = "SELECT * FROM Events_Users";
$Recordset1 = mysql_query($query_Recordset1, $events_users) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
mysql_query("DELETE FROM Events_Users");

/* Get data from backup table and insert it into original table */
mysql_query("insert into Events_Users select * from Events_Users2");

?>