Plugin - Friends Updates

Alright I've been hammering at this for hours on this 1 stupid line of code to delete old entries. Someone that knows more please step up to the plate on this one, because I'm stumped.

What I'm attempting to do is count the rows in table #__liveshoutbox from field nameid and order them by id desc. Then delete any row count greater then 10. So if it has 17 rows. It should delete the 7 oldest rows.

So far it's not doing squat. I can't figure this out or even know if I'm doing it right.

I just need it to delete the old entries. Even deleting by date would be effective. Say if the entries are 1 week only. It deletes them. Something along those lines would be fine too.

Any ideas?

Here's my query that doesn't work:
[code:1]DELETE #__liveshoutbox WHERE nameid NOT IN (SELECT TOP 10 nameid FROM #__liveshoutbox ORDER BY id DESC)[/code:1]

[code:1]select max id from #__...[/code:1]

[code:1]delete from #__... where id < maxid-10[/code:1]

SoM

Post edited by: steveometer, at: 2008/09/24 23:33

well i suppose...

[code:1]delete from #__... where ( id < ( max id ) )[/code:1]

Don't think I'm setting it up right, but it's not working.

The whole query thing is fairly new to me so if you could be as so kind to be a bit more detailed then I'd really appreciate it.

This is what I have:
[code:1] $query = "SELECT MAX nameid FROM #__liveshoutbox"
. "DELETE FROM #__liveshoutbox WHERE ( nameid < ( MAX nameid ) )";
[/code:1]

Also if it helps. This is the query to get the updates:
[code:1] $sql = "SELECT *"
. "\n FROM #__liveshoutbox AS a, #__comprofiler_members as b"
. "\n WHERE b.memberid = a.nameid"
. "\n AND b.referenceid=".$my->id
. "\n AND b.accepted=1 AND b.pending=0"
. "\n ORDER BY a.id DESC";
[/code:1]

Here's the PHP file if anyone wants to poke around in it and see what can be done about deleting the old entries. I sure as heck can't figure it out.

Post edited by: krileon, at: 2008/09/25 01:30

I don't get it. This is extremely frustrating. This should work perfectly fine.

[code:1] $query = "DELETE FROM #__liveshoutbox WHERE nameid='66'"
. "ORDER BY id DESC LIMIT 10";
$database->setQuery($query);
[/code:1]

66 would be replaced obviously, but as a test I let it run and NOTHING happened. What in the heck is going on with this. User 66 has over 20 rows. It should have deleted all but the 10 newest.

krileon wrote:

I don't get it. This is extremely frustrating. This should work perfectly fine.

[code:1] $query = "DELETE FROM #__liveshoutbox WHERE nameid='66'"
. "ORDER BY id DESC LIMIT 10";
$database->setQuery($query);
[/code:1]

66 would be replaced obviously, but as a test I let it run and NOTHING happened. What in the heck is going on with this. User 66 has over 20 rows. It should have deleted all but the 10 newest.

[code:1] $query = "DELETE FROM #__liveshoutbox WHERE nameid='66' "
. "ORDER BY id DESC LIMIT 10";
$database->setQuery($query);
[/code:1]

can you tell me, specifically the difference?

it is literally a matter of 1 charachter :pinch:

Post edited by: steveometer, at: 2008/09/25 01:59