Urgent help plz

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Urgent help plz

Post by stebut05 »

Hi all,

Would really appreciate as much help as possible. I have two tables in a database to store data.

Table1 detail
Table 2 description

I need to display the results of detail table on a page and if the table has a decription as well that will also need to be displayed. I have managed to run the query, but if there is no records in the description table relationing to the detail table then the detail table will not display the results, confussed yet!!

eg:

do { (repeating region)

detail table. details name
detail table. notes
descriptiontable. description name
descriptiontable. description notes

} while etc etc


query as follows

Code: Select all

SELECT * FROM jobcard_index, jobcard_detail, jobcard_description WHERE jobcard_description.detail_id = jobcard_detail.detail_id AND jobcard_description.page_number = jobcard_detail.page_number
                            AND jobcard_detail.jobcard_id = jobcard_index.jobcard_id AND jobcard_index.jobcard_id = '$jobcard_id'";

Really hope someone can help, i really appreciate any help advice given

Kind Regards,

Steven
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

$query = "SELECT * 
FROM jobcard_index AS a, jobcard_detail AS b, jobcard_description AS c 
WHERE c.detail_id = b.detail_id 
AND c.page_number = b.page_number
AND b.jobcard_id = a.jobcard_id 
AND a.jobcard_id = '$jobcard_id'";
Just to make it a little bit easier to read :)

You should look into using Joins btw, speeds things up a bit :)

Code: Select all

$query = "SELECT *
FROM jobcard_index AS a
INNER JOIN jobcard_detail AS b
(INNER JOIN jobcard_description as c
ON b.page_number = c.page_number)
ON b.jobcard_id = a.jobcard_id
WHERE a.jobcard_id = '$jobcard_id'";
Untested, but should work, give it a go (manually to see if it collects the correct columns)

Then the while loop should look like:

Code: Select all

<?php

while ($row = mysql_fetch_assoc($result)) {
    //do stuff with $row array
}

?>
Assuming $result is the result set, and that your Database is a MySQL DB.

HTH :)
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

error

Post by stebut05 »

Hi,

I get the following error

You have an error in your SQL syntax near '(INNER JOIN jobcard_description as c ON b.page_number = c.page_number) INNER JO' at line 4

Any ideas???


Regards,



Steven
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

remove the brackets (both the open and close) I seem to remember MySQL not liking them.
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post by stebut05 »

Removed the brackets and get the following error.

You have an error in your SQL syntax near 'INNER JOIN jobcard_description as c ON b.page_number = c.page_number ON b.jobc' at line 4

any ideas??

Regards,

Steven
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Try:

Code: Select all

SELECT *
FROM jobcard_index AS a
INNER JOIN jobcard_detail AS b
ON a.jobcard_id = b.jobcard_id
INNER JOIN jobcard_description as c
ON b.page_number = c.page_number
WHERE a.jobcard_id = '$jobcard_id'
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post by stebut05 »

Hi,

Thanks for all your help, but i get the same results as with the orginal query. I want to show the results of the b and c table, but sometimes there are no values in the c table relating to b table. So when i run the query if there are no values in the c table then the b table will not show the results. does this make sense???

Regards,


Steven
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

change
INNER JOIN jobcard_description as c

to

LEFT JOIN jobcard_description as c
Post Reply