Page 1 of 1

[SOLVED] Multitable Array Search : How to display results

Posted: Sat Jun 25, 2005 12:27 am
by facets
Hi All,

I've finally figured the Multitable Db search but now i'm unsure on how to echo the results.

So far the Query is :

Code: Select all

$query = "SELECT ausapapersummary.paperCategoryId, aupapercategory.paperCategory, ausapapersummary.colloPaperName, ausapapersummary.stockId, austock.description, ausapapersummary.adhesiveId, auadhesive.description, auliner.description, ausapapersummary.linerId FROM ausapapersummary ";

$query .="LEFT JOIN aupapercategory ON ausapapersummary.paperCategoryId = aupapercategory.papercategoryId
LEFT JOIN austock ON ausapapersummary.stockId = austock.StockId          
WHERE ausapapersummary.paperCategoryId = '$paperCategoryId'";
And i'm outputting the results from the ausapapersummary like so :

Code: Select all

while($query_data = mysql_fetch_array($result)) {
      $paperCategory = $query_data["paperCategory"];
      $colloPaperName = $query_data["colloPaperName"];
      $stockId = $query_data["stockId"];
    
      echo "<TR>\n";
      echo "<TD WIDTH=\"25%\" ALIGN=\"CENTER\">$paperCategory</TD>\n";
      echo "<TD WIDTH=\"25%\" ALIGN=\"CENTER\">$colloPaperName</TD>\n";
      echo "<TD WIDTH=\"25%\" ALIGN=\"CENTER\">$stockId</TD>\n";
But how does one assign ("$query_data") from another table?
ie, description from the austock table.

tia, Will.

Posted: Sat Jun 25, 2005 12:36 am
by programmermatt
Typically I never see tables that have the exact same fieldnames. In this case I would recommend adding prefixes to the fields based on the table.

Example:
ausapapersummary.paperCategoryId
becomes
ausapapersummary.ps_paperCategoryId

That way you can refer to them easier and use them in this instance

Posted: Sat Jun 25, 2005 12:41 am
by facets
your talking about the LEFT JOIN fieldnames here right?

Posted: Sat Jun 25, 2005 3:16 am
by timvw
They both have the same "key" in the hash/associative array. If you use the index number you can access table1.foo and table2.foo (no need to select foo as foo1)

Imho tables with the same column names are a good thing :)
It allows you to write fe: select * from foo inner join bar using (col_id)

Posted: Sat Jun 25, 2005 3:39 am
by facets
Ahh. Got it.. (after a coffee and some reading :)

Make sure you have unique fieldnames for the whole project.
It makes it alot easier to assign variables etc.

Cheers!.