John_A

First - this is a cross-posting, if this is wrong I apologise! What started out as a MySQL query question (original post http://www.phpfreaks.com/forums/index.php/topic,273323.0.html) has become a PHP coding question.... I have 2 tables - docs and doc_relationships: - +-------------+--------------+---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 | +--------+--------------+------------+ Each row in the doc_relationships table ties one document to a product_id (product_ids are held in another table and will be hardcoded), 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 WHERE doctype='newsletter' AND visible='Y' ORDER BY added DESC, document_id DESC"; The 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) ----------------------------------------------------------------------------------------- The query I'm using for the product pages is as follows: - $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: - 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?

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: - 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?

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.