Displaying multiple row stored data in a single <td>

[surveen]

I have stored supplier information in a table and same form user will select multiple products for that supplier and that will be stored in a seperate table against that supplier id. Now when i would like to display all supplier information with muliple products in a single line. Now all the information will be displayed as many time as the products are. How to display all the products in a same line. 

 

Now my display is like this

 

if(isset($_POST['submit']) && ($_POST['vendor']!='') && ($_POST['item']!=''))
{
$sql="SELECT supplier.id AS sid, supplier.name AS SNAME, supplier.category, supplier.website, supplier.email, supplier.phone, supplier.vat, supplier.pan, supplier_location.id, supplier_location.supplier_id, supplier_location.location, supplier_products.id, supplier_products.supplier_id, supplier_products.product_id, location.loc_id, location.name AS locname, products.product_id, products.name AS pname FROM supplier INNER JOIN supplier_location ON supplier.id = supplier_location.supplier_id INNER JOIN supplier_products ON supplier.id=supplier_products.supplier_id INNER JOIN location ON supplier_location.location = location.loc_id INNER JOIN products ON supplier_products.product_id=products.product_id WHERE supplier.id=".$sup." AND supplier_products.product_id=".$product;
$sql1 = mysql_query($sql) or die(mysql_error());


<table>
<thead><tr>
<th>Vendor ID</th>
<th>Vendor</th>
<th>Category</th>
<th>Website</th>

<th>Email</th> 

<th>Phone</th>
<th>Products</th>
<th>Locations</th>
 <th>VAT</th>
  <th>PAN</th>
                        
                       
                        


</tr>


</thead>
<tbody> 


<tr>
<?php
while($row = mysql_fetch_array($sql1))
{ 


?>
   
   
    <td><?php echo $row['sid'] ?></td>
<td><?php echo $row['SNAME'] ?></td>
    <td><?php echo $row['category'] ?></td>
    <td><?php echo $row['website'] ?></td>
     <td><?php echo $row['email'] ?></td>
    <td><?php echo $row['phone'] ?></td>
    <td><?php echo $row['iname']; ?></td>


    <td><?php echo $row['locname']; ?></td>


    <td><?php echo $row['vat'] ?></td>
    <td><?php echo $row['pan'] ?></td>




</tr>


<?php 




}
?>
</tbody></table>


}

 

[NotionCommotion]

Not sure if I understand your intent.  Do you wish to display a table similar to what you show?  You obviously need to close your PHP tag before displaying the table header, or echo the table header.  I also think you should clean up how you echo each row (I typically echo both the array elements as well as the HTML tags, and use double quotes so that I don't need to concatenate each).

[surveen]

I am attaching the screenshot of my output, As there is multiple locations and multiple products for that supplier, it displays all the information as many a times. instead of that i want to display only one time with all the products, as comma seperated

 

Edited December 19, 2014 by surveen

[NotionCommotion]

Untested, but should be close.

<?php

if(isset($_POST['submit']) && ($_POST['vendor']!='') && ($_POST['item']!='')) {
    $sql="SELECT supplier.id AS sid, supplier.name AS SNAME, supplier.category, supplier.website, supplier.email, supplier.phone, supplier.vat, supplier.pan, supplier_location.id, supplier_location.supplier_id, supplier_location.location, supplier_products.id, supplier_products.supplier_id, supplier_products.product_id, location.loc_id, location.name AS locname, products.product_id, products.name AS pname FROM supplier INNER JOIN supplier_location ON supplier.id = supplier_location.supplier_id INNER JOIN supplier_products ON supplier.id=supplier_products.supplier_id INNER JOIN location ON supplier_location.location = location.loc_id INNER JOIN products ON supplier_products.product_id=products.product_id WHERE supplier.id=".$sup." AND supplier_products.product_id=".$product;
    $sql1 = mysql_query($sql) or die(mysql_error());
    echo('<table>
        <thead>
        <tr>
        <th>Vendor ID</th>
        <th>Vendor</th>
        <th>Category</th>
        <th>Website</th>
        <th>Email</th>
        <th>Phone</th>
        <th>Products</th>
        <th>Locations</th>
        <th>VAT</th>
        <th>PAN</th>
        </tr>
        </thead>
    <tbody> ');
    while($row = mysql_fetch_array($sql1)) {
        echo("<tr>
            <td>{$row['sid']}</td>
            <td>{$row['SNAME']}</td>
            <td>{$row['category']}</td>
            <td>{$row['website']}</td>
            <td>{$row['email']}</td>
            <td>{$row['phone']}</td>
            <td>{$row['iname']}</td>
            <td>{$row['locname']}</td>
            <td>{$row['vat']}</td>
            <td>{$row['pan']}</td>
            </tr>");
    }
    echo('</tbody></table>');
}

else{echo('Nothing');}

[Barand]

If you don't want to repeat the supplier details then keep track of when the supplier changes and only output the details on that change. I have modified NotionCommotion's code to demonstrate. I also removed the superfluous columns that you were selecting but not using.

if(isset($_POST['submit']) && ($_POST['vendor']!='') && ($_POST['item']!='')) {
    $sql="SELECT supplier.id AS sid
    , supplier.name AS SNAME
    , supplier.category
    , supplier.website
    , supplier.email
    , supplier.phone
    , supplier.vat
    , supplier.pan
    , location.name AS locname
    , products.name AS pname 
    FROM supplier 
        INNER JOIN supplier_location ON supplier.id = supplier_location.supplier_id 
        INNER JOIN supplier_products ON supplier.id=supplier_products.supplier_id 
        INNER JOIN location ON supplier_location.location = location.loc_id 
        INNER JOIN products ON supplier_products.product_id=products.product_id 
    WHERE supplier.id='$sup' 
        AND supplier_products.product_id= '$product'
    ORDER BY sid";
    $sql1 = mysql_query($sql) or die(mysql_error());
    echo('<table>
        <thead>
        <tr>
        <th>Vendor ID</th>
        <th>Vendor</th>
        <th>Category</th>
        <th>Website</th>
        <th>Email</th>
        <th>Phone</th>
        <th>Products</th>
        <th>Locations</th>
        <th>VAT</th>
        <th>PAN</th>
        </tr>
        </thead>
    <tbody> ');
    $current = '';            // STORE THE SUPPLIER SID
    while($row = mysql_fetch_array($sql1)) {
        if ($row['sid'] != $current) {
            echo "<tr>
                <td>{$row['sid']}</td>
                <td>{$row['SNAME']}</td>
                <td>{$row['category']}</td>
                <td>{$row['website']}</td>
                <td>{$row['email']}</td>
                <td>{$row['phone']}</td>";
            $current = $row['sid'];   // RESET  STORED SID
        }
        else {
            echo "<tr><td colspan='6'> </td>";
        }
        
        echo "<td>{$row['pname']}</td>
            <td>{$row['locname']}</td>
            <td>{$row['vat']}</td>
            <td>{$row['pan']}</td>
            </tr>";
    }
    echo('</tbody></table>');
}

else{echo 'Nothing';}

[surveen]

Barand, actually even the locations are also multiple . suppose products are 2 and locations will be 3, that time its displays 3 times

[Barand]

Not in the sample you gave us. Perhaps if you gave us a more comprehensive illustration of of your current output problem and also an example of how you would want it to appear.

[surveen]

Thanks for the solutions Barand. I will make sure that from next time i will try to explain my query in detail. I will try for location thing. Thank you

[surveen]

I have used GROUP_CONCAT() and DISTINCT and it worked for me. Thanks for your time