MYSQL PHPFox Query

this is simple query

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');

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.

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');

It's not giving any error but the stat is coming up blank. I created 2 test entries in the DB so there should be at least 2 for this.

Thanks,

D

try this commad directly on database
SELECT * FROM exercise_table WHERE exercise_timestamp  > timestampadd(hour, -24, now());

Here are the records in the DB:


88
Derek
0
0:5
2013-01-02 00:33:01


89
Derek
0
0:2
2013-01-02 02:27:41

This works when I run the SQL directly on the DB. PHPFox is difficult to workaround at times.

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');

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?

->execute('getSlaveField'); 

Select all Open in new window

Try your query in phpmyadmin, and see if it produces any results there (query debugging). If not. Somehow you're querying the wrong thing.

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.

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.

Thank you,

When I try it with the error reporting on it gives:

Fatal error: Call to undefined function select()

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.

$aRows = Phpfox::getLib('database')->select('*')
         ->from('user')
         ->where('user_name = \'foo\'')
         ->execute('getRows');

Select all Open in new window

Thanks, I have this working now with your code above

SELECT * FROM exercise_table WHERE exercise_timestamp  > timestampadd(hour, -24, now());

Select all Open in new window

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'); 

Select all Open in new window

This checks for the members who have joined today. The joined field is an int .

How could I convert this to query the database for the members joined today?

Thanks,

D

If I want to change this query so that I can get the amount of records in the last week and also in the last month, how do I change this?

SELECT * FROM exercising_table WHERE exercise_timestamp  > timestampadd(hour, -24, now()

Select all Open in new window

Thank you!

D

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'); 

Select all Open in new window

If someone could advise me on how to access this information using a standard SQL query (not a PHPfox function) that would be great.

The above IS a standard mysql query.

time() returns an integer, see here.

Have you even tried my solution?

Thank you, I've tested it now but it's coming up blank :

	$query6 = mysql_query("SELECT * FROM phpfox_user WHERE ('joined > time() - 86400'); ") ;
	$new_members_today = mysql_num_rows($query6);

Select all Open in new window

For testing purposes, how could I change it so it shows all of the users joined in the last month?

Thanks,

D

I tried runnng the SQL directly in the database but it's coming up blank :

For example I tried it with a longer time but it does not return any results:

SELECT * FROM phpfox_user WHERE ('joined > time() - 40000000');

Select all Open in new window

Hey everyone, can anyone help me with this as it is still not resolved?

Thanks,

D

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?

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.

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.