Using mysql_fetch_array fanc
PMA03:51
we accessed attributes in order using the foreach loop statement. In
many cases, you'll also want to access the attributes in another way, and this
is usually best achieved by using the attribute names themselves. It's much
easier to remember that you want to show the user the vintage year, the wine's
name, the varieties, and the price, than to remember you want to show attributes
four, two, six, and one from the SELECT statement. It's also a much
better programming methodology because your code will be independent of the
structure of the SQL statement and it'll be more readable. What's more, it's
faster to access only the values you need.
$result = mysql_query("SELECT winery_name, phone, fax FROM winery");
while($row = mysql_fetch_array($result))
{
print "The {$row["winery_name"]} winery's fax is {$row["fax"]}".
print "Their phone is {$row["phone"]}.\n";
}
MySQL Database Using PHP
PMA03:45
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Wines</title>
</head>
<body> <pre>
<?php
// (1) Open the database connection
$connection = mysql_connect("localhost","fred","shhh");
// (2) Select the winestore database
mysql_select_db("winestore", $connection);
// (3) Run the query on the winestore through the connection
$result = mysql_query ("SELECT * FROM wine", $connection);
// (4) While there are still rows in the result set, fetch the current
// row into the array $row
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{ // (5) Print out each element in $row, that is, print the values of
// the attributes
foreach ($row as $attribute)
print "{$attribute} ";
// Print a carriage return to neaten the output print "\n"; }
?>
</pre>
</body>
</html>
<title>Wines</title>
</head>
<body> <pre>
<?php
// (1) Open the database connection
$connection = mysql_connect("localhost","fred","shhh");
// (2) Select the winestore database
mysql_select_db("winestore", $connection);
// (3) Run the query on the winestore through the connection
$result = mysql_query ("SELECT * FROM wine", $connection);
// (4) While there are still rows in the result set, fetch the current
// row into the array $row
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{ // (5) Print out each element in $row, that is, print the values of
// the attributes
foreach ($row as $attribute)
print "{$attribute} ";
// Print a carriage return to neaten the output print "\n"; }
?>
</pre>
</body>
</html>
Connect to the server with the MySQL function mysql_connect(
) .We use three parameters here: the
hostname of the database server, a username, and a password. Let's assume here
that MySQL is installed on the same server as the scripting engine and,
therefore, localhost is the hostname. If the servers are on different
machines, you can replace localhost with the domain name of the
machine that hosts the database server.
The function mysql_connect( ) returns a connection resource that is used later to work with the
server. Many server functions return resources that you pass to further calls.
In most cases, the variable type and value of the resource isn't important: the
resource is simply stored after it's created and used as required.
Configuring the Connection Mysql
PMA01:31
<?php
try
{
$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'ijdbuser',
'mypassword');
}
catch (PDOException $e)
{
$output = 'Unable to connect to the database server.';
include 'output.html.php';
exit();
}
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
If PHP is unable to connect to your MySQL server, or if the username and password
you provided are incorrect, you’ll instead see a similar screen to that in Figure 4.7.
To make sure your error-handling code is working properly, you might want to
misspell your password intentionally to test it out.
When an exception is caught, the value stored
in that variable is actually another PHP object; in fact, all exceptions are represented
by PHP objects! Like the PDO object we have stored in $pdo, the PDOException object
has properties we can access and methods we can call.
try
{
$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'ijdbuser',
'mypassword');
}
catch (PDOException $e)
{
$output = 'Unable to connect to the database server.';
include 'output.html.php';
exit();
}
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
If PHP is unable to connect to your MySQL server, or if the username and password
you provided are incorrect, you’ll instead see a similar screen to that in Figure 4.7.
To make sure your error-handling code is working properly, you might want to
misspell your password intentionally to test it out.
When an exception is caught, the value stored
in that variable is actually another PHP object; in fact, all exceptions are represented
by PHP objects! Like the PDO object we have stored in $pdo, the PDOException object
has properties we can access and methods we can call.
MySQL Data on the Web
PMA01:28
Before we leap forward, it’s worth taking a step back for a clear picture of our ultimate
goal. We have two powerful tools at our disposal: the PHP scripting language
and the MySQL database engine. It’s important to understand how these will fit
together.
The whole idea of a database driven website is to allow the content of the site to
reside in a database, so that content may be pulled dynamically from the database
to create web pages for viewing on a regular browser. So, at one end of the system
you have a visitor to your site using a web browser to request a page. That browser
expects to receive a standard HTML document in return.
Just so it’s clear and fresh in your mind, this is what happens when there’s a visitor
to a page on your database driven website:
1. The visitor’s web browser requests the web page from your web server.
2. The web server software (typically Apache) recognizes that the requested file
is a PHP script, so the server fires up the PHP interpreter to execute the code
contained in the file.
3. Certain PHP commands (which will be the focus of this chapter) connect to the
MySQL database and request the content that belongs in the web page.
4. The MySQL database responds by sending the requested content to the PHP
script.
5. The PHP script stores the content into one or more PHP variables, then uses
echo statements to output the content as part of the web page.
6. The PHP interpreter finishes up by handing a copy of the HTML it has created
to the web server.
7. The web server sends the HTML to the web browser as it would a plain HTML
file, except that instead of coming directly from an HTML file, the page is the
output provided by the PHP interpreter. The browser has no way of knowing
this, however. From its perspective, it’s requesting and receiving a web page
like any other.
Here’s how you use PDO to establish a connection to a MySQL server:
new PDO('mysql:host=hostname;dbname=database', 'username',
'password')
goal. We have two powerful tools at our disposal: the PHP scripting language
and the MySQL database engine. It’s important to understand how these will fit
together.
The whole idea of a database driven website is to allow the content of the site to
reside in a database, so that content may be pulled dynamically from the database
to create web pages for viewing on a regular browser. So, at one end of the system
you have a visitor to your site using a web browser to request a page. That browser
expects to receive a standard HTML document in return.
Just so it’s clear and fresh in your mind, this is what happens when there’s a visitor
to a page on your database driven website:
1. The visitor’s web browser requests the web page from your web server.
2. The web server software (typically Apache) recognizes that the requested file
is a PHP script, so the server fires up the PHP interpreter to execute the code
contained in the file.
3. Certain PHP commands (which will be the focus of this chapter) connect to the
MySQL database and request the content that belongs in the web page.
4. The MySQL database responds by sending the requested content to the PHP
script.
5. The PHP script stores the content into one or more PHP variables, then uses
echo statements to output the content as part of the web page.
6. The PHP interpreter finishes up by handing a copy of the HTML it has created
to the web server.
7. The web server sends the HTML to the web browser as it would a plain HTML
file, except that instead of coming directly from an HTML file, the page is the
output provided by the PHP interpreter. The browser has no way of knowing
this, however. From its perspective, it’s requesting and receiving a web page
like any other.
Here’s how you use PDO to establish a connection to a MySQL server:
new PDO('mysql:host=hostname;dbname=database', 'username',
'password')
Create Dynamic Database Access Objects
PMA05:23
Some simple PHP that makes for a surprisingly robust script
$rec = new DBRecord( "author", 2 );
print $rec->Name()."\n";
?>
PHP 5 represents a substantial upgrade in terms of object-oriented support in the PHP language. Along with a number of upgrades in performance, PHP 5 has a major upgrade in the ability to create dynamic classes. These are classes where the methods and attributes change from object to object. This can be very handy in building database applications.
<?php
require_once( "DB.php" );
$dsn = 'mysql://root:password@localhost/books';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
class DBRecord
{
var $h;
public function DBRecord( $table, $id )
{
global $db;
$res = $db->query( "SELECT * from $table WHERE id=?", array( $id ) );
$res->fetchInto( $row, DB_FETCHMODE_ASSOC );
$this->{'h'} = $row;
}
public function _ _call( $method, $args )
{
return $this->{'h'}[strtolower($method)];
}
public function _ _get( $id )
{
return $this->{'h'}[strtolower($id)];
}
}
?>
A simple script that tests the database access script
<?php require_once( "DBrecord.php" );$rec = new DBRecord( "author", 2 );
print $rec->Name()."\n";
?>
PHP 5 represents a substantial upgrade in terms of object-oriented support in the PHP language. Along with a number of upgrades in performance, PHP 5 has a major upgrade in the ability to create dynamic classes. These are classes where the methods and attributes change from object to object. This can be very handy in building database applications.
Convert CSV to PHP
PMA05:21
Every once in a while, I have a static list of values that I
don't want to put into a database, but that I do want to use in my PHP
application. That static data can come from a variety of sources, but often it's
in a spreadsheet. This handy hack converts any CSV
data (one of the easiest formats to pull from a spreadsheet) to PHP code that I
can then copy and paste into my PHP page.
<html> <body> <form method="post" action="commaconv.php" /> <table> <tr><td>CSV Data:</td> <td><textarea name="data" cols="40" rows="10"> "Alabama",4530182 "Alaska",655435 "Arizona",5743834 "Arkansas",2752629 "California",35893799 "Colorado",4601403 "Connecticut",3503604 "Delaware",830364 "District of Columbia",553523 "Florida",17397161 "Georgia",8829383 "Hawaii",1262840 "Idaho",1393262 "Illinois",12713634 "Indiana",6237569 "Iowa",2954451 "Kansas",2735502 "Kentucky",4145922 "Louisiana",4515770 "Maine",1317253 "Maryland",5558058 "Massachusetts",6416505 "Michigan",10112620 "Minnesota",5100958 "Mississippi",2902966 "Missouri",5754618 "Montana",926865 "Nebraska",1747214 "Nevada",2334771 "New Hampshire",1299500 "New Jersey",8698879 "New Mexico",1903289 "New York",19227088 "North Carolina",8541221 "North Dakota",634366 "Ohio",11459011 "Oklahoma",3523553 "Oregon",3594586 "Pennsylvania",12406292 "Rhode Island",1080632 "South Carolina",4198068 "South Dakota",770883 "Tennessee",5900962 "Texas",22490022 "Utah",2389039 "Vermont",621394 "Virginia",7459827 "Washington",6203788 "West Virginia",1815354 "Wisconsin",5509026 "Wyoming",506529</textarea></td></tr> <tr><td> Field Name 1:</td><td><input name="field0" value="state" /></td></tr> <tr><td> Field Name 2:</td><td><input name="field1" value="population" /></td> </tr> <tr><td> Field Name 3:</td><td><input name="field2" value="" /></td></tr> <tr><td> Field Name 4:</td><td><input name="field3" value="" /></td></tr> <tr><td> Field Name 5:</td><td><input name="field4" value="" /></td></tr> </table> <input type="submit" /> </form> </body> </html>
PHP that converts data from CSV just as easily as from XML or SQL
<html><body>
<div style="font-family:courier; font-size:small;">
$data = array(<br/>
<?
$fieldnames = array(
$_POST['field0' ],
$_POST['field1' ],
$_POST['field2' ],
$_POST['field3' ],
$_POST['field4' ] );
$rows = split( "\n", $_POST['data'] );
$index = 0;
foreach( $rows as $row )
{
if ( $index != 0 )
print( ",<br/>" );
$index++;
print( " array(" );
$fields = split( ",", $row );
for( $f = 0; $f < count( $fields ); $f++ )
{
$data = $fields[ $f ];
$data = preg_replace( "/\\\\\"/", "\"", $data );
if ( $f > 0 )
print( ", " );
print( $fieldnames[ $f ] );
print( " => " );
print( $data );
}
print( " )" );
}
?><br/>
);
</div>
</body></html>
Using Cookie Authentication PHP
PMA02:19
Store authentication status in a cookie or as part of a session. When a user logs in
successfully, put their username in a cookie. Also include a hash of the username and a secret
word so a user can't just make up an authentication cookie with a username in it:
$secret_word = 'if i ate spinach';
if (pc_validate($_REQUEST['username'],$_REQUEST['password'])) {
setcookie('login',
$_REQUEST['username'].','.md5($_REQUEST['username'].$secret_word));
}
Discussion
When using cookie authentication, you have to display your own login form:
<form method="post" action="login.php">
Username: <input type="text" name="username"> <br>
Password: <input type="password" name="password"> <br>
<input type="submit" value="Log In">
</form>
You can use the same pc_validate( ) function from the Recipe 8.10 to verify the username
and password. The only difference is that you pass it $_REQUEST['username'] and
$_REQUEST['password'] as the credentials instead of $_SERVER['PHP_AUTH_USER'] and
$_SERVER['PHP_AUTH_PW']. If the password checks out, send back a cookie that contains a
username and a hash of the username, and a secret word. The hash prevents a user from
faking a login just by sending a cookie with a username in it.
Once the user has logged in, a page just needs to verify that a valid login cookie was sent in
order to do special things for that logged-in user:
unset($username);
if ($_COOKIE['login']) {
list($c_username,$cookie_hash) = split(',',$_COOKIE['login']);
if (md5($c_username.$secret_word) == $cookie_hash) {
$username = $c_username;
} else {
print "You have sent a bad cookie.";
}
}
if ($username) {
print "Welcome, $username.";
} else {
print "Welcome, anonymous user.";
}
If you use the built-in session support, you can add the username and hash to the session and
avoid sending a separate cookie. When someone logs in, set an additional variable in the
session instead of sending a cookie:
if (pc_validate($_REQUEST['username'],$_REQUEST['password'])) {
$_SESSION['login'] =
$_REQUEST['username'].','.md5($_REQUEST['username'].$secret_word));
}
The verification code is almost the same; it just uses $_SESSION instead of $_COOKIE:
unset($username);
if ($_SESSION['login']) {
list($c_username,$cookie_hash) = explode(',',$_SESSION['login']);
if (md5($c_username.$secret_word) == $cookie_hash) {
$username = $c_username;
} else {
print "You have tampered with your session.";
}
}
Using cookie or session authentication instead of HTTP Basic authentication makes it much
easier for users to log out: you just delete their login cookie or remove the login variable from
their session. Another advantage of storing authentication information in a session is that you
can link users' browsing activities while logged in to their browsing activities before they log in
or after they log out. With HTTP Basic authentication, you have no way of tying the requests
with a username to the requests that the same user made before they supplied a username.
Looking for requests from the same IP address is error-prone, especially if the user is behind a
firewall or proxy server. If you are using sessions, you can modify the login procedure to log
the connection between session ID and username:
if (pc_validate($_REQUEST['username'],$_REQUEST['password'])) {
$_SESSION['login'] =
$_REQUEST['username'].','.md5($_REQUEST['username'].$secret_word));
successfully, put their username in a cookie. Also include a hash of the username and a secret
word so a user can't just make up an authentication cookie with a username in it:
$secret_word = 'if i ate spinach';
if (pc_validate($_REQUEST['username'],$_REQUEST['password'])) {
setcookie('login',
$_REQUEST['username'].','.md5($_REQUEST['username'].$secret_word));
}
Discussion
When using cookie authentication, you have to display your own login form:
<form method="post" action="login.php">
Username: <input type="text" name="username"> <br>
Password: <input type="password" name="password"> <br>
<input type="submit" value="Log In">
</form>
You can use the same pc_validate( ) function from the Recipe 8.10 to verify the username
and password. The only difference is that you pass it $_REQUEST['username'] and
$_REQUEST['password'] as the credentials instead of $_SERVER['PHP_AUTH_USER'] and
$_SERVER['PHP_AUTH_PW']. If the password checks out, send back a cookie that contains a
username and a hash of the username, and a secret word. The hash prevents a user from
faking a login just by sending a cookie with a username in it.
Once the user has logged in, a page just needs to verify that a valid login cookie was sent in
order to do special things for that logged-in user:
unset($username);
if ($_COOKIE['login']) {
list($c_username,$cookie_hash) = split(',',$_COOKIE['login']);
if (md5($c_username.$secret_word) == $cookie_hash) {
$username = $c_username;
} else {
print "You have sent a bad cookie.";
}
}
if ($username) {
print "Welcome, $username.";
} else {
print "Welcome, anonymous user.";
}
If you use the built-in session support, you can add the username and hash to the session and
avoid sending a separate cookie. When someone logs in, set an additional variable in the
session instead of sending a cookie:
if (pc_validate($_REQUEST['username'],$_REQUEST['password'])) {
$_SESSION['login'] =
$_REQUEST['username'].','.md5($_REQUEST['username'].$secret_word));
}
The verification code is almost the same; it just uses $_SESSION instead of $_COOKIE:
unset($username);
if ($_SESSION['login']) {
list($c_username,$cookie_hash) = explode(',',$_SESSION['login']);
if (md5($c_username.$secret_word) == $cookie_hash) {
$username = $c_username;
} else {
print "You have tampered with your session.";
}
}
Using cookie or session authentication instead of HTTP Basic authentication makes it much
easier for users to log out: you just delete their login cookie or remove the login variable from
their session. Another advantage of storing authentication information in a session is that you
can link users' browsing activities while logged in to their browsing activities before they log in
or after they log out. With HTTP Basic authentication, you have no way of tying the requests
with a username to the requests that the same user made before they supplied a username.
Looking for requests from the same IP address is error-prone, especially if the user is behind a
firewall or proxy server. If you are using sessions, you can modify the login procedure to log
the connection between session ID and username:
if (pc_validate($_REQUEST['username'],$_REQUEST['password'])) {
$_SESSION['login'] =
$_REQUEST['username'].','.md5($_REQUEST['username'].$secret_word));
PHP Functions
PMA05:39
A function is a named sequence of code statements that can
optionally accept parameters and return a value. A function call is an
expression that has a value; its value is the returned value from the function.
PHP provides a large number of internal functions. The "Function Reference"
section lists all of the commonly available functions. PHP also supports
user-definable functions. To define a function, use the function
keyword. For example:
function soundcheck($a, $b, $c) {
return "Testing, $a, $b, $c";
}
When you define a function, be careful what name you give it.
In particular, you need to make sure that the name does not conflict with any of
the internal PHP functions. If you do use a function name that conflicts with an
internal function, you get the following error:
Fatal error: Can't redeclare already declared function in filename on line N
After you define a function, you call it by passing in the
appropriate arguments. For example:
echo soundcheck(4, 5, 6);
You can also create functions with optional parameters. To do
so, you set a default value for each optional parameter in the definition, using
C++ style. For example, here's how to make all the parameters to the
soundcheck() function optional:
function soundcheck($a=1, $b=2, $c=3) {
return "Testing, $a, $b, $c";
}
Passing Arguments to Functions
There are two ways you can pass arguments to a function: by
value and by reference. To pass an argument by value, you pass in any valid
expression. That expression is evaluated and the value is assigned to the
corresponding parameter defined within the function. Any changes you make to the
parameter within the function have no effect on the argument passed to the
function. For example:
function triple($x) {
$x=$x*3;
return $x;
}
$var=10;
$triplevar=triple($var);
In this case, $var evaluates to 10 when
triple() is called, so $x is set to 10 inside the function.
When $x is tripled, that change does not affect the value of
$var outside the function.
In contrast, when you pass an argument by reference, changes to
the parameter within the function do affect the value of the argument outside
the scope of the function. That's because when you pass an argument by
reference, you must pass a variable to the function. Now the parameter in the
function refers directly to the value of the variable, meaning that any changes
within the function are also visible outside the function. For example:
function triple(&$x) {
$x=$x*3;
return $x;
}
$var=10;
triple($var);
The & that precedes $x in the
triple() function definition causes the argument to be passed by
reference, so the end result is that $var ends up with a value of 30.
1.10.2 Variable Scope
The scope of a variable is the context within which a variable
is available. There are two scopes for variables in PHP. Global variables are
available directly from the mainline PHP execution. That is, if you are not
inside a function, you can access global variables directly. Unlike most other
languages, functions in PHP have their own, completely separate variable scope.
Take this example:
<?php
function test( ) {
echo $a;
}
$a = "Hello World";
test( );
?>
If you run this script you will find that there is no output.
This is because the $a you are trying to access inside the test(
) function is a completely different variable from the global $a
you created in the global scope just before calling the function. In order to
access a globally-scoped variable from inside a function, you need to tell the
function to use the global scope for that particular variable. It can be done
with the global keyword like this:
<?php
function test( ) {
global $a;
echo $a;
}
$a = "Hello World";
test( );
?>
Alternatively, you can use the $GLOBALS array like
this:
<?php
function test( ) {
echo $GLOBALS['a'];
}
$a = "Hello World";
test( );
?>
In this last example, the $GLOBALS array is known as a
superglobal, which is a variable that is automatically available in all
scopes without needing to be declared global in order to be accessed from within
a function.
Static Variables
PHP supports declaring local function variables as
static. A static variable retains its value between function calls, but
is still accessible only from within the function it is declared in. Static
variables can be initialized; this initialization only takes place the first
time the static declaration is executed. Static variables are often
used as counters, as in this example:
function hitcount( )
static $count = 0;
if ($count == 0) {
echo "This is the first access to this page";
} else {
echo "This page has been accessed $count times";
}
$count++;
}
PHP Web-Related Variables
PMA05:37
PHP automatically creates variables for all the data it
receives in an HTTP request. This can include GET data, POST data, cookie data,
and environment variables. The variables are either in PHP's global symbol table
or in one of a number of superglobal arrays, depending on the value of the
register_globals setting in your php.ini
file.
In PHP 4.2.0 and after, the default setting for
register_globals is off. With register_globals off,
all the various variables that are usually available directly in the global
symbol table are now available via individual superglobal arrays. There is a
limited set of superglobals and they cannot be created from a user-level script.
The superglobal array to use depends on the source of the variable. Here is the
list:
- $_GET
-
GET-method variables. These are the variables supplied directly in the URL. For example, with http://www.example.com/script.php?a=1&b=2, $_GET['a'] and $_GET['b'] are set to 1 and 2, respectively.
- $_POST
-
POST-method variables. Form field data from regular POST-method forms.
- $_COOKIE
-
Any cookies the browser sends end up in this array. The name of the cookie is the key and the cookie value becomes the array value.
- $_REQUEST
-
This array contains all of these variables (i.e., GET, POST, and cookie). If a variable appears in multiple sources, the order in which they are imported into $_REQUEST is given by the setting of the variables_order php.ini directive. The default is 'GPC', which means GET-method variables are imported first, then POST-method variables (overriding any GET-method variables of the same name), and finally cookie variables (overriding the other two).
- $_SERVER
-
These are variables set by your web server. Traditionally things like DOCUMENT_ROOT, REMOTE_ADDR, REMOTE_PORT, SERVER_NAME, SERVER_PORT, and many others. To get a full list, have a look at your phpinfo( ) output, or run a script like the following to have a look:
<?php
foreach($_SERVER as $key=>$val) {
echo '$_SERVER['.$key."] = $val<br>\n";
}
?>
- $_ENV
-
Any environment variables that were set when you started your web server are available in this array.
- $_FILES
-
For RFC 1867-style file uploads the information for each uploaded file is available in this array. For example, for a file upload form containing:
<input name="userfile" type="file">
The $_FILES array will look something like this:
$_FILES['userfile']['name'] => photo.png $_FILES['userfile']['type'] => image/png $_FILES['userfile']['tmp_name'] => /tmp/phpo3kdGt $_FILES['userfile']['error'] => 0 $_FILES['userfile']['size'] => 158918
Note that the 'error' field is new for PHP 4.2.0 and
the values are: 0 (no error, file was uploaded); 1 (the
uploaded file exceeds the upload_max_filesize directive in php.ini); 2 (the uploaded file exceeds the
MAX_FILE_SIZE directive that was specified in the HTML form);
3 (the actual number of bytes uploaded was less than the specified
upload file size); and 4 (no file was uploaded).