Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Altering Query to include another call

Posted on 2013-01-27
6
Medium Priority
?
347 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

596 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