wframe Posted March 23, 2011 Share Posted March 23, 2011 Hello, we were able to get our php script to delete records in our database according to 'registration_date' parameters set in your lesson on "Using Conditionals" out of chapter 4. Below is the working script: ------------- <?php $con = mysql_connect(whateversometihing.com', 'dbname','pass'); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("dbname", $con); $sql="DELETE FROM table WHERE (registration_date < '2011-03-22 16:35:33') AND (registration_date > '2011-03-22 16:23:08')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "Added"; mysql_close($con); ?> ---------------- Our question is how can we get the script to delete a record after it has been in our database for 24 hours? For instance, if a member has a trial period, we would like to cancel that member's account once that trail period has expired. Of course, we would want to have each member's trial period start 24 hours after they have registered without affecting other members whose trail period has not yet expired. Thanks in advance, Link to comment Share on other sites More sharing options...
Matthaus Posted March 23, 2011 Share Posted March 23, 2011 I would use mysql's timestampdiff function (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff). Then use registration_date as expr1 in the example, and now() as expr2. So it would be something like... $sql="DELETE FROM joinmembersarea WHERE TIMESTAMPDIFF(HOUR, registration_date, now()) >= 24"; But rather than deleting records, you could always either use the date logic when logging in, and just issue an error if they registered more than 24 hours ago, or set an active flag to false when they've expired. I just feel funny about deleting records when I don't have to. Hope that helps. -matthaus 1 Link to comment Share on other sites More sharing options...
HartleySan Posted March 23, 2011 Share Posted March 23, 2011 One quick bit of advice: I would not post your database connection details. You could just write something in the code like the following: $con = mysqli_connect(); // Database details go here. Also, if you want to actually delete the record, you'd have to set up a script that would automatically execute, for instance, once a day. The easier solution though is to always just test the user's registration date. For example, if said user has not paid for a full membership and their registration date was more than 24 hours ago, then do not allow them to view premium content, etc. Either way is an option, but performing the check each time is easier than auto-deleting "unneeded" records. And I say "unneeded" because what's to say that their login info is no longer necessary? Wouldn't you normally want to retain their login info, even if they haven't paid for said content? Edit: I didn't realize that Matthaus was answering your post too. Whoops! 1 Link to comment Share on other sites More sharing options...
wframe Posted March 23, 2011 Author Share Posted March 23, 2011 Thanks for all the advice everyone. I will try and see about storing the records and setting the active to expire like you suggested..... Link to comment Share on other sites More sharing options...
HartleySan Posted March 24, 2011 Share Posted March 24, 2011 It's not that hard. Just get the appropriate information from the database and then test whether the person is A) a member, or has registered in the last 24 hours (i.e., NOW() - reg. date < 24 hrs.). 1 Link to comment Share on other sites More sharing options...
wframe Posted March 25, 2011 Author Share Posted March 25, 2011 Ok everyone, I have the MySQL row deletion script working. I am interested in deactivating the row but not totally deleting it, however. Could I set the password or active, etc to "NOT NULL" in order to save the information of the member? I tried the script below: --------------------------- <?php $con = mysql_connect('joinmembersclub.db.7477001.hostedresource.com', 'joinmembersclub','NI1957ja'); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("joinmembersclub", $con); $sql= 'UPDATE joinmembersarea SET password = "NOT NULL", WHERE registration_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE)'; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "Added"; mysql_close($con); ?> --------------------------------- Does his script make any sense? I keep getting the following error: Error: 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 'WHERE registration_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE)' at line 1 Link to comment Share on other sites More sharing options...
Jonathon Posted March 25, 2011 Share Posted March 25, 2011 A wise decision wframe. I can't think why you want to delete the entire user. Unless they are maybe being malicious with their use. If ti were me i'd have a column called active and give a boolean value. So if the the person is active they would have a value of 1 or 0 if false. Then you maintain all their information and they just have to update something in their profile or system to make themselves active again. i've not looked into your query in much depth but i'd say the comma after "NOT NULL" is not needed. Why are you setting the password to NOT NULL too? Do forgive me if its in the earlier posts I have only scan read them Jonathon 1 Link to comment Share on other sites More sharing options...
Stuart Posted March 26, 2011 Share Posted March 26, 2011 The error in your statement is random comma before the where clause - the following should be syntactically correct: $sql= 'UPDATE joinmembersarea SET password = "NOT NULL" WHERE registration_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE)'; Aside from that I'd be setting a BOOL flag to 1 to indicate the user is now banned/blocked. $sql= "UPDATE joinmembersarea SET banned = '1' WHERE registration_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE)"; 1 Link to comment Share on other sites More sharing options...
Jonathon Posted March 26, 2011 Share Posted March 26, 2011 Isn't that what i've said Link to comment Share on other sites More sharing options...
wframe Posted March 30, 2011 Author Share Posted March 30, 2011 Thanks for the info guys...got it working!!! Link to comment Share on other sites More sharing options...
Jonathon Posted March 30, 2011 Share Posted March 30, 2011 Thanks for the info guys...got it working!!! Well done glad you got it sorted Link to comment Share on other sites More sharing options...
Recommended Posts