In this tutorial we are going to expose basic mySQL queries, and how they can be used to print all data from a mySQL table into an HTML table.

This might be useful if you want to display all the data for review reasons. A couple of things to take into consideration however when following this tutorial; For one thing, this tutorial will assume that the mySQL table we will print does not contain a lot of records. Obviously, if the table contains thousands of records, the script will take a long time to process and the rendered HTML page itself will be enormous. You should only use this tutorial to get a basic understanding of how to print the data, and they modify the code to suit your needs.

Secondly, the code shown does not require you to know the mySQL table field names. It is dynamic, meaning it only requires a mySQL table and then the code does everything else automatically. This makes the code more portable. Finally, you should have a common knowledge of working with PHP and mySQL in order to stay up to speed when reading this tutorial.

So, let's get started! We will first display the entire code, then break it down and explain each section, and how it works...

The Code



			<?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 appropriate database for your server.



mysql_select_db( 'database' , $mysqlLink );



// Select mySQL Table.

// Replace '$table' with appropriate table for your server.



$table = 'table';



/**

 * Print Table.

 *

 * Print Table consists of selecting the mySQL table, determine

			 * if there is data in it, selecting

 * that data, then actually formatting it in an HTML table.

 *

 * If there is no data, then appropriate messages are displayed.

 */



// 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>";

    

    // 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>';

        }

    }

    

    // 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 );



?>

That was a lot of code! Its simple but rather long. So, let's break down the code into smaller parts and explain what each section does:



			<?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.


	Dumping mySQL Tables to HTML Tutorial Author - Limitless (http://www.llstudios.net)