Transcript mysql Functions

Chapter 6
PHP
Interacts
with Mysql Database
Introduction
In PHP, there is no consolidated interface. Instead, a set of library
functions are provided for executing SQL statements, as well as for
managing result sets returned from queries
We overview these functions here and show how they can be combined to
access the MySQL DBMS.
PHP provides support for MySQL through an array of functions that can
be used to manipulate MySQL data.
The purpose of this tutorial is to introduce those functions commonly used
in PHP data-driven applications for retrieving, updating, inserting, and
deleting data.
Since PHP 5 there are two interfaces available for MySQL programming:
The mysql functions are known to many PHP programmers from earlier
versions of PHP. New since PHP 5 is the object- oriented interface
mysqli, which enables more elegant programming features and access to
new MySQL functions
mysql Functions
The mysql functions are not an integral part of PHP, but an
extension.
In order for this extension to be used, PHP under Linux must
have been compiled with the option —with-mysql. With the
Windows version of PHP, the corresponding extension is
provided as a DLL. However, the extension must be activated
in php.ini: extension=php_mysql.dll
A.) Establishing a Connection
To establish a connection, execute mysql_connect, where you need to
pass three pieces of information: the computer name (hostname) of the
MySQL server, the MySQL user name, and the password. If MySQL is
running on the same computer as the PHP script, the computer name
should be localhost.
$conn = mysql_connect("localhost", "username", "xxx");
mysql Functions
A.) Establishing a Connection
The function returns an identification number. You will need this number in
the future only if you open more than one connection to MySQL. (As long
as there is only one connection to MySQL, this connection is the default
connection. The ID number therefore does not need to be given in calling
various mysql_xxx functions.)
If you wish to provide the PHP code for establishing the connection itself
with a readable error message, the necessary code looks something like
this:
$conn = @mysql_connect("localhost", "username", "xxx");
if($conn == FALSE) {
echo "<p>error message ...</p>\n";
exit();
}
mysql Functions
A.) Establishing a Connection
As soon as a connection has been made, you can execute SQL
commands with various mysql_xxx functions.
you can select a default database with mysql_select_db. (This
corresponds to the SQL command USE databasename.)
mysql_select_db("mylibrary");
You close the connection to MySQL with mysql_close.
Mysql_close(connection_id);
mysql Functions
B.) Executing SQL Commands
To execute SQL commands, you pass them as a character string to the
function mysql_query. If the command is not intended for the current
database, you can add the name of the desired database using
mysql_db_query.
With both of these functions an optional final parameter can be given: the
ID number of the connection (that is, the return value of mysql_connect) if
there is more than one
connection to MySQL.
$result = mysql_query("SELECT COUNT(*) FROM titles");
$result = mysql_db_query("mylibrary", "SELECT COUNT(*) FROM titles");
Every type of SQL command can be executed with mysql_query: queries
with SELECT; changes to data with INSERT, UPDATE, and DELETE;
changes to the database structure with CREATE TABLE; etc.
mysql Functions
B.) Executing SQL Commands
If an SQL command can be correctly executed, mysql_query returns a
nonzero value. If the command involved a query, the return value of
mysql_query is a reference to a PHP resource (e.g., a character string of
the form “Resource id #2”).
The return value can be used in various other functions (e.g.,
mysql_fetch_row) to evaluate individual fields of the table.
On the other hand, if an SQL command cannot be executed, mysql_query
returns the result FALSE (i.e., 0)
C.) Evaluating SELECT Results
When you execute a SELECT query with mysql_query, you obtain as
result a reference to a table with rows rows and cols columns:
$result = mysql_query("SELECT * FROM titles");
$rows = mysql_num_rows($result);
$cols = mysql_num_fields($result);
C.) Evaluating SELECT Results
It is much more efficient to evaluate the result row by row. For this there
are three functions:
$row
$row
$row
$row
=
=
=
=
mysql_fetch_row($result);
mysql_fetch_array($result);
mysql_fetch_assoc($result);
mysql_fetch_object($result);
• mysql_fetch_row returns a record in the form of a simple array. Access to the
columns is via $row[$n].
• mysql_fetch_array returns a record in the form of an associative array.
Access to the columns is via $row[$n] or $row[$colname] (so, for example,
$row[3] or $row[“publName”]). The column name is case-sensitive.
• mysql_fetch_assoc (available since PHP 4.0.3) also returns an associative
field, which can be read in the form $row[$colname]. In contrast to
mysql_fetch_array, one is not allowed to give the column number as parameter.
• mysql_fetch_object returns a data record as object. Access to the column is via
$row->colname.
C.) Evaluating SELECT Results
A common feature of all four functions is that with each call, the next
record is automatically returned (or FALSE if the end of the list of data has
been reached
PHP stores query results until the end of the script. If a query result must
be released earlier (for example, if you have executed a large number of
queries in a script and don’t want to waste memory), you can release the
result early with mysql_free_result. This is particularly useful if the script
contains a loop that executes SQL queries.
mysql_free_result($result);
mysqli Functions
The mysqli interface is considered, at least by developers of MySQL
applications, to be among the most important innovations in PHP 5. This
interface makes possible object-oriented programming of database
access with MySQL and is thus a great help in the creation of readable
code.
The mysqli interface is, like the mysql interface, not an integral component
of PHP, but an extension. For this extension to be usable, PHP must be
compiled under Linux with the option —with-mysqli. With the Windows
version of PHP the corresponding extension is provided as a DLL, and the
extension must be activated in php.ini: extension=php_mysqli.dll
A.) Connecting to the Database
The first step in any database transaction from PHP is connecting to the
database. When you're using MySQL directly, this is analogous to
executing the MySQL client application; however, in PHP this is done by
using the mysqli_connect() function. The syntax for this function is as
follows:
mysqli_connect([$hostname [, $username [, $password [, $dbname ]]]]);
mysqli Functions
B.) Selecting a Database
After a connection has been created, the mysqli_select_db() function can
be used to select the current database in the same way the USE SQL
command was used from the client. The syntax for the mysqli_select_db()
function is as follows:
mysqli_select_db($link, $dbname);
C.) Performing a Basic Query
Now that we know how to connect to a MySQL server and select a
database to use, it's time to start performing SQL queries against it. To
perform queries, we will use the mysqli_query() function with the following
syntax:
mysqli_query($link, $query );
mysqli Functions
D.) Fetching Resultset Rows
When performing queries that modify the tables within a database, such
as INSERT and UPDATE, generally there is no data to return from the
database. However, queries such as SELECT, which retrieve data from
the database, require that data must somehow be accessed from within
PHP.
When returning the results from a resultset into PHP, various options are
available, each providing a unique usefulness depending on the
circumstance.
The most general of the result-retrieving functions is the
ysqli_fetch_array() function. The syntax for this function is as follows:
mysqli_fetch_array($result [, $array_type])
MYSQLI_ASSOC:Return an associative array.
MYSQLI_NUM:Return an enumerated array.
MYSQLI_BOTH:Return both an enumerated and associative array.
Note:
If no value is provided, the MYSQLI_BOTH constant is the default value.
mysqli Functions
E.) Counting Rows and Columns
Often, it is useful to know how many rows or columns exist for a given
resultset. For these purposes, MySQLi provides the mysql_num_rows()
and mysqli_num_fields() functions, whose syntax follows:
mysqli_num_rows($result)
mysqli_num_fields($result)
F.) Freeing Results
After a query is performed, the resultset for that query is stored in memory
until the PHP script that performed the query is terminated. Although
generally this is acceptable, when you are working with large resultsets, it
becomes important to free the resultset in memory. This can be done
using the mysqli_free_result() function as follows:
mysqli_free_result($result)
mysqli Functions
G.) Closing the Database Connections
Although PHP will automatically take care of closing any outstanding
database connections (as appropriate), a connection to a database can be
explicitly closed using the mysqli_close() function with the following
syntax:
mysqli_close($link)
The end of Chapter 6
Thanks for your paying attention