Retrieving and Presenting SQL Joined Table Records with PHP, Sqlite (or MySql), and HTML

Question

Hello:

Please bare with me while I try to explain my question. I have a Sqlite database that has a table consisting of company information (Companies) and a table consisting of product information (Products). The Companies table id is Company_Name. It is joined with the Products table by Company_Name. For example, Company "A" can have many products associated with it, via inner join Companies on Company_Name = ProductID (ProductID consists of company names).

I am trying to display this information using a php while loop so that there will be a list of companies with their respective products. I cannot figure out how to do this. I can loop single records such as one company with one product, but I cannot list one company with multiple products. The following is what I am trying to achieve:

Company "A" - location CA product 1, product 2, product 3

Company "C" - location CA product 3, product 4, product 6

Company "F" - location CA product 1, product 8

In this example the query would pull all companies in California (CA).

My code is as follows, but it doesn't work:

//srchtext is the value in a HTML text box//
$txt = $_GET["srchtext"];
$dbstate = @$db->query("SELECT Company.Company_Name, Company.Company_City, Company.Company_State, Company.Company_Website, Company.Company_Service, Categories.Category_Name, Products.Product_Name FROM Company INNER JOIN Products On Company_Name = ProductID WHERE Company_State = '$txt'");
 while ($hmstate = $dbstate->fetchArray()){
echo "<ul>";
echo "<font id='srchclr'; color='#666666';>";
echo '<strong>'.$hmstate['Company_Name'].'</strong>'.'<br>'.$hmstate['Company_City'].','.$hmstate['Company_State'].'<br>'.
'<a href='.$hmstate['Company_Website'].'>'.$hmstate['Company_Website'].'</a>'.'<br>'.'<ul>'.
'<br>'.'<strong>Products or Services Provided by this Company:</strong>'.'<br>'.'<br>'.'<ul>'.$hmstate['Company_Service'].'</ul>';
echo "<br>";
echo "<strong>Company Product Category:</strong>";
echo "<br>";
echo "<br>";
  //Product loop//
  While ($hmstate = $dbstate->fetchArray()) {
  echo "<ul>";
  echo $hmstate['Product_Name'];
  echo "</ul>";
  }
}

Basically, my code gives me one company in California and lists all products associated with all companies in california:

Company "A" - location CA product 1, product 2, product 3, product 3, product 4, product 6, product 1, product 8

I tried taking out the second php "while loop", however I would get the following:

Company "A" - location CA Product 1

Company "A" - location CA Product 2

Company "A" - location CA Product 3...

Although I am using Sqlite, if someone has an example in MySql or any other sql Db, that would really help as well. I think my issue is with the query and the php "while loop".

I really appreciate your help.

Thank you,

DFM

Answer 1

Hi dmullins

Your Product while-loop is fetching all products because you do not "break" when Company_Name changes.

My PHP isn't good but perhaps something like this:

.
.
(Your company PHP code here)
.
.

//Product loop//
if ($hmstate = $dbstate->fetchArray()) {
$fetch_okay = true;
$Current_Company_name = $hmstate['Company_Name'];
$same_Company = true;
}

While (fetch_okay && same_Company) {
  echo "<ul>";
  echo $hmstate['Product_Name'];
  echo "</ul>";

  $fetch_okay = false;
  $same_Company_Name = false;
  if ($hmstate = $dbstate->fetchArray()) {
    $fetch_okay = true;
    if ($hmstate['Company_Name'] = $current_Company_name) {
      $same_Company_Name = true;
    }
  }
}