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: -
Code: $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: -
Code:
$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: -
Code: // 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: -
QuoteArray ( => 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: -
Code: 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?
post to self
Trying to get my form to submit to itself. However I am also trying to use an image for my submit button, when I set the type='image' it will not submit to itself. Can someone help me figure out
Data storage spaces in varchar2
Trying to understand what's happening.
Code doesn't print what i wanted it too. Please help!
Hi there,I wrote this code to mae it so that in the form before it that the user said their username and then on this page it matches up the username with the username on the database and then uses
elseif help!
OK so I'm new to the site, as well as PHP altogether (Trying my hand at managing an online RPG) and so far it's been quite smooth. However whilst trying to change an aspect of the game from giving the
Select Lists into MYSQL
Hello All, I am new to PHP @ 1 week. So borrowing code anywhere I can. I am making progress, but hit a snag. I have 2 list boxes, that I use to move items left to right. When I click submit I want
Save remote XML to local website folder
Hi, I hope you can help me out. I would like to be able to pull an XML file from another site and then store it in a local folder on my site. That way, I would only need to pull the xml file once in
Is there a function ... auto send IP
Is there some function that can be sent to a ddns service that returns the ip address of a ddns forward. I am working on a script that uses a dvr from a home and the owner wants to have the dvr stream
Disable Scrollbar in TableControl
Hello everbody,
Help with some dates
I have a list of dates in an array:$mondays = array( strtotime("October 12, 2009"), strtotime("October 19, 2009"), strtotime("October 26,
Using Microsoft Exchange Server with PHP
Hi,I wanted to know if its possible to fetch email attachments from the exchange server using php.I know its possible to read message using the source I found in an article here