CODEWITHSUNDEEP
phpmysql
Namit
Namit

Reputation: 1322

While loop not printing all answers

This might be really simple, but i cannot figure out the problem with this code:

$sql = mysql_query("select * from Temporary_Stock_Transfer where Emp_ID = '$emp_id' and Company_ID = '$company_id'");
    if(mysql_num_rows($sql) == 0) {
        echo "<tr><td colspan='3'><i>You currenty have no items</i></td></tr>";
    }else {
        while($row = mysql_fetch_array($sql)) {
            echo mysql_num_rows($sql);
            echo 'reached';
            $book_id = $row[1];
            $sql = mysql_fetch_row(mysql_query("select title from Book where Book_ID = '$book_id'"));
            echo "<tr><td>".$sql[0]."</td><td>".$row[2]."</td><td><span class='label label-important'>Remove</span></td></tr>";
        }
    }

Now based on my database the query is returning 2 results, the echo mysql_num_rows($sql) also gives out 2. However the reached is echoed only once. Does anyone see a potential problem with the code?

P.S: My bad, $sql is being repeated, that was a silly mistake

Upvotes: 2

Views: 138

Answers (6)

mgraph
mgraph

Reputation: 15338

try ($sql2 instead of $sql and $sql[0] to $sql2[0]):

$sql = mysql_query("select * from Temporary_Stock_Transfer where Emp_ID = '$emp_id' and Company_ID = '$company_id'");
    if(mysql_num_rows($sql) == 0) {
        echo "<tr><td colspan='3'><i>You currenty have no items</i></td></tr>";
    }else {
        while($row = mysql_fetch_array($sql)) {
            echo mysql_num_rows($sql);
            echo 'reached';
            $book_id = $row[1];
            $sql2 = mysql_fetch_row(mysql_query("select title from Book where Book_ID = '$book_id'"));
            echo "<tr><td>".$sql2[0]."</td><td>".$row[2]."</td><td><span class='label label-important'>Remove</span></td></tr>";
        }
    }

Upvotes: 4

Christopher Townsend
Christopher Townsend

Reputation: 1746

I think it'll be because you are ressetting the sql variable

            $sql = mysql_fetch_row(mysql_query("select title from Book where Book_ID = '$book_id'"));

when you are still using it in the while loop. :P

Upvotes: 3

Ryan
Ryan

Reputation: 28177

Inside the while loop you are re-using the same variable: $sql. This is used to control the terminating condition of the loop.

Using a different variable name, e.g. $sql2 should leave the original variable intact and the loop should proceed as expected.

Upvotes: 1

Ynhockey
Ynhockey

Reputation: 3932

You are changing the $sql variable inside the loop, therefore the next time you run $row = mysql_fetch_array($sql), the value will be different (probably won't be any based on your code).

Upvotes: 1

Matthew
Matthew

Reputation: 25743

It's probably this line:

 $sql = mysql_fetch_row(mysql_query("select title from Book where Book_ID = '$book_id'"));

Try using a different variable name.

Upvotes: 1

Manse
Manse

Reputation: 38147

It will only echo once because of this line :

$sql = mysql_fetch_row(mysql_query("select title from Book where Book_ID = '$book_id'"));

your overwriting the $sql variable.

Why not just run a single query and join the data you require ?

Upvotes: 4

Related Questions