[PHP + MYSQL] remove rows from database

3dhomejoe · May 8, 2010

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:

 <?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...

PHP:

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:

 <?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:

  <?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

PHP:

 		 			 $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 · May 8, 2010

PHP:

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 · May 8, 2010

3dhomejoe said:
PHP:

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.

3dhomejoe said:

PHP:

$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.

3dhomejoe said:

This one is missing a closing parentheses.

3dhomejoe said:

PHP:

$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:

Code:

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.

3dhomejoe said:

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 · May 8, 2010

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/en/show-create-table.html

3dhomejoe · May 9, 2010

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

descalzo said:
PHP:

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...

57f174211ef827a566cfbd29be49b37e1fe29c8a3a25a06139e63b5311ceb5fb20c5988083af10789ef8c702db25d0db31438c06dd0a9fa93aa16a2351561402658e33

(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,

misson said:
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.

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

Code:

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:

<?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

Twinkie said:
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

Twinkie said:
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(8) 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(8) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

misson · May 9, 2010

3dhomejoe said:
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.

3dhomejoe said:
Code:

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:

Code:

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:

Code:

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):

Code:

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 · May 9, 2010

ok, gave this a try...

PHP:

$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,

PHP:

$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 · May 9, 2010

As stated previously,

misson said:
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 · May 10, 2010

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

PHP:

$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 · May 10, 2010

misson said:
What's the result of EXPLAINing the SELECT?

misson said:

did you add the indices?

Click to expand...

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

3dhomejoe · May 10, 2010

Ok, here is what I got for the explain command

select `eq2`.`attacktype_table`.`encid` AS `encid`,`eq2`.`attacktype_table`.`attacker` AS `attacker`,`eq2`.`attacktype_table`.`victim` AS `victim`,`eq2`.`attacktype_table`.`swingtype` AS `swingtype`,`eq2`.`attacktype_table`.`type` AS `type`,`eq2`.`attacktype_table`.`starttime` AS `starttime`,`eq2`.`attacktype_table`.`endtime` AS `endtime`,`eq2`.`attacktype_table`.`duration` AS `duration`,`eq2`.`attacktype_table`.`damage` AS `damage`,`eq2`.`attacktype_table`.`extdps` AS `extdps`,`eq2`.`attacktype_table`.`chardps` AS `chardps`,`eq2`.`attacktype_table`.`dps` AS `dps`,`eq2`.`attacktype_table`.`average` AS `average`,`eq2`.`attacktype_table`.`median` AS `median`,`eq2`.`attacktype_table`.`minhit` AS `minhit`,`eq2`.`attacktype_table`.`maxhit` AS `maxhit`,`eq2`.`attacktype_table`.`resist` AS `resist`,`eq2`.`attacktype_table`.`hits` AS `hits`,`eq2`.`attacktype_table`.`crithits` AS `crithits`,`eq2`.`attacktype_table`.`blocked` AS `blocked`,`eq2`.`attacktype_table`.`misses` AS `misses`,`eq2`.`attacktype_table`.`swings` AS `swings`,`eq2`.`attacktype_table`.`tohit` AS `tohit`,`eq2`.`attacktype_table`.`averagedelay` AS `averagedelay`,`eq2`.`attacktype_table`.`critperc` AS `critperc` from `eq2`.`attacktype_table` where <in_optimizer>(`eq2`.`attacktype_table`.`encid`,<exists>(select 1 AS `Not_used` from `eq2`.`encounter_table` where ((`eq2`.`encounter_table`.`starttime` < (curdate() - interval 30 day)) and (<cache>(`eq2`.`attacktype_table`.`encid`) = `eq2`.`encounter_table`.`encid`))))

EDIT: forgot this...

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

and no, they don't finish

misson · May 10, 2010

Again,

misson said:
did you add the indices?

3dhomejoe said:
select `eq2`.`attacktype_table`.`encid` AS `encid`,`eq2`.`attacktype_table`.`attacker` AS [...]

Click to expand...

That's not the output I expect to see from EXPLAIN; it looks more like the result of running SHOW WARNINGS after an extended explain. The output of EXPLAIN should look more like:

Code:

+----+-------------+------------------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys | key     | key_len | ref                         | rows | Extra       |
+----+-------------+------------------+--------+---------------+---------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | attacktype_table | index  | NULL          | encid   | 5       | NULL                        | 5280 | Using index |
|  1 | SIMPLE      | encounter_table  | eq_ref | PRIMARY       | PRIMARY | 4       | test.attacktype_table.encid |    1 | Using where |
+----+-------------+------------------+--------+---------------+---------+---------+-----------------------------+------+-------------+

Some of the field values in the above are made up. Others will be the same as what you see. Still others you should want to be the same (e.g. the "key" column), but may not be. What you've posted so far tells me that you don't have an index on encounter_table.encid. As I said before (and keep asking about), index the encid columns. Index all applicable columns, really, which includes encounter_table.starttime (though you might want to make that an index on encounter_table (starttime, endtime)).

Your version of MySQL is old. You might be running up against a bug. Upgrade it. The current stable release is 5.1.46.

descalzo · May 10, 2010

PHP:

<?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(); 
} 
$num_rows = mysql_num_rows( $result ) ;
if($num_rows ==0){ 
     print "Nothing to do."; 
} else { 
echo "About to process $num_rows rows <br />\n" ;
while($row =  mysql_fetch_array($result)) 
  { 

  $id =  $row[0]; 
  echo "Deleting encid: $id <br />\n"; 
   mysql_query("DELETE FROM  attacktype_table WHERE encid='$id' "); 
  
  } 
} 
mysql_close($con); 
?>

3dhomejoe · May 10, 2010

The code works, checked in mysql and I saw that it was running the different encid #'s and the script did end like it should have. Now I can add some finishing touches to it and going to try what misson said at the beginning of the post, even though this is a private script, its still a good Idea to practice using correct code X+)

Thanks for your help everyone, this has helped me out alot, and I have learned some new things that I never knew before.

Thanks
Joe

[PHP + MYSQL] remove rows from database

3dhomejoe

Member

descalzo

Grim Squeaker

misson

Community Paragon

Twinkie

Banned

3dhomejoe

Member

misson

Community Paragon

3dhomejoe

Member

misson

Community Paragon

3dhomejoe

Member

misson

Community Paragon

3dhomejoe

Member

misson

Community Paragon

descalzo

Grim Squeaker

3dhomejoe

Member

Free Web Hosting

Our Community

Legal