John_A Posted October 18, 2009 Share Posted October 18, 2009 I need some help cross-referencing tables within a MySQL query in PHP. I currently have a database with 3 tables: - ------------------------------Table: docs-------------------------------------- | document_id | location | title | thumbnail | added |visible | ------------------------------------------------------------------------------- | 1 | pdf/a.pdf | a | img/a.jpg | 2001-04-12 20:16:58 | Y | | 2 | pdf/b.pdf | b | img/b.jpg | 2002-04-09 20:18:33 | Y | | 3 | pdf/c.pdf | c | img/c.jpg | 2003-05-21 11:18:54 | Y | | 4 | pdf/x.pdf | x | img/x.jpg | 2004-01-22 19:13:21 | Y | | 5 | pdf/y.pdf | y | img/y.jpg | 2005-02-17 17:11:17 | Y | | 6 | pdf/z.pdf | z | img/z.jpg | 2006-07-02 12:15:58 | Y | ------------------------------------------------------------------------------- --------Table: doctypes-------- | doctype_id | doctype_name | ------------------------------- | 1 | newsletter | | 2 | pressrelease | | 3 | instructions | ------------------------------- -----------Table: doc_relationships---------------- | rel_id | document_id | doctype_id | product_id | --------------------------------------------------- | 1 | 1 | 3 | 0 | | 2 | 1 | 0 | 4 | | 3 | 2 | 2 | 0 | | 4 | 2 | 0 | 3 | | 5 | 3 | 1 | 0 | | 6 | 4 | 3 | 0 | | 7 | 4 | 0 | 3 | | 8 | 5 | 2 | 3 | | 9 | 6 | 3 | 0 | | 9 | 6 | 0 | 2 | | 10 | 7 | 2 | 0 | | 11 | 7 | 0 | 3 | --------------------------------------------------- product_ids are held in another table and will be hardcoded for this purpose. Each row in the doc_relationships table ties one document to either a doctype_id or product_id, the above example gives: - doc 1: instructions, product 4 doc 2: pressrelease, product 3 doc 3: newsletter doc 4: instructions, product 3 doc 5: pressrelease doc 6: instructions, product 2 doc 7: pressrelease, product 3 Each product can have any number of Instructions, Press Releases and Newsletters related to it. Press Releases and Newsletters can be related to one or more product. Instruction manuals are usually related to one product only. So far I have three pages - newsletters, pressreleases and instructions, which simply list all the matching documents, using this query: - $query="SELECT * FROM docs, doc_relationships, doctypes WHERE doc_relationships.document_id=docs.document_id AND doc_relationships.doctype_id=doctypes.doctype_id AND visible='Y' AND doctype_name='newsletter' ORDER BY docs.added DESC, docs.document_id DESC"; This example returns all (in this case) newsletters in reverse chronoligical order. This works fine (although if there's a better way I'd be happy to change it) - my problem is I also want to list any relevant documents on product pages, under headings referrring to doc types, so for example on product 3's page: - ----------------------------------------------------------------------------------------- Product #3 - Super Widget Desription - this is a remarkable widget, you'll wonder how you ever managed without one! Instructions: (link to doc 4) Press Releases: - (link to doc 2) (link to doc 7) ----------------------------------------------------------------------------------------- So, I want a similar query to the first for each document type relevant to the product, i.e. I want to filter results further the by product_ID (product_ID will be hardcoded, as the doctype_name in my intital query is). I hope I've explained clearly enough! Any ideas? PHP 5.2.10 and MySQL 5.0.81. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 18, 2009 Share Posted October 18, 2009 Well, if you're trying to do this all in one query, you'll get stuck. Why not simply issue a simple one for each section, and then have PHP do the layout work? Quote Link to comment Share on other sites More sharing options...
rvdb86 Posted October 18, 2009 Share Posted October 18, 2009 Check this out: http://www.phpfreaks.com/forums/index.php/topic,270910.0.html Keith has been extremely helpful in explaining how to do complicated relationships between tables. I hope this helps.. it did for me! Quote Link to comment Share on other sites More sharing options...
fenway Posted October 18, 2009 Share Posted October 18, 2009 Yes, you could *try* and get them all back with a three-table join, but then I'd imagine that you'll likely have the usual layout questions & problems when iterating through a recordset that represents multiple entities. Quote Link to comment Share on other sites More sharing options...
John_A Posted October 19, 2009 Author Share Posted October 19, 2009 Thanks for the replies! It dawned on me that each document can only be one document type (newsletter, pressrelease or instructions) and so I added the doctype as a column in the documents table, so now have: - +-------------+--------------+---Table: docs-----+-----------+---------------------+---------+ | document_id | doctype | location | title | thumbnail | added | visible | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ | 1 | instructions | pdf/a.pdf | a | img/a.jpg | 2001-04-12 20:16:58 | Y | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ | 2 | pressrelease | pdf/b.pdf | b | img/b.jpg | 2002-04-09 20:18:33 | Y | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ | 3 | newsletter | pdf/c.pdf | c | img/c.jpg | 2003-05-21 11:18:54 | Y | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ | 4 | instructions | pdf/x.pdf | x | img/x.jpg | 2004-01-22 19:13:21 | Y | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ | 5 | pressrelease | pdf/y.pdf | y | img/y.jpg | 2005-02-17 17:11:17 | Y | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ | 6 | instructions | pdf/z.pdf | z | img/z.jpg | 2006-07-02 12:15:58 | Y | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ | 7 | pressrelease | pdf/p.pdf | p | img/p.jpg | 2002-03-01 18:31:26 | Y | +-------------+--------------+-----------+-------+-----------+---------------------+---------+ +-------Table: doc_relationships-----+ | rel_id | document_id | product_id | +--------+--------------+------------+ | 1 | 1 | 4 | +--------+--------------+------------+ | 2 | 2 | 3 | +--------+--------------+------------+ | 3 | 4 | 3 | +--------+--------------+------------+ | 4 | 6 | 2 | +--------+--------------+------------+ | 5 | 7 | 3 | +--------+--------------+------------+ Now the query for the three "list all of a certain doctype" pages is much easier, like: - $query="SELECT * FROM docs WHERE doctype='newsletter' AND visible='Y' ORDER BY added DESC, document_id DESC"; And the query for the product pages is a modified version of the oen from my first post: - $query="SELECT * FROM docs, doc_relationships WHERE doc_relationships.document_id=docs.document_id AND doc_relationships.product_id='3' AND visible='Y' ORDER BY docs.added DESC, docs.document_id DESC"; I could add another condition to this query for "doctype" and do it 3 times, once for each doctype, but ideally what I'd like to do on a product page is create 3 arrays (newsletters, pressreleases & instructions) containing the details of each for that product (if any). So here's what I have so far: - // connect to database and issue the query mysql_connect($server,$dbusername,$dbpassword); @mysql_select_db($db_name) or die( "Unable to select database"); $query="SELECT * FROM docs, doc_relationships WHERE doc_relationships.document_id=docs.document_id AND doc_relationships.product_id='3' AND visible='Y' ORDER BY docs.added DESC, docs.document_id DESC"; $all_documents=mysql_query($query); $total_docs=mysql_num_rows($all_documents); mysql_close(); if ($total_docs != 0) { while ($sql = mysql_fetch_object($all_documents)) { if ($sql -> doctype=='newsletter'){ echo 'document #' . $sql -> document_id . ' is a newsletter<br />'; // create the newsletters array if it doesnt exist if (!is_array($newsletters)) $newsletters= array(); // add to newsletters array; array_push($newsletters, $sql); } else if ($sql -> doctype=='pressrelease'){ echo 'document #' . $sql -> document_id . ' is a pressrelease<br />'; // create the pressreleases array if it doesnt exist if (!is_array($pressreleases)) $pressreleases= array(); // add to pressreleases array; array_push($pressreleases, $sql); } else if ($sql -> doctype=='instructions'){ echo 'document #' . $sql -> document_id . ' is instructions<br />'; // create the instructions array if it doesnt exist if (!is_array($instructions)) $instructions array(); // add to instructions array; // not quite sure how! array_push($instructions, $sql); } } } print_r($instructions); As you can see, I added some test echos and this works as expected. The result of the final print_r($instructions) command is: - Array ( [0] => stdClass Object ( [document_id] => 4 [doctype] => instructions [location] => pdf/x.pdf [title] => x [thumbnail] => img/x.jpg [added] => 2004-01-22 19:13:21 [visible] => Y [rel_id] => 3 [product_id] => 3 ) ) The problem is I can't seem to extract the data from this array individually, for example I thought: - echo $instructions[0]['document_id']; would echo '4' but it doesn't (and actually stops the page output dead). So: - 1) Am I inserting results into the arrays correctly? 2) How do I access individual document info from each array? 3) How do I (for each array) loop through all documents? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 29, 2009 Share Posted October 29, 2009 1,2 and 3 all sound like php questions. Quote Link to comment Share on other sites More sharing options...
John_A Posted October 29, 2009 Author Share Posted October 29, 2009 1,2 and 3 all sound like php questions. You are of course correct! Shortly after my last post here I realised that what had started out as a MySQL question had turned into a PHP one, so I cross-posted at http://www.phpfreaks.com/forums/index.php/topic,273536.msg1292207.html. I marked that thread as solved, and hadn't done this one - sorry! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.