Display mySQL Data Across and then Down

FireGeek21 · Apr 6, 2014

Hi,

On our fire department website we would like to add a page of pictures of our personnel. I am against adding names of our personnel directly into the html code so I have a mySQL database with their name, position, email address, and image file. I am using php to pull the data out and display it on our page. Here is a draft of the code I have so far:

PHP:

CODE --> php
<html>
     <body>
          <?php
               //Get data from database and display in HTML table
               $con = mysql_connect("localhost","mukwona2_Mfd3400","1913mfd!");
                    if (!$con)
                    {
                         die('Could not connect: ' . mysql_error());
                    }

                mysql_select_db("mukwona2_contacts", $con);

                $result = mysql_query("SELECT * FROM email_address ORDER BY Nbr");
                                    
                echo "<table>
                      <tr>
                      <th>Nbr</th>
                      <th>LName</th>
                      <th>FName</th>
                      <th>Email</th>
                      <th>Position</th>
                      <th>Picture</th>
                      </tr>";

                while($row = mysql_fetch_array($result))

                {
                     echo "<tr>";
                     echo "<td>" . $row['Nbr'] . "</td>";
                     echo "<td>" . $row['LName'] . "</td>";
                     echo "<td>" . $row['FName'] . "</td>";
                     echo "<td>" . $row['Email'] . "</td>";
                     echo "<td>" . $row['Position'] . "</td>";
                     echo "<td><img src=" . $row['Headshot'] . " height='200' width='150'></td>";
                     echo "</tr>";
                }

                echo "</table>";

                mysql_close($con);
          ?>
     </body>
</html>

Right now the data displays one row per person. I am looking to display 4 pictures across with name, position, and email address below each picture. Then, the next row will display the next 4 pictures across with name, position, and email address below each picture, etc...

How can I get the data to display across and then down???

Thanks,

Tammy

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

jpadie · Apr 6, 2014

If you insist on using a table to format your output then why not just put all the data for each person into one cell.

But the better method is to use floated divs rather than tables. Then the design will also scale nicely for smaller browser windows.

FireGeek21 · Apr 6, 2014

Thank you jpadie for your input. Guess I was using a table because I wasn't sure of another (better) way. Can you explain "floated divs"? Maybe offer an example please?

THANKS!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

jpadie · Apr 7, 2014

this is really an html + css question. php has nothing to do with the display of your data on a browser. floats are also a well used and travelled tool - there are hundreds of thousands of resources on the internet that can explain them better than me.

but in brief this should work although it is not tested (no real data to test with). Note that you should not post you real user name and password on a public forum. It's just inviting trouble.

Code:

<!DOCTYPE HTML>
<html>
<head>
    <meta charset='utf8'/>
    <title>test</title>
    <style type="text/css">
/* reset box model */
*, *:before, *:after, td, th, tr {-moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box;}
/* other basic resets */
html, body, div, img{margin:0; padding:0; border:0;outline:0;font-size:100%;vertical-align:baseline;background:transparent;}
body{line-height:1;}

/* centre the main display window and set some default widths */
.persons{width:75%; min-width:200px; margin: 0 auto;}
/* make each person div float. this will put up to four across a page */
.person{margin-right: 5px; min-width:170px; width: 24.5%; padding-right:10px; padding-left: 10px; overflow:hidden; clear:left; float:left; border:thin black solid;}
/* kill the floating inside each person box */
.person .wrapper{clear:left; text-align:center;}
/* style the actual information */
.person .wrapper .headshot{text-align:center;}
.person .wrapper .name{color:navy;font-size:100%; text-align: center;}
.person .wrapper .email{color:navy;font-size:80%; font-style:italic; text-align: center;}
.person .wrapper .position{color: red;font-size:90%; text-align: center;}
    </style>
