php mysql extensions n.
Download
Skip this Video
Download Presentation
PHP – MySQL Extensions

Loading in 2 Seconds...

play fullscreen
1 / 49

PHP – MySQL Extensions - PowerPoint PPT Presentation


  • 85 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'PHP – MySQL Extensions' - cody-byers


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
table used in most examples
Table used in most examples

CREATE TABLE product (

rowIDINT NOT NULL AUTO_INCREMENT,

productidVARCHAR(8) NOT NULL,

name VARCHAR(25) NOT NULL,

price DECIMAL(5,2) NOT NULL,

description MEDIUMTEXT NOT NULL,

PRIMARY KEY(rowID)

)

open close connections
Open/Close Connections
  • mysql_connect()
  • mysql_pconnect()
  • mysql_close()
m ysql connect
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
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
mysql_close()

booleanmysql_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
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
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
Choosing a Databasemysql_select_db()

booleanmysql_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
Querying MySQL
  • mysql_query()
  • mysql_db_query()
  • Resource Identifier
mysql query
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
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
Retrieving and Displaying Data
  • mysql_result()
  • mysql_fetch_row()
  • mysql_fetch_array()
  • mysql_fetch_assoc()
mysql result
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
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
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
mysql_fetch_row()
  • array mysql_fetch_row (resource result_set)
  • Retrieves an entire row
  • Places values in an indexed array
mysql fetch row cont
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
mysql_fetch_array()

array mysql_fetch_array (resource result_set [,intresult_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
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 cont1
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
mysql_fetch_assoc()

array mysql_fetch_assoc (resource result_set)

  • Identical to mysql_fetch_array() when MYSQL_ASSOC is passed in as the result_typeparameter.
inserting data
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
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 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
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
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
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
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
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
Deleting Data
  • Similar to Modification
  • Three steps
    • Display existing data
    • Select data to be deleted
    • Post changes
  • Example
    • PHP script deletes data
delete example
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
mysql_num_rows()

intmysql_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
mysql_affected_rows()

intmysql_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
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
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
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
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
mysql_fetch_field()

object mysql_fetch_field (resource result [, intfield_offset])

  • retrieves an object containing information pertinent to the field specified by field_offset
  • Object properties are:
    • name – field name - multiple_key
    • table – field table - numeric
    • max_length - blob
    • not_null - type
    • primary_key - unsigned
    • unique_key - zerofill
m ysql fetch field example
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
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
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
mysql_field_flags()

string mysql_field_flags (resource result_set, integer field_offset)

  • retrieves all options assigned to the field located in position field_offsetof 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
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
mysql_field_name()

string mysql_field_name (resource result_set, intfield_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
mysql_field_type()

string mysql_field_type (resource result_set, intfield_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
mysql_field_table()

string mysql_field_table (resource result_set, intfield_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
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
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");

?>

ad