Solved

Altering Query to include another call

Posted on 2013-01-27
6
341 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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