How to delete contents from a mysql database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

How to delete contents from a mysql database

Post by lovelf »

How to delete all contents of a table except the last 5 entries, what's the code?

Thanks.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How to delete contents from a mysql database

Post by pickle »

Untested, but maybe something like:

Code: Select all

DELETE
FROM
  `someTable`
WHERE
  `primaryKey` < MAX(`primaryKey`) - 4
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

Re: How to delete contents from a mysql database

Post by lovelf »

I get the following error:

Could not delete data: Unknown column 'primaryKey' in 'where clause'
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to delete contents from a mysql database

Post by Celauran »

You'd need to specify which column is your primary key.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: How to delete contents from a mysql database

Post 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
lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

Re: How to delete contents from a mysql database

Post 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
lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

Re: How to delete contents from a mysql database

Post 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	 	 
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to delete contents from a mysql database

Post 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
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: How to delete contents from a mysql database

Post 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);
}
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to delete contents from a mysql database

Post 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);
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: How to delete contents from a mysql database

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to delete contents from a mysql database

Post 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.
lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

Re: How to delete contents from a mysql database

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to delete contents from a mysql database

Post by Celauran »

You're using quotes around field and table names where you should be using backticks (`)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to delete contents from a mysql database

Post 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.
Post Reply