MySql Table formatting in PHP

Question

So this PHP Code would print all the table data from my database on a Web-Page.

<?php
$hostname = '127.0.0.1:3306';        
$dbname   = 'login'; // Your database name.
$username = 'root';             // Your database username.
$password = '';                 // Your database password. If your database has no password, leave it empty.

mysql_connect($hostname, $username, $password) or DIE('Connection to host is failed, perhaps the service is down!');
mysql_select_db($dbname) or DIE('Database name is not available!');
$query="SELECT * FROM markers";
$result=mysql_query($query);

$fields_num = mysql_num_fields($result);
echo "<table border='2'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td></div>";
}
echo "</tr>\n";
// printing table rows

while($row = mysql_fetch_row($result))

{
    echo "<table>";
    echo "<tr>";

    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "<td>$row[2]</td>";
    echo "<td>$row[3]</td>";
    echo "</tr>\n";
    echo "</table></div>";

}
?>

But what Essentially it's doing is that. It's not properly formatted.It comes like this.

Unformatted Table

What I wanted to do Is to bring this table exactly in the center of the web-page, Give it some fancy borders(curvy edges, Great fonts.)

You're creating a new table for each database row. You need to only create the table (<table>) once, and create new rows (<tr>) for each row — Pudge601
– Pudge601, Commented Jun 7, 2013 at 9:37
open view source or right click and click view source then see where html is broken. and solve it. — web2students.com
– web2students.com, Commented Jun 7, 2013 at 9:38
prepare a style sheet, read css styling..use Firebug/chrome developer tool etc ..adjust margin/padding ..border table blah blah!! :) — swapnesh
– swapnesh, Commented Jun 7, 2013 at 9:38
Please look into PDO or MySQLI instead of using mysql_* functions. — wazy
– wazy, Commented Jun 7, 2013 at 9:45

Pudge601 · Accepted Answer · 2013-06-07 09:41:36Z

2

You don't need to recreate the table for each row you are outputting, so change

while($row = mysql_fetch_row($result))

{
    echo "<table>";
    echo "<tr>";

    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "<td>$row[2]</td>";
    echo "<td>$row[3]</td>";
    echo "</tr>\n";
    echo "</table></div>";

}

to

echo "<div><table>";
while($row = mysql_fetch_row($result))

{
    echo "<tr>";
    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "<td>$row[2]</td>";
    echo "<td>$row[3]</td>";
    echo "</tr>\n";
}
echo "</table></div>";

answered Jun 7, 2013 at 9:41

Pudge601

2,0681 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rahul888 Over a year ago

Thanks for that, how can I bring this to exact center of the page using <div> ?

Pudge601 Over a year ago

Erm.. not entirely sure, I don't do too much with HTML and CSS anymore, but you can try setting the width of the div to 100%, and then set margin:auto on the table element

Purpletoucan · Accepted Answer · 2013-06-07 09:40:01Z

1

You're creating a new table for every iteration through rows. Move the <table> tags outside of the loop.

answered Jun 7, 2013 at 9:40

Purpletoucan

6,6223 gold badges25 silver badges29 bronze badges

Comments

zkanoca · Accepted Answer · 2013-06-07 09:41:22Z

0

Change

    while($row = mysql_fetch_row($result))

{
    echo "<table>";
    echo "<tr>";

    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "<td>$row[2]</td>";
    echo "<td>$row[3]</td>";
    echo "</tr>\n";
    echo "</table></div>";

}

to

 echo "<table>";
 while($row = mysql_fetch_row($result))

{
    echo "<tr>";
    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "<td>$row[2]</td>";
    echo "<td>$row[3]</td>";
    echo "</tr>\n";    
}
echo "</table>";

answered Jun 7, 2013 at 9:41

zkanoca

10k10 gold badges55 silver badges99 bronze badges

Comments

James Paterson · Accepted Answer · 2013-06-07 10:04:43Z

0

Use

echo "<table>";
while($row = mysql_fetch_row($result))

{
    echo "<tr>";
    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "<td>$row[2]</td>";
    echo "<td>$row[3]</td>";
    echo "</tr>\n";
}
echo "</table>";

Then in the head of the document, use the following code:

<style>
table {
margin: 0 auto;
width: 50%;
}
</style>

In order to correctly center the table, it is necessary to set a width for it. It won't work otherwise.

answered Jun 7, 2013 at 10:04

James Paterson

2,9533 gold badges33 silver badges43 bronze badges

Comments

Asenar · Accepted Answer · 2013-06-07 10:08:35Z

You need 2 loops (the 2nd inside the first),

$result = mysqli_query('your sql');
// begin table, only once
echo '<table>';
// use thead for the table header :)
echo '<thead>';
for($i=0; $i<$fields_num; $i++)
{
    $field = mysqli_fetch_field($result);
    echo '<th>'.$field->name.'</th>';
}
echo '</thead>';
echo '<tbody>';
// 1st loop, each rows
while($row = mysqli_fetch_assoc($result))
{
    // each rows start with  <tr> / ends with </tr>
    echo '<tr>';
    // 2nd loop, each field
    foreach($row as $field => $value)
    {
        // you can also add class name in each case of your table, to add custom style
        // for each field in your css
        echo '<td class="'.$field.'">'.$value.'</td>';
    }
    echo '</tr>';
}
echo '</tbody>';
echo '</table>';

James Paterson · Accepted Answer · 2015-02-19 15:28:30Z

0

You don't need to put table header in the loop because header will be the same all the time. For header you can try this:

<table border="1" align="left">
<tr>
<th>Employee ID</th>
<th>Designation ID</th>
<th>First Name</th>
</tr>
if(!result)
while($row = mysql_fetch_row($result))
{
   <tr>
           <td><?php $row[0];?></td>
   </tr>
 } 
</table>

edited Feb 19, 2015 at 15:28

James Paterson

2,9533 gold badges33 silver badges43 bronze badges

answered Jun 7, 2013 at 9:46

sanj

8510 bronze badges

Collectives™ on Stack Overflow

MySql Table formatting in PHP

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related