Why isn't this query working?

Donnie Walker
Donnie Walker used Ask the Experts™
on
I am converting a site from ColdFusion to PHP.

When I run this query:

$query = 'SELECT TOP e.date
                        ,e.startTime
                        ,e.endTime
                        ,e.title
                        ,e.details
                        ,l.name
                        ,e.room
                  FROM
                        WWW.dbo.Events e
                        LEFT OUTER JOIN WWW.dbo.Locations l
                              ON e.locationId = l.locationId
                  WHERE
                        WHERE e.date BETWEEN GETDATE() and DATEADD(month, 6, GETDATE())
                  ORDER BY
ORDER BY date ASC';

I get this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'e'.' in C:\inetpub\wwwroot\db2.php:67 Stack trace: #0 C:\inetpub\wwwroot\db2.php(67): PDO->query('SELECT TOP e.da...') #1 {main} thrown in C:\inetpub\wwwroot\db2.php on line 67

Any help would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to use AS to alias your tables.

                  FROM
                        WWW.dbo.Events e
                        LEFT OUTER JOIN WWW.dbo.Locations l

Should be:

                  FROM
                        WWW.dbo.Events AS e
                        LEFT OUTER JOIN WWW.dbo.Locations AS l

Author

Commented:
did that and got the same error.
Try this:

$query = 'SELECT TOP e.date
                        ,e.startTime
                        ,e.endTime
                        ,e.title
                        ,e.details
                        ,l.name
                        ,e.room
                  FROM
                        WWW.dbo.Events AS e
                        LEFT OUTER JOIN WWW.dbo.Locations AS l
                              ON e.locationId = l.locationId
                  WHERE
                        WHERE e.date BETWEEN GETDATE() and DATEADD(month, 6, GETDATE())
                  ORDER BY
ORDER BY e.date ASC';

What does that return?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
same error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'e'.' in C:\inetpub\wwwroot\db2.php:67 Stack trace: #0 C:\inetpub\wwwroot\db2.php(67): PDO->query('SELECT TOP e.da...') #1 {main} thrown in C:\inetpub\wwwroot\db2.php on line 67

if I do this:

$query = 'SELECT TOP 5 date ,startTime ,endTime ,title ,details ,room
FROM WWW.dbo.Events
WHERE date BETWEEN GETDATE() and DATEADD(month, 6, GETDATE())
ORDER BY date ASC';

without joining the second table I get results.

But I need the data from the second table as well.

I'm using "sqlsrv" and PDO if that makes a difference.
Duplicate WHERE and ORDER BY statements...

What about this?

$query = 'SELECT TOP e.date
                        ,e.startTime
                        ,e.endTime
                        ,e.title
                        ,e.details
                        ,l.name
                        ,e.room
                  FROM
                        WWW.dbo.Events AS e
                        LEFT OUTER JOIN WWW.dbo.Locations AS l
                              ON e.locationId = l.locationId
                  WHERE e.date BETWEEN GETDATE() and DATEADD(month, 6, GETDATE())
                  ORDER BY e.date ASC';

Author

Commented:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'e'.' in C:\inetpub\wwwroot\db2.php:73 Stack trace: #0 C:\inetpub\wwwroot\db2.php(73): PDO->query('SELECT TOP e.da...') #1 {main} thrown in C:\inetpub\wwwroot\db2.php on line 73
No numeric value after the TOP statement. Try this:

$query = 'SELECT TOP 5 e.date, e.startTime, e.endTime, e.title, e.details, l.name, e.room
FROM WWW.dbo.Events AS e
LEFT OUTER JOIN WWW.dbo.Locations AS l
ON e.locationId = l.locationId
WHERE e.date BETWEEN GETDATE() and DATEADD(month, 6, GETDATE())
ORDER BY e.date ASC';

Author

Commented:
duh! That worked. Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial