Crazy Horse

Thanks in advance for any help I have a page which has 5 recordsets. The first recordset gets a list of TeamNames. The other recordsets get these same names as well as count of records (from a database table with around 1million records) using different statements. Each recordset is working fine independently, and each has the correct list of names to use as the common factor. These need to show on one row like this. TeamName/Count1/Count2/Count3/Count4 (eg Team1 - 33 - 44 - 26 - 81) and then repeat the row for for each name What I need is something like; "select blah blah where name = TeamName" - so that it uses the teamname in the first column - matches against the data and then shows the correct info? And repeats for each row, per name. Or maybe the answer is nested while loops? Example code TeamNames: $query_rs_affinities = "SELECT miaffinityreports.affreportname FROM miaffinityreports WHERE affreportstatus = 'LIVE' ORDER BY miaffinityreports.affreportname "; $rs_affinities = mysql_query($query_rs_affinities, $conn_wiki) or die(mysql_error()); $row_rs_affinities = mysql_fetch_assoc($rs_affinities); $totalRows_rs_affinities = mysql_num_rows($rs_affinities); One of the counts (if one can work i can do the others): $query_rs_capture = "SELECT r.affreportname, COUNT(d.extcaseno) FROM miaffinityreports r LEFT JOIN (miaffinities a INNER JOIN mipartnerdata d ON d.affinitycode = a.affcode AND d.fileopened ='$midate' AND d.passenger='N' AND d.nrcase = 'N' AND d.status = 'LIVE') ON r.affreportcode = a.affreportcode WHERE r.affreportstatus = 'LIVE' GROUP BY r.affreportname ORDER BY r.affreportname ASC"; $rs_capture = mysql_query($query_rs_capture, $conn_wiki) or die(mysql_error()); $row_rs_capture = mysql_fetch_assoc($rs_capture); $totalRows_rs_capture = mysql_num_rows($rs_capture); And the code for the table row: (At present this just shows the correct counts for the first team and repeats for the rest, rather than correct per row) <?php do { ?> <tr> <td valign="top" bgcolor="#99CCCC"><strong><?php echo $row_rs_affinities['affreportname']; ?></strong></td> <td align="center" valign="top" bgcolor="#CCCCCC"> <?php echo $row_rs_capture['COUNT(d.extcaseno)'] ; ?> </td> <td align="center" valign="top">%</td> <td align="center" valign="top"><?php echo $row_rs_ulr['COUNT(d.extcaseno)']; ?></td> <td align="center" valign="top" bgcolor="#CCCCCC">%</td> <td align="center" valign="top" bgcolor="#CCCCCC"><?php echo $row_rs_pi['COUNT(d.extcaseno)']; ?></td> <td align="center" valign="top">%</td> <td align="center" valign="top"><?php echo $row_rs_hire['COUNT(d.extcaseno)']; ?></td> </tr> <?php } while ($row_rs_affinities = mysql_fetch_assoc($rs_affinities)); ?> btw, I have tried joins but it seems just too big, times out. Hope this makes sense, and thanks again

I have tried your statement also with left outer join, same result - any ideas anyone? Thanks

Thanks for the prompt reply! I tried your suggested statement but it still only shows 9 results as before, rather than the 10 i need from miteams - any ideas?

Hi, thanks in advance for any help I have the following 2 statements which I need to combine, however I need to show all records (10) from the first statement and 0 if the count returns nothing from the second statement. I can get them working fine and even combined working fine - except i always need to show 10 records even if count is empty (so show 0), but i only currently show 9 because one team has no count total This statement gets the list of teams from one table and returns 10 records SELECT teamname FROM miteams WHERE deptcode LIKE 'LEGSERV%' AND groupcode IS NULL ORDER BY teamname ASC This statement counts all the records from another table SELECT feteamname, COUNT(fileopened) FROM midata WHERE feteamname NOT IN ('Bike','Golf','Pretend','Contact Centre','Legal Services','Multi Track','Small Claims','') AND fileopened >= '2008-04-01' AND fileopened <= '2008-04-30' AND scotdesc !='Yes' AND sourcename = 'ACM ULR Ltd' AND rejectedreasons !='MIC' AND (smclaim = 'No' OR (smclaim = 'Yes' AND rejectedreasons !='TBA')) AND prodclassdesc !='BIKE' AND (rejectedreasons = 'D - Reject - MIB Untraced' OR rejectedreasons = 'A - Accepted English' OR rejectedreasons LIKE '%B - Minster%') GROUP BY feteamname ORDER BY feteamname ASC The common factor to join on is midata.feteamname and miteams.teamname I have tried left joins and left outer joins but i think i need brackets in the right places Hope this makes sense, thanks again

