Forums

Downloads

Bookmarks

Contact Us

One of the great things about PHP is the fact that is has many things that it can do. It can do things such as manage bank accounts, allow users to choose themes, or write client side programs that actually run on your computer (Check out PHP Gtk)

In this article, I will show you how to get data from a MySql database. I have commented the code with much detail so that it makes sense to you. Most of this article will be explained in the actual code below.

Before we run this code, you will need the following:

1. A MySql Database and access to the structure so that you can set it up
2. Run the following code to set up the database structure:

------------------------
CREATE TABLE `users` (
`id` INT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 32 ) NOT NULL ,
`email` VARCHAR( 75 ) NOT NULL
) TYPE = MYISAM ;
------------------------

Once you have ran the above code to create your database table, you may proceed with the rest of the article.

------------------------
<?php
// MySql_Query.txt

// This is the source code for the PHP - Work With MySql article

# Connect to MySql

/*
* It is best to put the database connection code in another file named
* something like connect.inc.
*
* The reason is because you end up needing the database connection from
* multiple pages, so create it once and use it in multiple places.
*
* // connect to the database server (which holds multiple databases)
* @mysql_connect("localhost", "username", "password") or die(mysql_error());
*
* // connect to the database you need
* @mysql_select_db("mysqltutorial") or die(mysql_error());
*
*/

// If you created the above database connection script in a different file,
// you would instead use the include function like I do here:

# Connect to the database:
// this included file uses the code that is commented above
include("connect.inc");

# Get data from the MySql Database:
// set up the query that needs to be executed
$query = "select * from users where username='kris'";

// execute the query or die (show the error message)
$result = @mysql_query($q) or die(mysql_error());

// put the data from the query into an array
$array = @mysql_fetch_array($query);

// get the number of rows that the query returns
$num = @mysql_num_rows($query);

// keep in mind that there are 3 fields: id, users, and email
// get the username that you specify above

if($num > 0){ // if the number of rows is greater than 0

echo $array['username'];

}else{

echo "There are no records matching your query";

}

?>
------------------------

In the above code, I color coded the important stuff so that it would be easier to follow. Variables are blue, functions are green, and strings or statements are red. I won't go into depth here about what is going on up there because the comments in the code do that. This is something you will have to get used to - programmers only have comments to go by when they are reading someone else's code.

Explanations:

What does the "@" do before the mysql_query's?
The @ is simply an error suppressor. This makes it so that if there is a problem with a query, it doesn't tell the whole world what is going on. This is very necessary since not putting them creates a security risk - an error tends to give the whole path to the file on the server. If you want the error to show up securely, use the die(mysql_error()) functions.

What is an array?
An array is a variable that can store multiple strings of information. When you get data from a database, all of the data is stored in an array. You an get the data you want from the array by telling the array what to give you. For example, in the above query, the array is called $array. The info you get from the array will be shown like so: $array['info']. If you want data from the username field, you would use $array['username']. If you want the email address, you would use $array['email'].

How do I display info from multiple rows?
This is a little more work, but is still easy to do. You would do just like we did above, but using a while() loop. For example:

To start, instead of using the query "select * from users where username='kris'", let's just try “select * from users" in its place.

------------------------

<?php
if($num > 0){ // if the number of rows is greater than 0

do{ // start the loop

// show the names (the ."<br />" just adds a line break to the line
echo $array['username']."<br />";

// as long as there is a result being returned, show it.
}while($result = @mysql_fetch_array($query));


}else{

echo "There are no records matching your query";

}

?>

------------------------

Although it may take a while for you to understand how this works, or to remember it all, just keep working with it.. Eventually you will be surprising yourself with what you can do! Believe it or not, but at the time of this article, I have only worked with PHP for about a year.

The source code for this article can be downloaded at http://www.kasl.info/source