Transcript PHP_Bible

PHP Bible
Chapter 18: PHP/MySQL Database Functions
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
1
 Wiley and the book authors, 2002
Summary







Connecting to MySQL
Making queries
Fetching data
Getting data about data
Using multiple connections
Error checking
Creating databases
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
2
 Wiley and the book authors, 2002
Connecting to MySQL


After you've designed and setup your MySQL database, you can begin to
write PHP scripts that interact with it
The basic command to initiate a MySQL connection is
resource mysql_connect ( [string server
[, string username [, string password
[, bool new_link [, int client_flags]]]]])

Returns a MySQL link identifier on success, or FALSE on failure
Typically, you will supply a server name, username, and password when
connecting to the specified database
$db = mysql_connect('sql.gettelco.com','MGMT380b','MGMT380b');

Once you've established the connection to the specified server, you need to
connect to a particular database on that server
 bool mysql_select_db ( string database_name [,
resource link_identifier]) sets the current active database
on the server that's associated with the specified link identifier. If no link
identifier is specified, the last opened link is assumed.
mysql_select_db('MGMT380b',$db) or die('Could not connect to MGMT380b');
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
3
 Wiley and the book authors, 2002
Making MySQL Queries

A database query from PHP is basically a MySQL command
wrapped up in a tiny PHP function resource
resource mysql_query ( string query [, resource
link_identifier]) sends a query to the currently active
database on the server that's associated with the specified link
identifier. If no link identifier is specified, the last opened link is
used.

This is where the workhorses of SELECT, INSERT, UPDATE,
and DELETE will be executed. The MySQL commands to
CREATE or DROP a table can also be used with this PHP
function, but you can't CREATE or DROP a database
$sql = 'SELECT * FROM catalog WHERE (category = "1")';
$query_handle = mysql_query($sql,$db);

The main reason you want to break up the query into 2 lines like
this is so that if you get an error, you can retrieve and print or log
the exact query that caused the problem.
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
4
 Wiley and the book authors, 2002
Making MySQL Queries (cont.)

If you are making a SELECT statement with a large number of
records which will be returned (e.g. several thousand or more),
you can use the following command which consumes less
memory on the server and allows you to begin working on the
data returned without waiting for the entire dataset to be
retrieved
resource mysql_unbuffered_query ( string query
[, resource link_identifier]) sends a SQL query query
to MySQL, without fetching and buffering the result rows
automatically
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
5
 Wiley and the book authors, 2002
Making MySQL Queries (cont.)

If your query was in INSERT, UPDATE, DELETE, CREATE
TABLE, or DROP TABLE and returned TRUE, you can use this
command to see how many rows were changed by the query
int mysql_affected_rows ( [resource
link_identifier]) returns the number of rows affected by the
last INSERT, UPDATE or DELETE query associated with
link_identifier

If your query was a SELECT statement, the value returned from
mysql_query is a resource called a result identifier. This is a
unique identifier for each SELECT statement executed. If you
want to see the number of rows returned by the SELECT, use
int mysql_num_rows ( resource result) returns the
number of rows in a result set. This command is only valid for
SELECT statements.
$num_rows = mysql_num_rows($query_result);
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
6
 Wiley and the book authors, 2002
Fetching Data Sets





One thing that often seems to temporarily stymie new PHP
users is the whole concept of fetching data from PHP
It would be logical to assume the result of a query would be the
desired data, but that is not the way PHP works
What actually happens is that a mysql_query() command
pulls the data out of the database and sends a receipt back to
PHP reporting on the status of the operation
At this point, the data exists in purgatory that is immediately
accessible from neither MySQL nor PHP
It requires one of the mysql_fetch functions to make the
data from a successful SELECT statement fully available to
PHP
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
7
 Wiley and the book authors, 2002
Fetching Data Sets (cont.)

The "fetching" functions are:
array mysql_fetch_row ( resource result) fetches one row of
data from the result associated with the specified result identifier. The row is
returned as an array. Each result column is stored in an array offset, starting
at offset 0
object mysql_fetch_object ( resource result) returns an
object with properties that correspond to the fetched row, or FALSE if there
are no more rows
array mysql_fetch_assoc ( resource result) returns an
associative array that corresponds to the fetched row, or FALSE if there are
no more rows
array mysql_fetch_array ( resource result [, int
result_type]) is an extended version of mysql_fetch_row(). In
addition to storing the data in the numeric indices of the result array, it also
stores the data in associative indices, using the field names as keys
bool mysql_data_seek ( resource result_identifier,
int row_number) moves the internal row pointer of the MySQL result
associated with the specified result identifier to point to the specified row
number
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
8
 Wiley and the book authors, 2002
Fetching Data Sets (cont.)



