[RESOLVED] mysql SUB and UNION help

[DeathfireD]

Hello, I'm stuck on a mysql data cleaner. Here's what I'm trying to do.
I have a list of online servers. I currently prune out servers older then 60 days.

My problem is I allow inner nested servers. So for example Server1 might have 7 other servers listed within Server1's private page. So the problem is, if I remove Server1 those 7 other servers are lost in mysql and never seen again lol. They're there they just wont be listed since their parent server, the one they where associated was deleted.

So what i'd like to do is take the most active server within the nest and make it the new dominant server for when the root one is pruned out.

Heres the steps I've though up in my head.
1) select server(s) in mysql that are 60 days old or older and get them ready for deletion.
2) select the top most online server associated with the server that's about to be deleted.
3) Make all other servers that are associated with the server thats 60 days old get re-associated with the most active server (in step 2).
4) After all servers are re-associated delete the server thats 60 days old or older.

So I'm stuck as to how to do this using a UNION or SUB method. Any ideas?

Heres some sandbox code I've been playing around with.

$query = ("SELECT pid,gid FROM serverlist WHERE gid='0' AND lastactive <= DATE_SUB(NOW(),INTERVAL 60 DAY)")
UNION
("SELECT pid,gid FROM serverlist") ORDER by lastactive DESC;

Oh almost forgot. I use pid as an unique ID for all servers. I use gid for pointing nested servers to a parent. So basically the gid is set to 0 if it's a parent. If it's a child it gets the parents pid added to it's gid. So serverabc with a gid of 2 would be a child of whatever server has the pid of 2.

[Weedpacket]

Sounds like

Update serverlist set gid=(Select pid of most active server however you determine that) where gid in (Select pid From serverlist where lastactive<60 days)

Delete from serverlist where lastactive<60 days

[DeathfireD]

So something like this?

mysql_query("UPDATE serverlist SET gid=(SELECT pid FROM serverlist ORDER BY lastactive DESC) where gid in (SELECT pid FROM serverlist WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 60 DAY)");

mysql_query("DELETE FROM serverlist WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 60 DAY)") or die("ERROR: <i>".mysql_error()."</i><p>Please write this down and send it to me.");

[DeathfireD]

hmm didn't seem to work.

mysql_query("UPDATE serverlist SET gid=(SELECT pid FROM serverlist ORDER BY lastactive DESC LIMIT 1) WHERE gid IN (SELECT pid FROM serverlist WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 11 DAY)");

[DeathfireD]

Hmmm I get the error "ERROR: You can't specify target table 'serverlist' for update in FROM clause". Any thoughts? I'm having a brain fart.

mysql_query("UPDATE `serverlist` SET gid=(SELECT pid FROM `serverlist` ORDER BY lastactive DESC LIMIT 1) WHERE gid IN (SELECT pid FROM `serverlist` WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 11 DAY))") or die("ERROR: <i>".mysql_error()."</i><p>Please write this down and send it to me.");

[Weedpacket]

I'm afraid I'm beat here; it works for me, but I don't use MySQL so I can't say why that's not valid for you. You may have to do the two subqueries separately and embed their results into the update (preferably within a transaction and preferably with a lock on the table in case the table's contents change in the meantime).

[DeathfireD]

hmm, I tried this

$query1 = "SELECT pid FROM serverlist ORDER BY lastactive DESC LIMIT 1";
$query2 = "SELECT pid FROM serverlist WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 11 DAY)";

mysql_query("UPDATE serverlist SET gid=($query1) WHERE gid IN ($query2)") or die("ERROR: <i>".mysql_error()."</i>");

Page turned white so I figured the variables where being ignored. Changed it to

$query1 = "SELECT pid FROM serverlist ORDER BY lastactive DESC LIMIT 1";
$query2 = "SELECT pid FROM serverlist WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 11 DAY)";

mysql_query("UPDATE serverlist SET gid=(".$query1.") WHERE gid IN (".$query2.")") or die("ERROR: <i>".mysql_error()."</i>");

ended up getting the same error message as before "ERROR: You can't specify target table 'serverlist' for update in FROM clause"

I'll try doing it the long way and use multiple separate select statements (something I was trying to avoid by using SUB or UNION). Not even sure how I should do this either lol.

[djjjozsi]

when you use subquery, try to use HAVING instead of WHERE

and don't delete, just set the active(field) to OFF

[DeathfireD]

