Iterating through two MYSQL Tables

[sahilansari]

Table1

Bill
billno
P001
P002


Table2
Patient
Patno service amount
P001 s1 100
P002 zz 200
P001 s2 400


....

(1)if more then one Patno exists for a particular billno then add all the services(concat enate) and add all the amount .

(2)if only one result ...then simply print that.

(3)This is how i am doing it.....

   for(traversing all the billno values one by one)
      {
         check=0;
          while($row = mysql_fetch_array($execute_query,MYSQL_NUM))
                 {
                       if(billno == Patno)
                        {
                           service = service + $row[1];
                           amt      = amt + $row[2] ;
                           check=1;
                        }
                 }

            if(check=1) /*make sure there is atleast one match*/
             {  
                 print the table as desired.
             }
     }

(4)My problem is that it works fine for printing the first row as shown in the table below.But does not move forward for the second row.During the second iteration of for loop it does not enter the while loop .Why does it not enter the while loop second time.I want to make it enter the while loop.Then only will my problem will be solved.

(5)Any other way of getting the result i want ..that you can give.


How i want the results........ .......
billno service amount
P001 s1,s2 500
P002 zz 200
...
...
...

[Mayur2007]

Hello,

Go through ID insert id field in your database and make it auto increment.
And link through ID.

Thanks & Regards,
Mayur Bhayani

[sahilansari]

well i got the solution to the problem......

Just before the while loop starts $execute_query= mysql_query($qu ery);

What actually happens,fist time while loop runs perfectly.On second run of for loop ...we don't eneter the while loop again.This is because after the first iteration of While Loop ...the pointer is at the end of the query value ie ==NULL.

So if within for loop we excute the query ...there will be no problem.