[PHP + MYSQL] remove rows from database

[3dhomejoe]

Hello, im trying to build a script that gets the date of an old row and select an ID number from it and then it would remove any rows the = that ID number, here is my code so far

it selects all of the id numbers but I can't get it to clear them, what is wrong?

I have posted this on other sites too but no-one has been able to help me yet after about a month or so, im hoping that someone here can help me out X+)

Below im going to post the code and some of the things that I have tried...


My starting code...

 <?php 
$con = mysql_connect('10.0.1.65','3dhomejoe','PASSWORDHERE'); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("eq2", $con); 

$result = mysql_query("SELECT encid FROM encounter_table where starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)"); 

if(!$result)  { 
    $err=mysql_error(); 
    print $err; 
    exit(); 
} 

if(mysql_affected_rows()==0){ 
     print "Nothing to do."; 
} else { 
while($row =  mysql_fetch_array($result)) 
  { 
  //echo "Clearing"; 
  echo $row[0]; 
   mysql_query("DELETE FROM  attacktype_table WHERE encid=$encid"); 
  
  } 
} 
mysql_close($con); 
?>

Then gave this a try, it gave a white page...

mysql_query("DELETE FROM attacktype_table WHERE encid="$encid""); 

Then re did the code to make it like this, still gave me a white page...

 <?php 
$con = mysql_connect('10.0.1.65','3dhomejoe','PASSWORDHERE'); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("eq2", $con); 

mysql_query("DELETE FROM attacktype_table WHERE encid IN (SELECT  encid FROM encounter_table WHERE startime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)"); 
  
  } 
} 
mysql_close($con); 
?>

Then I was told to try this

  <?php 
$con = mysql_connect('10.0.1.65','3dhomejoe','PASSGOESHERE'); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("eq2", $con); 

$query = "DELETE FROM attacktype_table WHERE encid IN (SELECT  encid FROM encounter_table WHERE starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY))"; 
mysql_query($query) or die('MySQL error: ' . mysql_error() . "<br>\nQuery: $query");  
  
mysql_close($con); 
?>

and it just kept on running, after about an hour of running, I killed the command, it did remove everything by request, but it was stuck in a loop

Ran this in my console...

[root@moomoo ~]# mysqladmin -u 3dhomejoe -p PROCESSLIST
Enter password:
+------+-----------+----------------------+--------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------+----------------------+--------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2943 | 3dhomejoe | http-159df05bee:2575 | eq2 | Sleep | 462 | | |
| 2946 | 3dhomejoe | 10.0.1.52:4384 | eq2 | Query | 747 | Sending data | DELETE FROM attacktype_table WHERE encid IN (SELECT encid FROM encounter_table WHERE starttime < DAT |
| 2954 | 3dhomejoe | http-159df05bee:2645 | information_schema | Sleep | 472 | | |
| 2961 | 3dhomejoe | http-159df05bee:2667 | mysql | Sleep | 454 | | |
| 2972 | 3dhomejoe | http-159df05bee:2695 | | Sleep | 205 | | |
| 2984 | 3dhomejoe | localhost | | Query | 0 | | show processlist |
+------+-----------+----------------------+--------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
[root@moomoo ~]#

and last but not least, gave this a try

                       $query = "DELETE a FROM attacktype a INNER JOIN encounter_table e  WHERE a.encid = e.encid and e.starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY);"; 

and gave me this...

MySQL error: Table 'eq2.attacktype' doesn't exist
Query: DELETE a FROM attacktype a INNER JOIN encounter_table e WHERE a.encid = e.encid and e.starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY);

I hope I did not lose someone between everything, but im clueless at why this does not work, I thought this was going to be an easy script to make but its starting to be a pain in the (INSERT WORD HERE).

Thanks for your help
Joe

[descalzo]

echo $row[0]; 
   mysql_query("DELETE FROM  attacktype_table WHERE encid=$encid"); 

First code block...
Did echo print anything?
And where did you assign a value to $encid and you should 'quote' it if it is a string.
Also mysql_affected_rows should be used on INSERTs, UPDATESs, DELETEs ...not on SELECTs. Use mysql_num_rows( $result )

[misson]

  die('Could not connect: ' . mysql_error()); 

Don't use or die (or exit) when outputting HTML, and don't output DBMS error messages for all to see.

$result = mysql_query("SELECT encid FROM encounter_table where starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)"); 

[...]

