How to delete contents from a mysql database
Moderator: General Moderators
How to delete contents from a mysql database
How to delete all contents of a table except the last 5 entries, what's the code?
Thanks.
Thanks.
Re: How to delete contents from a mysql database
Untested, but maybe something like:
Code: Select all
DELETE
FROM
`someTable`
WHERE
`primaryKey` < MAX(`primaryKey`) - 4Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: How to delete contents from a mysql database
I get the following error:
Could not delete data: Unknown column 'primaryKey' in 'where clause'
Could not delete data: Unknown column 'primaryKey' in 'where clause'
Re: How to delete contents from a mysql database
You'd need to specify which column is your primary key.
Re: How to delete contents from a mysql database
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):
-Greg
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;Re: How to delete contents from a mysql database
Something like
It pulls Could not delete data: Invalid use of group function
Code: Select all
$sql = 'DELETE FROM site
WHERE id < MAX(id) - 4';Re: How to delete contents from a mysql database
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
Have you tried a subquery?lovelf wrote:Something like
It pulls Could not delete data: Invalid use of group functionCode: Select all
$sql = 'DELETE FROM site WHERE id < MAX(id) - 4';
Code: Select all
DELETE FROM site
WHERE id < (SELECT MAX(id) FROM site) - 4Re: How to delete contents from a mysql database
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
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
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
-Greg
Re: How to delete contents from a mysql database
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
So I have this code
Is something wrong with the mysql syntax, anything I'd put wrong reformatting the functions.
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);
Re: How to delete contents from a mysql database
You're using quotes around field and table names where you should be using backticks (`)
Re: How to delete contents from a mysql database
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.