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?
read integers that returns a negative or prints the average
Im trying to finish this homework assignment by tommarow afternoon and I keep getting stuck.
Saving PHP output as a file
I wondered if anyone knows how to save the rendered output of a PHP script as a file?I have a script that creates dynamic PDF documents, but want to save it as a file, rather than render it to the
Production Order Enterprise Service to Manufacturing Exctn Sys (MES) ???
Hello Experts,
Object Interfaces
EDIT: Never mind, I just updated to php 5.Hey all,I'm currently experimenting with php object interfaces. However, whenever I try to implement one, I get a php error.interface iTemplate{ public
weeks in a year
hi,i found this snippet on php.netQuoteFor the week number for weeks starting on Sunday:<?phpfunction week_of_year($month, $day, $year) {$day_of_year = date('z', mktime(0, 0, 0, $month, $day,
php problem?
I don't think I'm stupid, but maybe I am.I am working on a sit for a friend I am try to put a link on a page and it is not working in FireFox and Safari. It works in IE but nothing else.I can get it
Pulling out some result data from MYSQL
Hey Guys, I've built a PHP page that has a for just imagine something like registration form, So obviously it posts data into my table in my DB, So Now what I’m trying to do is creating a form
regex for quoted text within a string
I am still getting the hang of regex expressions, but I cant seem to figure out how I would escape quoted text within a string. The qualitifcations for escaping them would be to have [a space] [a
gather checkbox data from form into email
Hello all,First, i'd like to thank everyone who responded to my previous posts regarding setting up an SMTP server for my php form. I ended up figuring it out and my form is up and functioning,
do while conditions
i want to display 6 images in a 2x3 tablenow what would be the correct logic tocount that there are 6 images resultsand if there are more than 6 images loop and echo the rest but not in the tablebut