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