[SOLVED] [PHP/MySQL] Split query rows by 3 to create table rows

AoN · Dec 27, 2012

I call myself an Internet Programmer (Webmaster), yet here I am looking for help on it. :S

Here's the short version. It's meant to be a "portfolio" (not much there because most of my work wasn't "legit"). You can see the simplied (written entirely in HTML/JavaScript) at Zrift.com - The Rift is Open. The below code can be seen at Zrift.com - The Rift is Open.

Now, it is a little more complicated than the title suggests. In addition to splitting it by three rows, I'm trying to dictate how it displays by calculating if it's the last one of not. Basically (if you have a slow Internet connection you already saw this from the live version), if I don't have a complete row it would put in a blank one with an image saying "No Template Available". I want that to go away. Instead, if there's only enough for 2 in the last row, for it to center those two, same with one.

If you take a look at the test page, you'll see what is happening with my current code. Any ideas on what I did wrong?

Code:

    $query = mysql_query('SELECT `id`, `name`, `icon` FROM `portfolio` ORDER BY `id`');
    $num = mysql_num_rows($query);
    $i = 0;
    $count = 0;
    while($i < $num)
    {
     $id = mysql_result($query,$i,'id');
     $name = mysql_result($query,$i,'name');
     $icon = mysql_result($query,$i,'icon');
     $code =  '
                                        <td>
                                         <table border="0" cellpadding="0" cellspacing="0" width="100%">
                                          <tr>
                                           <td background="templates/Lost_Love/images/content/box/port_top_bg.jpg" width="120" height="9"></td>
                                          </tr>
                                          <tr>
                                           <td align="center" background="templates/Lost_Love/images/content/box/port_middle_bg.jpg" width="120">
                                            <table border="0" cellpadding="0" cellspacing="0" width="100">
                                             <tr>
                                              <td background="templates/Lost_Love/images/content/box/port_screen_bg.jpg" style="border:0px;" width="100" height="100"><a href="javascript:unhide(\'port_viewer' . $id . '\');" title="' . $name . '"><img src="port/' . $icon . '"></a></td>
                                             </tr>
                                            </table>
                                           </td>
                                          </tr>
                                          <tr>
                                           <td background="templates/Lost_Love/images/content/box/port_bottom_bg.jpg" width="120" height="9"></td>
                                          </tr>
                                         </table>
                                        </td>';
     if($count == 0)
     {
      if($i = $num - 1)
      {
       echo '
                                       <tr>' . $code . '
                                       </tr>';
      }
      else
      {
       echo '
                                       <tr>' . $code;
       $count = 1;
      }
     }
     else if($count == 1)
     {
      if($i = $num - 1)
      {
       echo '
                                        <td width="15"></td>' . $code . '
                                       </tr>';
       $count = 0;
      }
      else
      {
       echo '
                                        <td width="15"></td>' . $code . '
                                        <td width="15"></td>';
       $count = 2;
      }
     }
     else
     {
      echo $code . '
                                       </tr>';
      $count = 0;
     }
     $i++;
    }

AoN · Dec 28, 2012

I can't believe how much trouble that gave me...and how stupid the solution was. Clearly I've been trying to program with VBScript. First I spend 2 hours using "&" instead of "." between variables, now I'm using "=" instead of "==" for if conditions. *sigh*

For any interested in the solution to this, here's what I ended up with, after some additional modifications for appearance. Please note, there are other ways of doing this that doesn't require as nested of code as I have, but there is a benefit of defining the different "approved" variations that prevents unwanted manipulation (mostly useful with forms, JavaScript, and SQL) of your code for malicious purposes. Also added in an IF for if the database results in nothing it displays the "No Template Available" icon, otherwise it checks the count to determine if it's one fo the last three and dictates the HTML off of that.

Code:

    $query = mysql_query('SELECT `id`, `name`, `icon` FROM `portfolio` ORDER BY `id`');
    $num = mysql_num_rows($query);
    $i = 0;
    $count = 0;
    if($num <> 0)
    {
     while($i < $num)
     {
      $id = mysql_result($query,$i,'id');
      $name = mysql_result($query,$i,'name');
      $icon = mysql_result($query,$i,'icon');
      $code =  '
                                           <td>
                                            <table border="0" cellpadding="0" cellspacing="0" width="100%">
                                             <tr>
                                              <td background="templates/Lost_Love/images/content/box/port_top_bg.jpg" width="120" height="9"></td>
                                             </tr>
                                             <tr>
                                              <td align="center" background="templates/Lost_Love/images/content/box/port_middle_bg.jpg" width="120">
                                               <table border="0" cellpadding="0" cellspacing="0" width="100">
                                                <tr>
                                                 <td background="templates/Lost_Love/images/content/box/port_screen_bg.jpg" style="border:0px;" width="100" height="100"><a href="javascript:unhide(\'port_viewer' . $id . '\');" title="' . $name . '"><img src="port/' . $icon . '"></a></td>
                                                </tr>
                                               </table>
                                              </td>
                                             </tr>
                                             <tr>
                                              <td background="templates/Lost_Love/images/content/box/port_bottom_bg.jpg" width="120" height="9"></td>
                                             </tr>
                                            </table>
                                           </td>';
      if($count == 0)
      {
       if($i == $num - 1)
       {
        echo '
                                       <tr>
                                        <td>
                                         <table align="center" border="0" cellpadding="0" cellspacing="0">
                                          <tr>' . $code . '
                                          </tr>
                                         </table>
                                        </td>
                                       </tr>';
       }
       else
       {
        echo '
                                       <tr>
                                        <td>
                                         <table align="center" border="0" cellpadding="0" cellspacing="0">
                                          <tr>' . $code;
        $count = 1;
       }
      }
      else
      {
       if($count == 1)
       {
        if($i == $num - 1)
        {
         echo '
                                           <td width="15"></td>' . $code . '
                                          </tr>
                                         </table>
                                        </td>
                                       </tr>';
         $count = 0;
        }
        else
        {
         echo '
                                           <td width="15"></td>' . $code . '
                                           <td width="15"></td>';
         $count = 2;
        }
       }
       else
       {
        if($count == 2)
        {
         if($i == $num - 1)
         {
          echo $code . '
                                          </tr>
                                         </table>
                                        </td>
                                       </tr>';
          $count = 0;
         }
         else
         {
          echo $code . '
                                          </tr>
                                         </table>
                                        </td>
                                       </tr>
                                       <tr>
                                        <td height="15"></td>
                                       </tr>';
          $count = 0;
         }
        }
       }
      }
      $i++;
     }
    }
    else
    {
     echo '
                                       <tr>
                                        <td>
                                         <table align="center" border="0" cellpadding="0" cellspacing="0">
                                          <tr>
                                           <td>
                                            <table border="0" cellpadding="0" cellspacing="0" width="100%">
                                             <tr>
                                              <td background="templates/Lost_Love/images/content/box/port_top_bg.jpg" width="120" height="9"></td>
                                             </tr>
                                             <tr>
                                              <td align="center" background="templates/Lost_Love/images/content/box/port_middle_bg.jpg" width="120">
                                               <table border="0" cellpadding="0" cellspacing="0" width="100">
                                                <tr>
                                                 <td background="templates/Lost_Love/images/content/box/port_screen_bg.jpg" style="border:0px;" width="100" height="100"></td>
                                                </tr>
                                               </table>
                                              </td>
                                             </tr>
                                             <tr>
                                              <td background="templates/Lost_Love/images/content/box/port_bottom_bg.jpg" width="120" height="9"></td>
                                             </tr>
                                            </table>
                                           </td>
                                          </tr>
                                         </table>
                                        </td>
                                       </tr>';
    }

I HAVE OFFICIALLY RECLAIMED MY TITLE AS A Webmaster!!!

niemiro · Dec 28, 2012

Well done, glad your problem is now resolved. I know full well what it is like to struggle for hours...and suddenly realise it was something stupid

Laxer · Dec 28, 2012

Depending on which version of PHP you are running the "&" can be used to separate delimiters in echo or print...

The logical operators are a bit tricky... you can get away with a single "=" in the sql query but sometimes PHP is a little bit of a pain...

IMO for SQL you're better off using the IN clause and with PHP you can use preg_match. Much more powerful and somewhat forgiving on syntax except REGEX :lolg:

AoN · Dec 28, 2012

Laxer said:
Depending on which version of PHP you are running the "&" can be used to separate delimiters in echo or print...

The logical operators are a bit tricky... you can get away with a single "=" in the sql query but sometimes PHP is a little bit of a pain...

IMO for SQL you're better off using the IN clause and with PHP you can use preg_match. Much more powerful and somewhat forgiving on syntax except REGEX :lolg:

lol, The issue wasn't with the query or anything like that. It was the logical operator to determine if a row from the query is last or not (I worked it so it didn't matter if it was for the last table row, just the last row from the query). For those, if you put "$i = $num - 1", which I had originally done, it actually changes the value for i to reflect num - 1, so it automatically marked the first row from the query as being the last. That's just one of those little things I overlooked because it's just been so long since I REALLY did any PHP programming like this. ^^'

I'm gonna put in an intro page once I figure out how to retreive an IP address from a viewer and convert it to an array. That's a project for tonight.

[SOLVED] [PHP/MySQL] Split query rows by 3 to create table rows

AoN

Internet Programmer

AoN

Internet Programmer

niemiro

Senior Administrator, Windows Update Expert

Laxer

Co-Founder
Senior Administrator

AoN

Internet Programmer

[SOLVED] [PHP/MySQL] Split query rows by 3 to create table rows

AoN

Internet Programmer

AoN

Internet Programmer

niemiro

Senior Administrator, Windows Update Expert

Laxer

Co-FounderSenior Administrator

AoN

Internet Programmer

Co-Founder
Senior Administrator