BIAD-07-PHP2

PHP Part 2

Access MySQL Database by using PHP

built-in support for accessing MySQL directly
- other server side scripting languages like ASP require the use of ODBC
build-in functions

Function name	Notes
mysql_connect	Open a connection to a MySQL Server, it accepts parameter of server name, username, password...etc. If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The link to the server will be closed as soon as the execution of the script ends. Return an MySQL link identifier if successful
mysql_pconnect	Open a persistent connection to a MySQL server, act similar to mysql_connect() except; when connecting, the function would first try to find a (persistent) link that's already open with the same parameter. If one is found, an identifier for it will be returned instead of opening a new connection. the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use
mysql_close	Close MySQL connection but isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.
mysql_select_db	Select a MySQL database by accepting 2 parameters, the name of database and the database link identifier
mysql_query	Submit a MySQL query to the database, returns a resource identifier if successful
mysql_fetch_array	Fetch the result row as an array. Returns an array that corresponds to the fetched row, or `FALSE` if there are no more rows.

Steps to communicate with MySQL:

Establish a connection to the database
Select the database to be used
Submit a query (e.g. SELECT * FROM student in the sample program below)
Fetch the result. (e.g. The result is stored in the array $row. $row["sid"] stores the value of field sid of a particular record (row) )

sample 1- student.htm

<html>
<head><title>Student enquiry</title></head>

<body>
<p>List all student records, please click submit: </p>
<form method="post" action="student.php">
<p><input type="submit" name="Submit" value="Submit"></p>

</form>
</body>
</html>

student.php

<html><head><title>PHP Test</title></head>
<body>
<?php
//Connect to the database on the server's machine as user "root".

$conn = mysql_connect("localhost", "root", "");
mysql_select_db("course", $conn);
$res = mysql_query("SELECT * FROM student", $conn);
?>

<table border=1>
<tr><th>Student ID</th><th>Student Name</th><th>Age</th>
<?php while ($row = mysql_fetch_array($res)){
print "<tr>";
print ("<td>".$row["sid"]."</td>");
print ("<td>".$row["sname"]."</td>");
print ("<td>".$row["age"]."</td><tr>");
}
?>
</table>
</body></html>

Result :display on browser after clicked the SUBMIT button:

sample 2 - student1.htm

<html>
<head><title>Student enquiry</title></head>

<body>
<p><font size="5">Search for record, please input name and click submit: </font></p>
<form method="post" action="student1.php">
<p>Input Name: <input type="text" name ="inputname" value ="">
</p>
<p>
<input type="submit" name="Submit" value="Submit">
</p>

</form>
</body>
</html>

Note 1 :
Variable inputname is passed in the query string as $_POST["inputname"]

Note 2 :
$_POST autoglobal contains all POST data. Use $_GET autoglobal if method GET is used, use the $_REQUEST autoglobal if you don't care the source of your request data.

student1.php

<html><head><title>PHP Test</title></head>
<body>
<?php
//Connect to the database on the server's machine as user "root".

$conn = mysql_connect("localhost", "root", "");
mysql_select_db("course", $conn);
$res = mysql_query("SELECT * FROM student where sname = '". $_POST["inputname"] ."'", $conn);
?>

<table border=1>
Records of table "student"
<tr><th>Student ID</th><th>Student Name</th><th>Age</th>
<?php while ($row = mysql_fetch_array($res)){

print "<tr>";
print ("<td>".$row["sid"]."</td>");
print ("<td>".$row["sname"]."</td>");
print ("<td>".$row["age"]."</td><tr>");
}
?>
</table>
</body></html>

Result :display on browser if "Peter Pan" is input to the textbox:

Hypertext Transfer Protocol (HTTP)

HTTP is a stateless protocol, i.e. the web server does not record any state information after serving a request
Every request is served independently regardless of any other previous request
It creates problems when an application is stateful. e.g.
- an e-shopping site need to keep track of products that a particular customer has selected on previous pages
- returned results of a search in a search engine are too large to be displayed, the remaining results are sent when the user requests them
- in a search engine, after a search has been done, the user may want to refine the search

State management - to store the session information

Methods to propagating session information to the client

embedding it in a URL
embedding it in a hidden field of a form
using cookies

Embedding it in a URL

keeping track of a session by attaching a session id at the end of a URL request
e.g.

Embedding it in a hidden field of a form

session information is stored as the value of a hidden field in the form
Pros : the session information will not be shown as URLs if POST method is used.

Using Cookie

When the web server returns a document to a client, it may include a set-cookie field in the header. e.g.

Set-Cookie:
UIDC=61.18.6.248:0990120596:499272;
domain=.netscape.com;path=/;expires=31-Dec-2010

When the same browser is used to browse any site in the domain .netscape.com and the date is before 31-Dec-2010, then the stored cookie, with name UIDC, will be sent as a field in the request header
Usage : cookies can be used to distinguish sessions for different domain, but not sessions for the same domain
Pros : the session information will not be shown as URLs
Cons : the client will store the cookie only if it is set to accept cookies

Set cookie in PHP

set cookie at browser using setcookie() function
setcookie() function adds headers to HTTP response, it must be set before any body output is printed

Setcookie($n_name,$name,time( )+3*24*3600 )

to mainitain session information, web servers will remove the records for sessions that have been inactive for certain periods (e.g. 3 days in the above case)
Example (original from http://www.zend.com/)

A simple feedback form which records the date and time that the user logged into the system and saves this information, in addition to the user's name and email address, in several cookies.

sample program : sample-cookie.php
sample cookie : http://192.168.237.52/notes05_06/biad_s/bia-it-1/cc_127_0_0.txt

Comparison of different methods for propagation session information

	Embedding sessions information in URLs	Embedding sessions information in hidden field	Use of cookies
Ease of entry to an old session	Yes, by bookmark the URL which includes the session id	No.	Yes, if a cookie has not expired
Distinguish 2 sessions of the same machine	Yes	Yes	No
Reliability	No, user can alter the session id of the URL	A little bit more difficult to change the session id	No, the user can reject cookies

Session management in PHP

In case of cookies are not accepted by some browser, PHP use both cookies and URL for session management. If the browser doesn't accept cookies, the system will automatically embed a session id in relative URLs
Session with cookies
- use session_get_cookie_params() to obtain the session cookie parameters
- use session_set_cookie_params($lifetime, $path, $domain) to set the session cookie parameters
Embedding session ID to URL
- append session ID (SID) to link, e.g.

Several session options can be defined and store in php.ini, e.g.
1. default value of session.save_path, which is the storage path of session information, is /tmp
2. default value of session.cookie_lifetime, which is the lifetime of the cookie, is 0. i.e. cookie is kept until browser is closed

Steps to manage a session:

Start a session

session_start();

Register session variable

//assign value to session variable
$myvar=5;
session_register("myvar");
// register more than one session variables
session_register("myvar1","myvar2");

Using session variable

// to check whether session variable is registered, return true if so, false otherwise.
$result = session_is_registered("myvar");
echo $myvar;

Remove session variable and close a session

// to remove session variable
session_unregistered("myvar");
// to remove all session variables
session _unset();

// to close a session
session_destroy();

sample 1- page1.php

<?
session_start();
session_register("sess_var");

$sess_var="Hello world!";
echo "The content of \$sess_var is $sess_var<br>";
?>

<a href = "page2.php">Next page</a>

page2.php

<?
session_start();
echo "The content of \$sess_var is $sess_var<br>";
session_unregister("sess_var");
?>

<a href = "page3.php">Next page</a>

page3.php

<?
session_start();
echo "The content of \$sess_var is $sess_var<br>";
session_destroy();
?>

Result:

sample 2- nextpage1.php

<?php

   session_start();
   if (!session_is_registered("count")) {
       session_register("count");
        $count = 1;
        }
     else {
         $count++;
          }
?>

Hello visitor, you have seen this page <?php echo $count; ?> times.<p>
To continue, <A HREF="nextpage1.php?<?=SID?>">click here</A>

Result:

References

http://conf.php.net/
http://www.zend.com/zend/art/intro.php
http://www.phpbuilder.com/
http://www.php.net/
http://www.zend.com/zend/tut/ (many useful examples)
http://www.mysql.com/