?
Solved

Altering Query to include another call

Posted on 2013-01-27
6
Medium Priority
?
345 Views
Last Modified: 2013-02-16
Hi everyone,

I have a call on the DB using PHPfox variables. We want to alter this slightly in the WHERE clause so that it only selects events from the db with an id of 1,5 and 7.

Also we would like to display events that are 15 before the start time. (the end time is fine the way it is)

Could someone let me know the best way to do this?

Thanks,

D
0
Comment
Question by:TLN_CANADA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825188
It's helpful to post your existing query.  

In your WHERE clause you can use IN to test for the events, like

WHERE event IN (1, 5, 7)

As for displaying events "15 before the start time" first, 15 what -- seconds minutes hours days? And second what are we comparing the start time to (the current date/time?  Some as-of date?)  

You can do

WHERE start_date - INTERVAL 15 MINUTE >= Your_DateTime AND end_date <= Your_DateTime

or something along those lines, or use the DATE_ADD() function similarly like

WHERE DATE_ADD(start_date, INTERVAL 15 MINUTE) >= Your_DateTime
0
 

Author Comment

by:TLN_CANADA
ID: 38825192
I'm sorry kmslogic, I forgot to post it. Here is it :

			$aRows = $dbase->select('e.image_path, e.time_stamp, e.start_time, e.end_time, e.location, e.event_id, e.title, e.server_id, '.Phpfox::getUserField())
				->from(Phpfox::getT('event'),'e')
				->join(Phpfox::getT('user'),'u','u.user_id = e.user_id')
				->where('e.start_time <= '.(PHPFOX_TIME).' AND e.end_time >= '.(PHPFOX_TIME))
				->limit(4)				
				->order('e.time_stamp DESC')
				->execute('getRows');

Open in new window

0
 
LVL 16

Accepted Solution

by:
kmslogic earned 2000 total points
ID: 38825251
Try this (assuming start_time is a datetime field):

->where('e.start_time - INTERVAL 15 SECOND <= '.(PHPFOX_TIME).' AND e.end_time >= '.(PHPFOX_TIME).' AND e.event_id IN (1,5,7))'
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:TLN_CANADA
ID: 38825932
sorry kmslogic, I forgot to mention it is 15 minutes before the start time rather than 15 seconds.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38826425
so change the word SECOND to MINUTE

where('e.start_time - INTERVAL 15 MINUTE <= '.(PHPFOX_TIME).' AND e.end_time >= '.(PHPFOX_TIME).' AND e.event_id IN (1,5,7))'

Open in new window

0
 

Author Comment

by:TLN_CANADA
ID: 38845524
I'm sorry for the delay in getting back to you kmslogic. We are still testing this and waiting for another issue to be resolved before we can test it properly. I will get back to you as soon as possible.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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