PHP cron to access events and user data

[photongbic]

I am trying to access the event and user tables through a PHP cron job and being a complete newbie, I am having issues!!!

I downloaded the tables as CSV files to get their field names and layout. I then used the following to access the data on my vb3.5.5 forum:

PHP Code:

<?php 
include('./global.php'); 

$events = $db->query_read("SELECT UNIX_TIMESTAMP() AS calendarid, dateline_from, title, lv_vb_eventforums_threadid, customfields 
                           FROM event
                           WHERE dateline_from > UNIX_TIMESTAMP()
                           ORDER BY dateline_from");

That didn't work (global.php errors), so let's try copying from some other cron jobs:

PHP Code:

?php
require_once(DIR . '/includes/functions_calendar.php');

$timenow = TIMENOW;

/$events = $vbulletin->$db->query_read("
  SELECT event.calendarid, dateline_from, event.title, lv_vb_eventforums_threadid, customfields, 
  FROM " . TABLE_PREFIX . "event AS event
  WHERE dateline_from >= $timenow AND event.calendarid = 1
  ORDER BY dateline_from"); 


Nope, no such luck!
Fatal error: Call to a member function on a non-object in .../includes/cron/events.php on line 16
Line 16 is the SELECT statement.

OK, I know I'm clueless but can you help me get this working?
When do you use $vbulletin->$db->query_read versus $db->query_read?
Why do the fields names change (event.title versus title)?
How do you read data from a table?

I have spent DAYS going through all the keyword searches I can think of and nothing conclusive. When I get this thing working, I am going to post an "Idiots Guide to Reading vBulletin Databases".

Thnaks in advance for any help you can offer me!!!!!

[bairy]

It's $vbulletin->db->query_read (there's no $ on the db part)

[photongbic]

Thanks for the help...don't know how I missed that one :-(

Now I get the following:

SELECT event.calendarid, dateline_from, event.title, lv_vb_eventforums_threadid, customfields,
FROM event AS event
WHERE dateline_from >= 1156455695 AND event.calendarid = 1
ORDER BY dateline_from;

MySQL 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 'FROM event AS event
WHERE dateline_from >= 1156455695 AND eve
Error Number : 1064

I tried several different combinations but no luck.

The CSV file shows the field names as calendarid, dateline_from, title, lv_vb_eventforums_threadid and customfields. But changing the field names doesn't seem to help.

Thoughts?

Also, how do you know which field names to use?

[bairy]

I re-read the first post and it said you're trying to access the event and user tables. Which fields from which tables are you trying to access?

[photongbic]

Originally Posted by bairy

I re-read the first post and it said you're trying to access the event and user tables. Which fields from which tables are you trying to access?

I decided I was having enough problems with the event table that I might want to just work on it first. The nightmare of the user table has yet to begin.

SELECT event.calendarid, dateline_from, event.title, lv_vb_eventforums_threadid, customfields,
FROM event AS event
WHERE dateline_from >= 1156455695 AND event.calendarid = 1
ORDER BY dateline_from;

MySQL 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 'FROM event AS event
WHERE dateline_from >= 1156455695 AND eve
Error Number : 1064

Any thoughts on the above error message?

[bairy]

No idea, it looks fine and works in my phpmyadmin.
The reason I asked was because I'm not certain over the way the sql has been put together, which means I can't figure out why it's erroring. I know it can be picky about the smallest of details, so I was wondering what the entire query was to do so I could construct one for you.

[photongbic]

Originally Posted by bairy

No idea, it looks fine and works in my phpmyadmin.
The reason I asked was because I'm not certain over the way the sql has been put together, which means I can't figure out why it's erroring. I know it can be picky about the smallest of details, so I was wondering what the entire query was to do so I could construct one for you.

I want to read the event database (where the events are curent or in the future) from calendar "1". I am only interested in calendarid, dateline_from, title, lv_vb_eventforums_threadid, customfields. I guess I could use an "*" instead of specifying the fieldnames but doesn't that take more time and waste memory? Maybe I am just over thinking it!

Thanks!

[Code Monkey]

First of all, you are calling the table AS event.. Then you are only prefixing a few columns with event.. Get rid of the whole AS event bit as well and any prefixes used. That is not needed with a single table call and confuses things.

The error message is most likely refering to your last column call. Get rid of the final comma before FROM.

PHP Code:

 customfields, 


Is the a scheduled task cron or a server cron?

[bairy]

I noticed the unnecessary 'event's, I was gonna tackle them in any rewrite.

And yes, the error is likely from that comma. It's always the tiny symbols you don't notice that cause the most trouble eh.


In case it's not clear, this is the tidy rewrite of that code:

SELECT calendarid, dateline_from, title, lv_vb_eventforums_threadid, customfields
FROM event
WHERE dateline_from >= 1156455695 AND calendarid = 1
ORDER BY dateline_from

[photongbic]

You guys rock! Thanks, the fog is lifting.

Is the a scheduled task cron or a server cron?

It will be a scheduled task cron job that runs once a night at off hours to seperate the calendar events so we can display our membership and events by county. Hopefully making it easier for our members to find each other and the surrounding events.

Thanks again!