PHP, MySQL and Cron - Moving Data Between Tables

Please help me with these scripts...

[Oblivion]

Hello,

Please can someone tell me how to make a system, I would add data into one table, and every 24 hours I would like the oldest 3 entries to be moved to the other table. I would like this to be securely coded aswell.

I'm very gratefull for anyone that is able to help me!

Regards,
Mark

[ergophobe]

What part do you need help with?

Basically, you would set a cron job to run every 24 hours and to call a script that would SELECT the three oldest records and then INSERT them in the second table and then DELETE those rows from the original table. Make sure you verify that the INSERT was successful before you do the DELETE though.

[Oblivion]

SELECT * FROM games ORDER by id ASC LIMIT 3

That's the only query I can work out. The table I'm taking data from is called 'games' and the table I want 3 rows moved to will be called 'newgames'. Now I don't know what needs to eb done next.

Thanks for your reply!

[ergophobe]

Not watching post counts - I see another welcome to WebmasterWorld is in order - welcome Mark! I hope you find what you need here. Anyway...

Cycle through that result set (if you don't know how to do that, look in the forum library for the "Basics of extracting data using MySQL" or a similar title - link to library in the upper left of the screen).

As you cycle through

$iq = "INSERT INTO newgames (col1, col2, col3) VALUES ('val1', 'val2', 'val3')";

$dq = "DELETE FROM games WHERE game_id = '$game_id'";

Does that help.

[Oblivion]

Thanks, I think I found the thread you were reffering to (http://www.webmasterworld.com/forum88/1971.htm), I still don't really understand it though.

<?
session_start();
include_once("config.php");
include_once("mysqlcon.php");

$query = "SELECT * FROM games ORDER by id ASC LIMIT 3";

$iq = "INSERT INTO newgames (id, title, url, dir, fid, description, keywords, catagory, height, width, date, hits, votes, totalvote) VALUES ('val1', 'val2', 'val3')";

$dq = "DELETE FROM games WHERE game_id = '$game_id'";
?>

that's what I've got, but I'm fairly certain it's terribly wrong :(

Thanks for the welcome aswell :D

[ergophobe]

In the thread referenced, look at items 4 and 5 of message 5.

You have some queries, but you need to send them to the DB server and fetch the results, then plug those into your other queries.

$errors = ''; // we'll collect error messages here
$query = "SELECT * FROM games ORDER by id ASC LIMIT 3";

// now we send the query to the DB server;
$result = mysql_query($query);

// now we cycle through the result set and retrieve our data
// then we insert that data into table2 and delete from table1

while ($game = mysql_fetch_assoc($result)) 
{ 
  extract($game); 
  $iq = "INSERT INTO newgames (id, title, url, ...) VALUES ('$id', '$title', '$url', ...)";   
  $ir = mysql_query($iq); 
  if ($ir) 
  { 
    $dq = "DELETE FROM games WHERE id = '$id'"; 
    $dr = mysql_query($dq); 
    if (!$dq) 
    { 
      $errors .= "<br>Game $id ($title) not deleted from 'games'\n"; 
    } 
  } 
  else 
  { 
    $errors .= "<br>Game $id ($title) not inserted in 'newgames'\n"; 
  } 
} 
if (!empty($errors)) 
{ 
  echo "<p>Problems encountered while running script:\n<br>$errors<\p>\n"; 
} 

Now of course, this is a cron job, so you want to write your errors to a log file most likely, not output them.

Also, if you want to throw caution to the wind or you are willing to lock the DB and prevent any inserts to 'games' while the script is running, you could do this super fast with a SELECT... INSERT statement and a DELETE statement that, instead of deleting a specific record one at a time, used the same WHERE clause as the SELECT... INSERT.

Faster and cleaner, to be sure, but if someone creates a new game record while you are adding the data to newgames, things will get messed up.

[Oblivion]

Thank You!

I feel like I'm finally getting somewhere with this, since it's been bugging me for a while now.

<?php
session_start();
include_once("config.php");
include_once("mysqlcon.php");

$errors = ''; // we'll collect error messages here
$query = "SELECT * FROM newgames ORDER by id ASC LIMIT 3";

// now we send the query to the DB server;
$result = mysql_query($query);

while ($game = mysql_fetch_assoc($result))
{
extract($game);
$iq = "INSERT INTO 'games' VALUES ('$id', '$title', '$url', '$dir', '$fid', '$description', '$keywords', '$catagory', '$height', '$width', '$date', '$hits', '$votes', '$totalvote')";
$ir = mysql_query($iq);
if ($ir)
{
$dq = "DELETE FROM newgames WHERE id = '$id'";
$dr = mysql_query($dq);
if (!$dq)
{
$errors .= "<br>Game $id ($title) not deleted from 'newgames'\n";
}
}
else
{
$errors .= "<br>Game $id ($title) not inserted in 'games'\n";
}
}
if (!empty($errors))
{
echo "<p>Problems encountered while running script:\n<br>$errors<\p>\n";
}
?>

That's the script I've setup now, but there is somethign wrong with it, I've played around with it and I can't find the problem though;

"Problems encountered while running script:

Game 120 (Hungry Bob) not inserted in 'games'
Game 121 (Sub Commander) not inserted in 'games'
Game 122 (Full Time Killer) not inserted in 'games' <\p>"

That's the error I'm getting, I've checked that the user has MySQL priveladges to insert and played aorund with it, but I can't seem to get it to move the data.

Thanks for everything so-far, I'm suprised how helpfull you are, I only know of a few places where people would be willing to help me so much :).

[ergophobe]

In theory the column names in the INSERT are optional (as you've chosen to approach it), but I pretty much never do it that way (see my example).

$iq = "INSERT INTO 'games' (id, title, ...) VALUES ('$id', '$title',...)";

That's how I do it. In fact, I almost never use SELECT * either.

So it looks like your insert is failing, I'm guessing, because you have the wrong column count or order (so the types don't match up) or something along those lines.

Also, in event of a failed query, you can return the mysql error, so

$errors .= "<br>Game $id ($title) not inserted in 'games' (SQL error: " . mysql_error() . "\n";

I only know of a few places where people would be willing to help me so much :).

Thanks, but this is just the weekend. Wait until the regular crew gets back on Monday! You'll have a whole team on it (but not me - Monday and Tuesday are days off for me).

[Oblivion]

I don't really know what I did, but something made it work!

Thanks for all of your help!