Retrieving Data from a Database

You want to retrieve data from a database.

Use SELECT to retrieve data from a table based on some criteria. use SELECT * FROM to list all the fields and rows in a table.

SELECT column_name FROM table_name;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="EN" lang="EN">
<head>
<title>Retrieving Data from a Database</title>
<meta Name="Author" Content="Hann So">
</head>
<body>
<p>
<?php

if (isset($_POST['submit'])) {
		process_form();
	}
	else {
		display_form();// display form for the first time
	}

function display_form() {
	echo <<<HTML
	<h2>Retrieving Data from a Database</h2>
	<form action = "$_SERVER[SCRIPT_NAME]" method="post">
	Username:
	<input type="text" name="username" size="50" value="hann" />
	<br />
	Password:
	<input type="password" name="password" size="50" />
	<br />
	Database name:
	<input type="text" name="db" size="50" value="hann_db" />
	<br />
	Table name:
	<input type="text" name="table" size="50" value="blog_entries" />
	<br />
	Sort (DESC= descending, ASC=Ascending):
	<input type="text" name="sort" size="50" value="date_entered DESC" />
	<br />
	<input type="submit" name="submit" value="Submit" />
	</form>
HTML;
}

function process_form() {

	DEFINE ('DB_HOST', "localhost");
	DEFINE ('DB_USER', "$_POST[username]");
	DEFINE ('DB_PASSWORD', "$_POST[password]");
	DEFINE ('DB_NAME', "$_POST[db]");
	DEFINE ('TABLE_NAME', "$_POST[table]");

	$sort = stripslashes(trim($_POST[sort]));

	echo "<p>Opening the connection to the database server.</p>";
	if ($link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)) {
		echo "<p>The connection worked. The link is $link</p>";

		// select a database
		if (@mysql_select_db(DB_NAME)) {
			echo "<P>The database ", DB_NAME, " has been selected.</p>";
		}
		else {
			die ("<p>Could not select the database because: ". mysql_error(). "</p>");
		}

		// define the query
		// every column of every row
		// DESC = descending, ASC= ascending
		$query = "SELECT * FROM " . TABLE_NAME . " ORDER BY " . "$sort";
		// run the query
		if ($r = @mysql_query($query)) {
			// retrieve and print every record
			echo "<h3>The table has the following entries:</h3>";
			// mysql_fetch_array fetches a result row as an associative array,
			// a numeric array, or both
			while ($row = mysql_fetch_array($r)) {
				echo "<p>blog_id={$row['blog_id']}<h4>{$row['title']}</h4>
				{$row['entry']}<br />
				<a href=\"example_u.php?id={$row['blog_id']}\">Edit</a>
				<a href=\"example_d.php?id={$row['blog_id']}\">Delete</a>
				</p><hr />";
			}
		// close the connection
		mysql_close($link);
		}
		else {
			die ("<p>Could not retrieve records because: ". mysql_error(). "</p>");
		}
	}
	else {
		die ("<p>Could not connect to MySQL because: ". mysql_error(). "</p>");
	}
	echo "<p><a href=\"$_SERVER[SCRIPT_NAME]\">Try again?</a></p>\n";
	echo "<p><a href=\"example_i.php\">Insert data?</a></p>\n";
	echo "<p><a href=\"example_u.php\">Update data?</a></p>\n";
	echo "<p><a href=\"example_d.php\">Delete data?</a></p>\n";

}

?>
</p>
</body>
</html>

View the effect


PHP and MySQL | Introduction | SQL Commands | Connect to MySQL | Creating and Selecting a Database | Creating a Table | Inserting Data into a Database | Retrieving Data from a Database | Updating data in a Database | Deleting Data in a Database
© 2008: Hann So
email: hso@voyager.deanza.edu