solon Posted October 28, 2008 Share Posted October 28, 2008 Hi guys , i have a problem in one of my pages. I have a database and 2 tables are needed for that page. So in the first table i have: ID Coupon_id Event Bet Winnings member_id agent_id session_id confirmed date time 292 1 v Aldershot VS Chesterfield v Aldershot 2.30 22 230457 1129c7d9450e62df71e11c375b68b5b1 y 27.10.08 16:27:02 293 1 Basel VS Barcelona Barcelona 1.53 22 230457 1129c7d9450e62df71e11c375b68b5b1 y 27.10.08 16:27:02 294 1 Inter VS Anorthosis Famagusta Anorthosis Famagusta 13.00 22 230457 1129c7d9450e62df71e11c375b68b5b1 y 27.10.08 16:27:02 295 2 Inter VS Anorthosis Famagusta Inter 1.15 22 230457 3228c874f7c16daf0ecd8b91e33986a9 y 27.10.08 16:28:40 296 2 v Aldershot VS Chesterfield Chesterfield 2.70 22 230457 3228c874f7c16daf0ecd8b91e33986a9 y 27.10.08 16:28:40 and the second table i have: coupon_id stake total_winings poss_total_winnings 1 7 45.75 320.25 2 2 3.11 6.22 Now what i want to do is the following: I need to get a result like: Coupon_id Event Bet Winnings date time 1 v Aldershot VS Chesterfield v Aldershot 2.30 27.10.08 16:27:02 1 Basel VS Barcelona Barcelona 1.53 27.10.08 16:27:02 1 Inter VS Anorthosis Famagusta Anorthosis Famagusta 13.00 27.10.08 16:27:02 [/td] total_winings 45.75 stake 7 poss_total_winnings 320.25 Coupon_id Event Bet Winnings date time 2 Inter VS Anorthosis Famagusta Inter 1.15 27.10.08 16:28:40 2 v Aldershot VS Chesterfield Chesterfield 2.70 27.10.08 16:28:40 total_winings 3.11 stake 2 poss_total_winnings 6.22 [td] The code that i am curently using and it doesn't do the work is the following: $result = mysql_query("SELECT * FROM `coupons` WHERE `member_id` = '$memb_id' && `agent_id` = '$agn_id'"); while($row = mysql_fetch_array($result)) { if ($result >= 1) { $coupon_ids = $row[coupon_id]; $id = $row[id] +1; $aresult = mysql_query("SELECT * FROM `coupons` WHERE `member_id` = '$memb_id' && `agent_id` = '$agn_id' && `id`='$id'"); while($row_a = mysql_fetch_array($aresult)) { if ($coupon_ids != $row_a[coupon_id]) { $result_i = mysql_query("SELECT * FROM `coupon_winnings` WHERE `coupon_id` = '$coupon_ids'"); if ($result_i >= 1) { while($row_i = mysql_fetch_array($result_i)) { echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Total:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_i[total_winings]</td></tr>"; echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Stake:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_i[stake]</td></tr>"; echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Possible Winnings:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_i[poss_total_winnings]</td></tr>"; echo"<tr><td></td><td></td><td></td><td></td></tr>"; echo"<tr><td></td><td></td><td></td><td></td></tr>"; echo"<table cellspacing='2' width='550' border='0' ><tr><td width='50'><u><b>Id:</b></u></td><td width='260'><u><b> Game </b></u></td><td width='100'><div><u><b> Bet </b></u></div></td><td width='40'><div align='center'><u><b> Winings </b></u></div></td></tr><tr>"; } } } echo "<tr> <td>$row[coupon_id]</td> <td>$row[event]</td> <td>$row[bet]</td> <td align='right'>$row[winings]</td> </tr>"; } } } Thanks in advance for any help.. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted October 28, 2008 Share Posted October 28, 2008 The first part of your intended look can be achieved by the following: 1.) Order the result set by the coupon id. 2.) Loop through the results, keeping track of the coupon id of the previous loop. 3.) If the coupon id of the current row is different to that of the previous row, then end the table, and set the previous coupon id to the current id. Here's the general idea: $sql = "SELECT ... ORDER BY couponid"; $result = mysql_result($sql); $previd = ''; while($row=mysql_fetch_assoc($result)){ if($row['couponid'] != $previd){ $previd = $row['couponid']; echo 'Coupon: '.$row['couponid'].'<br />'; } //echo row information - event, bet etc } As for the summary at the end of your ouput - how is it calculated? Shouldn't total winnings just be a summation? In which case, add that to the loop and echo it out when the coupon id changes. Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 The summary is calculated before hand and are in a table as i show above in my example Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 Your Post really helped getting the genaral idea but i still have the problem of how to display those values after each coupon_id like in my example.? ??? Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 Ok i got the result in the first and the second but it doesnt show the last one! here is the final code $result = mysql_query("SELECT * FROM `coupons` WHERE `member_id` = '$memb_id' && `agent_id` = '$agn_id' ORDER BY coupon_id"); $previd = ''; while($row = mysql_fetch_assoc($result)) { if ($result >= 1) { if($row['coupon_id'] != $previd) { $result_o = mysql_query("SELECT * FROM `coupon_winnings` WHERE `coupon_id` = '$previd' ORDER BY `coupon_id`"); while($row_o = mysql_fetch_assoc($result_o)) { if ($result_o >= 1) { echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Total:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_o[total_winings]</td></tr>"; echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Stake:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_o[stake]</td></tr>"; echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Possible Winnings:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_o[poss_total_winnings]</td></tr>"; echo"<tr><td></td><td></td><td></td><td></td></tr>"; echo"<tr><td></td><td></td><td></td><td></td></tr>"; echo"<table cellspacing='2' width='550' border='0' ><tr><td width='50'><u><b>Id:</b></u></td><td width='260'><u><b> Game </b></u></td><td width='100'><div><u><b> Bet </b></u></div></td><td width='40'><div align='center'><u><b> Winings </b></u></div></td></tr><tr>"; } } $previd = $row['coupon_id']; echo "<td>Coupon Id:</td><td>$row[coupon_id]</td>"; } echo "<tr> <td>$row[coupon_id]</td> <td>$row[event]</td> <td>$row[bet]</td> <td align='right'>$row[winings]</td> </tr><tr>"; } } Thanks Again. Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 what do you mean by but it doesnt show the last one Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 If you see the example i gave above you will see that it shows the total_winings 45.75 stake 7 poss_total_winnings 320.25 at the end of each coupon right? The problem is that the for the last coupon it doesn't show the total_winings 45.75 stake 7 poss_total_winnings 320.25 And another problem that occurred now is that if the coupon_id for the specific member is not +1 because another member played a coupon after the last coupon of the previous member it doesn't show the part mentioned above at all after that. Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 move $previd = $row['coupon_id']; here //HERE $result_o = mysql_query("SELECT * FROM `coupon_winnings` WHERE `coupon_id` = '$previd' ORDER BY `coupon_id`"); Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 I still have problem and now its bigger because this total_winings 45.75 stake 7 poss_total_winnings 320.25 is shown even before the table and still not for the last records Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 try this *untested* but looks correct to me $result = mysql_query("SELECT * FROM `coupons` WHERE `member_id` = '$memb_id' && `agent_id` = '$agn_id' ORDER BY coupon_id"); $previd = ''; while($row = mysql_fetch_assoc($result)) { echo "<tr> <td>{$row['coupon_id']}</td> <td>{$row['event']}</td> <td>{$row['bet']}</td> <td align='right'>{$row['winings']}</td> </tr><tr>"; if($row['coupon_id'] != $previd) { $previd = $row['coupon_id']; $result_o = mysql_query("SELECT * FROM `coupon_winnings` WHERE `coupon_id` = '$previd' ORDER BY `coupon_id`"); while($row_o = mysql_fetch_assoc($result_o)) { echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Total:</b></u></td><td bgcolor='#CCCCCC' align='right'>{$row_o['total_winings']}</td></tr>"; echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Stake:</b></u></td><td bgcolor='#CCCCCC' align='right'>{$row_o['stake']}</td></tr>"; echo"<tr><td></td><td></td><td bgcolor='#CCCCCC'><b><u>Possible Winnings:</b></u></td><td bgcolor='#CCCCCC' align='right'>{$row_o['poss_total_winnings']}</td></tr>"; echo"<tr><td></td><td></td><td></td><td></td></tr>"; echo"<tr><td></td><td></td><td></td><td></td></tr>"; echo"<table cellspacing='2' width='550' border='0' ><tr><td width='50'><u><b>Id:</b></u></td><td width='260'><u><b> Game </b></u></td><td width='100'><div><u><b> Bet </b></u></div></td><td width='40'><div align='center'><u><b> Winings </b></u></div></td></tr><tr>"; } echo "<td>Coupon Id:</td><td>{$row['coupon_id']}</td>"; } } Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 still the same problem except this time is shows one record from coupon 1 then the "total" and etc and then the rest of the records of coupon 1 Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 without look access to the DB it's hard to say.. but i would probably strip out the table for or view source, as the output doesn't seam to match the code Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 The DB looks exactly as i mention and shown above in my first post! They are identical to the DB tables Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 But i am not going to re-create it.. thus i can't truly test it, do you have the page online ? Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 This is the result i get on the webpage: Bet Id: Game Bet Winings Coupon Id: 1 1 v Aldershot VS Chesterfield v Aldershot 2.30 [/td] 1 Basel VS Barcelona Barcelona 1.53 1 Inter VS Anorthosis Famagusta Anorthosis Famagusta 13.00 Total: 45.75 Stake: 7 Possible Winnings: 320.25 Id: Game Bet Winings Coupon Id: 2 2 Inter VS Anorthosis Famagusta Inter 1.15 2 v Aldershot VS Chesterfield Chesterfield 2.70 Total: 3.11 Stake: 2 Possible Winnings: 6.22 Id: Game Bet Winings Coupon Id: 3 3 Pirin Blagoevgrad VS Slavia Sofia Pirin Blagoevgrad 2.25 3 Zenit St Petersburg VS Bate Boriso Bate Boriso 8.25 Coupon Id: 5 5 Kings Lynn VS Tamworth X 3.50 [td]5 Alfreton Town VS Gainsborough Alfreton Town 2.00 Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 Well that doesn't match the code... Coupon Id: should be at the very last thing printed in the code i posted.... plus that doesn't help me i don't know if theirs problems with the html or the database... Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 Well the difference in the code you posted is that the first thing displayed is: Bet Id Game Bet Winnings 1 team Vs team2 team2 2.30 Total: 45.75 Stake: 7 Possible Winnings: 320.25 and then: Bet Id: Game Bet Winings Coupon Id: 1 1 Basel VS Barcelona Barcelona 1.53 1 Inter VS Anorthosis Famagusta Anorthosis Famagusta 13.00 2 Inter VS Anorthosis Famagusta Inter 1.15 Total: 3.11 Stake: 2 Possible Winnings: 6.22 Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 This is pointless.. your showing me an output without letting me know the matching code and when i question it you show me another output that my code won't produce without changes.. have you even viewed source ? Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 Yes i did view the code and it doesnt even match the result shown on the page! It doesnt make any sense Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 i mean the HTML source.. Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 Me 2! Thats the weird part of it! Anw i came up to a partly solution, now my problem is the one i had at the beginning! I need to get in the first while loop once more than i normally do! so lets say if i have 5(five) records the loop goes 5(five) times. I, though need to get inside that loop 6(six) times to display the last total and etc. So here is the final code: $result = mysql_query("SELECT * FROM `coupons` WHERE `member_id` = '$memb_id' && `agent_id` = '$agn_id' ORDER BY coupon_id"); $previd = ''; while($row = mysql_fetch_assoc($result)) { if ($result >= 1) { if($row['coupon_id'] != $previd) { $result_o = mysql_query("SELECT * FROM `coupon_winnings` WHERE `coupon_id` = '$previd' ORDER BY `coupon_id`"); while($row_o = mysql_fetch_assoc($result_o)) { if ($result_o >= 1) { echo"<td></td><td bgcolor='#CCCCCC'><b><u>Total:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_o[total_winings]</td></tr>"; echo"<tr><td></td><td bgcolor='#CCCCCC'><b><u>Stake:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_o[stake]</td></tr>"; echo"<tr><td></td><td bgcolor='#CCCCCC'><b><u>Possible Winnings:</b></u></td><td bgcolor='#CCCCCC' align='right'>$row_o[poss_total_winnings]</td></tr>"; echo"<tr><td></td><td></td><td></td></tr>"; echo"<tr><td></td><td></td><td></td></tr>"; } } $previd = $row['coupon_id']; echo "<td colspan ='4'><u><b>Coupon Id:</b>$row[coupon_id]</u></td>"; echo" <table cellspacing='2' width='550' border='0' > <tr> <td width='260'><u><b> Game </b></u></td> <td width='100'><div><u><b> Bet </b></u></div></td> <td width='40'><div align='center'><u><b> Winings </b></u></div></td> </tr><tr>"; } echo "<tr> <td>$row[event]</td> <td>$row[bet]</td> <td align='right'>$row[winings]</td> </tr><tr>"; } } And thanks again for the quick responses Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 Okay... Lets sort this.. can you do a SMALL sql dump from phpmyadmin or something so i can create the tables on my local system.. that way i have more control and and run some tests also let me know the $memb_id $agn_id values Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 Ok this is the first table: -- Table structure for table `coupons` -- CREATE TABLE IF NOT EXISTS `coupons` ( `id` int(11) NOT NULL auto_increment, `coupon_id` int(15) NOT NULL, `event` varchar(40) collate utf8_unicode_ci NOT NULL, `bet` varchar(30) collate utf8_unicode_ci NOT NULL, `winings` varchar(5) collate utf8_unicode_ci NOT NULL, `member_id` int(11) NOT NULL, `agent_id` int(11) NOT NULL, `session_id` varchar(50) collate utf8_unicode_ci NOT NULL, `confirmed` varchar(2) collate utf8_unicode_ci NOT NULL, `date` varchar(10) collate utf8_unicode_ci NOT NULL, `time` varchar(10) collate utf8_unicode_ci NOT NULL, `won` varchar(3) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=311 ; -- -- Dumping data for table `coupons` -- INSERT INTO `coupons` (`id`, `coupon_id`, `event`, `bet`, `winings`, `member_id`, `agent_id`, `session_id`, `confirmed`, `date`, `time`, `won`) VALUES (292, 1, 'v Aldershot VS Chesterfield ', 'v Aldershot', '2.30', 22, 230457, '1129c7d9450e62df71e11c375b68b5b1', 'y', '27.10.08', '16:27:02', ''), (293, 1, 'Basel VS Barcelona', 'Barcelona', '1.53', 22, 230457, '1129c7d9450e62df71e11c375b68b5b1', 'y', '27.10.08', '16:27:02', ''), (294, 1, 'Inter VS Anorthosis Famagusta', 'Anorthosis Famagusta', '13.00', 22, 230457, '1129c7d9450e62df71e11c375b68b5b1', 'y', '27.10.08', '16:27:02', ''), (295, 2, 'Inter VS Anorthosis Famagusta', 'Inter', '1.15', 22, 230457, '3228c874f7c16daf0ecd8b91e33986a9', 'y', '27.10.08', '16:28:40', ''), (296, 2, 'v Aldershot VS Chesterfield ', 'Chesterfield ', '2.70', 22, 230457, '3228c874f7c16daf0ecd8b91e33986a9', 'y', '27.10.08', '16:28:40', ''), (307, 4, 'Zenit St Petersburg VS Bate Boriso', 'Zenit St Petersburg ', '1.30', 22, 230457, 'c9ad47a0f20a2bfbb6f75513f06442e8', 'y', '28.10.08', '17:50:58', ''), (308, 3, 'Bangor City VS Caersws', 'Bangor City ', '2.00', 2, 230457, '34a9ecab8fa311d47187d6e386068615', 'y', '28.10.08', '17:50:47', ''), (309, 4, 'Southport VS Vauxhall Motors', 'X', '5.25', 22, 230457, 'c9ad47a0f20a2bfbb6f75513f06442e8', 'y', '28.10.08', '17:50:58', ''), (310, 3, 'Stafford Rangers VS Hucknall', 'Stafford Rangers ', '1.45', 2, 230457, '34a9ecab8fa311d47187d6e386068615', 'y', '28.10.08', '17:50:47', ''); And this is the second table: -- Table structure for table `coupon_winnings` -- CREATE TABLE IF NOT EXISTS `coupon_winnings` ( `coupon_id` int(15) NOT NULL auto_increment, `stake` varchar(6) character set utf8 collate utf8_unicode_ci NOT NULL, `total_winings` varchar(7) character set utf8 collate utf8_unicode_ci NOT NULL, `poss_total_winnings` varchar(10) character set utf8 collate utf8_unicode_ci NOT NULL, `session_id` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`coupon_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `coupon_winnings` -- INSERT INTO `coupon_winnings` (`coupon_id`, `stake`, `total_winings`, `poss_total_winnings`, `session_id`) VALUES (1, '7', '45.75', '320.25', ''), (2, '2', '3.11', '6.22', ''), (3, '6', '2.90', '17.40', '34a9ecab8fa311d47187d6e386068615'), (4, '7', '6.83', '47.81', 'c9ad47a0f20a2bfbb6f75513f06442e8'); Hope This helps. Quote Link to comment Share on other sites More sharing options...
MadTechie Posted October 28, 2008 Share Posted October 28, 2008 try this <?php $result = mysql_query("SELECT * FROM `coupons` WHERE `member_id` = '$memb_id' && `agent_id` = '$agn_id' ORDER BY coupon_id") or die(mysql_error()); $previd = ''; $data = ""; echo '<table border="0">'; while(($row = mysql_fetch_assoc($result))) { //titles if($row['coupon_id'] != $previd) { echo $data; echo "<tr>"; echo "<td colspan='3'><u><b>Coupon Id:</b>{$row['coupon_id']}</u></td>"; echo "</tr>"; echo "<tr>"; echo " <td width='260'><u><b>Game</b></u></td>"; echo " <td width='100'><u><b>Bet</b></u></td>"; echo " <td width='40'><u><b><u><b> Winings </b></u></b></u></td>"; echo "</tr>"; } //results echo "<tr>"; echo " <td>{$row['event']}</td>"; echo " <td>{$row['bet']}</td>"; echo " <td align='right'>{$row['winings']}</td>"; echo "</tr>"; //Totals if($row['coupon_id'] != $previd) { $previd = $row['coupon_id']; $data = BuildTotals($previd); } } echo BuildTotals($previd); echo "</table>"; function BuildTotals($previd) { $data =""; $result_o = mysql_query("SELECT * FROM `coupon_winnings` WHERE `coupon_id` = '$previd' ORDER BY `coupon_id`"); while($row_o = mysql_fetch_assoc($result_o)) { $data .= "<tr><td> </td><td bgcolor='#CCCCCC'><b><u>Total:</b></u></td><td bgcolor='#CCCCCC' align='right'>{$row_o['total_winings']}</td></tr>"; $data .= "<tr><td> </td><td bgcolor='#CCCCCC'><b><u>Stake:</b></u></td><td bgcolor='#CCCCCC' align='right'>{$row_o['stake']}</td></tr>"; $data .= "<tr><td> </td><td bgcolor='#CCCCCC'><b><u>Possible Winnings:</b></u></td><td bgcolor='#CCCCCC' align='right'>{$row_o['poss_total_winnings']}</td></tr>"; $data .= "<tr><td> </td><td> </td><td> </td></tr>"; $data .= "<tr><td> </td><td> </td><td> </td></tr>"; } return $data; } Quote Link to comment Share on other sites More sharing options...
solon Posted October 28, 2008 Author Share Posted October 28, 2008 Well i really dont know how to thank you:) You saved my day! Thank you so much! 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.