Connecting to mySQL
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
// Replace 'database' with appropiate database for your server.
mysql_select_db( 'database' , $mysqlLink );
// Select mySQL Table.
// Replace '$table' with appropiate table for your server.
$table = 'table';
?>
|
This is very simple and you should already have knowledge of
the commands here, so I won't get into detail in what it does. Basically we are
connecting to mySQL and selecting the database that contains the table we want.
To keep things as simple and user friendly as possible, we have created a variable called $table which houses the table name
- You should change this variable to your table name if you want this script to
work.
Now let's check out the heart of our code - the part that prints the actual
data:
Printing The Field Names
<?php
// Check If Query Was A Success.
if( $result = mysql_query('SELECT * FROM ' . $table ) )
{
// Start Table.
echo '<table border="1">';
// Get Number Of Fields In Table.
$fieldCount = mysql_num_fields( $result );
// Start First Row.
echo "<tr>";
// Loop Through Each Field And Display It In Row.
for( $i = 0; $i < $fieldCount; $i++ ) {
echo '<td><strong>' . mysql_field_name( $result, $i ) . '</strong></td>';
}
// Close First Row.
echo "</tr>";
?>
|
It is important to understand that the row housing the field
names is part of the actual table. We first check our query to determine whether
its request to select all
data from the table was a success. If it was, we start a new table by printing
the appropriate HTML code.
We then determine the number of fields in the table by way of the PHP function
mysql_num_fields( ). We will use this number to loop through each field
and print it.
We print each field by way of the function mysql_field_name( ). This
function takes two arguments, the first being the mySQL query result and the
second the current field index. Because we are using a For Loop, the index is
equal to the current position of the For Loop. This makes sure we print all
fields.
Printing Table Date
<?php
// Check If There Is Data In Table.
if( mysql_num_rows( $result ) )
{
// Loop Through Each Row And Display It.
while( $row = mysql_fetch_row( $result ) )
{
// Start Row.
echo '<tr>';
// Loop Through Each Field For Current Row.
for( $i = 0; $i < $fieldCount; $i++ ) {
echo '<td>' . $row[$i] . '</td>';
}
// Close Row.
echo '</tr>';
}
}
?>
|
We first check if there is actual data in the mySQL table.
This is to avoid any errors. If there is, we then start a While Loop to loop
through every single row and print its value for each field by way of the
function mysql_fetch_row( ).
The function mysql_num_rows( ) helps us determine if there is actual data
in the table. It returns an integer equal to the number of rows, if any, or
FALSE if non exist.
The function mysql_fetch_row( ) returns an enumerated array for each row,
where each key is equal to the field in the table. So if there are 6 fields in
the table, the keys of the array would be numbered 0 - 5. We use this function,
as opposed to mysql_fetch_array( ) because we do not know the field names
in the table. With an enumerated array, we can loop through each key using a
simple For Loop, as we have done.
The For Loop simply loops through each array index, printing its value. Each row
in the table is equal to a HTML table row, that is why we print an HTML table
row tag at the start of the While Loop and at the end of it.
Closing & Error Handling
<?php
// No Data In Table.
else {
echo '<tr><td colspan="' . $fieldCount . '">No rows selected...</td></tr>';
}
// Close Table.
echo '</table>';
}
// Invalid Query Or Un-Success.
else {
echo 'Query Error: ' . mysql_error( );
}
// Close mySQL Connection.
mysql_close( $mysqlLink );
?>
|
Finally we end the table and close the mySQL connection. If
there were any errors encountered, appropriate messages are printed.
Conclusion
In this tutorial, we simply learned how to print a mySQL table into an HTML
table to be displayed in your browser. We introduced several new PHP functions,
including mysql_num_rows, mysql_num_fields( ), mysql_field_name(
), and mysql_fetch_row( ).
You can use your new knowledge to alter the code to your liking, perhaps
providing alternating row colors, limiting the number of rows per page, or even
creating a wrapper function of some sort to easily call from within any script
to print data.
- Tutorial written by Limitless
|
