Show all in table

Self_Taught_Still_Learning · January 5, 2014

Ok, so in a database table i have:

name contact code type price quantity

Name 1 xxxxxxxxx GHT3 Food £10.50 2

Name 1 xxxxxxxxx GHTd Food £10.30 2

Name 1 xxxxxxxxx GHTs Food £10.90 2

Name 2 xxxxxxxxx GHT3 Food £10.50 2

Name 3 xxxxxxxxx GHTs Food £10.50 2

Name 3 xxxxxxxxx GHTd Food £10.50 2

I want them to read on another page like this:

Name Contact Code Type Quantity

Name 1 xxxxxxxxxx GHT3 Food 2

GHTd Food 2

GHTs Food 2

Total Price: £xx.xx

Name 2 xxxxxxxxxx GHT3 Food 2

Total Price: £xx.xx

Name 3 xxxxxxxxxx GHTs Food 2

GHTd Food 2

Total Price: £xx.xx

So it only reads the name and number once but puts in the whole of what they are after. Hope someone understands this and can lend some help. Don't need the table doing just need to know the sql i would need to use.



<table>

<tr><td colspan=5>Table</td></tr>

<tr><td>Name</td><td>Contact</td><td>Code</td><td>Type</td><td>Price</td><td>Quantity</td></tr>

<tr><td>Name 1</td><td>xxxxxxxx</td><td>GTH1</td><td>Food</td><td>£10</td><td>2</td></tr>

<tr><td>Name 1</td><td>xxxxxxxx</td><td>GTH2</td><td>Food</td><td>£10</td><td>2</td></tr>

<tr><td>Name 1</td><td>xxxxxxxx</td><td>GTH3</td><td>Food</td><td>£10</td><td>2</td></tr>

<tr><td>Name 2</td><td>xxxxxxxx</td><td>GTH1</td><td>Food</td><td>£10</td><td>2</td></tr>

<tr><td>Name 2</td><td>xxxxxxxx</td><td>GTH3</td><td>Food</td><td>£10</td><td>2</td></tr>

<tr><td>Name 3</td><td>xxxxxxxx</td><td>GTH2</td><td>Food</td><td>£10</td><td>2</td></tr>

</table>


What i want is to be able to pull all data needed for each name in on go like


<table>

<tr><td colspan=5>Table</td></tr>

<tr><td>Name</td><td>Contact</td><td>Code</td><td>Type</td><td>Price</td><td>Quantity</td></tr>

<tr><td colspan=1>Name 1</td><td colspan=1>xxxxxxxx</td><td colspan=3>GTH1 x 2<br>GHT2 x 2<br>GHT3 x 2<br>Total Price: £30.</td></tr>

<tr><td colspan=1>Name 2</td><td colspan=1>xxxxxxxx</td><td colspan=3>GTH1 x 2<br>GHT2 x 2<br>Total Price: £20.</td></tr>

<tr><td colspan=1>Name 3</td><td colspan=1>xxxxxxxx</td><td colspan=3>GTH1 x 2<br>Total Price: £10.</td></tr>

</table>

jazzman1 · January 6, 2014

So it only reads the name and number once but puts in the whole of what they are after.

Try,

SELECT tbl.name,tbl.contact, GROUP_CONCAT(tbl.Code DESC SEPARATOR '<br />') as Code, GROUP_CONCAT(tbl.Type SEPARATOR '<br />') as Type,GROUP_CONCAT(tbl.Quantity SEPARATOR '<br />') as Quantity FROM database.tbl GROUP BY tbl.Name

jazzman1 · January 6, 2014

Please, don't double posting! You did edit the post when I've posted mine. I see the HTML content right now and what you wanted to do!

Barand · January 6, 2014

Check for when the name changes and only output totals and name when it does.

<html>
<head>
<style type="text/css">
th {
    text-align: left;
}
td {
    width: 100px;
}
.ar {
    text-align: right;
}
</style>
</head>
<body>
<?php
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$sql = "SELECT name, contact, code, type, price, quantity
        FROM selftaught
        ORDER BY name";
$res = $db->query($sql);
$currname = '';

echo "<table border='0' cellpadding='4'>
<tr><th>Name</th><th>Contact</th><th>Code</th><th>Type</th><th class='ar'>Price</th><th class='ar'>Quantity</th></tr>";

while (list($name,$con,$code,$type,$pr,$qty) = $res->fetch_row()) {
    // check if name has changed
    if ($currname != $name) {
        // is it not the first name
        if ($currname) {
            echo "<tr><td colspan='4' class='ar'>Total price</td><td class='ar'>" . number_format($totprice,2) . "</td><td></td></tr>";
        }
        echo "<tr><td>$name</td><td>$con</td><td>$code</td><td>$type</td><td class='ar'>" . number_format($pr,2) . "</td><td class='ar'>$qty</td></tr>";
        $currname = $name;
        $totprice = 0;
    }
    else {
        echo "<tr><td colspan='2'></td><td>$code</td><td>$type</td><td class='ar'>" . number_format($pr,2) . "</td><td class='ar'>$qty</td></tr>";
    }
    $totprice += $pr;
}
echo "<tr><td colspan='4' class='ar'>Total price</td><td class='ar'>" . number_format($totprice,2) . "</td><td></td></tr>";
echo "</table>";
?>
</body>
</html>

And if that really is your table and not the result of a query then you should read up on "data normalization"

Barand · January 6, 2014

I've just spotted another post of yours that is already answered.

You have just totally wasted my time with this second post of the same question. I'll remember your name and stay clear in future

Sign In

Show all in table

Recommended Posts

Self_Taught_Still_Learning

Link to comment

Share on other sites

jazzman1

Link to comment

Share on other sites

jazzman1

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information