Displaying Even and Odd SQL Results

[JakePoulios]

I have a PHP while loop that pulls from an SQL database and displays the contents in a table with two columns.

// Check Connection
    if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

// Select Data Table
    $result = mysqli_query($con,"SELECT * FROM Recommendations") or die(mysqli_error);

// Split Data
    $mid = ceil(mysqli_num_rows($result)/2);

// Display Content
    while ($rows = mysqli_fetch_array($result)) {
        $Name = $rows['Name'];
        $Author = $rows['Author'];
        $Cover = $rows['Link to Cover'];
        $Link = $rows['Link to Profile'];
    echo "<table><tr><td>
    <a href='" . $Link . "' >$Name</a>
    <br />
    $Author
    <br />
    <a href='" . $Link . "' ><img src='" . $Cover . "' /></a>
    </td>
    <td>
    <a href='" . $Link . "' >$Name</a>
    <br />
    $Author
    <br />
    <a href='" . $Link . "' ><img src='" . $Cover . "' /></a>
    </td></tr></table>";
    }
?>

I want to be able to display the looped results side by side in columns of two.

Example:

1 2

3 4
5 6

 

I've tried using pseudo classes to display only the even and odd results in the different table columns, but honestly have no idea how to do this. I'm new to PHP, so my apologies if the results are really obvious. Thanks in advance!

[PravinS]

Check this simple logic

 

<table border="0" cellspacing="0" cellpadding="5">
<tr>
<?php
    $array = array(1,2,3,4,5,6);
    $i = 1;
    foreach($array as $val)
    {
        echo '<td>'.$val.'</td>';
        if ($i % 2 == 0)
             echo "</td></tr>";
      $i++;
    }
?>
</tr>
</table>

I hope this will help you

Edited August 11, 2014 by PravinS

[ginerjm]

You need to fetch a second row in the middle of your loop.  Currently you create a row, insert a td element and 3 values from the first row.  After that you need to do another fetch (while still inside your loop) and insert a 2nd td and the fields from the new row, THEN end that row and let the loop repeat.  Of course if the second fetch fails (you need to check that), you need to output an empty td element and still close the row.  After the loop you close the table (you only need one table element)

[Ch0cu3r]

 

 

After that you need to do another fetch (while still inside your loop) and insert a 2nd td and the fields from the new row, THEN end that row and let the loop repeat.

No, not at all. The code example PravinS provided in the foreach loop has the correct logic for outputting 2 table cells per row. Although the echo after if ($i % 2 == 0)  should read   echo "</tr><tr>";   . You need to close the current row and then open a new row.

 

@JakePoluis To apply PravinS solution to your loop you need to change your while loop look like this

// start table and open a row
echo "<table>
  <tr>";
$i = 0;
while(...) {

    // output a table cell for each result
    echo "
    <td>
       ... output for each result ...
    </td>";
    
    // close and open table row every 2 cells, NOTE replace 2 below for how many columns you want to repeat horizontally before starting a new row
    if (++$i % 2 == 0) {
        echo "\n  </tr>\n  <tr>";
    }
}

// close row and table
echo "
   </tr>
</table>";

Edited August 11, 2014 by Ch0cu3r

[ginerjm]

My solution works without loading an array and was addressed to the OP, not PravinS

[Ch0cu3r]

My solution works without loading an array and was addressed to the OP, not PravinS

I never said to use arrays. I was pointing out PravinS had the correct logic to use in a loop for outputting two columns per row.  Your solution of fetching the next row within the loop was unnecessary.

Edited August 11, 2014 by Ch0cu3r

[ginerjm]

?? Pravins solution utilizes an array.  Of course he doesn't give the OP any guidance on creating an array that fits his situation, but yes, it can easily be done.  My solution simply uses what is at hand and avoids that.

[CroNiX]

The OP is already using an array fetched from mysql though. Pravin was just showing the concept using a smaller custom array.

[ginerjm]

I may be having a bad day but 'mysqli_fetch_array' fetches ONE row of data stored in array format.  No?  Where is the 2nd row coming from to complete the first table row?

 

The OP's original logic is clearly flawed and my comments should help him see that, as well as give him a method to get two rows of data in each pass thru his loop.

[Ch0cu3r]

 

 

I may be having a bad day but 'mysqli_fetch_array' fetches ONE row of data stored in array format.  No?  Where is the 2nd row coming from to complete the first table row?

on the next iteration of the while loop.

 

How a new table row is created is controlled by the following if statement (this is what I am referring to earlier when I said 'logic')

    // close and open table row every 2 cells, NOTE replace 2 below for how many columns you want to repeat horizontally before starting a new row
    if (++$i % 2 == 0) {
        echo "\n  </tr>\n  <tr>";
    }

++$ increments $i by one on every iteration of the loop. It will then $i % 2 (meaning return the remainder of the division). If the results is zero output a new table row.

 

