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".
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;
}
}
}
精彩评论