• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

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.
0
Donnie Walker
Asked:
Donnie Walker
  • 4
  • 4
1 Solution
 
brendonfeeleyCommented:
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
0
 
Donnie WalkerAuthor Commented:
did that and got the same error.
0
 
brendonfeeleyCommented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Donnie WalkerAuthor 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.
0
 
brendonfeeleyCommented:
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';
0
 
Donnie WalkerAuthor 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
0
 
brendonfeeleyCommented:
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';
0
 
Donnie WalkerAuthor Commented:
duh! That worked. Thanks again!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now