</head>
<body>
<?php
//Get data from database and display in HTML table
$con = mysql_connect("localhost",$dbUser, $dbPwd);
if (!$con) die('Could not connect: ' . mysql_error());
mysql_select_db("mukwona2_contacts", $con);
$result = mysql_query("SELECT * FROM email_address ORDER BY Nbr");
?>
    <div class="persons">
    <?php while($row = mysql_fetch_assoc($result)):?>

        <div class="person">
            <div class="wrapper">
                <div class="headshot"><img src="<?=$row['Headshot'];?>" height='200' width='150'/></div>
                <div class="name"><?=htmlspecialchars($row['FName'])?> <?=htmlspecialchars($row['LName']);?></div>
                <div class="email"><?=htmlspecialchars($row['Email'];?></div>
                <div class="position"><?=htmlspecialchars($row['Position']);?></div>    
            </div><!-- end wrapper class -->
        </div><!-- end person class -->

    <?php endwhile;?>
    </div><!-- end persons -->   
</body>
</html>

FireGeek21 · Apr 13, 2014

jpadie,

This is an awesome solution but I still get the data returned in one column. I was hoping to get 4 pictures and their data (as you have helped me compile below each picture) to display across:

Picture Picture Picture Picture
Data--- Data--- Data--- Data---

Picture Picture Picture Picture
Data--- Data--- Data--- Data---

and so on.

Someone else helped with a solution including a while loop. I tried to adapt it to your solution with no success. Here is what I now have. It doesn't seem to be working. Looking for additional assistance. THANKS!!

PHP:

<html>
	<head>
		<style type="text/css">
			/*Reset box model */
			*, *:before, *:after, td, th, tr {-moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box;}
			
			/*Other basic resets */
			html, body, div, img{margin:0; padding:0; border:0;outline:0;font-size:100%;vertical-align:baseline;background:transparent;}
			body{line-height:1;}

			/*Center the main display window and set some default widths */
			.persons{width:75%; min-width:200px; margin: 0 auto;}

			/*Make each person div float.  This will put up to four across a page */
			.person{margin-right: 5px; min-width:170px; width: 24.5%; padding-right:10px; padding-left: 10px; overflow:hidden; clear:left; float:left; border:thin black solid;}
			
			/*Kill the floating inside each person box */
			.person .wrapper{clear:left; text-align:center;}
			
			/*Style the actual information */
			.person .wrapper .headshot{text-align:center;}
			.person .wrapper .name{color:navy;font-size:100%; text-align: center;}
			.person .wrapper .email{color:navy;font-size:80%; font-style:italic; text-align: center;}
			.person .wrapper .position{color: red;font-size:90%; text-align: center;}
		</style>
	</head>
	<body>
		<?php
			//Get data from database and display in HTML table
			$con = mysql_connect("localhost",$dbUser, $dbPwd);
			if (!$con)
				{
					die('Could not connect: ' . mysql_error());
				}
			mysql_select_db("mukwona2_contacts", $con);
			$result = mysql_query("SELECT * FROM email_address ORDER BY Nbr");
			
			echo "<table>
				  <tr>";
				  
			$pos = 0;
            $per_row = 4;      // Change this to whatever number you want
		?>
		
		<div class="persons">
			<?php 
				while($row = mysql_fetch_array($result)):
				{
					if ($pos >= $per_row)
						{
							echo "</tr><tr>";
							$pos=0;
						}
			?>
				<div class="person">
					<div class="wrapper">
						<div class="headshot"><img src="<?=$row['Headshot'];?>" height='200' width='150'/></div>
						<div class="name"><?=htmlspecialchars($row['FName'])?> <?=htmlspecialchars($row['LName']);?></div>
						<div class="email"><?=htmlspecialchars($row['Email']);?></div>
						<div class="position"><?=htmlspecialchars($row['Position']);?></div>    
					</div><!-- end wrapper class -->
				</div><!-- end person class -->
			<?php 
				$pos++;
				}
					while ($pos < $per_row)
					{
						 echo "<td> </td>";
						 $pos++;
					}
					echo "</tr></table>";
			?>
		</div><!-- end persons -->   
	</body>
</html>

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

jpadie · Apr 13, 2014

Hi FireGeek

my solution works fine but for two tiny errors (for which my apologies: it is difficult to test without dummy data). The while loop is right there in the middle of the code and you certainly don't need the ugly table cells as per your more recent posting. That will not degrade well in mobile browsers or smaller viewer sizes. One of the advantages of using floats and percentage widths is that as the browser window narrows the floats just stack under each other. try it with the test page I provide below (shrink your browser window to see the effect).

the fixes are as follows:

1. the htmlspecialchars() function was missing a close bracket on the email line
2. the css for the person class had an extra clear in it. Ideally it should have some top margin too to make it display nicely. below is a fresh class description for you to paste in

As mentioned above I have also put a test page up so that you can play with the width setting to test different layouts. You can find it at: [URL unfurl="true"]http://tt.rathercurious.net/firegeek.php[/url]

Code:

.person {
    float: left;
    width: 24.5%; /* for four across.  for three across change to 32%. for five change to 19% or numbers close to that.  nb for five across you [i]may[/i] also need to change the min-width */
    min-width: 170px;
    overflow: hidden;
    /* change the below as you want for aesthetic purposes */
    border: thin solid #000000;
    padding-left: 10px;
    padding-right: 10px;
    margin-top: 5px;
    margin-right: 5px;
}

the code logic for the table based solution is quite a bit uglier.

1. start with a table tag
2. calculate the number of rows of your table
3. create an external loop around the number of rows
4. output row opener
5. create an internal loop around the number of columns
6. output cell opener
7. get the next row from the database.
8. if the row is false (no more data) output a non-breaking space. otherwise output the row content (you don't need floats in a table)
9. output cell closer
10. outside the internal loop, output a row closer;
11. outside the external loop output a table closer

if you have set borders then the last few boxes will look odd. so you may want to set a style or something on the 'empty' cells to suppress the borders.

Code:

$cols = 4;
$numRows = ceil(mysql_num_rows($result)/$cols);
echo '<table>;
for($i=0; $i<$numRows; $i++):
 echo "<tr>";
  for($j=0;$j<$cols;$j++):
    $row = mysql_fetch_assoc($result);
    $class = $row === false ? 'no-border' : 'normal-border';
    echo "<td class=\"$class\">";
     
      if($row === false):
        echo "&nbsp;";
      else: ?>
        //output row contents
<div class="person">
 <div class="wrapper">
  <div class="headshot"><img src="<?=$row['Headshot'];?>" height='200' width='150'/></div>
  <div class="name"><?=htmlspecialchars($row['FName'])?> <?=htmlspecialchars($row['LName']);?></div>
  <div class="email"><?=htmlspecialchars($row['Email']);?></div>
  <div class="position"><?=htmlspecialchars($row['Position']);?></div>    
 </div><!-- end wrapper class -->
</div><!-- end person class -->
<?php
    echo "</td>";
  endfor;
 echo "</tr>";
endfor;
echo '</table>';

but the float code is by far the better.

FireGeek21 · Apr 16, 2014

jpadie,

Thanks again for your response and explanations! Where do you suggest I put the chunk of code you last posted? The CSS portion is not a problem, it's the php/html portion I am not sure of.

THANKS AGAIN!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

jpadie · Apr 16, 2014

honestly i would not put it anywhere. it makes use of tables which are not advisable. use my original code and just change the css as per my previous post.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Display mySQL Data Across and then Down

FireGeek21

Technical User

jpadie

Technical User

FireGeek21

Technical User

jpadie

Technical User

FireGeek21

Technical User

jpadie

Technical User

FireGeek21

Technical User

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor