multiple JOIN query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nagyagi81
Forum Newbie
Posts: 2
Joined: Sat May 03, 2008 3:44 pm

multiple JOIN query

Post by nagyagi81 »

Getting into database normalization and it's just one big headache.

I'm working on a query for an appointment page. It takes data from 5 different tables.

This is the table structure

Code: Select all

job            |job_employee |employee     |customer      |customer_address
---------------+-------------+-------------+--------------+---------------
job_id(PK)     |assign_id(PK)|emp_id(PK)   |cust_id(PK)   |address_id(PK)
customer_id(FK)|job_id(FK)   |emp_firstname|cust_firstname|customer_id(FK)
address_id (FK)|employee_id  |emp_lastname |cust_lastname |
job_date       |             |             |special       |
job_status     |             |             |              |
This is the php query that does not seem to work:

Code: Select all

$sql = "SELECT job.job_id, job_employee.assign_id, job.job_status, job.job_date, job.address_id,
customer_address.cust_id, customer_address.google_address,
customer.cust_firstname, customer.cust_lastname, customer.special FROM job
INNER JOIN job_employee ON (job.job_id = job_employee.job_id AND job_employee.employee_id = '$prEmployeeId[$i]')
INNER JOIN customer_address ON (job.address_id = customer_address.address_id)
INNER JOIN customer ON (customer_address.customer_id = customer.cust_id)
WHERE (job.job_date >= '$weekDateLow[$d]' && job.job_date <= '$weekDateHigh[$d]') AND (job.job_status = 'D' || job.job_status = 'S') ORDER BY job.job_date";
The query is supposed to return results for a date range ($weekDateLow[$d] to $weekDateHigh[$d]) and for one specific employee ($prEmployeeId[$i]).

Please note that more than one employee is assigned to each job. I'm only trying to retreive one employee's schedule even if he/she's assigned to do that job with someone else.

For some reason I'm gettin an error as follows:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple JOIN query

Post by califdon »

What that error means is that your query failed, therefore there is no valid result for it to count the number of rows. If your code had included (as it always should) an instruction to display the error message and halt upon failure, you would know exactly why. Check out http://www.quate.net/newsnet/read.php?10.

Now, there are several problems with your application. To begin with, you are evidently missing one table, namely: address. You have the linking table customer_address, but no address table to link to. The SQL you showed is joining to the linking table, which does absolutely nothing for you. If you don't need the address, you don't need the join or the linking table. If you do need the address, you don't have the table that contains the addresses.

Then, I suspect that your employee ID variable, $prEmployeeId[$i], may be a problem. First, is it an alpha or numeric ID? You have enclosed it in single quotes in your SQL, which is correct if it's alpha, but not if it's numeric. Second, does it contain a valid value? That's the first thing to check; echo that variable somewhere before the SQL statement. Third, I'm suspicious when I see it expressed as an array element. If you're just looking up one employee, why would it be in an indexed array?

You would be well served to study some techniques for debugging PHP and MySQL errors. Here's a good place to start: http://www.devshed.com/c/a/PHP/Error-Ha ... fensively/.
nagyagi81
Forum Newbie
Posts: 2
Joined: Sat May 03, 2008 3:44 pm

Re: multiple JOIN query

Post by nagyagi81 »

My bad. The table structure posted was incomplete. The actual table structure includes a lot more fields, but they're not relevant to my question so I was trying to keep it simple. However the customer_address is the address table and I forgot to post the google_url field here.

This is the table structure

Code: Select all

job            |job_employee |employee     |customer      |customer_address
---------------+-------------+-------------+--------------+---------------
job_id(PK)     |assign_id(PK)|emp_id(PK)   |cust_id(PK)   |address_id(PK)
customer_id(FK)|job_id(FK)   |emp_firstname|cust_firstname|customer_id(FK)
address_id (FK)|employee_id  |emp_lastname |cust_lastname |google_url
job_date       |             |             |special       |
job_status     |             |             |              |
The variables in use are fine. I checked them. The variable $prEmployeeId[$i] is an array of the employee_id's that are to be displayed(basically out of all employees in the db that has any appointments for the current week being displayed. and yes it's the PK value of employee_id). The query gets the data for one employee only for one specific date, but is placed inside two for statements: one going Monday thru Sunday, and the other for all the employees that have scheduled jobs for the week being displayed.

To give you a basic idea what it looks like:

Code: Select all

         |    Monday      |     Tuesday    |   Wednesday    |    Thursday    |  Friday  | Saturday |  Sunday
Employee |   05/01/08     |    05/02/08    |   05/03/08     |    05/04/08    | 05/05/08 | 05/06/08 | 05/07/08
---------+----------------+----------------+----------------+----------------+----------+----------+--------
Joe S.   |[color=#FF0000]09:00 XYZ client[/color]|09:00 SGY client|[color=#FF0000]09:00 HGT client[/color]|      OFF       |    OFF   |   OFF    |   OFF
---------+----------------+----------------+----------------+----------------+----------+----------+-----
Jim M.   |[color=#FF0000]09:00 XYZ client[/color]|10:00 HYR client|[color=#FF0000]09:00 HGT client[/color]|10:00 KJH client|    OFF   |   OFF    |   OFF
Normally employees have more than one jobs a day, but I kept it simple here. It's also possible that more than one employee is assigned to the same job. See appointments in red.
The job table does not include the employee assigned to the job. That's actually stored in the job_employee table with the job_id as a FK and each row with the same job_id would represent one employee that's assigned to the job. One, two or more...

My query is inside the 2 for statements pulling the data for each day for a specific employee, then the next day, then the next day, until you get to Sunday, when you start with the next employee...
- Pull all jobs for Joe on Monday
- Pull all jobs for Joe on Tuesday
- Pull all jobs for Joe on Wednesday
- Pull all jobs for Joe on Thursday
- Pull all jobs for Joe on Friday
- Pull all jobs for Joe on Saturday
- Pull all jobs for Joe on Sunday
then
- Pull all jobs for Jim on Monday
- Pull all jobs for Jim on Tueday
- Pull all jobs for Jim on Wednesday
- Pull all jobs for Jim on Thursday
- Pull all jobs for Jim on Friday
- Pull all jobs for Jim on Saturday
- Pull all jobs for Jim on Sunday

For displaying I need
- job.job_date to create the appointemnt time
- customer.special (long story... it's needed)
- customer.cust_id for a link
- customer.cust_firstname and lastname (duh, displaying the name)
- job.job_status for color coding the appointment for display based on the status
- customer_address.google_url is for a quick link to place customer's address on a google map

Hope this helps.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple JOIN query

Post by califdon »

Please re-read the first paragraph of my reply. Unless you can see the error message, there's no point in trying to debug the code.
Post Reply