Thanks in advance for any help, sorry for the long explanation! I have a page which has for example 4 sql statements (see below) This is successfully pulling the correct data for each statement and i have it showing on the page in columns, for each statement However what I need is to show these per row based on the first statement - so the first column shows the affinity - then the totals are shown across the page, where the affinity matches. I then need to do some calculations per row with this data This is what I need for the finished result; AFFINITY - CAPTURE - ULR/CAPTURE(%) - ULR - PI/ULR(%) - PI - HIRE/ULR(%) - HIRE Currently I have the data showing correctly per column (20 rows each time) but I have put it into its own table, I need it per row based on the affinity I know that there is a better, cleaner, sharper way to do this, I just dont know how?? SQL STATEMENTS AFFINITIES SELECT miaffinityreports.affreportname FROM miaffinityreports WHERE affreportstatus = 'LIVE' ORDER BY miaffinityreports.affreportname CAPTURE $query_rs_capture = "SELECT r.affreportname, COUNT(d.extcaseno) FROM miaffinityreports r LEFT JOIN (miaffinities a INNER JOIN mipartnerdata d ON d.affinitycode = a.affcode AND d.fileopened ='$midate' AND d.passenger='N' AND d.nrcase = 'N' AND d.status = 'LIVE') ON r.affreportcode = a.affreportcode WHERE r.affreportstatus = 'LIVE' GROUP BY r.affreportname ORDER BY r.affreportname ASC"; $rs_capture = mysql_query($query_rs_capture, $conn_wiki) or die(mysql_error()); $row_rs_capture = mysql_fetch_assoc($rs_capture); $totalRows_rs_capture = mysql_num_rows($rs_capture); ULR $query_rs_ulr = "SELECT r.affreportname, COUNT(d.extcaseno) FROM miaffinityreports r LEFT JOIN (miaffinities a INNER JOIN mipartnerdata d ON d.affinitycode = a.affcode AND d.fileopened ='$midate' AND d.passenger='N' AND d.nrcase = 'N' AND d.faultornot = 'N' AND d.status = 'LIVE') ON r.affreportcode = a.affreportcode WHERE r.affreportstatus = 'LIVE' GROUP BY r.affreportname ORDER BY r.affreportname ASC"; $rs_ulr = mysql_query($query_rs_ulr, $conn_wiki) or die(mysql_error()); $row_rs_ulr = mysql_fetch_assoc($rs_ulr); $totalRows_rs_ulr = mysql_num_rows($rs_ulr); PI $query_rs_pi = "SELECT r.affreportname, COUNT(d.extcaseno) FROM miaffinityreports r LEFT JOIN (miaffinities a INNER JOIN mipartnerdata d ON d.affinitycode = a.affcode AND d.fileopened ='$midate' AND d.nrcase = 'N' AND d.datesolinst = d.datesol1stinst AND d.datesolinst != '0000-00-00') ON r.affreportcode = a.affreportcode WHERE r.affreportstatus = 'LIVE' GROUP BY r.affreportname ORDER BY r.affreportname ASC"; $rs_pi = mysql_query($query_rs_pi, $conn_wiki) or die(mysql_error()); $row_rs_pi = mysql_fetch_assoc($rs_pi); $totalRows_rs_pi = mysql_num_rows($rs_pi); HIRE $query_rs_hire = "SELECT r.affreportname, COUNT(d.extcaseno) FROM miaffinityreports r LEFT JOIN (miaffinities a INNER JOIN mipartnerdata d force INDEX(idx_mipdhi) ON d.affinitycode = a.affcode AND d.datehireinst ='$midate' AND d.passenger ='N' AND d.nrcase ='N' AND d.datehireinst = d.datehire1stinst AND d.datehireinst != '0000-00-00' AND d.hiretypecode = 'CDHR') ON r.affreportcode = a.affreportcode WHERE r.affreportstatus = 'LIVE' GROUP BY r.affreportname ORDER BY r.affreportname ASC"; $rs_hire = mysql_query($query_rs_hire, $conn_wiki) or die(mysql_error()); $row_rs_hire = mysql_fetch_assoc($rs_hire); $totalRows_rs_hire = mysql_num_rows($rs_hire); CODE FOR THE ROWS <tr> <td width="200" valign="top" bgcolor="#99CCCC"> <?php do { ?> <table width="100%" border="1" cellpadding="0" cellspacing="0" style="border-top:none; border-left:none; border-right:none"> <tr> <td><strong><?php echo $row_rs_affinities['affreportname']; ?></strong></td> </tr> </table> <?php } while ($row_rs_affinities = mysql_fetch_assoc($rs_affinities)); ?> </td> <td width="40" align="center" valign="top" bgcolor="#CCCCCC"><?php do { ?> <table width="100%" border="1" cellpadding="0" cellspacing="0" style="border-top:none; border-left:none; border-right:none"> <tr> <td align="center"><?php echo $row_rs_capture['COUNT(d.extcaseno)']; ?></td> </tr> </table> <?php } while ($row_rs_capture = mysql_fetch_assoc($rs_capture)); ?></td> <td width="40" align="center" valign="top">%</td> <td width="40" align="center" valign="top"><?php do { ?> <table width="100%" border="1" cellpadding="0" cellspacing="0" style="border-top:none; border-left:none; border-right:none"> <tr> <td align="center"><?php echo $row_rs_ulr['COUNT(d.extcaseno)']; ?></td> </tr> </table> <?php } while ($row_rs_ulr = mysql_fetch_assoc($rs_ulr)); ?></td> <td width="40" align="center" valign="top" bgcolor="#CCCCCC">%</td> <td width="40" align="center" valign="top" bgcolor="#CCCCCC"><?php do { ?> <table width="100%" border="1" cellpadding="0" cellspacing="0" style="border-top:none; border-left:none; border-right:none"> <tr> <td align="center"><?php echo $row_rs_pi['COUNT(d.extcaseno)']; ?></td> </tr> </table> <?php } while ($row_rs_pi = mysql_fetch_assoc($rs_pi)); ?></td> <td width="40" align="center" valign="top">%</td> <td width="40" align="center" valign="top"><?php do { ?> <table width="100%" border="1" cellpadding="0" cellspacing="0" style="border-top:none; border-left:none; border-right:none"> <tr> <td align="center"><?php echo $row_rs_hire['COUNT(d.extcaseno)']; ?></td> </tr> </table> <?php } while ($row_rs_hire = mysql_fetch_assoc($rs_hire)); ?></td> </tr> Hope this makes sense, thanks again

