Avatar of Donnie Walker
Donnie Walker
Flag for United States of America asked on

Why isn't this query working?

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.
PHP

Avatar of undefined
Last Comment
Donnie Walker

8/22/2022 - Mon
brendonfeeley

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
Donnie Walker

ASKER
did that and got the same error.
brendonfeeley

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Donnie Walker

ASKER
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.
brendonfeeley

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';
Donnie Walker

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
brendonfeeley

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Donnie Walker

ASKER
duh! That worked. Thanks again!