CODEWITHSUNDEEP
phpmysqldatecron
denikov
denikov

Reputation: 869

Working with date function mysql, setting up a cron job

I'm working on a members account page. The page works fine and I have a row in my table which displays the date when the person registered. People will be placing orders and on their account page I want to display their accumulating points. The function I want to write is after 30 days, the accumulating points start over at 0. I was thinking about setting up a cron job for this through my cPanel. But I don't know how to work with the date function to make it check if 30 days have gone by. This is basically the thinking I have so far:

`<?php 
//connect to my database and run a query for the date I already have
//set the date to a variable using mysql_fetch_assoc(query)

//now the part where I need help for the function
if(registerdate == (registerdate+30days){
sql = mysql_query("UPDATE members SET accPoints='0' WHERE id='$userid' and username='$username'");
}
?>`

So please help me out with the code for this function. Also one other quick question: is it possible to populate a table column with multiple data and then retrieve it for display? Basically put all one person's orders in the Orders column and then search through it and show it on their member page? Thanks in advance!

Upvotes: 0

Views: 342

Answers (2)

Alex
Alex

Reputation: 219

You should be able to do that in the query itself:

"UPDATE members SET accPoints='0' WHERE id='$userid' and username='$username' AND registerdate + interval 30 DAY < NOW()"

Second question:

If I understand you correctly, you want to associate multiple pieces of data with each user. To do so, you would want to create another table with a many-to-many relationship to the users table:

orders(order_id, user_id, orderinfo, ...)

You can then query for every order by user 1 like this:

SELECT o.* FROM users u, orders o WHERE u.user_id = 1 AND o.user_id = u.user_id

Upvotes: 1

Dathan Vance Pattishall
Dathan Vance Pattishall

Reputation: 141

You can do a bunch of different things. You can turn the date into a UNIX timestamp with strtotime and add the number of seconds to see if your cron job hits the threshold or add dates or use pure SQL to return all the people that need "points" based on your WHERE clause.

Upvotes: 0

Related Questions