asked on

How do I get the next/top 5 events based on a date range?

I have a database of events for the next 12 months.

How do I query the top 5 events based on today's date for the next 6 months?

What is the syntax for the today's date and six months from now?

I've tried this:

$query = 'SELECT TOP 5 date
                        ,startTime
                        ,endTime
                        ,title
                        ,details
                        ,room
                  FROM
                        WWW.dbo.Events
WHERE date BETWEEN (today) and (six months from today)
                   ORDER BY date DESC';

brendonfeeley

Current date: GETDATE()

6 months from now: DATEADD(month, 6, GETDATE())

Donnie Walker

ASKER

when I do that it says:

Notice: Array to string conversion

how would I use the dates in the query above?

Donnie Walker

ASKER

ok, I can get the dates I need in the proper format this way:

$today = date("Y-m-d");
echo $today;

$sixmonths = mktime(0,0,0,date("m"),date("d")+180,date("Y"));
echo "<br>Six Months is ".date("Y-m-d", $sixmonths);

but when I add them to my query they say they are not columns?

ASKER CERTIFIED SOLUTION

brendonfeeley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Donnie Walker

ASKER

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '$today'.' in C:\inetpub\wwwroot\db2.php:72 Stack trace: #0 C:\inetpub\wwwroot\db2.php(72): PDO->query('SELECT TOP 5 e....') #1 {main} thrown in C:\inetpub\wwwroot\db2.php on line 72

brendonfeeley

Doing it that way:

$query = "SELECT TOP 5 date, startTime, endTime, title, details, room
FROM WWW.dbo.Events
WHERE date BETWEEN '" . $today . "' and '" . $sixmonths . "'
ORDER BY date DESC;"