[SOLVED] PHP MySQL Query Help Needed

[John_A]

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.

[fenway]

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?

[rvdb86]

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!

[fenway]

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.

[John_A]

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?

[fenway]

1,2 and 3 all sound like php questions.

[John_A]

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!