if(mysql_affected_rows()==0){ 
     print "Nothing to do."; 
} else { 
while($row =  mysql_fetch_array($result)) 
  { 
  //echo "Clearing"; 
  echo $row[0]; 
   mysql_query("DELETE FROM  attacktype_table WHERE encid=$encid"); 

Nowhere do you set $encid, which is why this fails to delete rows.

mysql_query("DELETE FROM attacktype_table WHERE encid IN (SELECT  encid FROM encounter_table WHERE startime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)"); 

This one is missing a closing parentheses.

$query = "DELETE FROM attacktype_table WHERE encid IN (SELECT  encid FROM encounter_table WHERE starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY))"; 
mysql_query($query) or die('MySQL error: ' . mysql_error() . "<br>\nQuery: $query"); 

This one should work. What is the structure (i.e. the CREATE statements) of the tables attacktype_table and encounter_table? What happens if you turn the query into a SELECT (e.g. SELECT * FROM attacktype_table WHERE encid IN ...) and run that? What's the result of EXPLAINing the SELECT?

Rewriting this query to use EXISTS rather than IN may result in a faster query:

DELETE FROM attacktype_table AS a
    WHERE EXISTS(
        SELECT  encid FROM encounter_table AS e
          WHERE a.encid = e.encid
              AND starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)
    )

Turn the statements into SELECTs and apply EXPLAIN to see which will be faster on your tables.

                        $query = "DELETE a FROM attacktype a INNER JOIN encounter_table e  WHERE a.encid = e.encid and e.starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY);"; 

DELETE doesn't support joins (always check the syntax in the MySQL documentation), nor does doing so make sense. Joins notionally result in a new table; deleting from this table is pointless. If joining didn't result in a new table, deleting joined rows would logically delete them from all tables in the join, which isn't what you want here (DELETE does support deleting from multiple tables simultaneously).

[Twinkie]

Is this on your own server, or X10? Also could you include the table dumps for the tables you are accessing?

Some of the examples you posted had syntax errors and it looks like error reporting in PHP was off.

Learn how to enable it here: http://php.net/manual/en/errorfunc.configuration.php
Learn how to show tables: http://dev.mysql.com/doc/refman/5.0/...ate-table.html

[3dhomejoe]

Ok, give me some time to reply to everyone please, going to edit this post a few times as I reply...

echo $row[0]; 
   mysql_query("DELETE FROM  attacktype_table WHERE encid=$encid"); 

First code block...
Did echo print anything?
And where did you assign a value to $encid and you should 'quote' it if it is a string.
Also mysql_affected_rows should be used on INSERTs, UPDATESs, DELETEs ...not on SELECTs. Use mysql_num_rows( $result )

Ok, echo did print out alot of stuff...

57f174211ef827a566cfbd29be49b37e1fe29c8a3a25a06139 e63b5311ceb5fb20c5988083af10789ef8c702db25d0db3143 8c06dd0a9fa93aa16a2351561402658e33

(Edit: it goes on for a long time, lots of data, so I removed 95% of what it gave me to make it fit in the post, I can post a txt file of what it gave out if you want)

that is each row that needs to be deleted, there is no space between them when it printed it to me

the value $encid is from the database, I was trying to get the script to read the encid from the rows and then put it into that line so it would delete it.

so make this line
if(mysql_affected_rows()==0){
like this?
if(mysql_num_rows($result){


EDIT: my next reply,

Nowhere do you set $encid, which is why this fails to delete rows.

I was trying to get the php script to do that for me, to get the encid from the database.

Rewriting this query to use EXISTS rather than IN may result in a faster query:

DELETE FROM attacktype_table AS a
    WHERE EXISTS(
        SELECT  encid FROM encounter_table AS e
          WHERE a.encid = e.encid
              AND starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)
    )

Gave that a try and once again error, going to look into it in a few moments, but here is what it gave me...

MySQL 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 'WHERE EXISTS( SELECT encid FROM encounter_table AS e WHERE ' at line 2
Query: DELETE FROM attacktype_table AS a WHERE EXISTS( SELECT encid FROM encounter_table AS e WHERE a.encid = e.encid AND starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY) )

on the code,

<?php 
$con = mysql_connect('10.0.1.65','3dhomejoe','PASSWORD'); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("eq2", $con); 

$query = "DELETE FROM attacktype_table AS a
    WHERE EXISTS(
        SELECT  encid FROM encounter_table AS e
          WHERE a.encid = e.encid
              AND starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)
    )"; 
mysql_query($query) or die('MySQL error: ' . mysql_error() . "<br>\nQuery: $query");  
  
mysql_close($con); 
?>

EDIT: and my last reply

Is this on your own server, or X10? Also could you include the table dumps for the tables you are accessing?

This is on my own server, I think my account would have been terminated if I let the command run for an hour or so lol

Some of the examples you posted had syntax errors and it looks like error reporting in PHP was off.

Ah yes, I will turn that on.

and I will post the tables in a few moments

EDIT: Ok here is what I got...

mysql> use eq2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW CREATE TABLE attacktype_table\G
*************************** 1. row ***************************
Table: attacktype_table
Create Table: CREATE TABLE `attacktype_table` (
`encid` char( default NULL,
`attacker` varchar(64) default NULL,
`victim` varchar(64) default NULL,
`swingtype` tinyint(4) default NULL,
`type` varchar(64) default NULL,
`starttime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`endtime` timestamp NOT NULL default '0000-00-00 00:00:00',
`duration` mediumint(9) default NULL,
`damage` int(11) default NULL,
`extdps` float default NULL,
`chardps` float default NULL,
`dps` float default NULL,
`average` float default NULL,
`median` mediumint(9) default NULL,
`minhit` mediumint(9) default NULL,
`maxhit` mediumint(9) default NULL,
`resist` varchar(64) default NULL,
`hits` mediumint(9) default NULL,
`crithits` mediumint(9) default NULL,
`blocked` mediumint(9) default NULL,
`misses` mediumint(9) default NULL,
`swings` mediumint(9) default NULL,
`tohit` float default NULL,
`averagedelay` float default NULL,
`critperc` varchar( default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

[misson]

the value $encid is from the database, I was trying to get the script to read the encid from the rows and then put it into that line so it would delete it.
[...]
I was trying to get the php script to do that for me, to get the encid from the database.

You might have intendend $encid to be the value from the database, but you never set it. You need an $encid=$row[0]; statement, or simply use $row[0] when constructing the statement.

DELETE FROM attacktype_table AS a
    WHERE EXISTS(
        SELECT  encid FROM encounter_table AS e
          WHERE a.encid = e.encid
              AND starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY)
    )

Gave that a try and once again error, going to look into it in a few moments, but here is what it gave me...

Apparently, DELETE doesn't support table aliases in the FROM clause. Remove the "AS a" and replace the alias with the table name in the rest of the statement.

There's a third alternative: DELETE supports joins with a USING clause:

DELETE FROM attacktype_table 
  USING attacktype_table
    INNER JOIN encounter_table 
    ON attacktype_table.encid = encounter_table.encid
  WHERE encounter_table.starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY)

It seems aliases aren't supported in the USING clause either.

To handle orphaned attacktype_table entries, use:

DELETE FROM attacktype_table 
  USING attacktype_table
    LEFT JOIN encounter_table 
    ON attacktype_table.encid = encounter_table.encid
  WHERE encounter_table.starttime < CURDATE() - INTERVAL 30 DAY
    OR encounter_table.encid IS NULL

Add an index on attacktype_table.encid to speed up deletion. Otherwise MySQL will need to scan attacktype_table in order to find rows to remove.

Another solution is to change attacktype_table and encounter_table to use the InnoDB engine, then define attacktype_table.encid as a foreign key (since it is):

ALTER TABLE encounter_table ENGINE=InnoDB;
ALTER TABLE attacktype_table ENGINE=InnoDB;

ALTER TABLE attacktype_table 
  ADD FOREIGN KEY (encid) 
  REFERENCES encounter_table (encid) 
  ON DELETE CASCADE ON UPDATE CASCADE;

Then, when you delete an encounter, it automatically deletes the corresponding entries from attacktype_table. Again, you'll want indices on the encid columns.

[3dhomejoe]

ok, gave this a try...

$query = "DELETE FROM attacktype_table 
  USING attacktype_table
    LEFT JOIN encounter_table 
    ON attacktype_table.encid = encounter_table.encid
  WHERE encounter_table.starttime < CURDATE() - INTERVAL 30 DAY
    OR encounter_table.encid IS NULL"; 

The good news, it works

The bad news, it loops

It removed everything from the database, but got stuck in a loop again, so its like I was back to were I was before, I don't understand why its stuck in a loop.

edit: this works also but its stuck in a loop also,

$query = "DELETE FROM attacktype_table 
  USING attacktype_table
    INNER JOIN encounter_table 
    ON attacktype_table.encid = encounter_table.encid
  WHERE encounter_table.starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY)"; 

[misson]

As stated previously,

This one should work. What is the structure (i.e. the CREATE statements) of the [table encounter_table]? What happens if you turn the query into a SELECT (e.g. SELECT * FROM attacktype_table WHERE encid IN ...) and run that? What's the result of EXPLAINing the SELECT?

As it says in the sig, my questions aren't rhetorical; follow all instructions in the sig. Also, did you add the indices?

How do you know that it's stuck in a loop, as opposed to stuck in a deadlock or stuck in some other manner? Run whatever checkdisk utility you have, then check and repair the tables (the exact method depends on the table types).

[3dhomejoe]

Ah missed that,
I believe it should be like this right?

$query = "SELECT * FROM attacktype_table WHERE encid IN (SELECT  encid FROM encounter_table WHERE starttime <  DATE_SUB(CURDATE(),INTERVAL 30 DAY));"; 

Ran it and IIS gave me a timeout error, and in mysql it was still running, I killed it after 30 min, cpu usage from mysql went up to about 95%.

Ran a disk check, that came up good, also ran maintenance on the database, that came up good also, no errors were reported on any of them.

Other information:
IIS Verison: 7.0 - Windows Server 2008 STD SP2
PHP Version: 5.3.2
Mysql Version: 5.0.77

Thanks for your time.
Joe

[misson]

What's the result of EXPLAINing the SELECT[s]?

did you add the indices?

Do the non-finishing queries finish if run from a MySQL client (e.g. MySQL query browser)?