Question

[Mike516]

The code should take the IDs and delete them from the tables but it isn't we are getting errors. Anyone see anything wrong?

<?php
###############
#  Prune.php  #
###############

//Variables
$dbhost = "localhost"; //host name
$dbuser = "root"; // username for db
$dbname = "dbname"; // database name
$dbpass = ""; //password for db
$error = "There was a problem connecting to the database"; // error statement

//connect to db
mysql_connect ($dbhost, $dbuser, $dbpass) or die ($error);
mysql_select_db($dbname); 

$lastlogin = "1";
$query = "SELECT id FROM accounts WHERE lastlogin < CURRENT_TIMESTAMP() - $lastlogin";
$result = mysql_query($query) or die ('Error: '.mysql_error ());

    while ($players=mysql_fetch_array($result)){
            
mysql_query("DELETE FROM buddies WHERE characterid = $players");
mysql_query("DELETE FROM inventoryitems WHERE characterid = $players");
mysql_query("DELETE FROM skills WHERE characterid = $result");
mysql_query("DELETE FROM keymap WHERE characterid = $players");
mysql_query("DELETE FROM characters WHERE accountid = $players");
            
            
            }
        ?>

[edwinhermann]

The code should take the IDs and delete them from the tables but it isn't we are getting errors. Anyone see anything wrong?

In your code, $players is an array. You can't specify an array in your SQL string. So you need to reference the correct array element, such as:

mysql_query("DELETE FROM buddies WHERE characterid = $players['characterid']");

Also I noticed that line 25 has $result (which should also be $players).

[Mike516]

In your code, $players is an array. You can't specify an array in your SQL string. So you need to reference the correct array element, such as:

mysql_query("DELETE FROM buddies WHERE characterid = $players['characterid']");

Also I noticed that line 25 has $result (which should also be $players).

What is stored in that array is ids and what i need to do is go into the table "buddies" and go to the row with that id and delete the entire row

[edwinhermann]

What is stored in that array is ids and what i need to do is go into the table "buddies" and go to the row with that id and delete the entire row

You state you're getting errors. What exactly are the error messages?

I can take a guess at what you're trying to achieve:
Change the five SQL lines to this:

mysql_query("DELETE FROM buddies WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM inventoryitems WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM skills WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM keymap WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM characters WHERE characterid = ".$players['id']);

I'll explain what this does:

Line 21 (in your original code listing) produces an array, with the indexes of the array being the names of the columns in your database. This array is stored in the variable $players.

Based on what you have in Line 18, the array with the key 'id' will be the ID of the player to delete.

Therefore, $players is what represents the ID of the player whose data is to be deleted.

The only thing I'm dubious about is your logic for determining which players should be purged (i.e. the WHERE clause in line 18). I don't know enough about your data to be able to say whether or not your logic is correct, but to check whether it's selecting the right players you can always insert a

print_r($players);

line after the while statement. That will show you a print out of each row found.

What does the column 'lastlogin' represent? The time the user logged in before this session? And are you wanting to remove all data from previous login sessions? If so, your logic ought to be

$query = "SELECT id FROM accounts WHERE lastlogin < CURRENT_TIMESTAMP()";

But as I stated before, without knowing more about what your data represents I can only guess what logic you are wanting to implement.

[i-hate-blue]

What is the content of $players? can you put

var_dump($players);

after while();

[Mike516]

Ok pretty much what I'm doing is searching the the table accounts

$res = mysql_query("SELECT id FROM accounts WHERE lastlogin < CURRENT_TIMESTAMP() - $lastlogin");

It selects all account ids that have not logged in since certain time..

Now what i need to do is take all the selected ids and store them and use them in the delete statements...

while ($players=mysql_fetch_array($res)){
            
            mysql_query("DELETE FROM buddies WHERE characterid = $players");
			mysql_query("DELETE FROM inventoryitems WHERE characterid = $players");
			mysql_query("DELETE FROM skills WHERE characterid = $players");
			mysql_query("DELETE FROM keymap WHERE characterid = $players");
			mysql_query("DELETE FROM characters WHERE accountid = $players");
            
            
            }

Thanks in advanced if you can help

[edwinhermann]

Did you try the code I posted (just before i-hate-blue)? If so, what were the results?
Also i-hate-blue has asked a question for which you haven't provided an answer yet.

[Mike516]

array(2) { [0]=> string(1) "1" ["id"]=> string(1) "1" } array(2) { [0]=> string(1) "6" ["id"]=> string(1) "6" }

[i-hate-blue]

array(2) { [0]=> string(1) "1" ["id"]=> string(1) "1" } array(2) { [0]=> string(1) "6" ["id"]=> string(1) "6" }

you can try edwinhermann script below then :

mysql_query("DELETE FROM buddies WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM inventoryitems WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM skills WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM keymap WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM characters WHERE characterid = ".$players['id']);

so... is it working?

[edwinhermann]

Great, so that confirms the array structure of $players.

So in an earlier post I suggested the following:

Change the five SQL lines to this:

mysql_query("DELETE FROM buddies WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM inventoryitems WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM skills WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM keymap WHERE characterid = ".$players['id']);
mysql_query("DELETE FROM characters WHERE characterid = ".$players['id']);

Did this work for you? If not, were error messages generated?

[Mike516]

It worked thank you all very much!