Avatar of TLN_CANADA
TLN_CANADA
Flag for Afghanistan asked on

MYSQL PHPFox Query

Hi everyone,

I'm trying to query the MYSQL database using the MYSQL PHPFox format. I would like to display the new records from this table in the past 24 hours.

Here is an example of a MYSQL query that is working that checks how many songs have been added in the past 24 hours:

$this->database()
->select('COUNT(*)')
->from(Phpfox::getT('music_song'))
->where('view_id = 0 AND time_stamp > '. (PHPFOX_TIME - 86400)) // less than 24 hours ago
->execute('getSlaveField'); 

Open in new window


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

Open in new window


Thanks for your help,

D
PHPMySQL Server

Avatar of undefined
Last Comment
TLN_CANADA

8/22/2022 - Mon
Pratima

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');
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');
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
TLN_CANADA

ASKER
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
Pratima

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

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TLN_CANADA

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
mcnute

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

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.
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TLN_CANADA

ASKER
Thank you,

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.

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

Open in new window

TLN_CANADA

ASKER
Thanks, I have this working now with your code above

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

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

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
TLN_CANADA

ASKER
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()

Open in new window


Thank you!

D
ASKER CERTIFIED SOLUTION
mcnute

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
TLN_CANADA

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

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.
mcnute

The above IS a standard mysql query.

time() returns an integer, see here.

Have you even tried my solution?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TLN_CANADA

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

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
TLN_CANADA

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

Open in new window

TLN_CANADA

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

Thanks,

D
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ray Paseur

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.