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
Who is Participating?
Chris StanyonConnect With a Mentor Commented:
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
Ray PaseurCommented:
The table name comes first, like this:

SELECT mytable.mycolumn FROM ...
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

Ray PaseurCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.