Forum Moderators: open
I'll explain my situation a little, I've made a section where users can upload certain information for display, which is ordered from latest to oldest using ORDER BY id DESC.
They also have the ability to delete and re add new information (maximum of 2), but the problem is, people could just delete somthing and add the same information back again so they'd be at the top of the list.
To combat this once the user deletes somthing, it will be added to another table temporarily which will just store their username and the date in: Y-m-d H:i:s format. So say if they have one active, and one deleted I'll use PHP and if it adds up to 2 (which obviously in this case it does) then they will not be allowed to submit another one.
What I need to know is how can I only select records that were added less than 3 days ago and what is the most appropriate way to delete ones that are over 3 days ago as they are no longer needed (eg. run the delete script everytime someone tries to upload somthing, or using Cron (which I'm not sure of how to use)).
Thanks,
Jeigh.
DELETE FROM tblSomething WHERE (Adate >= '" & theVariable & "')
If "date'now'" (or "'now'::date") doesn't work on you system, try "now()", but then your query might not use your index.
SELECT * FROM example WHERE date>=(date'now'-interval'3d')::0000-00-00 00:00:00 AND username='$username'
DELETE FROM example WHERE date<('now'-interval'3d')::0000-00-00 00:00:00
Thanks,
Jeigh
SELECT * FROM example WHERE date>=(date'now'-interval'3d')::timestamp AND username='$username';
DELETE FROM example WHERE date<(date'now'-interval'3d')::timestamp;
Don't forget the type attached to 'now' (like I did earlier on the DELETE example).
To verify that it work as expected, first try the SELECT query. See if it returns "young" records. Also, check the correct type of the 'date' column.
SELECT * FROM deleted_items WHERE date>=(date('Y-m-d H:i:s', time())-interval'3d')::datetime AND username='$username';
It came up with:
ERROR: Unknown Punctuation String @ 83
STR: ::
Not sure if I followed what you said correctly though.
Thanks.
SELECT * FROM deleted_items WHERE date>=(date('Y-m-d H:i:s', time())-interval'3d')::datetime AND username='$username';
Why would you need this
)::datetime
Try without it, and somebody pls explain me on what that is.
SQL query:
SELECT *
FROM deleted_items
WHERE date >= ( date( 'Y-m-d H:i:s', time( ) ) - INTERVAL '3d' )
AND username = '$username'
LIMIT 0 , 30
MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( 'Y-m-d H:i:s' , time( ) ) - interval '3d' ) AND username = '$
I'm not sure what that line did, but I was just following the format he used.
Thanks.
or
change it to
`date`
SELECT *
FROM deleted_items
WHERE time >= ( date( 'Y-m-d H:i:s', time( ) ) - INTERVAL '3d' )
AND username = 'Jeigh'
LIMIT 0 , 30
But it gave me:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( 'Y-m-d H:i:s' , time( ) ) - interval '3d' ) AND username = 'J
Thanks for all the help guys.
Change that to `time` or time_new or something similar.
Habtom
[edited by: Habtom at 12:55 pm (utc) on Aug. 9, 2007]
SELECT *
FROM deleted_items
WHERE time_added >= ( CURRENT_DATE( ) - INTERVAL 3
DAY )
Which I'm pretty sure is working, so problem solved I guess.
Thanks for all the help again :)
