a tough query

[harmor19]

I need help writing a query.

In the "user" table I have "planid"
I want to link that to a table named "hosting_plans".
In "hosting_plans" I have "subscriptionid".
I want to link that to the table "subscriptionlog".

Then I'll have to write up a cron that will check if the user still has the subscription which I also don't know how to write but I'll look at others for an idea.

[Adrian Schneider]

So um... what data do you need? Just the columns listed above? Are you showing users that ARE subscribed or those who are not? (etc)

This might get you started... need more info though:

SELECT
	u.userid,
	u.username,
	u.planid,
	p.title as planname,
	h.title as hostname,
	sl.status,
	sl.regdate,
	sl.expirydate
	
FROM user as u
LEFT JOIN hosting_pans as p on (u.planid = p.planid)
LEFT JOIN subscription as s on (h.subscriptionid = s.subscriptionid)
LEFT JOIN subscriptionlog as sl on (sl.subscriptionid = s.subscriptionid)

WHERE u.planid > 0

Then when you process through the results, you can use the dates to do whatever. Or, use the date in the query to restrict it to expired or valid subscriptions.

[harmor19]

I want to show who are not subscribed. I edited the code to show "sl.status = 1" but it doesn't return any rows. I know that there is row in the "subscriptionlog" table with the "status" using the value "1".
What I want it to do is check if the user's subscription has expired.

SELECT
u.userid,
u.username,
u.planid,
p.title as planname,
p.title as hostname,
sl.status,
sl.regdate,
sl.expirydate

FROM user as u
LEFT JOIN hosting_plans as p on (u.planid = p.planid)
LEFT JOIN subscription as s on (p.subscriptionid = s.subscriptionid)
LEFT JOIN subscriptionlog as sl on (sl.subscriptionid = s.subscriptionid)

WHERE u.planid > 0 && sl.status = 1

[Adrian Schneider]

Not subscribed, as in they used to be? Add "&& sl.expirydate < " . TIMENOW . "

[harmor19]

Thank you. Now I have to put it in PHP form but I can manage that.