Solved

PHP Query Syntax

Posted on 2013-10-25
5
261 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 108

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 108

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now