We are trying to pull my the exercise_table how many view records have been created in the last 24 hours. The table is called exercise_table , the auto-increment field is called exercise_id and the timestamp field is called exercise_timestamp
Here is what I've been trying but it's not working correctly:
$this->database()->select('COUNT(*)')->from(Phpfox::getT('exercise_table'))->where('exercise_timestamp > '. (PHPFOX_TIME - 86400)) // less than 24 hours ago->execute('getSlaveField');
SELECT * FROM orders WHERE `date` > timestampadd(hour, -24, now());
try this login in your query
$this->database()
->select('COUNT(*)')
->from(Phpfox::getT('exercise_table'))
->where('exercise_timestamp > '. timestampadd(hour, -24, now())) // less than 24 hours ago
->execute('getSlaveField');
TLN_CANADA
ASKER
Thank you,
It gives this error:
Fatal error: Call to undefined function timestampadd()
This is a pre-made social networking script so I guess we'll have to use their already made functions in order to make this work.
Pratima
try this
$this->database()
->select('COUNT(*)')
->from(Phpfox::getT('exercise_table'))
->where('exercise_timestamp > timestampadd(hour, -24, now())') // less than 24 hours ago
->execute('getSlaveField');
This works when I run the SQL directly on the DB. PHPFox is difficult to workaround at times.
Pratima
try this
select('COUNT(*)')
->from(Phpfox::getT('exercise_table'))
->where('exercise_timestamp > date_sub(now(), INTERVAL 1 day)') // less than 24 hours ago
->execute('getSlaveField');
TLN_CANADA
ASKER
Nope :( Gives the error call to undefined function.
you're saying it is coming up blank. Is there any code which is responsible to display the results. For now i can see only the query itself in your code. I'm new to phpfox, though.
Is this maybe the line of code which should display the results acutally?
Try your query in phpmyadmin, and see if it produces any results there (query debugging). If not. Somehow you're querying the wrong thing.
TLN_CANADA
ASKER
It works in PHPMyadmin so it's a syntax issue with PHPfox. I'm not sure what else to try with it to be honest. Let me know if you have any other ideas.
mcnute
If you add a error_reporting(E_ALL); on top of everything where your query is, you likely to get some error or warning information or at least a notice which can give you a hint. Maybe the error lies somewhere before doing the query. Check that too.
Otherwise give us the whole code where your query lies within, to get an idea, and paste in here within the code delimiters which you can bring up simply by clicking on 'code' on the toolbar of the textbox here.
When I try it with the error reporting on it gives:
Fatal error: Call to undefined function select()
mcnute
The phpfox api says something like this. In their code they are loading the database library prior to call select(). Which you should have done also by using $this->database...etc.
What I can think of, since php can't find the select function it could be that you havn't instantiated the phpfox class beforehand so it doesn't know of a library database and a function select within that class. Because the error occures even before your actual query. So it is not your query syntax which is broken, this error tells you it can't find the select function within that database library. So why is this? How do you tell the php documet that is must include the phpfox library? Is that happening in the document you're working?
That's why the entire code is important! Not just the snippet with the query.
I used an iframe on the page and am now able to use standard PHP code to query the database.
Here is one I am having an issue with though and am wondering how to turn it into standard PHP code:
$this->database()->select('COUNT(*)')->from(Phpfox::getT('user'))->where('joined > '. (PHPFOX_TIME - 86400)) // Joined less than 24 hours ago->execute('getSlaveField');
It's an int, an example of the time in this field is 1353726174. I have showed how PHPfox accesses this information here :
$this->database()->select('COUNT(*)')->from(Phpfox::getT('user'))->where('joined > '. (PHPFOX_TIME - 86400)) // Joined less than 24 hours ago->execute('getSlaveField');
What is the added value that makes PHPFox worth the added trouble? We would almost certainly be able to get things going with "plain vanilla" SQL statements through MySQLi or PDO. Any hope for doing things that way?
TLN_CANADA
ASKER
I know Ray, it sure can be a real pain working with it. Normally I just iframe on their pages and this serves as a workaround but in this case it's a db issue. Leave it with me for another day and I'll see what else I can do with this. Thanks for responding.
TLN_CANADA
ASKER
Thanks everyone for your help, I've add a regular timestamp field to the DB so I hopefully will be able to work around this in the future too.
SELECT * FROM orders WHERE `date` > timestampadd(hour, -24, now());
try this login in your query
$this->database()
->select('COUNT(*)')
->from(Phpfox::getT('exerc
->where('exercise_timestam
->execute('getSlaveField')