Dynamic Join

Brusca

Hi Guys,
I am attempting to dynamically join two tables.

Table one: Catalogue
Table Two: Products

Each table has a column called "catName" so I can make the join accordingly. "SELECT products.catName, catalogue.catName where products.catName = catalogue.catName;

What I thought would be easy is turning out to be quite difficult.

I want to dispaly all products which reside under each catName.
There are 4 catNames which describe where a product fits in the catalogue. Instead of creating 4 select querys for each catName I want to create a looping code so it is easy to add new catNames on the fly.

so for example, to display this without using 4 select queries. I have tried coding a loop but just can't get it working.

CatName1
Product1 in This Category
Product2 in This Category
Product3 in This Category

CatName2
Product1 in This Category
Product2 in This Category
Product3 in This Category

CatName3
Product1 in This Category
Product2 in This Category
Product3 in This Category

CatName4
Product1 in This Category
Product2 in This Category
Product3 in This Category

//while (catalogue.catName == products.catName){
//display these products
//}

Just not sure...any hints would be great.

Cheers

bad76

Hi,
try with "order" sql statement:

" SELECT P., C.
FROM products P, catalogue C
WHERE P.catName = C.catName
ORDER BY P.catName "

This will join two tables and sort all product by catName.
Just fetch result to display exactly:

CatName1 - Product1 in This Category
CatName1 - Product2 in This Category
CatName1 - Product3 in This Category
CatName2 - Product1 in This Category
CatName2 - Product2 in This Category
....
....

see you

Brusca

Cheers...I will give that a Try.

toma42

The root of your problem is your database design. Products should be joined to cataloges through a primary and foreign key relationship. Each column should be an integer.

Catalogue

Catalogue_key int unsigned not null primary key auto_increment,
Name char(20)

Products

Product_key int unsigned not null primary key auto_increment,
Name char(20),
ref_catalogue int unsigned not null

Then:
Select Catalougue.name as cname, products.name as pname from products, catalogue where ref_catalogue = catalogue_key;

You could then add an 'order by catalogue.name' to it too.

Make sense?

Brusca

I see. So what you are saying is instead of using a word/name to join the tables I should use an integer.

catalogue_key = ref_catalogue.

Makes perfect sense and much appreciated.

What I am most having trouble with is the output. What sort of a loop can I then use to display the Catalogue_Name and then all products which reference this Catalogue underneath it.

For Example.

Catalogue_Name1
Product1 in This Catalogue
Product2 in This Catalogue
Product3 in This Catalogue

And then once it has finished with this Catalogue_Name it then loops through to the next one.

Catalogue_Name2
Product1 in This Catalogue
Product2 in This Catalogue
Product3 in This Catalogue

Cheers

toma42

Ok, order by catalog name and product name then, using pear (http://pear.php.net) syntax:

$rows = $db->getAll($sql);
$last_group = '';
foreach ($rows as $r) {
  if ($r.cname != $last_group) {
    echo "$r.cname<br>"
  }
   echo "$r.pname<br>";
   $last_group = $r.cname;
}

Brusca

Thats interesting. I am returning an error on that.

"Call to a member function on a non-object"

the error is in this line:

$rows = $db->getAll($sql);

I am unsure of pear syntax.

Brusca

OK I have done a bit of a work around (with the help of dreamweaver). I understood the method but just could not get it to work so I have this which actually works. No fancy alias names just wanted to get it working first.

<?php
//database query and select
mysql_select_db($database_maliaaus, $maliaaus);
$query_sqlquery = "SELECT * FROM catalogue left join products on catalogue.catalogue_key = products.ref_cat";
$sqlquery = mysql_query($query_sqlquery, $maliaaus) or die(mysql_error());
$row_sqlquery = mysql_fetch_assoc($sqlquery);
$totalRows_sqlquery = mysql_num_rows($sqlquery);
?>

<?php
//loop to display catelogue names and joined product names
do {
$last_group = '';
foreach ($row_sqlquery as $r) {
if ($row_sqlquery['catName'] != $last_group) {
echo $row_sqlquery['catName'];
echo "<br>";
}
echo $row_sqlquery['modelName'];
echo "<br>";
$last_group = $row_sqlquery['catName'];
}
} while ($row_sqlquery = mysql_fetch_assoc($sqlquery));
?>

My display result is throwing out 11 products (duplicating results) in each category (I only have one in each). I have had a bit of a look at joining tables but am struggling to find how to narrow the results down to the correct output.