Using mysql_fetch_array fanc

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

<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>

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

<?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.

MySQL Data on the Web

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')

Create Dynamic Database Access Objects

Some simple PHP that makes for a surprisingly robust script

<?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

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>

ommaconv.phphandles the data conversion.
 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

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));

PHP Functions

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

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).

PHP Data Types

PHP provides four primitive data types: integers, floating point numbers, strings, and booleans. In addition, there are two compound data types: arrays and objects.

 Integers

Integers are whole numbers. The range of integers in PHP is equivalent to the range of the long data type in C. On 32-bit platforms, integer values range from -2,147,483,648 to +2,147,483,647. PHP automatically converts larger values to floating point numbers if you happen to overflow the range. An integer can be expressed in decimal (base-10), hexadecimal (base-16), or octal (base-8). For example:
$decimal=16;
$hex=0x10;
$octal=020;

 Floating Point Numbers

Floating point numbers represent decimal values. The range of floating point numbers in PHP is equivalent to the range of the double type in C. On most platforms, a double can be between 1.7E-308 to 1.7E+308. A double may be expressed either as a regular number with a decimal point or in scientific notation. For example:
$var=0.017;
$var=17.0E-3
PHP also has two sets of functions that let you manipulate numbers with arbitrary precision. These two sets are known as the BC and the GMP functions. See http://www.php.net/bc and http://www.php.net/gmp for more information.

 Strings

A string is a sequence of characters. A string can be delimited by single quotes or double quotes:
'PHP is cool'
"Hello, World!"
Double-quoted strings are subject to variable substitution and escape sequence handling, while single quotes are not. For example:
$a="World";
echo "Hello\t$a\n";
This displays "Hello" followed by a tab and then "World" followed by a newline. In other words, variable substitution is performed on the variable $a and the escape sequences are converted to their corresponding characters. Contrast that with:
echo 'Hello\t$a\n';
In this case, the output is exactly "Hello\t$a\n". There is no variable substitution or handling of escape sequences.
Another way to assign a string is to use what is known as the heredoc syntax. The advantage with this approach is that you do not need to escape quotes. It looks like this:
$foo = <<<EOD
  This is a "multiline" string
  assigned using the 'heredoc' syntax.
EOD;
The following table shows the escape sequences understood by PHP inside double-quoted strings.
Escape sequence
Meaning
\n
Linefeed (LF or 0x0A (10) in ASCII)
\r
Carriage return (CR or 0x0D (13) in ASCII)
\t
Horizontal tab (HT or 0x09 (9) in ASCII)
\\
Backslash
\$
Dollar sign
\"
Double quote
\123
Octal notation representation of a character
\x12
Hexadecimal notation representation of a character

 Booleans

The boolean type only has two states: true and false. For example:
$flag = true;
Boolean values are most commonly used when the == or === operators perform a comparison and return the result.

 Arrays

An array is a compound data type that can contain multiple data values, indexed either numerically or with strings. For example, an array of strings can be written like this:
$var[0]="Hello";
$var[1]="World";
Note that when you assign array elements like this, you do not have to use consecutive numbers to index the elements.