MySQL Join Question

jack.mitchell

jack.mitchell

jack.mitchell

Associate
Joined
15 Apr 2008
Posts
1,031
Location
West Didsbury, Manchester
Ok, joins are my pet hate. I cannot get my head round them no matter how much I read. I keep trying and one day I know they will click, but today isn't that day. So I need some help with this query.

What I have:
Code: 
 $query = "SELECT 
                  wiki_pageContents.content, 
                  wiki_pageTypeDefinitions.heading 
                FROM 
                  wiki_pageTypeDefinitions, 
                  wiki_pageContents  
                WHERE 
                  wikiPageTypeID = '".$data['wikiPageTypeID']."' AND 
                  wiki_pageContents.wikiPageTypeDefinitionID = wiki_pageTypeDefinitions.wikiPageTypeDefinitionID 
                ORDER BY 
                  headingOrder ASC";

What I think is my line of issue:

Code: 
                  wiki_pageContents.wikiPageTypeDefinitionID = wiki_pageTypeDefinitions.wikiPageTypeDefinitionID

wiki_pageTypeDefinitions will always have a definition of a wiki page which includes 'header' however it may not always have it's corresponding value from wiki_pageContent.content.

So, what I need is a join of some sort which will always return the header, and if it doesn't find the corresponding content then just return nothing. At the moment it works perfectly as long as the header has its corresponding content, otherwise it doesn't return the row at all, obviously because it evaluate the "==" to true.

If you need any more info or further explanation i can try explaining again. Thanks for reading I really am at my wits end with this!!!!
 
Right, I tried this and numerous other methods today and last night to no avail. I think I may have my schema wrong, if someone would kindly take a look and see if my architecture is leading me towards the edge of a cliff I would be very grateful!

What I currently have:

wiki_pageContents

_____________________________________________
| wikiPageTypeDefinitionID | wikiPageID | content |
----------------------------------------------------
| 1 | 1 | sometext1 |
| 2 | 1 | sometext2 |
-----------------------------------------------------

wiki_pages

___________________________
| wikiPageID | wikiPageTypeID |
------------------------------
| 1 | 1 |
| 2 | 1 |
--------------------------------

wiki_pageTypeDefinitions

____________________________________________________________________________________
| wikiPageTypeDefinitionID | wikiPageTypeID | headingOrder | heading | description | contentType |
------------------------------------------------------------------------------------------------
| 1 | 1 | 0 | body1 | body text1 | text |
| 2 | 1 | 1 | body2 | body text2 | text |
------------------------------------------------------------------------------------------------

wiki_pageTypes
________________________________________
| wikiPageTypeID | name | description |
----------------------------------------------
| 1 | generic | generic template |
----------------------------------------------


What I want is to be able to ask for page 1 and get the headings with associated content, and also ask for page 2 but only get the headings and a content listing of nothing as no content has yet been added.

I hope this makes sense! I even put the original design in a drawer, started from scatch and I came out with the same design again! exact.

EDIT: Also, is there a quick way to export tables in a decent readable format from phpmyadmin?
 
Ok, scrap that I've made some minor changes. I now have:

wiki_templateDefinitions

| wikiTemplateID | headingOrder | heading | description |

wiki_pageContent

| wikiPageID | wikiTemplateID | wikiTemplateDefinitionID | content |

This query:
Code: 
SELECT 
   wpc.content,
   wtd.heading
FROM 
   wiki_templateDefinitions wtd
LEFT OUTER JOIN
   wiki_pageContents wpc
ON
   wpc.wikiTemplateID = wtd.wikiTemplateID
WHERE
   wpc.wikiPageID = '2'
AND
   wtd.wikiTemplateID = '1'
ORDER BY
   headingOrder
ASC

Returns:

Code: 
content 	                                heading
---------------------------------------------------------
I am linked with heading 2 and wiki page 2	Heading 1
I am linked with heading 2 and wiki page 2	Heading 2

So, it so SO VERY NEARLY there BUT, there is no matching row for wiki page 2 heading 1, so that should be null, any final ideas?

May I add that is currently for a free open source project so I'm not trying to contract help for profit.
 
Last edited:
Sorry for not updaing on this guys, but I finally figured it out, this was the query I ended up with:

Code: 
SELECT 
                  wpc.content,
                  wtd.heading,
                  wtd.wikiTemplateDefinitionID
                FROM 
                  wiki_templateDefinitions wtd
                LEFT JOIN
                  wiki_pageContents wpc
                ON
                  wpc.wikiTemplateDefinitionID = wtd.wikiTemplateDefinitionID
                AND
                  wpc.wikiTemplateID = wtd.wikiTemplateID
                AND
                  wpc.wikiPageID = ".$id."
                WHERE
                  wtd.wikiTemplateID = ".$this->templateID."
                AND
                  (wpc.isCurrent = 1
                  OR
                  wpc.isCurrent IS NULL)
                ORDER BY
                  headingOrder
                ASC

Is there a tool show map out mysql schemas in a visual format, as it takes ages to drawn them out.
 
You must log in or register to reply here.
Back
Top Bottom