Typically, when retrieving data from an SQL SELECT query,
you will want to use either mysql_fetch_array() or
mysql_fetch_assoc() since it provides you the ability to
access the fields returned by their field name rather than the
specific offset as mysql_fetch_row() requires
This way, if modifications are made to the order of the fields in
the database or new fields are added (or deleted), you may not
have to modify your code for accessing the data in the
individual fields
Also, in queries which result in very large numbers of fields
returned, it can be extremely difficult to determine exactly
which offset the field you want is located at
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
9
 Wiley and the book authors, 2002
Fetching Data Sets (cont.)

For example, this is an actual SQL query used in one of my applications:
SELECT cdr_id, Record_ID, DateStamp,
CONCAT(Time_Of_Record_Hours,":",Time_Of_Record_Minutes,":",Time_Of_Record_Seconds,".",Time_Of_Record_Seconds_100) AS Time_Of_Record,
LEFT(Calling_NPA_Digits,Calling_NPA_Length) AS Calling_NPA,
LEFT(Calling_Party_Number_Digits,IF(Calling_Party_Number_Length<3,Calling_Party_Number_Length,3)) AS Calling_Party_NXX,
LEFT(Combined_Calling_NPA_Party_Number_Digits,Combined_Calling_NPA_Party_Number_Length-4) AS Calling_Party_NPANXX,
SUBSTRING(Calling_Party_Number_Digits,4,IF(Calling_Party_Number_Length<7,Calling_Party_Number_Length-3,4)) AS Calling_Party_Station,
LEFT(Calling_Party_Number_Digits,Calling_Party_Number_Length) AS Calling_Party_Number,
LEFT(Combined_Calling_NPA_Party_Number_Digits,Combined_Calling_NPA_Party_Number_Length) AS ANI,
LEFT(Called_NPA_Digits,Called_NPA_Length) AS Called_NPA,
LEFT(Called_Party_Number_Digits,IF(Called_Party_Number_Length<3,Called_Party_Number_Length,3)) AS Called_Party_NXX,
LEFT(Combined_Called_NPA_Party_Number_Digits,Combined_Called_NPA_Party_Number_Length-4) AS Called_Party_NPANXX,
SUBSTRING(Called_Party_Number_Digits,4,IF(Called_Party_Number_Length<7,Called_Party_Number_Length-3,4)) AS Called_Party_Station,
LEFT(Called_Party_Number_Digits,Called_Party_Number_Length) AS Called_Party_Number,
LEFT(Combined_Called_NPA_Party_Number_Digits,Combined_Called_NPA_Party_Number_Length) AS DNIS,
LEFT(Originators_Charge_Number_Digits,Originators_Charge_Number_Length) AS Originators_Charge_Number,
LEFT(Terminating_Number_Digits,Terminating_Number_Length) AS Terminating_Number,
CONCAT(Originating_Line_Shelf,":",Originating_Line_Card,":",Originating_Line_DS1,":",Originating_Line_DS0) AS Originating_Line,
CONCAT(Terminating_Line_Shelf,":",Terminating_Line_Card,":",Terminating_Line_DS1,":",Terminating_Line_DS0) AS Terminating_Line,
Incoming_Trunk_Group_Number AS Incoming_Trunk_Group, Outgoing_Trunk_Group_Number AS Outgoing_Trunk_Group, Incoming_Signaling_Protocol,
Outgoing_Signaling_Protocol, Time_Of_Record_Hours AS Answer_Time_I_Hour,
CONCAT(Answer_Time_I_Hour,":",Answer_Time_I_Minute,":",Answer_Time_I_Seconds,".",Answer_Time_I_Seconds_100) AS Answer_Time_I,
CONCAT(Carrier_Connect_Time_Hour,":",Carrier_Connect_Time_Minute,":",Carrier_Connect_Time_Seconds,".",Carrier_Connect_Time_Seconds_100) AS
Carrier_Connect_Time, CONCAT(Disconnect_Time_Hour,":",Disconnect_Time_Minute,":",Disconnect_Time_Seconds,".",Disconnect_Time_Seconds_100)
AS Disconnect_Time, Conversation_Time_I_Minute*60 + Conversation_Time_I_Seconds + Conversation_Time_I_Seconds_100/100 AS
Conversation_Time_I, Carrier_Elapsed_Time_Minute*60 + Carrier_Elapsed_Time_Seconds + Carrier_Elapsed_Time_Seconds_100/100 AS
Carrier_Elapsed_Time, Call_Duration_Minute*60 + Call_Duration_Seconds + Call_Duration_Seconds_100/100 AS Call_Duration, (Call_Duration_Minute*60
+ Call_Duration_Seconds + Call_Duration_Seconds_100/100) - (Conversation_Time_I_Minute*60 + Conversation_Time_I_Seconds +
Conversation_Time_I_Seconds_100/100) AS Answer_Delay, Line_Information, Called_Party_Number_Digit_Type, Call_Handling_Code, Terminator_Type,
Caller_ID_Status, CNAM_Status, SAC_Translation_Type, SAC_Translation_Status, SAC_Translation_CIC,
LEFT(SAC_Translation_Digits,SAC_Translation_Length) AS SAC_Translation, LNP_Status, LEFT(LRN_Digits,LRN_Length) AS LRN,
Called_Party_Offhook_Indicator, Emergency_Service_Call_Indicator, Carrier_Code, LEFT(Country_Code_Digits,Country_Code_Length) AS Country_Code,
Overseas_Indicator, ICINC_Trunk_Group_Number, Dialing_Presubscription_Indicator, Timing_Guard_Flag, Short_Called_Party_Offhook_Indicator,
Long_Duration_Call_Indicator, Number_Times_CID_Information_Delivered, Number_Times_CID_Information_Unavalable,
Jurisdiction_Information_Parameter, Terminating_Access_Carrier_Code, Cause_Value FROM CSX21_CDR_2004 WHERE
((Combined_Calling_NPA_Party_Number_Digits like "618434____%"))
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
10
 Wiley and the book authors, 2002