djjjozsi;10891068 wrote:

when you use subquery, try to use HAVING instead of WHERE

and don't delete, just set the active(field) to OFF

mysql_query("UPDATE serverlist SET gid=(SELECT pid FROM serverlist ORDER BY lastactive DESC LIMIT 1) HAVING gid IN (SELECT pid FROM serverlist WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 11 DAY))") or die("ERROR: <i>".mysql_error()."</i>");

Gives me this error
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING gid IN (SELECT pid FROM serverlist WHERE lastactive <= DATE_SUB(NOW(),I' at line 1

[djjjozsi]

try to do that with IF...

for example:

SELECT IF(date <= DATE_SUB(NOW(),INTERVAL 1 DAY),"OLD","NOT OLD") AS is_old,
server_id , date,mother,ip,
FROM servers

with this thinking try to make UPDATE included into IF statements.

[Weedpacket]

DeathfireD wrote:

hmm, I tried this

I actually meant to do it as three separate queries, not as three strings that are assembled into one query: that wouldn't make any difference because the same thing would be sent to the database either way.

• Do one select to find out the most recently active server.

• Do another select to get the list of old servers.

• Put the results of those two queries (not the queries themselves) into the Update statement. (For the second you'd want to use IN(...comma-separated list of IDs...).

[djjjozsi]

if prefer that too.
in one SQL command that will be too complicated.

Weedpacket;10891083 wrote:

I actually meant to do it as three separate queries, not as three strings that are assembled into one query: that wouldn't make any difference because the same thing would be sent to the database either way.

• Do one select to find out the most recently active server.

• Do another select to get the list of old servers.

• Put the results of those two queries (not the queries themselves) into the Update statement. (For the second you'd want to use IN(...comma-separated list of IDs...).

[Weedpacket]

djjjozsi wrote:

in one SQL command that will be too complicated.

Actually, that "one SQL command" is given earlier, but I gather from this that MySQL is one DBMS that doesn't like it.

[DeathfireD]

djjjozsi;10891074 wrote:

try to do that with IF...

for example:

SELECT IF(date <= DATE_SUB(NOW(),INTERVAL 1 DAY),"OLD","NOT OLD") AS is_old,
server_id , date,mother,ip,
FROM servers

with this thinking try to make UPDATE included into IF statements.

I've never worked with IF statements in MySQL. I understood the first part of that statement but got lost at the "OLD, "NOT OLD") AS...etc part.

@ - Ya, I realized that after I made the post haha. I'll try that next and see what the outcome is. I'm gonna switch your first two steps though. I only want to make changes to parents that are older then 60 days. So by checking for a parent server thats older then 60 days and then checking for the most active child for that parent would be better. If I went by your steps I would end up selecting every child in the database that was the most active when I would really only need to do that to 2 groups since only 2 parents are older then 60 days.

Also I noticed something, since I need to update all the children to use the new parent id I'll probably have to do a loop too.

[Weedpacket]

DeathfireD wrote:

Also I noticed something, since I need to update all the children to use the new parent id I'll probably have to do a loop too.

Why? Once you've got all the IDs of the children then it's just a single update to change them all to the same parent; that's what the update statement I wrote does. 😕

[DeathfireD]

Weedpacket;10891170 wrote:

Why? Once you've got all the IDs of the children then it's just a single update to change them all to the same parent; that's what the update statement I wrote does. 😕

oh, my bad. If only your code worked for me 🙁.

[Weedpacket]

DeathfireD wrote:

oh, my bad. If only your code worked for me .

Well, it would; like I said, you'd just need to do the selects statements separately from the update command.

[DeathfireD]

Ok so I did something like this which I know isn't completely right or short.

$old = mysql_query("SELECT pid FROM `serverlist` WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 11 DAY)") or die("ERROR: <i>".mysql_error()."</i><p>Please write this down and send it to me.");
//$o = mysql_fetch_row($old);
while($o = mysql_fetch_array($old))
  {
   $oldid = $o['pid'];
  }
//$oldid = $o['pid'];
echo "is ".$oldid."<br />";

$get_active = mysql_query("SELECT pid FROM `serverlist` WHERE gid=$oldid ORDER BY lastactive DESC LIMIT 1") or die("ERROR: <i>".mysql_error()."</i><p>Please write this down and send it to me.");
//$a = mysql_fetch_row($get_active);
while($a = mysql_fetch_array($get_active))
  {
   $activeid = $a['pid'];
  }

//$activeid = $a['pid'];
echo "is new parent ".$activeid."<br />";



mysql_query("UPDATE `serverlist` SET gid=$activeid WHERE gid IN ($oldid)") or die("ERROR: <i>".mysql_error()."</i><p>Please write this down and send it to me.");

mysql_query("DELETE FROM alliancelist WHERE lastactive <= DATE_SUB(NOW(),INTERVAL 11 DAY)") or die("ERROR: <i>".mysql_error()."</i><p>Please write this down and send it to me.");

Which works however i've run into two problems whatever child server is supposed to be the new root has it's gid changed to it's Id when its supposed to be set to 0.

So, for example
pid-------gid-------servername
1---------0----------parent_server
2---------1----------child_server_one
3---------1----------child_server_two
4---------1----------child_server_three

when I run the code it takes the most active child, we'll use child_server_one for the example. It then changes child_server_one's gid to 2. This brakes my front page since I display parent servers by searching if they have a gid of 0. To fix this I ended up placing

mysql_query("UPDATE `alliancelist` SET gid='0' WHERE pid=$activeid") or die("ERROR: <i>".mysql_error()."</i><p>Please write this down and send it to me.");

at the end after the first update part. Which seems to work.

The only problem now is that the above code I did only applies to one parent server. I'm guessing because I don't loop the process.

So I figured id post my dirty code and see if anyone could come up with a simplified less messy way of doing it that works.

Thanks.

[djjjozsi]

its gonna make it with a loop. and if you like that, check the $main_sql sting to clean the servers table.
it checks row by row if that row is an old one or not, and update if its needed.
in that case, the row is a parent, but there is no active child containing in the table, that means in the following those clild elements will be deleted.

<?
include("connection.php");
$sql="SELECT 
IF(`lastactive` <= DATE_SUB(NOW(),INTERVAL 11 DAY),'OLD','NOT OLD') AS `is_old`,`lastactive`,`pid`,`gid`,`servername`
FROM `serverlist`
ORDER BY gid";

//it ckechs the parents first with an alias value, is_old...
$main_sql="";
$result=mysql_query($sql);
while($row=mysql_fetch_assoc($result))
{
  if($row["is_old"]=="OLD" AND $row[gid]>0 ) //Old, but a child(kill'em'all)
  {
  print "DELETE THIS(child) ".$row[servername]." - ".$row["is_old"];
  $sql="DELETE FROM serverlist WHERE pid=".$row["pid"]." ;";
  print "($sql)";
$main_sql.=$sql;
  }

  if($row["is_old"]=="NOT OLD" AND $row["gid"]>0 ) // not old, and child
  {
  print "LEAVE THIS(child) ".$row["servername"]." - ".$row["is_old"];
  }

  if($row["is_old"]=="OLD" AND $row["gid"]==0 ) //its an old parent...
  {
  print "DELETE this parent, but set the most active child to parent, and update those g_id's, and set the most active child gid to 0...".$row["servername"]." - ".$row["is_old"];

  $topid="SELECT pid FROM serverlist WHERE (`lastactive` >= (DATE_SUB( NOW( ) , INTERVAL 11 DAY)) AND gid ='".$row["pid"]."') ORDER BY lastactive DESC LIMIT 1";
  $row2=mysql_fetch_assoc(mysql_query($topid));
  $topid=$row2["pid"];

  $sql1="UPDATE serverlist SET gid='".$topid."' WHERE gid='".$row["pid"]."';";
  print "( $sql1 )";

  $sql2="UPDATE serverlist SET gid='0' WHERE pid='$topid' ;";
  print "( $sql2 )";

  $sql3="DELETE FROM serverlist WHERE pid=".$row["pid"]." LIMIT 1 ;";
  print "  DEL: ($sql3)";

$main_sql.=$sql1.$sql2.$sql3;
  }

  if($row["is_old"]=="NOT OLD" AND $row["gid"]==0 ) //not old and parent
  {
  print "LEAVE THIS ".$row["servername"]." - ".$row["is_old"];
  }

  if($row["gid"]==0)
    print " - parent";
  else
    print " - child";

  print "<br>";

}
print "<br>The main sql is:".$main_sql;
?>

[DeathfireD]

looks interesting, I'll try and make sense out of it tomorrow when I wake up. Thanks for the help so far guys.