Jump to content
Larry Ullman's Book Forums

Mysql Row Deletion With Php Command


Recommended Posts

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

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

  • Upvote 1
Link to comment
Share on other sites

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!

  • Upvote 1
Link to comment
Share on other sites

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

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

  • Upvote 1
Link to comment
Share on other sites

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)";

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...