470,870 Members | 1,742 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ


PHP MySQL: Starting Off

By Blair Ireland
Senior Editor, TheScripts.com

PHP and Accessing MySQL Database Data

If you are new to the TheScripts.com series of tutorials on PHP and MySQL tutorials, I will restate a few things...

This tutorial is based on MySQL, available at http://www.mysql.com/. It's free for Unix based systems, but Windows users can get the shareware copy. You are asked to pay if you use the windows version.

Once you know you got MySQL installed on your server, and your account is setup to use it, I would recommend a group of PHP scripts to administer your databases. This application is called phpMyAdmin 2.0.2 and is available at http://www.htmlwizard.net/phpMyAdmin/. This application makes life a breeze.... you won't need to remember all of the syntax for MySQL with it.... but I would still recommend knowing the syntax for a general understanding. After all, what you know can help you in the future.

Ok, we now have our data in the database (if you don't know what I'm talking about, see last weeks informant)... so how do we actually put this data to use?

Well, we will first off be using the same database that has been used for this entire series of PHP 3.0 tutorials....

mysql> CREATE TABLE info (
> id INT NOT NULL AUTO_INCREMENT,
> name VARCHAR (50),
> email VARCHAR (50),
> opinion VARCHAR (30),
> PRIMARY KEY (id));

So we have our names. email addresses and opinions all in the database. Now we want to access them..... so in this case, we will search for all the users that have stated that the site is great. So what does this syntax look like? Something like this....

<html>
<head><title> How to Grab your MySQL Data </title></head>
<body bgcolor="#FFFFFF">

<?
/* declare some relevant variables */
$DBhost = "Your-MySQL-servers-IP-or-domainname";
$DBuser = "your user name";
$DBpass = "Your Password";
$DBName = "The Name of the Database";
$table = "info";

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable toconnect to database");
@mysql_select_db("$DBName") or die("Unable to select database $DBName"); $sqlquery = "SELECT * FROM $table WHERE opinion = 'is great'";
$result = mysql_query($sqlquery);
$number = mysql_numrows($result);

$i = 0;

if ($number < 1) {
print "<CENTER><P>There Were No Results for Your Search</CENTER>";
}
else {
while ($number > $i) {
$thename = mysql_result($result,$i,"name");
$theemail = mysql_result($result,$i,"email");
print "<p><b>Name:</b> $thename<br><b>E-Mail:</b>
$theemail</p>";
$i++;
}
}
?>
</BODY></HTML>

Lets save this as listing.php3.

Now, say you didn't want to show all of these results.... could get boring. If you just wanted to retrieve a certain number or rows from the database, or a specific range of rows, the syntax would be something like this...

$sqlquery = "SELECT * FROM $table LIMIT 5,10";

This would grab the rows 6 to 15... as the first number is the offset, and the second number is the length. If you just wanted to grab the first 5 rows though, it would look like,

$sqlquery = "SELECT * FROM $table LIMIT 5";

So one argument given means that it is just stating the maximum number of rows to return. Snazzy eh? Not really.

Let me explain some of this other stuff though, like the while loop in listing.php3

The syntax $number = mysql_numrows($result); gets the number of rows resulting from the query. This helps us within the while statement, as it lets us know how many results there are so we know when you stop looping. The first results is located in 0, so that is why $i starts at zero, and steadily increases to list all of the results. If you don't understand that, I would recommend reading this paragraph until you do. It's kind of important, and an important thing to know.

The line : $thename = mysql_result($result,$i,"name"); is just saving the data from the table column "name" in that particular row to the variable, $thename. There are other ways to get this data, but I thought this method would be the easiest to understand for beginners. The $result part is stating the array to look for this information, the $i is the row number we are fetching (see the while loop explanation), and the "name" part just asks for the column you are looking for.

Anyhow, that is an extremely basic example on how to grab your database information, which you should now be able to mirror and modify for your own PHP projects. Hope I didn't lose you anywhere......

  Advanced Methods »