Thanks for your help! It uses several sql statements on the same row, for example.. $query_rs_affinities = "SELECT miaffinityreports.affreportname FROM miaffinityreports WHERE affreportstatus = 'LIVE' ORDER BY miaffinityreports.affreportname "; $rs_affinities = mysql_query($query_rs_affinities, $conn_wiki) or die(mysql_error()); $row_rs_affinities = mysql_fetch_assoc($rs_affinities); $totalRows_rs_affinities = mysql_num_rows($rs_affinities); and.. SELECT r.affreportname, COUNT(d.extcaseno) FROM miaffinityreports r LEFT JOIN (miaffinities a INNER JOIN mipartnerdata d ON d.affinitycode = a.affcode AND d.fileopened ='$midate' AND d.passenger='N' AND d.nrcase = 'N' AND d.status = 'LIVE') ON r.affreportcode = a.affreportcode WHERE r.affreportstatus = 'LIVE' GROUP BY r.affreportname ORDER BY r.affreportname ASC and.. SELECT r.affreportname, COUNT(d.extcaseno) FROM miaffinityreports r LEFT JOIN (miaffinities a INNER JOIN mipartnerdata d ON d.affinitycode = a.affcode AND d.fileopened ='$midate' AND d.passenger='N' AND d.nrcase = 'N' AND d.faultornot = 'N' AND d.status = 'LIVE') ON r.affreportcode = a.affreportcode WHERE r.affreportstatus = 'LIVE' GROUP BY r.affreportname ORDER BY r.affreportname ASC etc

Hi - and thanks in advance for any help!! I have a table which has the following row.. (8 columns).. this includes the output from 5 statements Currently I have while loops for each statement within individual <td></td> which successfully shows the data for each statement (each statement shows 20 rows - correct) but within the table cell and with no realtion to other columns. However ideally I need one while loop - the common factor in each statement is the first column "affreportname" So something like repeat this row for each statement where "affreportname" matches on that row I then need to create some calculations on this repeated row like column3/column4 but thats fine as long as I can get the columns related per row Hope this makes sense? And thanks again <tr> <td width="200" valign="top" bgcolor="#99CCCC"> <?php do { ?> <?php echo $row_rs_affinities['affreportname']; ?><br /> ------------------------------<br /> <?php } while ($row_rs_affinities = mysql_fetch_assoc($rs_affinities)); ?> </td> <td width="40" align="center" valign="top" bgcolor="#CCCCCC"><?php do { ?> <?php echo $row_rs_capture['COUNT(u.TeamCode)']; ?><br /> -----<br /> <?php } while ($row_rs_capture = mysql_fetch_assoc($rs_capture)); ?></td> <td width="40" align="center" valign="top">%</td> <td width="40" align="center" valign="top"><?php do { ?> <?php echo $row_rs_ulr['COUNT(u.TeamCode)']; ?><br /> -----<br /> <?php } while ($row_rs_ulr = mysql_fetch_assoc($rs_ulr)); ?></td> <td width="40" align="center" valign="top" bgcolor="#CCCCCC">%</td> <td width="40" align="center" valign="top" bgcolor="#CCCCCC"><?php do { ?> <?php echo $row_rs_pi['COUNT(u.TeamCode)']; ?><br /> -----<br /> <?php } while ($row_rs_pi = mysql_fetch_assoc($rs_pi)); ?></td> <td width="40" align="center" valign="top">%</td> <td width="40" align="center" valign="top"><?php do { ?> <?php echo $row_rs_hire['COUNT(u.TeamCode)']; ?><br /> -----<br /> <?php } while ($row_rs_hire = mysql_fetch_assoc($rs_hire)); ?></td> </tr>