How to display a table in mysql

The show or list table is very important when we have many databases that contain various tables. Sometimes the table names are the same in many databases; in that case, this query is very useful. We can get the number of table information of a database using the following statement:

The following steps are necessary to get the list of tables:

Step 1: Open the MySQL Command Line Client that appeared with a mysql> prompt. Next, log in to the MySQL database server using the password that you have created during the installation of MySQL. Now, you are connected to the MySQL server, where you can execute all the SQL statements.

Step 2: Next, choose the specific database by using the command below:

Step 3: Finally, execute the SHOW TABLES command.

Let us understand it with the example given below. Suppose we have a database name "mystudentdb" that contains many tables. Then execute the below statement to list the table it contains:

The following output explains it more clearly:

We can also use the FULL modifier with the SHOW TABLES query to get the type of table (Base or View) that appears in a second output column.

This statement will give the following output:

If we want to show or list the table name from different databases or database to which you are not connected without switching, MySQL allows us to use the FROM or IN clause followed by the database name. The following statement explains it more clearly:

The above statement can also be written as:

When we execute the below statements, we will get the same result:

Output:

Show Tables Using Pattern Matching

Show Tables command in MySQL also provides an option that allows us to filter the returned table using different pattern matching with LIKE and WHERE clause.

Syntax

The following are the syntax to use pattern matching with show table command:

We can understand it with the example given below where percent (%) sign assumes zero, one, or multiple characters:

The above statement will give the following output:

Let us see another statement that returned the table names starting with "time":

The above query will give the following output:

Now, we are going to see how we can use the WHERE clause with the SHOW TABLES command to list different types of tables (either Base or View type) in the selected database:

This statement gives the below output:

It is noted that if MySQL does not provide the privileges for accessing a Base table or view, then we cannot get the tables in the result set of the SHOW TABLES command.

Here, we can also see another example of Show Tables statement with the WHERE clause:

It will give the following output:

Very often you will need to use a MySQL table to store data inside it and then output that data by using a PHP script. To display the table data it is best to use HTML, which upon filling in some data on the page invokes a PHP script which will update the MySQL table.

To populate a new database table with data you will first need an HTML page which will collect that data from the user. The following HTML code that and passes the information to a PHP script:

    Value1: 

    Value2: 

    Value3: 

    Value4: 

    Value5: 

    

The above HTML code will show the user 5 text fields, in which the user can input data and a Submit button. Upon clicking the Submit button the data submitted by the user will be passed to a script named insert.php.

That script can have a syntax similar to the following:

real_escape_string($_POST['field1']);
$field2 = $mysqli->real_escape_string($_POST['field2']);
$field3 = $mysqli->real_escape_string($_POST['field3']);
$field4 = $mysqli->real_escape_string($_POST['field4']);
$field5 = $mysqli->real_escape_string($_POST['field5']);

$query = "INSERT INTO table_name (col1, col2, col3, col4, col5)
            VALUES ('{$field1}','{$field2}','{$field3}','{$field4}','{$field5}')";

$mysqli->query($query);
$mysqli->close();

After the user submits the information, the insert.php script will save it in the database table. Then you may want to output that information, so that the user can see it on the page. The first command you will need to use is the SELECT FROM MySQL statement that has the following syntax:

SELECT * FROM table_name;

This is a basic MySQL query which will tell the script to select all the records from the table_name table. After the query is executed, usually you would want the result from it stored inside a variable. This can be done with the following PHP code:

query("SELECT * FROM table_name");

The whole content of the table is now included in a PHP array with the name $result. Before you can output this data you should change each piece into a separate variable. There are two stages.

Now, we have to set up the loop. It will take each row of the result and print the data stored there. This way we will display all the records in the table:

$query = "SELECT * FROM table_name";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        $field1name = $row["col1"];
        $field2name = $row["col2"];
        $field3name = $row["col3"];
        $field4name = $row["col4"];
        $field5name = $row["col5"];
    }

    /* free result set */
    $result->free();
}

You can now write a full script to output the data. In this script the data is not formatted when it is printed:

 Database Output  
 
";

if ($result = $mysqli->query($query)) {

    while ($row = $result->fetch_assoc()) {
        $field1name = $row["col1"];
        $field2name = $row["col2"];
        $field3name = $row["col3"];
        $field4name = $row["col4"];
        $field5name = $row["col5"];

        echo ''.$field1name.$field2name.'
';
        echo $field5name.'
';
        echo $field5name.'
';
        echo $field5name;
    }

/*freeresultset*/
$result->free();
}

This outputs a list of all the values stored in the database. This will give you a very basic output which is not useful for a live website. Instead, it would be better if you could format it into a table and display the information in it. To apply formatting you need to use HTML to print the result by including the variables in the correct spaces. The easiest way to do this is by closing the PHP tag and entering HTML normally. When you reach a variable position, include it as follows:

in the correct position in your code.

You can also use the PHP loop to repeat the appropriate code and include it as part of a larger table. The final output is: