Page 1 of 2

How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 10:20 am
by lovelf
How to delete all contents of a table except the last 5 entries, what's the code?

Thanks.

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 10:50 am
by pickle
Untested, but maybe something like:

Code: Select all

DELETE
FROM
  `someTable`
WHERE
  `primaryKey` < MAX(`primaryKey`) - 4

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:00 am
by lovelf
I get the following error:

Could not delete data: Unknown column 'primaryKey' in 'where clause'

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:03 am
by Celauran
You'd need to specify which column is your primary key.

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:05 am
by twinedev
Well, obviously without information on the table, you are not going to get EXACT code, and will have to improvise.

Do you have a main ID field that is the primary key, and is (preferably) auto incremented? If not, do you have a field with time/date it was inserted? Basically, what field would you currently sort by to find the 5 most recent entries by hand?

Don't know? at least execute the following query, and post the results here (note, again, you will have to improvise this to use the actual table name):

Code: Select all

DESCRIBE table_name;
-Greg

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:06 am
by lovelf
Something like

Code: Select all

$sql = 'DELETE FROM site
        WHERE id < MAX(id) - 4';
It pulls Could not delete data: Invalid use of group function

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:09 am
by lovelf
I would sort id to delete by hand. I have a primary key which is id and is auto incremented.

Code: Select all

    Field	Type	Null	Key	Default	Extra
			id	int(11)	NO	PRI	NULL	auto_increment
			title	varchar(200)	NO	 	NULL	 
			url	varchar(200)	NO	 	NULL	 
			date	datetime	NO	 	NULL	 
			path	varchar(350)	NO	 	NULL	 	 

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:14 am
by Celauran
lovelf wrote:Something like

Code: Select all

$sql = 'DELETE FROM site
        WHERE id < MAX(id) - 4';
It pulls Could not delete data: Invalid use of group function
Have you tried a subquery?

Code: Select all

DELETE FROM site
WHERE id < (SELECT MAX(id) FROM site) - 4

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:15 am
by twinedev
SQL doesn't like using something you are modifying as part of a condition, and this is probably the case here (technically, each row it deletes, MAX(id) could change). Not sure if it wiull complain on subquery, if so split it into two queries.

Code: Select all

$intMax = FALSE;
$rsMax = mysql_query('SELECT MAX(`id`) FROM `sites`');
if ($rsMax && mysql_num_rows($rsMax)>0) {
    $intMax = mysql_result($rsMax,0);
    mysql_free_result($rsMax);
}
if ($intMax) {
    mysql_query('DELETE FROM `site` WHERE `id` < ' $intMax-4);
}

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:20 am
by Celauran
If you're going to go with two queries, why not something like this?

Code: Select all

$query  = "SELECT id FROM site ORDER BY id DESC LIMIT 5";
$result = $sql->query($query)->fetch_all();
$do_not_delete = "";
foreach ($result as $row)
{
    $do_not_delete .= "{$row['id']}, ";
}
$do_not_delete = rtrim($do_not_delete, ", ");

$query = "DELETE FROM site WHERE id NOT IN ({$do_not_delete})";
$sql->query($query);

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:28 am
by twinedev
Your way required more data transfer, PHP and mySQL have to communicate to retrieve 4 additional records. (yea, not such a big deal for something small, but good to be in the practice to minimize loads for when you maybe work up to more complex things. Just habit for me ;-)

-Greg

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:30 am
by Celauran
True, but this also ensures that you're keeping the last five rows. id - 4 doesn't necessarily; id = MAX(id) - 3, say, could have already been removed for some other reason.

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:40 am
by lovelf
So I have this code

Code: Select all

$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'pass';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

mysql_select_db('colla');

$intMax = FALSE;
$rsMax = mysql_query('SELECT MAX("id") FROM "site"',$conn);
if ($rsMax && mysql_num_rows($rsMax)>0) {
    $intMax = mysql_result($rsMax,0);
    mysql_free_result($rsMax);
}
if ($intMax) {
    mysql_query('DELETE FROM "site" WHERE "id" <  $intMax-4',$conn);
}

mysql_close($conn);
Is something wrong with the mysql syntax, anything I'd put wrong reformatting the functions.

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:43 am
by Celauran
You're using quotes around field and table names where you should be using backticks (`)

Re: How to delete contents from a mysql database

Posted: Mon Jan 16, 2012 11:46 am
by califdon
This is beyond the issue of "how" to delete "all but the last 5" rows, but it always gets my attention when someone poses a question like this. It is often an indication that the questioner is not using a database in the most efficient manner. In many cases it is not a good idea to delete "old" data. The "cost" of retaining data indefinitely is usually negligible and it is hard to guarantee that at some later date someone will not suddenly need to recover that data. Of course, this doesn't apply in every case, but it has been my observation that when a beginner asks about deleting "unneeded" data, it is usually a bad idea to do so.