Thư viện tri thức trực tuyến
Kho tài liệu với 50,000+ tài liệu học thuật
© 2023 Siêu thị PDF - Kho tài liệu học thuật hàng đầu Việt Nam
PHP 5 Recipes A Problem-Solution Approach 2005 phần 10 pptx
Nội dung xem thử
Mô tả chi tiết
//The next thing you must do is select a database.
try {
if (!mysql_select_db ($whichdb,$db)){
throw new exception ("Sorry, database could not be opened.");
}
} catch (exception $e) {
echo $e->getmessage();
}
}
//A function to close the connection to MySQL.
function closedatabase ($db){
//When you finish up, you have to close the connection.
mysql_close ($db);
}
//First, open a connection to the database.
$db = opendatabase ("localhost","apress","testing");
//Then select a database.
selectdb ("cds",$db);
//Now, let's create a script to output the information found within the table.
if ($aquery = mysql_query ("SELECT * FROM cd ORDER BY cdid ASC")){
//You can loop through the rows in the table, outputting as you go.
while ($adata = mysql_fetch_array ($aquery)){
echo "ID: " . $adata['cdid'] . "<br />";
echo "Title: " . stripslashes ($adata['title']) . "<br />";
echo "Artist: " . stripslashes ($adata['artist']) . "<br />";
echo "-------------------------------<br />";
}
} else {
echo mysql_error();
}
//Then close the database.
closedatabase ($db);
?>
ID: 1
Title: Chuck
Artist: Sum 41
-------------------------------
ID: 2
Title: Meteora
Artist: Linkin Park
556 15-3 ■ RETRIEVING AND DISPLAYING RESULTS
5092_Ch15_FINAL 8/26/05 10:00 AM Page 556
-------------------------------
ID: 3
Title: Mezmerize
Artist: System of a Down
-------------------------------
ID: 4
Title: Greyest of Blue Skies
Artist: Finger Eleven
-------------------------------
How It Works
The major difference between this code sample and the previous one is that you have placed
the selection of a database into a function for portability. Past that, you simply check if the
query is valid (and if not, echo the error) and then loop through all the resulting rows that are
returned from the mysql_fetch_array() function. As you loop through the different rows, you
can output the value of the row in the array by referencing the name of the field in the database. By doing this, as you can see from the results, you can succeed in outputting the entire
contents of the cd table.
15-4. Modifying Data
Obviously, database functionality would be pretty useless if the data stored in the database
itself could only remain static. Luckily, MySQL provides you with a means to modify certain
data. The general method for modifying a set of data is to reference a unique record ID number and then change the row if it matches the ID argument. Let’s say, for instance, you want to
change the information contained within the record for the Linkin Park album currently in the
database. By looking at the previous results, the Linkin Park dataset looks as follows:
ID: 2
Title: Meteora
Artist: Linkin Park
Since you know that the ID number for that particular row is 2, you can easily modify the
record based on that argument, as shown in the following example.
The Code
<?php
//sample15_4.php
//A function to open a connection to MySQL.
function opendatabase ($host,$user,$pass) {
//Attempt to open a connection to MySQL.
try {
15-4 ■ MODIFYING DATA 557
5092_Ch15_FINAL 8/26/05 10:00 AM Page 557
//And then supply them to the mysql_connect() function.
if ($db = mysql_connect ($host,$user,$pass)){
//Return the identifier.
return $db;
} else {
throw new exception ("Sorry, could not connect to mysql.");
}
} catch (exception $e) {
echo $e->getmessage ();
}
}
function selectdb ($whichdb, $db){
//The next thing you must do is select a database.
try {
if (!mysql_select_db ($whichdb,$db)){
throw new exception ("Sorry, database could not be opened.");
}
} catch (exception $e) {
echo $e->getmessage();
}
}
//A function to close the connection to MySQL.
function closedatabase ($db){
//When you finish up, you have to close the connection.
mysql_close ($db);
}
//First, open a connection to the database.
$db = opendatabase ("localhost","apress","testing");
//Then select a database.
selectdb ("cds",$db);
//Create a query to modify the Linkin Park record.
$updatequery = "UPDATE cd SET title='Hybrid Theory' WHERE cdid='2'";
//Then attempt to perform the query.
try {
if (mysql_query ($updatequery, $db)){
echo "Your record has been updated.";
//Now, let's output the record to see the changes.
if ($aquery = mysql_query ("SELECT * FROM cd WHERE cdid='2'")){
$adata = mysql_fetch_array ($aquery);
echo "<br />Title: " . stripslashes ($adata['title']);
} else {
558 15-4 ■ MODIFYING DATA
5092_Ch15_FINAL 8/26/05 10:00 AM Page 558
echo mysql_error();
}
} else {
throw new exception (mysql_error());
}
} catch (exception $e) {
echo $e->getmessage();
}
//Then close the database.
closedatabase ($db);
?>
Naturally, you can test to ensure the change is valid. The results of a successful transaction are as follows:
Your record has been updated.
Title: Hybrid Theory
How It Works
As you can see, by performing a query that will update the record (with the ID as the defining
attribute of the row), you can quickly and efficiently change a record at your whim. In this
case, you merely changed the title of the album to another album and then outputted the
change. Note that when you displayed the changed result, you specified which row you
wanted to see, again via the ID number.
15-5. Deleting Data
Removing data is largely the same as updating data. You will definitely want to specify which
record you are attempting to remove, as you can quite easily lose an entire table if you are not
careful. The following example enables you to remove a record from your table. Should you
want to remove an entire table’s contents, simply leave out the where clause in the SQL code.
The Code
<?php
//sample15_5.php
//A function to open a connection to MySQL.
function opendatabase ($host,$user,$pass) {
//Attempt to open a connection to MySQL.
try {
//And then supply them to the mysql_connect() function.
if ($db = mysql_connect ($host,$user,$pass)){
15-5 ■ DELETING DATA 559
5092_Ch15_FINAL 8/26/05 10:00 AM Page 559
//Return the identifier.
return $db;
} else {
throw new exception ("Sorry, could not connect to mysql.");
}
} catch (exception $e) {
echo $e->getmessage ();
}
}
function selectdb ($whichdb, $db){
//The next thing you must do is select a database.
try {
if (!mysql_select_db ($whichdb,$db)){
throw new exception ("Sorry, database could not be opened.");
}
} catch (exception $e) {
echo $e->getmessage();
}
}
//A function to close the connection to MySQL.
function closedatabase ($db){
//When you finish up, you have to close the connection.
mysql_close ($db);
}
//First, open a connection to the database.
$db = opendatabase ("localhost","apress","testing");
//Then select a database.
selectdb ("cds",$db);
//Create a query to remove the recently modified Linkin Park record.
$updatequery = "DELETE FROM cd WHERE cdid='2'";
//Then attempt to perform the query.
try {
if (mysql_query ($updatequery, $db)){
echo "Your record has been removed.";
//Now, let's output the record to see the changes.
if ($aquery = mysql_query ("SELECT * FROM cd WHERE cdid='2'")){
//You will notice that the record has been removed.
echo "<br />" . mysql_num_rows ($aquery); //Should output a 0.
} else {
echo mysql_error();
}
560 15-5 ■ DELETING DATA
5092_Ch15_FINAL 8/26/05 10:00 AM Page 560
} else {
throw new exception (mysql_error());
}
} catch (exception $e) {
echo $e->getmessage();
}
//Then close the database.
closedatabase ($db);
?>
If everything goes well, you should receive a response to the screen that looks something
like this:
Your record has been removed.
0
How It Works
As you can see, the vast majority of the work that went into modifying this piece of code from
the previous example was in the SQL statement. Rather than using the update statement, you
use the delete statement and specify the record you want to remove. To prove that the record
is indeed gone, you can use the mysql_num_rows() function, which specifies the number of
rows that has been returned from a select statement. Be careful when using the delete statement, as data removed in this way cannot be returned. The prototype for mysql_num_rows() is
as follows:
int mysql_num_rows ( resource result )
15-6. Building Queries on the Fly
You will have plenty of opportunities to build a query on the fly. A fairly common example is
receiving data from a form that will allow you to log into your account. While the functionality
behind this is useful and rather powerful, it is also the preferred method for crackers to gain
entry into your system. By using a technique known as SQL injection, malicious users can
insert potentially dangerous code into your dynamic queries that could, in turn, allow them
to damage your data, pull all the information from your database, or destroy the database in
its entirety. Therefore, it is important that, when building dynamic queries, you take the necessary efforts to ensure all received data is stripped of potentially hazardous characters. The
following example will receive posted values (from a form) and log them in accordingly if they
have the right username and password.
15-6 ■ BUILDING QUERIES ON THE FLY 561
5092_Ch15_FINAL 8/26/05 10:00 AM Page 561
For this particular recipe, set up a new table in the cds database called userlogin. The
userlogin table structure is as follows:
userloginid INT AUTO_INCREMENT PRIMARY KEY
username TINYTEXT
password TINYTEXT
This table has one row with the following information:
1 apress testing
The Code
<?php
//sample15_6.php
//A function to open a connection to MySQL.
function opendatabase ($host,$user,$pass) {
//Attempt to open a connection to MySQL.
try {
//And then supply them to the mysql_connect() function.
if ($db = mysql_connect ($host,$user,$pass)){
//Return the identifier.
return $db;
} else {
throw new exception ("Sorry, could not connect to mysql.");
}
} catch (exception $e) {
echo $e->getmessage ();
}
}
function selectdb ($whichdb, $db){
//The next thing you must do is select a database.
try {
if (!mysql_select_db ($whichdb,$db)){
throw new exception ("Sorry, database could not be opened.");
}
} catch (exception $e) {
echo $e->getmessage();
}
}
//A function to close the connection to MySQL.
function closedatabase ($db){
//When you finish up, you have to close the connection.
mysql_close ($db);
}
562 15-6 ■ BUILDING QUERIES ON THE FLY
5092_Ch15_FINAL 8/26/05 10:00 AM Page 562
//First, open a connection to the database.
$db = opendatabase ("localhost","apress","testing");
//Then select a database.
selectdb ("cds",$db);
//Now, assume you received these values from a posted form.
$_POST['user'] = "apress";
$_POST['pass'] = "testing";
function validatelogin ($user,$pass){
//First, remove any potentially dangerous characters.
mysql_real_escape_string ($user);
mysql_real_escape_string ($pass);
//Next, check the user and pass against the database.
$thequery = "SELECT * FROM userlogin WHERE➥
username='$user' AND password='$pass'";
//Now, run the query.
if ($aquery = mysql_query ($thequery)){
//Now, you can check for a valid match using the➥
mysql_num_rows() function.
if (mysql_num_rows ($aquery) > 0){
return true;
} else {
return false;
}
} else {
echo mysql_error();
}
}
//Now, let's attempt to validate the login.
if (validatelogin ($_POST['user'],$_POST['pass'])){
echo "You have successfully logged in.";
} else {
echo "Sorry, you have an incorrect username and/or password.";
}
//Then close the database.
closedatabase ($db);
?>
15-6 ■ BUILDING QUERIES ON THE FLY 563
5092_Ch15_FINAL 8/26/05 10:00 AM Page 563
How It Works
As you can see, building a dynamic query is not all that difficult. The most important aspect
when building the query is to remember to validate the data submitted in the query. The function mysql_real_escape_string() is necessary when dealing with string type values (as in this
case), and the function intval() can help you when dealing with numerical values by ensuring a valid numerical response. Apart from that, you can treat a dynamic query just as you
would treat a string. By using string functionality, you can dynamically build the query of
your choice.
The mysqli Extension vs. the PHP 4 MySQL
Extension
Over time, the mysql extension contained with PHP has performed, generally, quite well. However, certain features began to get implemented into newer versions of MySQL that began to
showcase a few flaws with the mysql extension. Now, with the advent of PHP 5, a few problems
have occurred (mostly with default and automatic connections). To combat these issues and
bring the mysql library into the PHP 5 way of thinking (which is object-oriented), a new extension has been established, the mysqli extension.
The mysqli extension (developed by Georg Richter), which is an object-oriented version
of the mysql extension, can use the new MySQL 4.1+ functionality to improve the speed, diversity, and functionality of PHP’s connection with MySQL. To make the mysqli extension work in
PHP, you must add the following line to the extensions area of the php.ini file:
extension=php_mysqli.dll
Now, you have to make sure you are using MySQL 4.1 or higher to implement the new
extension. From there it is simply a matter of getting familiar with some new syntaxes and
concepts, which will be explained as you go through this chapter.
15-7. Using the mysqli Object-Oriented API
Using the new object-oriented application programming interface (API) in the mysqli extension is really no big deal for those familiar with using objects. Basically, you create an instance
of a mysqli object and use its methods rather than simply using the mysql extension’s functions. The syntax is naturally a little different, but the concept behind it is easy to understand.
The following example guides you through several new syntaxes and a couple of the new algorithms you can perform with the mysqli extension.
The Code
<?php
//sample15_7.php
//The first thing you need to do, like any other time is➥
connect to the mysql server.
//You can do so by creating a new mysqli instance.
564 15-7 ■ USING THE MYSQLI OBJECT-ORIENTED API
5092_Ch15_FINAL 8/26/05 10:00 AM Page 564