| Maggie Simpson | 555-3142 |
+----------------+----------+
5 rows in set (0.00 sec)
Looking good so far? Excellent.
Creating a Database User
We've created the database and put some data in there, now we must create a user
account. This user will have access to this database. We create the user and grant
privileges with the GRANT command:
mysql> GRANT usage
-> ON example.*
-> TO webuser@localhost;
Query OK, 0 rows affected (0.15 sec)
13
This creates a new user called webuser. This user can connect only from localhost, and he
has the ability to connect to the example database. Next we have to specify what
operations webuser can perform:
mysql> GRANT select, insert, delete
-> ON example.*
-> TO webuser@localhost;
Query OK, 0 rows affected (0.00 sec)
This gives webuser the ability to execute SELECT, INSERT, and DELETE queries on every
table of the example database. Our work is done here, exit the MySQL client:
mysql> exit
Bye
14
Web DB Example Part 2
We've created the database, now let's make the PHP scripts that form the guts of our web
database example.
Creating the PHP Scripts
To keep things really simple, we will just create two scripts: one that lists all the entries in
the database, and one that allows us to add new entries.
index.php3
Create a new directory called example in your web directory. Note, if you installed the
Apache 1.3.3 RPM, your home pages are stored in /home/httpd/html, not htdocs.
# cd /home/httpd/htdocs
# mkdir example
Next, create a file called index.php3 in this directory. It should contain:
Web Database Sample Index
Data from mytable
mysql_connect("localhost", "webuser", "");
$query = "SELECT name, phone FROM mytable";
$result = mysql_db_query("example", $query);
if ($result) {
echo "Found these entries in the database:";
while ($r = mysql_fetch_array($result)) {
$name = $r["name"];
$phone = $r["phone"];
echo "$name, $phone";
}
echo "";
} else {
echo "No data.";
}
mysql_free_result($result);
?>
Add new entry
15
add.php3
Next, we create add.php3 in the same directory. This script does two things, first it will
prompt the user for information to add to the database. Second, it will add this information
to the database. This second function is normally put in a separate file, but it is so easy to
do that we cram them both into one PHP script:
Web Database Sample Inserting
mysql_connect("localhost", "webuser", "");
if (isset($name) && isset($phone)) {
$query = "INSERT INTO mytable VALUES ('$name', '$phone')";
$result = mysql_db_query("example", $query);
if ($result) {
echo "$name was added to the database";
}
}
?>
Add an entry
Name:
Phone:
Back to index
That's it, nice and simple. Now let's test it.
16
Web DB Example Part 3
All the work is done, let's see if it works! Make sure everyone has read permissions (chmod
ugo+r) for these files, otherwise you'll get an Access Denied error message from Apache.
You should now be able to use your web browser to access what you've just created.
Assuming you're using lynx:
$ lynx http://localhost/example
This should bring up a page that lists all the entries you added to the database earlier. For
example:
Found these entries in the database:
* Homer Simpson, 555-1234
* Bart Simpson, 555-4321
* Lisa Simpson, 555-3214
* Marge Simpson, 555-2314
* Maggie Simpson, 555-3142
Add new entry
Select the Add new entry link, and you should go to a page that allows you to enter data:
Name: ____________________
Phone: ____________________
Submit
Back to index
Enter something and select the Submit link. This submits the filled-out form back to the
same PHP script. This time, there are values in the two fields name and phone, so these
get added to the database.
You should see a message telling you something was added to the database. Select the
Back to index link to confirm that your new entry is in fact there.
If everything worked, then congratulations! You've completed this howto and built your first
web database application! It was pretty easy wasn't it?
17
Conclusion
This concludes this step-by-step howto. You've now had a peek at the power and relative
ease of building a web database from scratch. We've only scratched the surface, so at this
point you should go on to the Apache, PHP, and MySQL homepages to read up on their
documentation.
Of particular interest is the MySQL documentation. You should find out and understand the
limitations and compatibility issues of using this product. While it has excellent
performance and really easy to use, it has it's limitations. For example, as of this writing
there is no support for nested selects.
You will also find a lot of tutorials by searching on the Internet. Some good places to start
are:
" Developer's Shed (http://www.devshed.com)
" PHP Homepage (http://www.php.net)
By now, you should have a pretty good understanding of how everything fits together. Now
you should be able to get a good feel for any other tool you decide to use, because the
underlying concepts are pretty much the same.
I hope you've found this document useful!
18 [ Pobierz całość w formacie PDF ]