vb.org Archive - a tough query

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - a tough query (https://vborg.vbsupport.ru/showthread.php?t=137292)

harmor19

01-23-2007 05:27 AM

a tough query

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

01-23-2007 05:57 AM

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:

Code:

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

01-23-2007 06:09 AM

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.

Code:

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

01-23-2007 06:10 AM

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

harmor19

01-23-2007 06:52 AM

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

All times are GMT. The time now is 09:49 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01467 seconds Memory Usage 1,714KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (5)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: