Solved

Altering Query to include another call

Posted on 2013-01-27
6
337 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
  • 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 500 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

896 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

14 Experts available now in Live!

Get 1:1 Help Now