Solved

PHP Query Syntax

Posted on 2013-10-25
5
263 Views
Last Modified: 2013-10-25
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
Comment
Question by:rgranlund
  • 2
  • 2
5 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39600831
The table name comes first, like this:

SELECT mytable.mycolumn FROM ...
0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39600865
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39600883
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39600893
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39600912
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question