Jump to content

remove rows from database


3dhomejoe

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.