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?

Thanks
3dhomejoe

<?php
$con = mysql_connect('10.0.1.65','3dhomejoe','PASSWORDGOESHERE!!!!');
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";
 mysql_query(DELETE FROM 'attacktype_table' WHERE ('encid'='$row[0]'));
  }
}
mysql_close($con);
?>

[3dhomejoe]

I could not find the edit button but here is my updated 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);
?>

[Fritz_fx]

I haven't played around with mysql for a while so I'm not sure apart from trying this:

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

[3dhomejoe]

That does not work, just spits out a white page

[3dhomejoe]

I just did a test, I ran this code to see if it would work and it did NOT work...

DELETE FROM attatcktype_table WHERE encid=57f17421

There is more then one row where the encid # is the same and i want it to remove all rows that = the same encid #

[bradgrafelman]

Get rid of the SELECT query and just use a single query:

DELETE FROM attacktype_table
WHERE encid IN (
    SELECT encid
    FROM encounter_table
    WHERE starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY)
)

Running a SQL query inside a loop is quite often a good sign of unoptimized code.

EDIT: Also note that your query above won't work because you have an unquoted string value.

[3dhomejoe]

Ok, so this is my current code, correct? Gave it a run and it did nothing...

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

[bradgrafelman]

I don't have a MySQL server handy to test at the moment... does mysql_query() return false? If so, what is the MySQL error message?

[3dhomejoe]

It gives me nothing, it is at http://www.3dhomejoe.info/test/test.php

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

mysql_select_db("eq2", $con);

mysql_query()
  }
}
mysql_close($con);
?>

but i have another script that selects data from a database and that works just fine, is there anything that I dont have setup right in my php.ini file to spit out errors?

[bradgrafelman]

3dhomejoe wrote:

that I dont have setup right in my php.ini file to spit out errors?

Well that's possible... display_errors has to be On and error_reporting set to something like E_ALL for errors to be displayed on screen.

It's more common to debug SQL errors in the application while developing, though. Example:

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

EDIT: To elaborate, the above method will spit out the MySQL error message on failure, something that isn't included in the PHP error message regardless if your php.ini settings.

[3dhomejoe]

ahh we are getting somewhere, code is

<?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 WHERE encid IN (SELECT encid FROM encounter_table WHERE startime < DATE_SUB(CURDATE(),INTERVAL 30 DAY)";
mysql_query($query) or die('MySQL error: ' . mysql_error() . "<br>\nQuery: $query"); 

mysql_close($con);
?>

and this 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 '' at line 1
Query: DELETE FROM attacktype_table WHERE encid IN (SELECT encid FROM encounter_table WHERE startime < DATE_SUB(CURDATE(),INTERVAL 30 DAY)

[bradgrafelman]

Oops, didn't catch it before - you're missing a closing ')' after the subquery.

[3dhomejoe]

I just noticed that too, now there is a different error...

MySQL error: Unknown column 'startime' in 'where clause'
Query: DELETE FROM attacktype_table WHERE encid IN (SELECT encid FROM encounter_table WHERE startime < DATE_SUB(CURDATE(),INTERVAL 30 DAY))

in the 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 startime < DATE_SUB(CURDATE(),INTERVAL 30 DAY))";
mysql_query($query) or die('MySQL error: ' . mysql_error() . "<br>\nQuery: $query"); 

mysql_close($con);
?>

[bradgrafelman]

Well the error message pretty much spells out your problem... not much we can suggest other than to use the right column name. Perhaps you misspelled it?

[3dhomejoe]

I think i need stronger glasses lol misspelled starttime

I think that worked, the script has been running for a while (i think there was 68,000 iteams or so) its taking its time, the sql server cpu usage jumped to 95% so I think its doing something now, I will post back if there are any more issues

Thanks
Joe

[3dhomejoe]

Good news, it works
Bad news, it was stuck in a loop that never ended, after 1hour and 30 min, i killed the command

[bradgrafelman]

Did you use MySQL's 'SHOW FULL PROCESSLIST' command to see what the process was doing at the time that it appeared to be stuck in a loop?

[3dhomejoe]

no I did not, how do I use that command?

[bradgrafelman]

You simply execute it and read the output. See the MySQL manual page here for more info.

[3dhomejoe]

Ran this in the 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 ~]#