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?
problem in program for counting no of chars using pointers
Hi all, I was trying to make a program which counts number of chars in a string using concpt of pointers. Following is the code:
Java API in PHP?
I have an application that we use internally here at the office.The software company provides a Java API.Is there a way to use this Java API with PHP?or am i stuck having to use tomcat and jsp to
PHP Session Issue
I'm having an issue with one section on my website dealing with sessions. Now I know that myself and two other individuals can get it to work without any issues on IE, FF, or other browsers, however,
How to make URL loads only on login
On my page, when a user login he's redirects to user_home.php. I made it also to open the chat screen when the user login.This is the code:Code: // USER IS LOGGED IN, FORWARD TO USER HOMEif(
Passing Arguments to execlp()
I'm writing a program that mimics a unix shell. It's supposed to take commands with arguments and execute them. I'm having trouble passing in the arguments into the execlp call to correctly execute
Quick variable question
hey guys/gals, im trying to write a php script and it works fine as i have it, but i need to set it to where it pulls the var from mysql table that it is displayed differeantly. i.e. if $var==abcdefg
2 decima places & How to reload my page
Hello There,How do i put full-stop (.) after second figure from behind? ie if i have 123456 in my database and i want it to echo as 1234.56Secondly, am designing a shopping website. I want the pages
Check something, wait, check again, do something!
Hi guys. I wonder if someone can help me with this.Basically, what I want to do is (for arguments sake I will use google.com as an example) check to see if google.com is alive. If its alive, I want it
Date help - fetch dates for Mondays between xxx and yyy?
Does anyone know the best way to do this? I have two dates, say:2010-01-26 and 2010-05-30and I want to return the dates of all the Mondays between them:2010-02-012010-02-082010-02-15....etc.Thanks
Undefined Variable: PHP_SELF, pls help
Hi,Im a newbie on PHP / MySQL programming and Im running a script to search one field on my DB table. The results will show after I key in my search word but there's an error below that says: