85596

how to move current record to another table and delete the current record

Question:

I am currently using PHP to create a system locally for user to insert data into 14 tables located in one page, then end of day all the data in that table should be move into a new table in database automatically named dashboard (will store daily data) but recently I used a button by click manually to take the action and at the same time all the data in 14 tables state above will be delete automatically. It easy to make user insert new data at the next day because today's data has been moved to another table also current table's data was empty. I have searching at all website about this matter but don't get any solution. Hope all you guys can help me out of this problem.

I used <strong>INSERT INTO</strong> but not working. Where should I used the <strong>FROM</strong> tag? Because there is 8 table need to transfer, eg: sr1, sr2, sr3 and so on.

My Code as below:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO sr1_full (date, total_pending, appt_today, percent_appt_today, percent_complete, partial_complete, full_complete, return_technical, return_customer, return_cancel, cancel, reappt, focus, total_tepi, total_bawah) SELECT date, total_pending, appt_today, percent_appt_today, percent_complete, partial_complete, full_complete, return_technical, return_customer, return_cancel, cancel, reappt, focus, total_tepi, total_bawah FROM sr1 TRUNCATE TABLE sr1", GetSQLValueString($_POST['date'], "date"), GetSQLValueString($_POST['total_pending'], "int"), GetSQLValueString($_POST['appt_today'], "int"), GetSQLValueString($_POST['percent_appt_today'], "int"), GetSQLValueString($_POST['percent_complete'], "int"), GetSQLValueString($_POST['partial_complete'], "int"), GetSQLValueString($_POST['full_complete'], "int"), GetSQLValueString($_POST['return_technical'], "int"), GetSQLValueString($_POST['return_customer'], "int"), GetSQLValueString($_POST['return_cancel'], "int"), GetSQLValueString($_POST['cancel'], "int"), GetSQLValueString($_POST['reappt'], "int"), GetSQLValueString($_POST['focus'], "int"), GetSQLValueString($_POST['total_tepi'], "int"), GetSQLValueString($_POST['total_bawah'], "int")); mysql_select_db($database_pods, $pods); $Result1 = mysql_query($insertSQL, $pods) or die(mysql_error()); $insertGoTo = "main.php"; if (isset($_SERVER['QUERY_STRING'])) { $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?"; $insertGoTo .= $_SERVER['QUERY_STRING']; } header(sprintf("Location: %s", $insertGoTo)); } mysql_select_db($database_pods, $pods); $query_sr1 = "SELECT * FROM sr1"; $sr1 = mysql_query($query_sr1, $pods) or die(mysql_error()); $row_sr1 = mysql_fetch_assoc($sr1); $totalRows_sr1 = mysql_num_rows($sr1); mysql_select_db($database_pods, $pods); $query_sr2 = "SELECT * FROM sr2"; $sr2 = mysql_query($query_sr2, $pods) or die(mysql_error()); $row_sr2 = mysql_fetch_assoc($sr2); $totalRows_sr2 = mysql_num_rows($sr2); mysql_select_db($database_pods, $pods); $query_sr3 = "SELECT * FROM sr3"; $sr3 = mysql_query($query_sr3, $pods) or die(mysql_error()); $row_sr3 = mysql_fetch_assoc($sr3); $totalRows_sr3 = mysql_num_rows($sr3); mysql_select_db($database_pods, $pods); $query_sr4 = "SELECT * FROM sr4"; $sr4 = mysql_query($query_sr4, $pods) or die(mysql_error()); $row_sr4 = mysql_fetch_assoc($sr4); $totalRows_sr4 = mysql_num_rows($sr4); mysql_select_db($database_pods, $pods); $query_sr5 = "SELECT * FROM sr5"; $sr5 = mysql_query($query_sr5, $pods) or die(mysql_error()); $row_sr5 = mysql_fetch_assoc($sr5); $totalRows_sr5 = mysql_num_rows($sr5); mysql_select_db($database_pods, $pods); $query_stx = "SELECT * FROM stx"; $stx = mysql_query($query_stx, $pods) or die(mysql_error()); $row_stx = mysql_fetch_assoc($stx); $totalRows_stx = mysql_num_rows($stx); mysql_select_db($database_pods, $pods); $query_kl = "SELECT * FROM kl"; $kl = mysql_query($query_kl, $pods) or die(mysql_error()); $row_kl = mysql_fetch_assoc($kl); $totalRows_kl = mysql_num_rows($kl); mysql_select_db($database_pods, $pods); $query_msc = "SELECT * FROM msc"; $msc = mysql_query($query_msc, $pods) or die(mysql_error()); $row_msc = mysql_fetch_assoc($msc); $totalRows_msc = mysql_num_rows($msc); mysql_select_db($database_pods, $pods); $query_total = "SELECT * FROM total_bawah"; $total = mysql_query($query_total, $pods) or die(mysql_error()); $row_total = mysql_fetch_assoc($total); $totalRows_total = mysql_num_rows($total);

Answer1:

use this SQL to copy data.

insert into `CopyTable` (`colum1`,`colum2`) select `colum1`,`colum2` from `Sourcetable`

empty table by

TRUNCATE TABLE `Sourcetable`

example :

$insertSQL = "INSERT INTO sr1_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`, `percent_complete`, `partial_complete`, `full_complete`, `return_technical`, `return_customer`, `return_cancel`, `cancel`, `reappt`, `focus`, `total_tepi`, `total_bawah`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today`, `percent_complete`, `partial_complete`, `full_complete`, `return_technical`, `return_customer`, `return_cancel`, `cancel`, `reappt`, `focus`, `total_tepi`, `total_bawah` FROM `sr1`"; mysql_query($insertSQL); // data copied. $removeSQL = "TRUNCATE TABLE sr1"; mysql_query($removeSQL); // deleted all data from sr1 //data moved

Answer2:

The reason you're having a hard time finding information on how to do this is that this is <em>not</em> the correct way to do this.

You should not be duplicating the structure of a table to store data for "today" and data for previous days. What you <em>should</em> do is store all items in a single dashboard table, and filter based on a date column to determine which items are considered today's items. The approach you're trying to take, of moving rows from a current table to an archive table, is flat-out <em>wrong</em>. That isn't how databases work.

All that said, you would accomplish this by using <a href="http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html" rel="nofollow">select into table</a> to move all the data from today_dashboard into all_dashboard, and then delete from today_dashboard to clear it out. <strong>Don't do this</strong>. Rethink your architecture to store both in one table.

Answer3:

$current_date = date("m.d.y"); $query = "INSERT INTO table_name (date,column1, column2, column3,...) VALUES ($current_date,value1, value2, value3,...)"; $result=mysql_query($query); <ul><li>This way you have to insert all the dats to your tabel with the current date.</li> <li>

After saving records to database you have to check the current date was matched to any records. IF any records found you have to delete this record by using delete query. This way you can delete all existing records from that table

Hope this will be useful for you...

</li> </ul>

Recommend