Using Inner Join and mysql_num_rows() is Always returning 1 less row

Question

I checked throught the existing topics. I have a fix for my problem but I know its not the right fix and I'm more interested making this work right, than creating a workaround it.

I have a project where I have 3 tables, diagnosis, visits, and treatments. People come in for a visit, they get a treatment, and the treatment is for a diagnosis.

For displaying this information on the page, I want to show the patient's diagnosis, then show the time they came in for a visit, that visit info can then be clicked on to show treatment info.

To do this a made this function in php:

<?
function returnTandV($dxid){
    include("db.info.php"); 
    $query = sprintf("SELECT treatments.*,visits.* FROM treatments LEFT JOIN visits ON
    treatments.tid = visits.tid WHERE treatments.dxid = '%s' ORDER BY visits.dos DESC",
    mysql_real_escape_string($dxid));

    $result = mysql_query($query) or die("Failed because: ".mysql_error()); 
    $num = mysql_num_rows($result);
    for($i = 0; $i <= $num; ++$i) {
        $v[$i] = mysql_fetch_array($result MYSQL_ASSOC);
        ++$i;
    }

    return $v;
}
?>

The function works and will display what I want which is all of the rows from both treatments and visits as 1 large assoc. array the problem is it always returns 1 less row than is actually in the database and I'm not sure why. There are 3 rows total, but msql_num_rows() will only show it as 2. My work around has been to just add 1 ($num = mysql_num_rows($result)+1;) but I would rather just have it be correct.

Answer 1

This section looks suspicious to me:

for($i = 0; $i <= $num; ++$i) {
    $v[$i] = mysql_fetch_array($result MYSQL_ASSOC);
    ++$i;
}

1. You're incrementing i twice
2. You're going to $i <= $num when you most likely want $i < $num

This combination may be why you're getting unexpected results. Basically, you have three rows, but you're only asking for rows 0 and 2 (skipping row 1).