Solved

PHP Query Syntax

Posted on 2013-10-25
5
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 110

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 110

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

705 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