Multi-table Select Query using Joins
My question is a bit involved, so I apologise in advance! Any help with this would be greatly appreciated though.
I am working on a site at the moment in PHP and MySQL, which has the following table structures to contain the information on page items, related images, and timestamp history:
Table - tbItems
id (int) (pk)
Table - tbImages
id (int) (pk)
Table - lnItems_Images
(links tbItems to tbImages in a many-to-many relationship)
Table - tbHistory
id (int) (pk)
Table - tbUsers
What I currently have is a query string which, using joins, retrieves the necessary data from tbItems and tbImages. The way this site will operate is that each item (item = some kind of header or title, plus associated descriptive text) can have multiple images associated with it, while each single image could also be associated with multiple items.
I am then parsing this into a multi-dimensional associative array which is then passed from the controller object to the page where it is to be displayed (to minimise the coding I need to use on the displaying page).
The string I am using is below:
$queryString = "SELECT tbItems.id as item_id, tbItems.header as item_header, tbItems.fulltext as item_text, tbItems.idPage as page_id, tbImages.id as image_id, tbImages.location as image_location, tbImages.title as image_title, tbImages.description as image_description FROM tbItems LEFT OUTER JOIN lnItems_Images ON tbItems.id = lnItems_Images.idItem LEFT OUTER JOIN tbImages ON lnItems_Images.idImage = tbImages.id WHERE tbItems.idPage = '".$this->pageId."'";
After I finish dumping the output from the above query into the array (and before I return it), I am running another query to retrieve the data from the history table. The query is below:
$queryString = "SELECT tbHistory.id, tbHistory.idElement, tbHistory.elementType, tbHistory.timestamp, tbHistory.idUser, tbHistory.modType, tbUsers.id as user_id, tbUsers.firstname, tbUsers.lastname FROM tbHistory INNER JOIN tbUsers ON tbHistory.idUser = tbUsers.id";
This basically retrieves the timestamp info for each item. "elementType" identifies whether it is an image or a page item, as either of these can have a creation timestamp and a "last modification" timestamp. "modType" is either "c" for created, or "m" for "last modified".
After I do this query, I'm then looping through the original array, which has the item and image info, and slotting the timestamp info in the appropriate section. This is fine, except that I haven't been able to sort on timestamp via the SQL query, which I'd like to do as it's so much easier to let SQL do it for me. The alternative is to sort the array after it has been populated with the item and image info, but this seems overly complex to me.
My question is, is there a single select query I can use which would link the info I get from the first query, with the timestamp info I get from the second query, which would enable me to sort according to timestamp at the time I execute the SQL query? I'm ok at SQL with relatively simple queries, but this is making my head spin and I can work out how I would do it.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL