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.

People will be able to answer your questions faster if you format your code so it's easy to read. — Brendan Long, Jun 27 '12 at 16:29
On another note, if you're just learning PHP, you should be [using mysqli or PDO](http://www.php.net/manual/en/mysqlinfo.api.choosing.php), not the old broken `mysql` functions. — Brendan Long, Jun 27 '12 at 16:36
thanks everyone, I had the ++$i in the for loop and didn't even see it there, completely invisible to me the entire time. Thanks for catching it. It works now. The new functions are news to me I didn't know about them, thanks for the link. — Binxalot, Jun 27 '12 at 16:49
This is an example of how formatting your code is important. The extra `++i` was extremely obvious once the `for` loop was formatted normally. — Brendan Long, Jun 27 '12 at 16:51

score 2 · Accepted Answer · answered Jun 27 '12 at 16:32

This section looks suspicious to me:

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

You're incrementing i twice
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).

score 0 · Answer 2 · answered Jun 27 '12 at 16:28

0

Programmers always count from 0. So, you are starting your loop at 0. If you end at 2, you have reached 3 rows.

Row0, Row1, Row2.

answered Jun 27 '12 at 16:28

Mike Mackintosh

13,917
6
60
87

score 0 · Answer 3 · answered Jun 27 '12 at 16:34

if $i = 0, and u increment it BEFORE adding something to the array, u skip the first row. increment $i AFTER the loop runs to start at 0 (first key).

For loops are not good for this: rather do:

$query=mysql_query('  --mysql ---  ');


while ($row=mysql_fetch_array($query)){

$v[]=$row["dbcolumn"];

}

return $v for your function then.compact and neat. you can create an associative array, as long as the key name is unique (like primary ids).. $v["$priid"]=$row[1];

This is incorrect. The loop they're using will correctly fetch the first row, but it skips the *second* one. — Brendan Long, Jun 27 '12 at 16:37

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

3 Answers3