Transcript PHP * MySQL Extensions

PHP – MySQL Extensions
Table used in most examples
CREATE TABLE product (
rowID INT NOT NULL AUTO_INCREMENT,
productid VARCHAR(8) NOT NULL,
name VARCHAR(25) NOT NULL,
price DECIMAL(5,2) NOT NULL,
description MEDIUMTEXT NOT NULL,
PRIMARY KEY(rowID)
)
Open/Close Connections
• mysql_connect()
• mysql_pconnect()
• mysql_close()
mysql_connect()
resource mysql_connect ([string hostname [:port] [:/path/to/socket]
[, string username] [, string password]])
<?php
$link1 = @mysql_connect("www.example.com", "webuser", "abcde")
or die("Could not connect to MySQL server!");
$link2 = @mysql_connect("www.example.org", "webuser", "secret")
or die("Could not connect to MySQL server!");
?>
mysql_pconnect()
• Works just like mysql_connect() except it
checks to see if the connection is already
open. If an open connection already exists it
uses that connection instead of creating a new
one.
mysql_close()
boolean mysql_close ([resource link_id])
<?php
@mysql_connect("localhost", "webuser", "secret")
or die("Could not connect to MySQL server!");
@mysql_select_db("company")
or die("Could not select database!");
echo "You're connected to a MySQL database!";
mysql_close();
?>
Storing Connection Information in a Separate File
Store the connect script in a separate header file
<?php
@mysql_connect("localhost","webuser","secret")
or die("Could not connect to MySQL server!");
?>
This file can then be included as necessary, like so:
<?php
include "mysql.connect.php";
// begin database selection and queries.
?>
Securing Your Connection Information
• Use system-based user permissions to ensure that only
the user owning the Web server daemon process is
capable of reading the file. On Unix-based systems, this
means changing the file ownership to that of the user
running the Web process and setting the connection
file permissions to -r--------.
• If you’re connecting to a remote MySQL server, keep in
mind that this information will be passed in plain text
unless appropriate steps are taken to encrypt that data
during transit. Your best bet is to use Secure Sockets
Layer (SSL) encryption.
Choosing a Database
mysql_select_db()
boolean mysql_select_db (string db_name
[, resource link_id])
<?php
@mysql_connect("localhost", "webuser", "secret")
or die("Could not connect to MySQL server!");
@mysql_select_db("company")
or die("Could not select database!");
?>
Querying MySQL
• mysql_query()
• mysql_db_query()
• Resource Identifier
mysql_query()
resource mysql_query (string query, [resource link_id])
<?php
/* Connect to MySQL server and select database. */
$linkID = @mysql_connect("localhost","webuser","secret")
or die("Could not connect to MySQL server");
@mysql_select_db("company") or die("Could not select database");
/* Create and execute query. */
$query = "INSERT INTO product set productid='abcd123', name='pants', price='45.20'";
$result = mysql_query($query);
/* Close connection to database server. */
mysql_close();
?>
Resource Identifier
• DESCRIBE, EXPLAIN, SELECT, and SHOW
queries return a Resource Identifier on
success and FALSE on failure
• ALL other queries TRUE on success and FALSE
on failure
• The Resource Identifier can be passed to other
functions that can provide information about
the query
Retrieving and Displaying Data
•
•
•
•
mysql_result()
mysql_fetch_row()
mysql_fetch_array()
mysql_fetch_assoc()
mysql_result()
mixed mysql_result (resource result_set, int row [, mixed field])
• Retreives data from one filed of the specified
row found in the result set.
• Simple but inefficient.
• Note in examples that
– Row is specified as an offset
– Field is identified by field name
mysql_result() cont.
Find two values in a single row
<?php
...
$query = "SELECT productid, name FROM product
ORDER BY name";
$result = mysql_query($query);
$productid = mysql_result($result, 0, "productid");
$name = mysql_result($result, 0, "name");
...
?>
mysql_result() cont.
Find all values (rows) of two fields
<?php
...
$query = "SELECT productid, name FROM product ORDER BY name";
$result = mysql_query($query);
// Loop through each row, outputting the productid and name
for ($count=0; $count <= mysql_numrows($result); $count++)
{
$productid = mysql_result($result, $count, "productid");
$name = mysql_result($result, $count, "name");
echo "Product: $name ($productid) <br />";
}
...
?>
mysql_fetch_row()
• array mysql_fetch_row (resource result_set)
• Retrieves an entire row
• Places values in an indexed array
mysql_fetch_row() cont.
<?php
...
$query = "SELECT productid, name FROM product ORDER BY name";
$result = mysql_query($query);
while (list($productid, $name) = mysql_fetch_row($result))
{
echo "Product: $name ($productid) <br />";
}
...
?>
• By using the list() function and a while loop, you can assign the field values
to a variable as each row is encountered, foregoing the additional steps
otherwise necessary to assign the array values to variables.
mysql_fetch_array()
array mysql_fetch_array (resource result_set [,int result_type])
• an enhanced version of mysql_fetch_row()
• result_type determines how data is retrieved
– MYSQL_ASSOC: Returns the row as an associative array, with the key
represented by the field name and the value by the field contents.
– MYSQL_NUM: Returns the row as a numerically indexed array, with
the ordering determined by the ordering of the field names as
specified within the array. If an asterisk is used (signaling the query to
retrieve all fields), the ordering will correspond to the field ordering in
the table definition. Designating this option results in
mysql_fetch_array() operating in the same fashion as
mysql_fetch_row().
– MYSQL_BOTH: Returns the row as both an associative and a
numerically indexed array. Therefore, each field could be referred to
in terms of its index offset and its field name. This is the default.
mysql_fetch_array()
cont.
$query = "SELECT productid, name FROM product ORDER BY
name";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$name = $row['name'];
$productid = $row['productid'];
echo "Product: $name ($productid) <br />";
}
mysql_fetch_array()
cont.
$query = "SELECT productid, name FROM product ORDER
BY name";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
$name = $row[1];
$productid = $row[0];
echo "Product: $name ($productid) <br />";
}
mysql_fetch_assoc()
array mysql_fetch_assoc (resource result_set)
• Identical to mysql_fetch_array() when
MYSQL_ASSOC is passed in as the result_type
parameter.
Inserting Data
• Inserting data is similar to retrieving data
except that the query often contains variable
data.
• Two steps
– Collect the data
– Insert the data into the database
• Example
– HTML form collects data
– PHP script inserts data
HTML form insert
(insert.php)
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<p>
Product ID:<br />
<input type="text" name="productid" size="8" maxlength="8" value="" />
</p>
<p>
Name:<br />
<input type="text" name="name" size="25" maxlength="25" value="" />
</p>
<p>
Price:<br />
<input type="text" name="price" size="6" maxlength="6" value="" />
</p>
<p>
Description:<br />
<textarea name="description" rows="5" cols="30"></textarea>
</p>
<p>
<input type="submit" name="submit" value="Submit!" />
</p>
</form>
PHP code for the insert
<?php
// If the submit button has been pressed
if (isset($_POST['submit']))
{
// Connect to the server and select the database
$linkID = @mysql_connect("localhost","webuser","secret")
or die("Could not connect to MySQL server");
@mysql_select_db("company") or die("Could not select database");
// Retrieve the posted product information.
$productid = $_POST['productid'];
$name = $_POST['name'];
$price = $_POST['price'];
$description = $_POST['description'];
// Insert the product information into the product table
$query = "INSERT INTO product SET productid='$productid', name='$name',
price='$price', description='$description'";
$result = mysql_query($query);
// Display an appropriate message
if ($result) echo "<p>Product successfully inserted!</p>";
else echo "<p>There was a problem inserting the product!</p>";
mysql_close();
}
// Include the insertion form
include "insert.php";
?>
Modifying Data
• Similar to Inserting
• Three steps
– Display existing data
– Collect changes
– Post changes
• Example
– HTML form displays existing value
– HTML form collects changes
– PHP script inserts data
HTML form captures row to be modified.
<form action="modify.php" method="post">
<select name="rowID">
<option name="">Choose a product:</option>
<option name="2">Apples</option>
<option name="1">Bananas</option>
<option name="3">Oranges</option>
</select>
<input type="submit" name="submit" value="Submit"
/>
</form>
PHP code retrieves row & displays filled in form
(modify.php)
// If the form has been submitted
if (isset($_POST['submit']))
{
// Retrieve the posted rowID
$rowID = $_POST['rowID'];
// Select the product data based on the rowID
$query = "SELECT name, productid, price, description FROM product
WHERE rowID='$rowID'";
$result = mysql_query($query);
// Assign the product information to variables
list($name,$productid,$price,$description) = mysql_fetch_row($result);
// Include the form where the product data will be populated
include "modifyform.php";
}
HTML form
(modifyform.php)
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<input type="hidden" name="rowID" value="<?php echo $rowID;?>">
<p>
Product ID:<br />
<input type="text" name="productid" size="8" maxlength="8"
value="<?php echo $productid;?>" />
</p>
<p>
Name:<br />
<input type="text" name="name" size="25" maxlength="25"
value="<?php echo $name;?>" />
</p>
<p>
Price:<br />
<input type="text" name="price" size="6" maxlength="6"
value="<?php echo $price;?>" />
</p>
<p>
Description:<br />
<textarea name="description" rows="5" cols="30">
<?php echo $description;?></textarea>
</p>
<p>
<input type="submit" name="submit" value="Submit!" />
</p>
</form>
Update Example
if (isset($_POST['submit']))
{
// Assign the posted information to variables
$rowID = $_POST['rowID'];
$productid = $_POST['productid'];
$name = $_POST['name'];
$price = $_POST['price'];
$description = $_POST['description'];
// Update the database with the new product information
$query = "UPDATE product SET productid='$productid', name='$name',
price='$price', description='$description'
WHERE rowID='$rowID'";
$result = mysql_query($query);
// Inform the reader whether the update process was successful
if ($result) echo "<p>The product has been successfully updated.</p>";
else echo "<p>There was a problem updating the product.</p>";
}
Deleting Data
• Similar to Modification
• Three steps
– Display existing data
– Select data to be deleted
– Post changes
• Example
– PHP script deletes data
Delete example
<?php
// Connect to the server and select the database
mysql_connect("localhost","webuser","secret");
mysql_select_db("company");
// Has the form been submitted?
if (isset($_POST['submit']))
{
// Loop through each product with an enabled checkbox
foreach($count=0; $count < count($_POST['rowID']); $count++)
{
$rowID = $_POST['rowID'][$count];
$query = "DELETE FROM product WHERE rowID='$rowID'";
$result = mysql_query($query);
// Should have one affected row
if ((mysql_affected_rows() == 0) || mysql_affected_rows() == -1) {
echo "<p>There was a problem deleting some of the selected items.</p>";
exit;
}
}
echo "<p>The selected items were successfully deleted.</p>";
}
?>
mysql_num_rows()
int mysql_num_rows (resource result_set)
• Return value is number of row that are
returned by a SELECT query.
• Return value of -1 indicates query error.
$query = "SELECT name FROM product WHERE price > 15.99";
$result = mysql_query($query);
echo "There are ".mysql_num_rows($result)." product(s) priced above \$15.99.";
mysql_affected_rows()
int mysql_affected_rows ([resource link_id])
• Return value is number of rows affected by
INSERT, UPDATE, or DELETE query.
$query = "UPDATE product SET price = '39.99' WHERE price='34.99'";
$result = mysql_query($query);
echo "There were ".mysql_affected_rows()." product(s) affected. ";
mysql_list_dbs()
resource mysql_list_dbs ([resource link_id])
• Retrieves the names of all databases found on
the server.
• If link_id specified then databases for that
server connection, otherwise most recently
opened server connection.
mysql_db_name()
string mysql_db_name (resource result_set, integer index)
• retrieves the name of the database located at position index
mysql_list_tables()
resource mysql_list_tables (string database [, resource link_id])
• Return the names of all tables in the database.
<?php
mysql_connect("localhost","webuser","secret");
$tables = mysql_list_tables("company");
while (list($table) = mysql_fetch_row($tables))
{
echo "$table <br />";
}
?>
mysql_tablename()
string mysql_tablename (resource result_set, integer index)
• retrieves the name of the table located at position index
<?php
mysql_connect("localhost","webuser","secret");
$tables = mysql_list_tables("company");
$count = 0;
while ($count < mysql_numrows($tables))
{
echo mysql_tablename($tables,$count)."<br />";
$count++;
}
?>
mysql_fetch_field()
object mysql_fetch_field (resource result [, int field_offset])
• retrieves an object containing information pertinent to the
field specified by field_offset
• Object properties are:
–
–
–
–
–
–
name – field name
table – field table
max_length
not_null
primary_key
unique_key
- multiple_key
- numeric
- blob
- type
- unsigned
- zerofill
mysql_fetch_field() example
•
•
•
•
•
•
•
•
•
•
•
•
<?php
mysql_connect("localhost","webuser","secret");
mysql_select_db("company");
$query = "SELECT * FROM product LIMIT 1";
$result = mysql_query($query);
$fields = mysql_num_fields($result);
for($count=0;$count<$fields;$count++)
{
$field = mysql_fetch_field($result,$count);
echo "<p>$field->name $field->type ($field->max_length)</p>";
}
?>
mysql_num_fields()
integer mysql_num_fields (resource result_set)
• returns the number of fields located in the result_set
<?php
...
$query = "SELECT productid, name FROM product ORDER BY name";
$result = mysql_query($query);
echo "Total number of fields returned: ".mysql_num_fields($result).".<br />";
...
?>
mysql_list_fields()
resource mysql_list_fields (string database_name, string table_name
[, resource link_id])
• retrieves the names of all fields located in
table_name
$fields = mysql_list_fields("company","product");
echo "Total number of fields returned:
".mysql_num_fields($fields).".<br />";
mysql_field_flags()
string mysql_field_flags (resource result_set, integer field_offset)
• retrieves all options assigned to the field
located in position field_offset of the
result_set
$query = "SELECT productid, name FROM product ORDER BY name";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_flags($result, 0);
mysql_field_len()
integer mysql_field_len (resource result_set, integer field_offset)
• retrieves the length of the field residing in the
field_offset position of result_set
$query = "SELECT description FROM product WHERE productid='tsbxxl'";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_len($result, 0);
mysql_field_name()
string mysql_field_name (resource result_set, int field_offset)
• returns the name of the field specified by the
field_offset position of result_set
$query = "SELECT productid as Product_ID, name FROM product ORDER BY name";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_name($result, 0);
mysql_field_type()
string mysql_field_type (resource result_set, int field_offset)
• returns the type of the field specified by the
field_offset position of result_set
$query = "SELECT productid, name FROM product ORDER BY name";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_type($result, 0);
mysql_field_table()
string mysql_field_table (resource result_set, int
field_offset)
• returns the name of the table that contains
the field specified by the field_offset position
of result_set.
$query = "SELECT productid as Product_ID, name FROM product ORDER BY name";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_table($result, 0);
Viewing Table Properties
<?php
mysql_connect("localhost","webuser","secret");
// The view_db_properties() function retrieves table information for
// the database defined by the input parameter $db, and invokes
// view_table_properties() for each table instance located within
// that database.
function view_db_properties($db)
{
mysql_select_db($db);
$tables = mysql_list_tables($db);
while (list($tableName) = mysql_fetch_row($tables))
{
echo "<p>Table: <b>$tableName</b></p>";
echo "<table border='1'>";
echo "<tr><th>Field</th><th>Type</th><th>Length</th><th>Flags</th>";
echo view_table_properties($tableName);
echo "</table>";
}
}
Viewing Table Properties (cont)
// The view_table_properties() function retrieves
// field properties for the table defined by the input parameter $table. */
function view_table_properties($table)
{
$tableRows = "";
// Retrieve a single row from the table,
// giving us enough field information to determine field properties.
$result = mysql_query("SELECT * FROM $table LIMIT 1");
$fields = mysql_num_fields($result);
for($count=0; $count < $fields; $count++)
{
// Retrieve field properties
$name = mysql_field_name($result,$count);
$type = mysql_field_type($result,$count);
$length = mysql_field_len($result,$count);
$flags = mysql_field_flags($result,$count);
$tableRows .= "<tr><td>$name</td>
<td>$type</td>
<td>$length</td>
<td>$flags</td></tr>";
}
return $tableRows;
}
view_db_properties("company");
?>