web blazonry web development scripts and tutorials Get best price on Unlocked Moto G Power
   PHP       Name Generators       Perl       CSS       Javascript       Java       MySql       How Tos       Resources   

MySQL Home

MySQL Tutorial
  Getting Started
  Create Database
  Insert Data
  View Data
  Manage Data

Bookmark and Share





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:

SELECT (columns) FROM (table)
WHERE (exclusive criteria)

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:

SELECT * FROM links
WHERE category = 'Local Docs'

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.

<?php
  $usr = "--username--";
  $pwd = "--password--";
  $db = "linksdb";
  $host = "localhost";

  $cid = mysql_connect($host,$usr,$pwd);
  if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }
?>

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.

<?php
  $category = "Local Docs";
  $SQL = " SELECT * FROM links ";
  $SQL = $SQL . " WHERE category = '$category' ";
  $retid = mysql_db_query($db, $SQL, $cid);
  if (!$retid) { echo( mysql_error()); }

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:

$row = mysql_fetch_array($retid);

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:

$siteurl = $row["siteurl"];

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:

while ($row = mysql_fetch_array($retid)) {
$siteurl = $row["siteurl"];
$sitename = $row["sitename"];

echo ("<dd><a href='$siteurl'>$sitename</a></dd>\n");
}
echo ("</dt></p>");

?>

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

 

 

Newest Pages
Test Google Ads Ver. 2
Free Linux Admin Books
Free Linux Books for Programmers
Free Books for Linux on the Desktop
Free PHP Books
Free JavaScript Books
Free Java Books - Advanced
Free Java Books - Basic
Free Perl Books
Free Python Books
Quote of the Day (PHP)
Debugging Part 2
How to Test Google Ads
Most Popular Pages
Baby Name Generator
U.S. Name Generator
Wu Name Generator
Popup Windows (JavaScript)
Upload and Resize an Image (PHP)
How To Install Apache + PHP + MySQL
Intro to Web Databases (PHP, MySQL)

Least Popular Pages
iNews Applet (Java)
Java Resources
Site Monitor (Perl)
PHP Resources
 
 

  privacy policy     ||     © 1997-2016. astonishinc.com   All Rights Reserved.