Fetching Data Sets (cont.)

The following example takes the result of any SQL SELECT
query and prints out the result in an HTML table
print('<TABLE BORDER="1">');
$header_printed = false;
while ($row = mysql_fetch_assoc($result_handle))
{
if (!$header_printed)
{
print ('<TR>');
foreach ($row as $field_name => $val)
print('<TH>'.$field_name.'</TH>');
print ('</TR>');
$header_printed = true;
}
print ('<TR>');
foreach ($row as $val)
print ('<TD>'.$val.'</TD>');
print ('</TR>');
}
print('</TABLE>');
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
11
 Wiley and the book authors, 2002
Fetching Data Sets (cont.)

The following example prints data from each named field
returned from the query, "SELECT ItemID, Title, Price,
Inventory, Description, PictureURL FROM catalog WHERE
(Category = '1')"
while ($row = mysql_fetch_assoc($result_handle))
{
print ("<TR>
<TD>${row['ItemID']}</TD>
<TD><B>${row['Title']}</B></TD>
<TD>\$${row['Price']}</TD>
<TD>${row['Inventory']}</TD>
<TD><FONT SIZE=\"-1\"
${row['Description']}</FONT></TD>
<TD><IMG SRC=\"${row[‘PictureURL']}\"></TD>
</TR>");
}
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
12
 Wiley and the book authors, 2002
Getting data about data



PHP offers built-in functions to help you learn the name of the table in which
your data resides, the data type handled by a particular column, or the
number of the row into which you just inserted data
In most cases, you need a higher level of security into the database or table
you are accessing in order to use these functions than is required for just
SELECT statements
The MySQL metadata functions fall into two major categories:
 Functions that return information about the previous operation


E.g. int mysql_insert_id ( [resource
link_identifier]) returns the ID generated for an
AUTO_INCREMENT column by the previous INSERT query
Functions that return information about the database structure in general

E.g. string mysql_field_type ( resource result, int
field_offset) returns the type of data stored at the field located at
field_offset. The field type will be one of "int", "real", "string", "blob", and
others
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
13
 Wiley and the book authors, 2002
Multiple connections



Unless you have a specific reason to required multiple
connections, you only need to make one database connection
per PHP script (consequently, you will not need to specify the
link_identifier in most function calls)
If you're querying two or more completely separate database
servers, you will need to create multiple connections (e.g.
SELECT from the slave and UPDATE to the master)
If this is required, simply hang onto the value returned by
mysql_connect calls and use these in the appropriate
link_identifier arguments in the mysql functions
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
14
 Wiley and the book authors, 2002
Building in Error-Checking




This section could have been titled "Die, die, die!" because the main errorchecking function is actually called die().
die() is not a MySQL-specific function, it simply terminates the script and
returns a string of your choice
Since PHP no longer prints error messages for database connections or
queries by default, it is important to die with a meaningful message unless
you want your visitors to be left wondering what happened
Particularly useful during the development and debugging phases, and
located in places where they can easily be commented out or routed to an
error log, the following functions can be useful for determining why your
script is not functioning the way you expected
string mysql_error ( [resource link_identifier]) returns
the error text from the last MySQL function, or '' (the empty string) if no
error occurred
int mysql_errno ( [resource link_identifier]) returns the
error number from the last MySQL function, or 0 (zero) if no error occurred
$handle = mysql_query($sql) or die('ERROR: '.mysql_error().' in '.$sql);
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
15
 Wiley and the book authors, 2002