Split Mysql array results into table columns

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Split Mysql array results into table columns

Post by dyluck »

Firstly this is me as I search google for answers: :banghead:

Can someone help with the following:

I would like to query the database and have the array results display 1/2 of the rows in table column 1 and then the other 1/2 in the same table's column 2

Like below:

Row 1 Result | Row 2 Result
Row 3 Result | Row 4 Result
Row 5 Result

So on and so forth

Code: Select all

<tr class="bg">
<td align="center" class="first"><div align="right">
<input type="checkbox" name=box[] value="'.$row.'" id="checkbox2" >
</div></td>
<td align="center" class="last">'.$row.'</td>
<td align="center" class="last"><div align="right">
<input type="checkbox" name=box[] value="'.$anotherrow.'" id="checkbox2" >
</div></td>
<td align="center" class="last">'.$anotherrow.'</td>
</tr>
It gets more complicated then this, but I need to work out the simple display first.

Thanks!!!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Split Mysql array results into table columns

Post by califdon »

You can do it by using a counter to determine if each consecutive row is odd or even, like this:

Code: Select all

 $result=mysql_query($sql) or die("Query failed " . mysql_error());
  $counter = 0;
  while($row=mysql_fetch_assoc($result)) {
     if ( ($counter % 2) ) == 0 {    // % is the modulo operator, returns 0 or 1
        echo "</tr><tr>";
     }
     echo "<td>" . $row[0] . "</td>";   // or whatever should be printed
     $counter++;
  }
Of course, you have to have started an HTML table, etc. and you will have to format whatever it is that you're listing, but that should basically do it.
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: Split Mysql array results into table columns

Post by dyluck »

Im getting a T_IS_EQUAL parse error..

here's the code I'm using with what you gave me:

Code: Select all

$sql='SELECT * from streams where streamtype = 1 ORDER BY streamid ASC';
$result=mysql_query($sql) or die("Query failed " . mysql_error());
$counter = mysql_num_rows($result);
while($row=mysql_fetch_assoc($result)) {
     if ( ($counter % 2) ) == 0 {    // % is the modulo operator, returns 0 or 1
       
        echo "</tr><tr>";
     }
     echo "<td>" . $row['streamname'] . "</td>";   // or whatever should be printed
     $counter++;
  }
I only want it to be limited to 2 columns. Don't know if the above does that.
For some reason, I can't wrap my head around this one. can you help me get the above to look like below?

Code: Select all

<tr class="bg">
<td align="center" class="first"><div align="right">
<input type="checkbox" name=box[] value="'.$row.'" id="checkbox2" >
</div></td>
<td align="center" class="last">'.$row.'</td>
<td align="center" class="last"><div align="right">
<input type="checkbox" name=box[] value="'.$anotherrow.'" id="checkbox2" >
</div></td>
<td align="center" class="last">'.$anotherrow.'</td>
</tr>
Thanks for your help once again
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Split Mysql array results into table columns

Post by califdon »

The problem is that you used $counter to contain the number of rows in your result. Wrong. $counter needs to start at zero, then the first row will cause the <tr>(new html table row) to be echoed before the <td> because zero modulo 2 is zero. Then $counter is incremented to 1. When the next row is read, only the <td> will be echoed because 1 modulo 2 is 1. Then $counter is incremented to 2. When the next row is read, the </tr><tr> will be echoed before the <td> because 2 modulo 2 is also zero. And so on. In this manner, your html table will begin a new row before every second row from the database, which I believe is what you are looking for. Mind you, I didn't test this code, but I've done the same thing so many times that I'm sure it will work unless I just slipped a cog somehow. In any case, that's the logic to use. I'm assuming you know what the modulo operator does. It's the remainder left when dividing, so 10 % 2 = 0 because the remainder is zero, but 9 % 2 = 1 because the remainder is 1.
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: Split Mysql array results into table columns

Post by dyluck »

Hi califdon

