As in many languages, there are 150 different ways to do just about everything, the same rule applies for a simple connection and output of data in a mySQL database using PHP. This tutorial will give you only one way of accomplishing this, and while it works, it is definitely not the only way.
We'll assume you already have some sort of database setup, whether it's from a pre-installed script, or a database of your own creation, it doesn't matter. It should at least have a few records in it for testing purposes.
For the sake of this tutorial, we will place all of the code in one file, however, it is good practice to place the connection variables and connection string in a separate config type file which can be included each time you make the database connection.
So lets get started, first we should define out server, username, password, and the database we will be connecting to. The easiest way to do this is with a few variables:
<?php // MySQL Connection Information $server = "localhost"; // The name of your server $username = "username"; // The MySQL username $password = "password"; // The MySQL password $dbname = "database_name"; // The name of the database ?>
Once we have our connection variables, we can go ahead and make the physical connection to the database server and select the database to use:
<?php
// mySQL Connect String
$connection = mysql_connect("$server","$username","$password");
// Select the database
mysql_select_db("$dbname");
?>
Alrighty, so now we've got an open connection to the database, from this point we can pass SQL commands. For this tutorial we'll just be using a SELECT statement. However, this is where you would put your INSERT, UPDATE, or DELETE statement depending on what you were trying to accomplish.
<?php
$sql = "SELECT id, username, email, posts FROM members;";
// execute SQL query and get result
$sql_result = mysql_query($sql,$connection)
or die("Query $sql failed: " . mysql_error());
?>
This will result in all records from the 'members' table being retrieved and stored in the $sql_result variable. Since we've selected the id, username, and email columns, we will need to pull them back out in to a usable form. This is done using the mysql_fetch_array function:
<?php
// Loop through the data set and extract each row in to its own variable set
while ($row = mysql_fetch_array($sql_result)) {
extract($row);
?>
This is the beginning of the loop which will pull each row from the $sql_result variable. Each time the while loop runs, it goes to the next row. You will notice we are using extract($row) in the above code, what does this do you ask? In most tutorials you read, data in each column is referenced like this: $row['username'], well, that's one too many keys to press, so by using extract(), we are able to pull each column in to its own variable. So, instead of $row['username'], we use $username to reference data in the username column. Now, let's take the piece of code above, and make it actually show us something:
<?php
// Loop through the data set and extract each row in to its own variable set
while ($row = mysql_fetch_array($sql_result)) {
extract($row);
?>
<li>Username: <?php echo $username; ?> (<?php echo $email; ?>)
<?
// End while loop
}
?>
So what exactly did we just do? We looped through the entire dataset and each time we made a bullet with the username and the email address in parentheses:
Quote
Username (email@domain.com)
Username2 (email2@domain.com)
Username3 (email3@domain.com)
Username2 (email2@domain.com)
Username3 (email3@domain.com)
You'll notice that instead of using one big echo, like this:
echo ("Username: $username ($email)");
We instead opted to close our PHP tag and write basic HTML with nested <?php echo $variable; ?> statements inside. This makes reading the code a little easier, and also makes the code process faster because only code in between the <?php and ?> is sent to PHP.
So that's it, you've connected to the database, executed a SQL statement, retrieved the records, and displayed them using the actual column name as the variable.
MultiQuote
Posted 31 August 2006 - 12:45 AM
| 