[RESOLVED] mysql_fetch_array problem

[dutchboy83]

Im trying to print out a table with employee information, but I also want to print out a seperate table per employee on the same page. The thing is my loop is working but I canfigure out why after the first time it doesn't go to the next value in the array that is retrieved from the database. I'm coding in php and working with mysql.

Here is a bit of my code maybe some one can show me what I have missed.

$query = "Select distinct(empl_Id) from employee_involved";
//runQuery is a function that is created in the code, works properly
$queryResult = runQuery($query);

//if you use this it actually prints out the values that the while loop
//should be looping through but for some reason mysql_fetch_array($queryResult)
//won't retrieve any info after $row[0], anything after comes out blank...
//printTable($queryResult);

$num = mysql_num_rows($queryResult);

while($row = mysql_fetch_array($queryResult))
{
for($i=0;$i<$num;$i++)
{
$query1 = "Select department from employee where Id = '$row[$i]'";
$queryResult = runQuery($query1);
$deptarray = mysql_fetch_array($queryResult);

echo	"        ";
echo	"        ";
echo	"        ";
echo	"        ";

$query2 = "Select name from employee where Id = '$row[$i]'";
$queryResult = runQuery($query2);
$namearray = mysql_fetch_array($queryResult);

            echo	"<font size='4'><b> Employee Name: ";
echo	"$namearray[0]</font>";

//if statement to perform the right select based on department
//
if ($deptarray[0] == 'Financing')
{	
            //there are other if statements and query's but I ommited them 
            //because they aren't part of the problem...

            $query = "Select distinct(invoice.stocknum) AS 'Stock No.',  datecreated  
                            AS 'Date Created', basecomm AS 'BASE', ficomm AS 'F&I',  
                            csicomm AS 'CSI', primecomm AS 'PRIME', totalcomm 
                            AS 'TOTAL', comment AS 'COMMENT'from employee, invoice, 
                            financecommission, employee_involved where employee.Id 
                             = '$row[$i]' and invoice.datecreated > '$startDate' and 
                            invoice.datecreated < '$endDate' and employee.Id = 
                            employee_involved.empl_Id and   
                            employee_involved.stocknum = invoice.stocknum and 
                            employee_involved.stocknum = 
                            financecommission.stocknum;"; 			
}

            $queryResult = runQuery($query);
echo	"</br></br></br></br></br>";

//this is a function that I have defined in another class, it prints the 
            //table properly...
printTable($queryResult);			
}

}

[illzz]

I think your problem may be with how you are accessing you ID's in your loop.

Change these lines...

    $query1 = "Select department from employee where Id = '$row[$i]'";

$query2 = "Select name from employee where Id = '$row[$i]'";

To...

    $query1 = "Select department from employee where Id = '$row[0]'";

$query2 = "Select name from employee where Id = '$row[0]'";

Your $row variable is going to be updated with each iteration, but there will still only be one item in it's array (based upon your query).

That is why it works for the first one ($i = 0), but not the rest...

[illzz]

There is also another instance of $row[$i] in the query within your IF block, update that one as well...

[dutchboy83]

I've tried that, the result is that it prints the table the right amount of time but always for the same employee...So its like it doesnt want to go to the record...

[illzz]

Looking at the code again... You don't need the FOR loop, since the WHILE is looping through each record and you're accessing the ID through $row[0].

I think I may have found the culprit.... You are redefining $queryResult within the loop so when it is evaluated in the WHILE statement, it is using the new result set rather than the original. Change the $queryResult inside of the loop to a different name.

[Ashley_Sheridan]

I think you are misunderstanding how to loop through your results.

while($row = mysql_fetch_array($queryResult))
{}

will loop through each row. You seem to be trying to use the while loop to check if there are rows, then use a for loop to iterate them, which isn't possible, so it looks like only one row is being parsed. Take out that for loop and let the while loop handle each row.

You can access fields of a given row by using the $row array: $row['field_name']. The $row array is updated to reflect the current rows worth of data, it doesn't hold every row all at once, just the current row.

[dutchboy83]

Thanks so much I didn't realise that I keep reinitialising that variable, obviously it wasn't going to work...!!