Solved

MYSQL PHPFox Query

Posted on 2013-01-02
26
3,350 Views
Last Modified: 2013-01-26
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
0
Comment
Question by:TLN_CANADA
  • 15
  • 5
  • 4
  • +1
26 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38735883
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');
0
 

Author Comment

by:TLN_CANADA
ID: 38735911
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.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38735959
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');
0
 

Author Comment

by:TLN_CANADA
ID: 38736081
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
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38736087
try this commad directly on database
SELECT * FROM exercise_table WHERE exercise_timestamp  > timestampadd(hour, -24, now());
0
 

Author Comment

by:TLN_CANADA
ID: 38736088
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
0
 

Author Comment

by:TLN_CANADA
ID: 38736100
This works when I run the SQL directly on the DB. PHPFox is difficult to workaround at times.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38736178
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');
0
 

Author Comment

by:TLN_CANADA
ID: 38737697
Nope :( Gives the error call to undefined function.
0
 
LVL 11

Expert Comment

by:mcnute
ID: 38739529
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38742254
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.
0
 
LVL 11

Expert Comment

by:mcnute
ID: 38742296
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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:TLN_CANADA
ID: 38742753
Thank you,

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

Fatal error: Call to undefined function select()
0
 
LVL 11

Expert Comment

by:mcnute
ID: 38743155
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

0
 

Author Comment

by:TLN_CANADA
ID: 38776997
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
0
 

Author Comment

by:TLN_CANADA
ID: 38777002
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
0
 
LVL 11

Accepted Solution

by:
mcnute earned 500 total points
ID: 38777693
I'm not totally sure, but I think phpfox_time is a current timestamp. So you would do something like this.

SELECT COUNT(*) FROM user WHERE ('joined > time() - 86400');

Open in new window

0
 

Author Comment

by:TLN_CANADA
ID: 38780594
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.
0
 
LVL 11

Expert Comment

by:mcnute
ID: 38780617
The above IS a standard mysql query.

time() returns an integer, see here.

Have you even tried my solution?
0
 

Author Comment

by:TLN_CANADA
ID: 38780685
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
0
 

Author Comment

by:TLN_CANADA
ID: 38780700
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

0
 

Author Comment

by:TLN_CANADA
ID: 38799695
Hey everyone, can anyone help me with this as it is still not resolved?

Thanks,

D
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38812521
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?
0
 

Author Comment

by:TLN_CANADA
ID: 38816309
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.
0
 

Author Comment

by:TLN_CANADA
ID: 38823175
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now