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?

(Using mysql)

 

Thanks

3dhomejoe

 

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

 

something else that I tried is this direct in my sql database and it did nothing even though the encid number existed a few times

DELETE FROM attatcktype_table WHERE encid=57f17421

 

also I tried this

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

gave nothing but a white page

[Ken2k7]

Hello,

Because your encid field is obviously of type string, you need to enclose it in quotes. The first query also has a typo. Try the following query:

 

DELETE FROM attacktype_table WHERE encid = '57f17421';

 

Note that if you use double quotes (like in your second code), you need to enclose the $encid in single quotes to avoid messing up the pairing quotes. Basically, the quote before $encid closes the quote before DELETE and so the variable $encid is left by itself and it causes an error.

[3dhomejoe]

Ok, here is an update, I got a code that kinda works, it selects the data and removes it but its like stuck in a loop even though I don't have a loop in my code...

 

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

 

here is what it was doing...

 

[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 ~]#

 

The code was running for about 2 hours b4 I killed the command

 

Any help?

Thanks

Joe

[Ken2k7]

Try

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

[3dhomejoe]

here is what i get with that code...

 

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 a.encid IN (SELECT e.encid FROM encounter_table e WHERE e.starttime < DATE' at line 1

Query: DELETE FROM attacktype_table a WHERE a.encid IN (SELECT e.encid FROM encounter_table e WHERE e.starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY))

[3dhomejoe]

Version is "MySQL 5.0.77" for mysql version

[3dhomejoe]

Does anyone have new information on this?

[Ken2k7]

Would these queries make sense to what you're trying to do?

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

 

Or:

$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));";

[3dhomejoe]

They both give me errors, the code that I had before worked, but it was just stuck in a loop

 

$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");

[Ken2k7]

What's the error? It's always helpful to post up the errors. That avoids having us ask for the errors and waste an extra one post.

[Muddy_Funster]

going back to your OP you could try cahnging your delete query to a truncate one:

$qry = "TRUNCATE attacktype_table WHERE encid = '".$encid."'";

Just a thought.

[3dhomejoe]

going back to your OP you could try cahnging your delete query to a truncate one:

$qry = "TRUNCATE attacktype_table WHERE encid = '".$encid."'";

Just a thought.

 

It gives me this...

MySQL error: Query was empty

Query:

 

What's the error? It's always helpful to post up the errors. That avoids having us ask for the errors and waste an extra one post.

 

It says the same thing as before, same thing for each line you provided.

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 ')' at line 1

Query: DELETE a FROM attacktype_table a, encounter_table e WHERE a.encid = e.encid and e.starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY));

[Ken2k7]

Oh, remove the last ')'

[3dhomejoe]

This just loops it looks like...

 

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

 

Mysql reported that the command was in the state of "Sending data", I left it running for about 551 sec before I killed the command, going to try the other one now

[3dhomejoe]

And for the other line you gave me, here is what it said...

 

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

 

for

$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 also have uploaded an image from the first line of code I ran, IIS gave it to me when I killed the Mysql command, not sure if it will help or not.

 

[attachment deleted by admin]

[Ken2k7]

What's eq2.attacktype? =\

[3dhomejoe]

that is what it gave me...

 

My DB's Name is eq2

My tables are

• attacktype_table
  
• combatant_table
  
• current_table
  
• damagetype_table
  
• encounter_table
  
• swing_table
  

[3dhomejoe]

Could it be that my database is so large that is making this happen? I would not think that it would matter.

[3dhomejoe]

(Too bad I can't edit my own post...)

 

Also, is there anyway I can have mysql not send information back to the webserver so it does not have to wait for a reply?

 

Thanks

Joe

[3dhomejoe]

Ok, thanks everyone for your help, I got some code that works...

 

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