Solved

Altering Query to include another call

Posted on 2013-01-27
6
342 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 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
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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