MySQL PHP DB Tutorial: View Data from Database

Now that we have inserted data into the database, we want to retrieve the data and display it as links on the page. The SQL statement that gets data from a database is SELECT. The format is:

For our database, we want to select all columns. For ease, we use a '*' instead of listing out each column. We also want to select only a specific category of links. Let's say we want the category "Local Docs"

Our SELECT SQL statement would be:

Single quotes specify a string value in the WHERE clause. If we were using a column which was a number value, no quotes would be needed. The WHERE part is optional. If you want to select everything from the database, you can leave off the WHERE portion. Review throughout this SQL Tutorial for more examples of what you can do with SELECT statements and WHERE clauses.

The PHP code to display links from the database starts with the usual code to initialize the database connection.

The next part of the script sets up and executes the SQL statement. This will look familiar from the previous pages of this database tutorial.

I set the category as a variable at the beginning, so the code can be copied and pasted for other category selects, and only one change is needed.

The SELECT statement returns rows of data from the database. We need to loop through that data and display the information we want, which will be our links.

The command to grab a row is:

This sets $row as an array holding one record from the database, with the column names as the "keys" for the array.

To retrieve the siteurl value from that array you would use:

When the mysql_fetch_array command is called next, it moves to the next data record returned by the SELECT. If there are no more rows of data the command returns false. To loop through all rows of data, we can use a while statement with the mysql_fetch_array in it.

Here is the code snippet to loop through the data and display it to the screen:

Remember that PHP writes out HTML to the page which the browser then renders.

The complete script we just discussed above is here. (view_data.phps)

This completes most of tutorial for a MySQL PHP web application. We will next collect links entered into a web page form and then display these links according to the categories they were assigned.

The next step of the tutorial deals with managing these links after they are in the database.

Troubleshooting a Common SQL Error

"Your select statement does not return any data." You should check that the category specified in the WHERE clause has links under it. For example, if there are no links with the category "Local Docs" then no records will be selected and there will be nothing to display.

Related Links

Tutorial Sections

• Getting Started

• Create Database

• Insert Data into DB

• View Data from DB (this page)

• Manage Data