Have you not seen code like this before? Its seems you are confused by this, try and apply it  to one of your queries and see what you get.

Edited August 11, 2014 by Ch0cu3r

[Psycho]

Although the OP asked for a two column solution, I would propose a solution that allows the columns to be defined separately. I would not hard-code it to only support two columns.

 

To OP:

- Don't use field names with spaces in them. Yes, it can work, but it will only end up costing you lost time when the inevitable problems arise.

- Don't use '*' for your SELECT statement if you really don't need ALL the fields

- No need to make new variables from the $row array just to use them one time.

 

The following allows you to define the number of columns at the beginning of the script. So, if you later decide you want three columns or four or whatever, you only need to change the variable at the beginning of the script and it will "just work". Note, I don't have your database, so I didn't test it. There may be a small syntax error or two, but the logic is sound.

<?php
 
// Set column count
$max_columns = 2;
 
// Check Connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
 
// Select Data Table
$query = "SELECT `Name`, `Author`, `Link to Cover`, `Link to Profile`  FROM Recommendations";
$result = mysqli_query($con, $query) or die(mysqli_error);
 
// Create Content
$record_count = 0;
$output = '';
while ($row = mysqli_fetch_array($result))
{
    $record_count++;
    //Open new row if needed
    if($record_count%$max_columns == 1)
    {
        $output .= "<tr>\n";
    }
    //Create output for current record
    $output .= "<td>";
    $output .= "<a href='{$row['Link to Profile']}' >{$row['Name']}</a><br />\n";
    $output .= "{$row['Author']}<br />\n";
    $output .= "<a href='{$row['Link to Profile']}' ><img src='{$row['Link to Cover']}' /></a>\n";
    $output .= "</td>\n";
    //Close row if needed
    if($record_count%$max_columns == 0)
    {
        $output .= "</tr>\n";
    }
}
//Close last row if needed
if($record_count%$max_columns != 0) {
    $output .= "</tr>\n";
}
 
?>
 
<table>
<?php echo $output; ?>
</table>

Edited August 11, 2014 by Psycho

[ginerjm]

Chocu3r:  I don[t know what code you are seeing but I see a loop that fetches one row, moves array values into local vars, begins a table and row and td element and then outputs name, link, author and cover two times and then closes the table.  Then it loops and gets the second query result row and repeats.  End result - a table for each result row containing two images side by side of the same data.  The if statement you refer to does not exist in OP's code.

 

I stand by my original post, which I have used in the past.

 

Here is my version of OP's code:

 

// Display Content
echo "<table>";
while ($rows = mysqli_fetch_array($result))
{
   echo "<tr>";
   // output current row
   WriteResult($rows);
   //  get a second result row and output it
   if ($rows = mysqli_fetch_array($result))
   {
      WriteResult($rows);
   }
   else
      echo "<td> </td>";
   //  complete the current row
   echo "</tr>";
}
echo "</table>";
//**********************
function WriteResult($rows)
{ 
 $Name = $rows['Name'];
 $Author = $rows['Author'];
 $Cover = $rows['Link to Cover'];
 $Link = $rows['Link to Profile'];
 echo "<td>";
 echo "<a href='$Link'>$Name</a>";
 echo "<br />$Author<br />";
 echo "<a href='$Link'><img src='$Cover' /></a>";
 echo "</td>";
 return;
}

[Ch0cu3r]

 

 

The if statement you refer to does not exist in OP's code.

I know. That is what  the OP needs to add in order to output 2 results per row.  I then showed the OP how to add to it to their code in my post here. Psycho has also demonstrated the exact same principle too in his post above.

 

Your code will work yes, but it is not necessary to have two calls to mysql_fetch_array(). This what I was saying when I quoted you earlier.

[ginerjm]

I truly believe my solution is the simpler one.  It is concise and clear to someone who may be new to php and this kind of processing.  What's the objection to having a second fetch in the loop?  And no, it is not designed to handle 3 column tables or more - it simply works for the problem outlined and works simply.

[Jacques1]

ginerjm's solution is much, much cleaner than messing with opening and closing tags based on the current state.

 

In his code, one iteration assembles one row. You can actually see the markup in front of you. In your code, a row is assembled across multiple iterations, and you have to follow the control flow to figure out the resulting markup.

[ginerjm]

I have been truly blessed. 

[Psycho]

I would disagree in that it is hard-coded explicitly for two-columns. The WriteResult(); function was a nice addition though. Even with ginerjm's approach, the process could be expanded so that it is not hard-coded for specifically two columns. But, this has turned into an argument of the 'optimal' approach and, with it, comes lots of opinions. The bottom line is whether the requirements are met or not. Several methods of meeting those requirements have been provided.

[mikosiko]

+1 @Psycho