Self_Taught_Still_Learning Posted January 5, 2014 Share Posted January 5, 2014 (edited) 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> Edited January 6, 2014 by Self_Taught_Still_Learning Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted January 6, 2014 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted January 6, 2014 Share Posted 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! Quote Link to comment Share on other sites More sharing options...
Barand Posted January 6, 2014 Share Posted 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" Quote Link to comment Share on other sites More sharing options...
Barand Posted January 6, 2014 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.