Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3858
  • Last Modified:

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
0
TLN_CANADA
Asked:
TLN_CANADA
  • 15
  • 5
  • 4
  • +1
1 Solution
 
Pratima PharandeCommented:
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
 
TLN_CANADAAuthor Commented:
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
 
Pratima PharandeCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
TLN_CANADAAuthor Commented:
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
 
Pratima PharandeCommented:
try this commad directly on database
SELECT * FROM exercise_table WHERE exercise_timestamp  > timestampadd(hour, -24, now());
0
 
TLN_CANADAAuthor Commented:
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
 
TLN_CANADAAuthor Commented:
This works when I run the SQL directly on the DB. PHPFox is difficult to workaround at times.
0
 
Pratima PharandeCommented:
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
 
TLN_CANADAAuthor Commented:
Nope :( Gives the error call to undefined function.
0
 
mcnuteCommented:
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
 
TLN_CANADAAuthor Commented:
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
 
mcnuteCommented:
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
 
TLN_CANADAAuthor Commented:
Thank you,

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

Fatal error: Call to undefined function select()
0
 
mcnuteCommented:
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
 
TLN_CANADAAuthor Commented:
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
 
TLN_CANADAAuthor Commented:
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
 
mcnuteCommented:
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
 
TLN_CANADAAuthor Commented:
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
 
mcnuteCommented:
The above IS a standard mysql query.

time() returns an integer, see here.

Have you even tried my solution?
0
 
TLN_CANADAAuthor Commented:
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
 
TLN_CANADAAuthor Commented:
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
 
TLN_CANADAAuthor Commented:
Hey everyone, can anyone help me with this as it is still not resolved?

Thanks,

D
0
 
Ray PaseurCommented:
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
 
TLN_CANADAAuthor Commented:
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
 
TLN_CANADAAuthor Commented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 15
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now