Explore our sites

Stack Exchange
tour help
Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

GO TO THE BOTTOM OF THIS QUESTION FOR THE ANSWER

I'm currently working on producing a table output which basically will list users row by row. The columns on the table output will be populated by one specific SQL column, but from multiple rows (say John Smith has 4 rows, but column X has a different value on each row in this table).

To give you more context this is a textual representation of the current table state.

Name  |  Col1  |  Col2  |  Col3
---------------------------------
Name1 |   0    |   X    |   1

Name2 |   3    |   2    |   <--- Value missing

Name3 |   2    |   1    |   <--- Value missing (and this continues through the table..

As you can see on that table, the first row populates fine - but the rest of the rows afterwards seem to be ignoring an iteration of the data (hence why the rest of the rows only fill 2 columns.

The relevant code for the loop is as follows:

while ($row = mysql_fetch_assoc($result)) {

    echo "<tr><td>". $row['forename'] . "</td>
      <td>". $row['status'] ."</td>";

             while ($col = mysql_fetch_assoc($result)) {

                 if ($col['id'] == $row['id']) {

                     echo "<td>" . $col['status'] . "</td>";

                 }

                 else if ($col['id'] != $row['id']){
                     echo "</tr>";
                     break;
                 }
             }
}

Does anyone have any ideas about why this might be happening? I hope I've provided enough information, but if not please let me know! :)

share|improve this question
5  
You're using $result in both instances of mysql_fetch_assoc(). Should you not be looping on different result sets? –  Martin Bean May 31 '12 at 13:43
 
All the data is coming from one query though which has joined two tables together. All I'm trying to do is say: John Smith has 3 rows, and the column im using has values 1,2,3. Then The next user, say: Alan Man has 3 rows also, and again has 3 different values for each row (but the same column). I only want the users name once, but I want the 3 different values from each of those rows for that user, to be set out as the column values in my table. –  Wudhead May 31 '12 at 13:50
 
Are you sure the query is returning the correct data? –  Ignas May 31 '12 at 13:53
 
Well when I run the query in SQL to get the table as is, the data returned is spot on. It's just after the first row, it just seems to miss the first status value - but the other 2 afterwards are the same as in the table query. –  Wudhead May 31 '12 at 13:56
add comment

3 Answers

up vote 0 down vote 
if ($col['id'] == $row['id'])
{
     make a td
}

I assume that the problem is that sometimes $col['id'] != $row['id']

share|improve this answer
 
Thanks for the answer, but could you elaborate a bit more on what you mean? –  Wudhead May 31 '12 at 13:54
add comment
up vote 0 down vote

From looking at your code, I can suggest an adjustment.

By calling mysql_fetch_assoc($result) in the inner WHILE loop, you're moving the pointer in the result object (although not intentionally).

You should instead use the $row array retrieved in the first loop.

If you really need to do comparisons like i can see, your best bet would be to run through the entire result set first placing individual records into an array that you can then use in your loop like so:

$records = array();
//array to hold the retrieved records from the database
while($row = mysql_fetch_assoc($result)){
    //loop through the data
    $records[] = $row;
}
//continue with your processing, this time using $records as your result set
//an looping through it this time as an array -- you don't need mysql_fetch_assoc
.....

Good luck!

share|improve this answer
 
Right, yeah I see what you mean. I shall give it a go later and see how it turns out. Cheers :) –  Wudhead May 31 '12 at 14:04
 
Thanks Okeke I went about it a new method with your outline, and got it working nicely! :D –  Wudhead May 31 '12 at 16:08
 
then you really should've rewarded him with the "appropriate answer" points –  Harald Brinkhof Jun 12 '12 at 23:26
add comment
up vote 0 down vote accepted

Expanding on Okekes idea (cheers mate) I revised to do it another method, which now works very nicely!

//declare new array variable
                $resultSet[] = array();

                //set offset integer
                $i = 0;

                //While loop to return all rows from query, and place into multi dimensional array format
                while ($row = mysql_fetch_assoc($result)) {
                    $resultSet[$i] = $row;
                    $i++;
                }

                //check that the array isn't empty
                if ($resultSet) {

                    $innerCount = "0";

                    //count how many rows are in the array
                    $rowCount = count($resultSet);

                    //loop through the array, each row, then go through inner loop for columns
                    for ($row = 0; $row < $rowCount; $row=$row+$numRows) {

                        //declare variables for the row data
                        $foreName = $resultSet[$row]['forename'];
                        $surName = $resultSet[$row]['surname'];

                        echo "<tr><td>" . $foreName . " " . $surName . "</td>";

                        for ($col = $row; $col < $rowCount; $col++) {

                            if ($innerCount < $numRows) {

                            $innerCount++;
                            $currStatus = $resultSet[$col]['status'];
                            echo "<td>" . $currStatus . "</td>";

                            }

                            else {

                                echo "</tr>";
                                $innerCount = "0";
                                break;
                            }

                        }
                    }
                }
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.