-Facilitates Sharing of Data
-Providing Data Independence
-Ad Hoc Querying
-Securing Data
-Minimizing necessary programmer expertise
-Achieving Computational Efficiency
2. Using MySQL Databases
Database Interaction Sequential Pattern
-Open a Connection the database server
-Specify the database to be accessed
-Issue sql queries, access query results, and perform non-sql functions
-Close the connection to the sql server
3. Connecting to the Database
Opening a connection to MySQL database from PHP is easy. Just use the mysql_connect() function like this
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');?>
You can omit the hostname, id, and password or all of the three arguments. But the following values will be assumed:
Host name : localhost
User ID : the ID of the MySQL server process
Password : an empty password
4. Selecting the Database
Once your program has been connected to the database server, the program can specify the database it will access
Invoke the mysql_select_db() function which has this form
mysql_select_db(database)
Example
$db_selected = mysql_select_db("test_db", $con);
if (!$db_selected)
{ die ("Can\'t use test_db : " . mysql_error()); }
5. Checking for Errors
mysql_errno
returns the numerical value of the error message from previous MySQL operation
Example:
$link = mysql_connect("localhost", "mysql_user", "mysql_password");
if (!mysql_select_db("nonexistentdb", $link))
{echo mysql_errno($link) . ": ".mysql_error($link). "\n";}
Output:
1049: Unknown database 'nonexistentdb'
6. Suppressing Unwanted Errors and warnings
error_reporting()
Sets which PHP errors are reported
error_reporting() level constants and bit values
Suppressing Unwanted Errors and warnings
<?php // Turn off all error reporting error_reporting(0);// Report simple running errors error_reporting(E_ERROR | E_WARNING | E_PARSE); // Reporting E_NOTICE can be good too (to report uninitialized // variables or catch variable name misspellings ...) error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE); // Report all errors except E_NOTICE // This is the default value set in php.ini error_reporting(E_ALL ^ E_NOTICE);?>
7. Closing the Mysql Connection
bool mysql_close ( [resource $link_identifier] )
closes the non-persistent connection to the MySQL server that's associated with the specified link identifier.
Returns TRUE on success or FALSE on failure.
If link_identifier isn't specified, the last opened link is used
Example
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) { die('Could not connect: ' . mysql_error());}
echo 'Connected successfully';
mysql_close($link);
8. Performing CRUD
Create
-add new entries
Read
-retrieve or view existing entries
Update
-edit existing entries
Delete
-Remove existing entries
resource mysql_query ( string $query [, resource $link_identifier] )
sends unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
9. Parameters
query
A SQL query
The query string should not end with a semicolon.
10. link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.
11. Return Values
For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error
For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
12. Checking Queries That Do Not Return Table Rows
int mysql_affected_rows ( [resource $link_identifier] )
Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier.
Returns the number of affected rows on success, and -1 if the last query failed.
Example
//this should return the correct numbers of deleted records mysql_query('DELETE FROM mytable WHERE id < 10');printf("Records deleted: %d\n", mysql_affected_rows());//with a where clause that is never true, it should return 0mysql_query('DELETE FROM mytable WHERE 0');printf("Records deleted: %d\n", mysql_affected_rows());
Output
Records deleted: 10
Records deleted: 0
13. Using Auto_Increment Table Columns
int mysql_insert_id ( [resource $link_identifier] )
Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
The ID generated for an AUTO_INCREMENT column by the previous INSERT query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.
Example of mysql_insert_id
mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
14. Processing the results of Select Queries
int mysql_num_rows ( resource $result )
Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set.
Example
$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows\n";
array mysql_fetch_row ( resource $result )
Returns a numerical array that corresponds to the fetched row and moves the internal data pointer ahead.
Returns an numerical array of strings that corresponds to the fetched row, or FALSE if there are no more rows.
etches 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
mysql_fetch_row() example
$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");
if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;
}
$row = mysql_fetch_row($result);
echo $row[0]; // echo $row[1]; // the email value
array mysql_fetch_array ( resource $result [, int $result_type] )
Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.
Parameters
result
The result resource that is being evaluated. This result comes from a call to mysql_query().
result_type
The type of array that is to be fetched. It's a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and the default value of MYSQL_BOTH.
Returns
array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined
Example of mysql_fetch_array() function
mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());
mysql_select_db("mydb");
$result = mysql_query("SELECT id, name FROM mytable");
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { printf ("ID: %s Name: %s", $row[0], $row["name"]);}
mysql_free_result($result);
15. Working with ResultSet
Obtaining the Number of ResultSet Columns using mysql_num_fields()
Returns the number of fields in the result set resource on success, or FALSE on failure.
Example
$result = mysql_query("SELECT name,address FROM people WHERE id = '42'");
if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}
echo mysql_num_fields($result);
Output
/* returns 2 because name,address === two fields */
Obtaining the Name of a ResultSet Columns using mysql_field_name()
Returns the name of the specified field index on success, or FALSE on failure.
Example
$res = mysql_query('select * from users', $link);
echo mysql_field_name($res, 0) . "\n";
echo mysql_field_name($res, 2);
The above example will output:
user_id
password
16. Obtaining the length of a ResultSet Columns using mysql_field_len()
Returns the length of the specified field index on success, or FALSE on failure.
Example
$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");
$length = mysql_field_len($result, 0); echo $length;
// Will get the length of the id field as specified in the database schema.
Example of mysql_field_type() function
mysql_connect("localhost", "mysql_username", "mysql_password");mysql_select_db("mysql");
$result = mysql_query("SELECT * FROM func");
$fields = mysql_num_fields($result);
$rows = mysql_num_rows($result);
$table = mysql_field_table($result, 0);
echo "Your '" . $table . "' table has " . $fields . " fields and " . $rows . " record(s)\n";
echo "The table has the following fields:\n";
for ($i=0; $i < $fields; $i++) { $type = mysql_field_type($result, $i);
$name = mysql_field_name($result, $i);
$len = mysql_field_len($result, $i);
$flags = mysql_field_flags($result, $i); echo $type . " " . $name . " " . $len . " " . $flags ."\n";}mysql_free_result($result);
mysql_close();
17. Determining the MySQL Table Associated with a ResultSet Column
Returns the name of the table on success of the specified field
Example
$table = mysql_field_table($result, 'name');
Obtaining the complete ResultSet Structure using mysql_fetch_field()
Returns an object containing field information. This function can be used to obtain information about fields in the provided query result.
name - column name
table - name of the table the column belongs to
def - default value of the column
max_length - maximum length of the column
not_null - 1 if the column cannot be NULL
primary_key - 1 if the column is a primary key
unique_key - 1 if the column is a unique key
multiple_key - 1 if the column is a non-unique key
numeric - 1 if the column is numeric
blob - 1 if the column is a BLOB
type - the type of the column
unsigned - 1 if the column is unsigned
zerofill - 1 if the column is zero-filled
Example
$meta = mysql_fetch_field($result, 0);
18. Accessing ResultSet Columns Non-Sequentially using mysql_data_seek()
moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row.
Example
for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--)
{
if (!mysql_data_seek($result, $i))
{ echo "Cannot seek to row $i: " . mysql_error() . "\n";continue;}
if (!($row = mysql_fetch_assoc($result))) {continue;}
echo $row['last_name'] . ' ' . $row['first_name'] . "<br />\n";
}
19. Exploring the DBMS
Determining the database hosted by a server
Returns a result pointer containing the databases available from the current mysql daemon.
Example
$db_list = mysql_list_dbs($link);
while ($row = mysql_fetch_object($db_list))
{echo $row->Database . "\n";}
20. Quotes and Magic Quotes
Magic Quotes - Are They Enabled?
First things first, you need to check to see if you have magic quotes enabled on you server. The get_magic_quotes_gpc function will return a 0 (off) or a 1 (on). These boolean values will fit nicely into an if statement where 1 is true and 0 is false.
Determine if they are enabled
if(get_magic_quotes_gpc())
echo "Magic quotes are enabled";
else
echo "Magic quotes are disabled";
21. Quotes and Magic Quotes get_magic_quotes_gpc()
<?php echo "Removed Slashes: ";
// Remove those slashes
if(get_magic_quotes_gpc())
echo stripslashes($_POST['question']);
else echo $_POST['question']; ?>
<form method='post'>
Question: <input type='text' name='question'/>
<br />
<input type='submit'>
</form>
22. Escaping and Unescaping
Example
$escaped = addslashes($text);
$unescaped = stripslashes($text);
$htmltext = htmlspecialchars(text[,quote_style]);
Permissible quote_style values
ENT_COMPAT – only the double quotes
ENT_QUOTES – both quotes
ENT_NOQUOTES – no quotes
Nice tutorials Mario - Possibly this one could be updated to use mysqli now ?
ReplyDelete