[PHP + MYSQL] remove rows from database

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by 3dhomejoe, May 8, 2010.

  1. 3dhomejoe

    3dhomejoe New Member

    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    0
    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...
    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...

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

    descalzo Grim Squeaker Community Support

    Messages:
    9,375
    Likes Received:
    327
    Trophy Points:
    83
    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 )
     
    Last edited: May 8, 2010
  3. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    Don't use or die (or exit) when outputting HTML, and don't output DBMS error messages for all to see.

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

    This one is missing a closing parentheses.

    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.

    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).
     
    Last edited: May 8, 2010
  4. Twinkie

    Twinkie Banned

    Messages:
    1,389
    Likes Received:
    12
    Trophy Points:
    0
  5. 3dhomejoe

    3dhomejoe New Member

    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    0
    Ok, give me some time to reply to everyone please, going to edit this post a few times as I reply...

    Ok, echo did print out alot of stuff...
    (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,

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

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

    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

    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

    Ah yes, I will turn that on.

    and I will post the tables in a few moments

    EDIT: Ok here is what I got...

     
    Last edited: May 9, 2010
  6. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    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.

    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.
     
    Last edited: May 9, 2010
  7. 3dhomejoe

    3dhomejoe New Member

    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    0
    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)"
    ;
     
    Last edited: May 9, 2010
  8. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    As stated previously,
    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).
     
    Last edited: May 9, 2010
  9. 3dhomejoe

    3dhomejoe New Member

    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  10. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48


    Do the non-finishing queries finish if run from a MySQL client (e.g. MySQL query browser)?
     
    Last edited: May 10, 2010
  11. 3dhomejoe

    3dhomejoe New Member

    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    0
    Ok, here is what I got for the explain command

    EDIT: forgot this...
    and no, they don't finish
     
    Last edited: May 10, 2010
  12. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    Again,
    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.
     
    Last edited: May 11, 2010
  13. descalzo

    descalzo Grim Squeaker Community Support

    Messages:
    9,375
    Likes Received:
    327
    Trophy Points:
    83
    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); 
    ?>
     
  14. 3dhomejoe

    3dhomejoe New Member

    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page