[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

PHP Query Syntax

I'm not sure how I write the following:
$sql = "SELECT workorder_id.workorders, shippers_full_name.workorders, origin_address.workorders, destination_address.workorders
		DATE_FORMAT(start_date, '%d').schedule AS day, workorder_id.schedule
		FROM workorders, schedule
		WHERE DATE_FORMAT(start_date, '%Y-%m') = '$year_month'.schedule AND workorder_id.workorders = workorder_id.schedule
		GROUP BY workorder_id.schedule DESC";

Open in new window


These two parts I'm not sure about:
1.       DATE_FORMAT(start_date, '%d').schedule AS day, workorder_id.schedule

2.      WHERE DATE_FORMAT(start_date, '%Y-%m') = '$year_month'.schedule
0
rgranlund
Asked:
rgranlund
  • 2
  • 2
1 Solution
 
Ray PaseurCommented:
The table name comes first, like this:

SELECT mytable.mycolumn FROM ...
0
 
Chris StanyonCommented:
You'll need something like this:

SELECT w.workorder_id, w.shippers_full_name, w.origin_address, w.destination_address, DATE_FORMAT(s.start_date, '%d') AS day, s.workorder_id
FROM workorders w, schedule s
WHERE DATE_FORMAT(s.start_date, '%Y-%m') = '$year_month' AND w.workorder_id = s.workorder_id
GROUP BY s.workorder_id DESC

Open in new window

I've aliased the tables as w and s for brevity. Your WHERE clause should be checking $year_month against s.start_date, not checking start_date against $year_month.s
0
 
rgranlundAuthor Commented:
Is the following correct cause I'm still getting an error and I can't seem to see what is wrong:
ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.DATE_FORMAT(start_date, '%d') AS day, schedule.workorder_id FROM workorders,' at line 2
$sql = "SELECT workorders.workorder_id, workorders.shippers_full_name, workorders.origin_address, workorders.destination_address
		schedule.DATE_FORMAT(start_date, '%d') AS day, schedule.workorder_id
		FROM workorders, schedule
		WHERE schedule.DATE_FORMAT(start_date, '%Y-%m') = '$year_month' AND workorders.workorder_id = schedule.workorder_id
		GROUP BY .schedule.workorder_id DESC";

Open in new window

0
 
Ray PaseurCommented:
0
 
Chris StanyonCommented:
Have a look through the query I posted. This line:

schedule.DATE_FORMAT(start_date, '%d') AS day

should be:

DATE_FORMAT(schedule.start_date, '%d') as day

This line:

schedule.DATE_FORMAT(start_date, '%Y-%m') = '$year_month'

should be:

DATE_FORMAT(schedule.start_date, '%Y-%m') = '$year_month'

and you have an error in your GROUP BY clause (extra period). It should be:

GROUP BY schedule.workorder_id
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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