Ok, I switched it back to 0 and it still gives me a parse T_IS_EQUAL error
on the line that "if ( ($counter % 2) ) == 0 { "
sits on.
:(
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: Split Mysql array results into table columns

Post by dyluck »

Ok, I figured out how to get rid of the error:

it should be:" if ( ($counter % 2) == 0 ) { "

The one bracket needed to go on the other side of the 0

Its not displaying what I want still though.. I guess I will have to reverse engineer the statement and see what its doing. I will post again if I can't manipulate it to do my bidding :lol:
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Split Mysql array results into table columns

Post by califdon »

Oops! Sorry, I omitted the closing parenthesis, didn't I? :oops:

If you don't sort out the display, post again and tell us how it is displaying.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Split Mysql array results into table columns

Post by Bill H »

This is kind of splitting hairs because there's nothing wrong with califdon's solution, but for readability I rather like to take the boolean approach, something like this:

Code: Select all

$sql='SELECT * from streams where streamtype = 1 ORDER BY streamid ASC';
$result=mysql_query($sql) or die("Query failed " . mysql_error());
$rt_col = 0;
while($row=mysql_fetch_assoc($result)) {
      if ($rt_col) {    // if we are outputting the right column, start a new row
        
         echo "</tr><tr>";
      }
      echo "<td>" . $row['streamname'] . "</td>";   // or whatever should be printed
      $rt_col = !rt_col;
   }
In a small bit of code like this it doesn't make much difference, but on larger samples it can make the column issue much clearer.
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: Split Mysql array results into table columns

Post by dyluck »

Hi guys!

I did get it to work! :) Here's the working code for one of the applications:

Code: Select all

$counter = 0;
while($row=mysql_fetch_assoc($result)) {
     if ( ($counter % 2)  == 0 ){    // % is the modulo operator, returns 0 or 1
       
        echo '</tr><tr>';
     }
     echo '<td width="51" align="center" ><label>
                          <div align="right">
                            <input type="checkbox" CHECKED  DISABLED/>
                          </div>
                        </label></td>
 
                      <td width="256" align="left" >'.$row['streamname'].'<br><img src="'.$row['logosmall'].'" alt="'.$row['logosmallalt'].'" width ="50" height = "50"></td>';   // or whatever should be printed
     $counter++;
  }
The only problem is, the table has some css and the right column cells that do not contain data are left completly cell formatless. don't know how to fix that... tried a few things but doesn't work :) Not a big deal.
So Bill H and califdon, what if I need 4 columns?
Would I use the boolean one now? or how would that work :) Im being a pain in the bum, but I know that I will be eventually crossing that bridge.

Thanks for your help so far! Its so cool to see this code in action!
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Split Mysql array results into table columns

Post by Bill H »

Yes, I'd still go that route, not quite boolean, but using the same principle.

Code: Select all

  $sql='SELECT * from streams where streamtype = 1 ORDER BY streamid ASC';
   $result=mysql_query($sql) or die("Query failed " . mysql_error());
   $rt_col = 4;
   $cur_col = 1;
   while($row=mysql_fetch_assoc($result)) {
          if ($cur_col == $rt_col ) {    // if we are outputting the right column, start a new row
           
             echo "</tr><tr>";
          }
          echo "<td>" . $row['streamname'] . "</td>";   // or whatever should be printed
         $cur_col = ($cur_col == $rt_col) ? 1 : $cur_col++;
      }
Again, the issue is readibility, the ability to see by the code itself what the purpose of the code is. In a small sample like this one the issue is really minor, but when you are writing bigger programs it becomes much more important. Variable names make a really big difference, and using names that indicate what the purpose of the variable is can really save much confusion. Others will tell you to use thames that tell you what kind of variable it is as well (integer, float, etc.), and that's not bad advice, but I seldom do it.

In this case you could use the constant 4 for the number of columns instead of $rt_col, but I find that setting factors like that in variables saves errors when I go to make changes, as I can be sure that a single change is going to get the task done.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Split Mysql array results into table columns

Post by califdon »

Glad it's working now. I agree with Bill H, his variation makes the intent clearer and thus easier to maintain.

You asked 2 questions. For more than 2 columns, just change the the number in the modulo expression to the number you want. The whole point is that when it is evenly divided, the remainder is zero, and when that's the case, you want to insert the </tr><tr> tags to end the current row and start a new one.

On your other question, the issue is that the loop, as it now is, stops when there are no more rows to fetch, so if the last row produces the "left" table cell, there is no <td> ... </td> echoed for the "right" cell. You could test for the value of the modulo result, after the loop ends, and if it is not zero, you could echo another cell, containing just a &nbsp; .
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: Split Mysql array results into table columns

Post by dyluck »

Woot you guys rock!
Thanks for the theory too cause its not just good to hash down code, but being able to understand it. It also makes for doing funky custom functions easier, andto help reverse engineer it if the time comes when a redesign/recode is necessary.

Once again, thanks for your help!
Until next time :P
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Split Mysql array results into table columns

Post by califdon »

It's always a good feeling to help someone who is interested in learning, not just in solving a momentary problem.

And with two old Navy guys here to help you, you're bound to learn a lot! :wink:
Post Reply