Recordset : show a given value if dynamic field is empty

[PinkFloyd007]

Hey people,

Looking for a solution to this one :

I've got a table with a unique ID, persons, dates and #shots.

I want to display the total #shots for each person last month. If however, this person has not entered any #shots in that month, the recordset should still return a row with his name, but total #shots = 0.

This is what I have so far :

<?php
$curdate = getdate();
if ($curdate['mon'] = 1) {
$lastmonth = 12;
} else { 
$lastmonth = ($curdate['mon'] - 1);
}
if ($curdate['mon'] = 1) {
$lmyear = ($curdate['year'] - 1);
} else { 
$lmyear = $curdate['year'];
}

mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM);
$query_all_archers_lm = "SELECT TPERSON, SUM(TSHOTS) AS TOTSHOTS_LM FROM tir_avg_mem WHERE YEAR(TDATE) = '" . $lmyear . "' AND MONTH(TDATE) = '" . $lastmonth . "' GROUP BY TPERSON ORDER BY TPERSON ASC";
$all_archers_lm = mysql_query($query_all_archers_lm, $TIR_AVG_MEM) or die(mysql_error());
$row_all_archers_lm = mysql_fetch_assoc($all_archers_lm);
$totalRows_all_archers_lm = mysql_num_rows($all_archers_lm);
?>

On the page :

<table width="170" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="85" align="center"><font size="1"><b><?php print $lang['Shots_archer']; ?></b></font></td>
    <td width="85" align="center"><font size="1"><b><?php print $lang['Shots_archer_tot_lm']; ?></b></font></td>
  </tr>
  <?php do { ?>
  <tr>
    <td width="85" align="center"><font size="2"><?php echo $row_all_archers_lm['TPERSON']; ?></font></td>
    <td width="85" align="center"><font size="2"><?php echo $row_all_archers_lm['TOTSHOTS_LM']; ?></font></td>
  </tr>
  <?php } while ($row_all_archers_lm = mysql_fetch_assoc($all_archers_lm)); ?>
</table>

And release the recordset :

<?php
mysql_free_result($all_archers_lm);
?>

Now this only gives me a resulting row when SUM(TSHOTS) returns a value, meaning a value for #shots has been entered for last month by that person. I want it to give me a row for every existing person, and in the case that this person has not entered any #shots last month, display 0 as the resulting value for SUM(TSHOTS).

Anyone ? Anyone ?

Tnx,

PF

[bastien]

2 ways to handle this:

1. use the case when then construct in sql to evaluate the null to 0

2. use code to handle it

<?php 
$tot =  $row_all_archers_lm['TOTSHOTS_LM']; 

//check to see if value is null or empty string
if ((is_null($tot))||($tot == "")) $tot = 0;

echo $tot;

?>

[edit]My bad, there should have been 2 equal signs in the check[/edit]

[PinkFloyd007]

Thanks. I tried the second way :

It just replaced the existing values with 0, without adding rows for records with a Null value.

As for the first way : how would that look ? I've never used that construct before. I looked it up in the MySQL manual, but still can't figure out how to weave it into the existing recordset.

PF

[bastien]

Do your users and scores reside in the same table. ie if there is no score for that user, is there a record for it in that table?

[PinkFloyd007]

Yeps, I think that's part of the issue. There is only one table with entries for each score recorded containing a unique ID, the person's name, the date and the score.

[bastien]

so is there one table(scores with users) or two tables(users and scores)?

[PinkFloyd007]

Only one.