Download
1 / 23
270 likes | 476 Views
PHP and MySQL Database. Connecting to MySQL. Note: you need to make sure that you have MySQL software properly installed on your computer before you attempt to make a connection. You also need to have a user name and password. Syntax for connecting to a database is:
E N D
Connecting to MySQL • Note: you need to make sure that you have MySQL software properly installed on your computer before you attempt to make a connection. You also need to have a user name and password. • Syntax for connecting to a database is: • $dbc = mysql_connect (‘localhost’, ‘username’, ‘password’); • Once you are done working with the DB close the connection: • mysql_close();
PHP Script to Test Database Connection <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>Connect to MySQL</title> </head> <body> <?php // Script 12.1 - mysql_connect.php (This script connects to the MySQL server). // Address error handling. ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); // Attempt to connect to MySQL and print out messages. if ($dbc = mysql_connect ('localhost', 'username', 'password')) { print '<p>Successfully connected to MySQL.</p>'; mysql_close(); // Close the connection. } else { print '<p>Could not connect to MySQL.</p>'; } ?> </body> </html>
MySQL Error Handling • Common errors you will encounter are: • Failure to connect to MySQL • Failure in selecting a database • Inability to run a query • No results being returned by a query • Data not being inserted into a table • Using mysql_error() function returns a textual version of the error that the MySQL server returned: • Die (‘<p>Could not connect to MySQL because:<b>’. Mysql_error() . ‘</b></p>’);
Creating and Selecting a Databse • To create a database with PHP use: • mysql_query(‘CREATE DATABASE mydatabase’); • To select the database use: • Mysql_select_db(‘mydatabase’);
PHP Code to Create and Select a DB <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>Create the Database</title> </head> <body> <?php // Script 12.3 - create_db.php (This script connects to the MySQL server. It also creates and selects the database. // Address error handling. ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); // Attempt to connect to MySQL and print out messages. if ($dbc = @mysql_connect ('localhost', 'username', 'password')) { print '<p>Successfully connected to MySQL.</p>'; if (@mysql_query ('CREATE DATABASE myblog')) { print '<p>The database has been created.</p>'; }
Continued else { die ('<p>Could not create the database because: <b>' . mysql_error() . '</b></p>'); } if (@mysql_select_db ('myblog')) { print '<p>The database has been selected.</p>'; } else { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); } mysql_close(); // Close the connection. } else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>'); } ?> </body> </html>
Creating a Table <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>Create a Table</title> </head> <body> <?php // Script 12.4 - create_table.php (This script connects to the MySQL server, selects the database, and creates a table). // Address error handling. ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); // Connect and select. if ($dbc = @mysql_connect ('localhost', 'username', 'password')) { if (!@mysql_select_db ('myblog')) { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); } } else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>'); }
Continued // Define the query. $query = 'CREATE TABLE blog_entries ( blog_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100) NOT NULL, entry TEXT NOT NULL, date_entered DATETIME NOT NULL )'; // Run the query. if (@mysql_query ($query)) { print '<p>The table has been created.</p>'; } else { die ('<p>Could not create the table because: <b>' . mysql_error() . '</b>.</p> <p>The query being run was: ' . $query . '</p>');} mysql_close(); // Close the connection. ?> </body> </html>
Inserting Data into a DB <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>Add a Blog Entry</title> </head><body> <?php // Script 12.5 - add_entry.php (This script adds a blog entry to the database). // Address error handling. ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); if (isset ($_POST['submit'])) { // Handle the form. // Connect and select. if ($dbc = @mysql_connect ('localhost', 'username', 'password')) { if (!@mysql_select_db ('myblog')) { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); } } else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>'); }
Continued // Define the query. $query = "INSERT INTO blog_entries (blog_id, title, entry, date_entered) VALUES (0, '{$_POST['title']}', '{$_POST['entry']}', NOW())"; // Execute the query. if (@mysql_query ($query)) { print '<p>The blog entry has been added.</p>'; } else { print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>"; } mysql_close(); } // Display the form. ?> <form action="add_entry.php" method="post"> <p>Entry Title: <input type="text" name="title" size="40" maxsize="100" /></p> <p>Entry Text: <textarea name="entry" columns="40" rows="5"></textarea></p> <input type="submit" name="submit" value="Add to the Blog!" /> </form> </body> </html>
Retrieving Data from a Database • Retrieved information must be assigned to a variable: $query = ‘SELECT * FROM tablename’; • You may use the WHERE clause to restrict which records to be retrieved:$query = ‘SELECT Lname, Fname FROM employees WHERE (dept = ‘sales’);
Retrieving Table Information <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>View My Blog</title> </head> <body> <?php // Script 12.6 - view_blog.php // This script retrieves blog entries from the database. // Address error handling. ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); // Connect and select. if ($dbc = @mysql_connect ('localhost', 'username', 'password')) { if (!@mysql_select_db ('myblog')) { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); }} else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');} // Define the query. $query = 'SELECT * FROM blog_entries ORDER BY date_entered DESC'; if ($r = mysql_query ($query)) {
Code Continued // Run the query. // Retrieve and print every record. while ($row = mysql_fetch_array ($r)) { print "<p><h3>{$row['title']}</h3> {$row['entry']}<br /> <a href=\"edit_entry.php?id={$row['blog_id']}\">Edit</a> <a href=\"delete_entry.php?id={$row['blog_id']}\">Delete</a> </p><hr />\n"; }} else { // Query didn't run. die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $query.</p>");} // End of query IF. mysql_close(); // Close the database connection. ?> </body> </html>
Deleting Data • The syntax is: DELETE FROM tablename WHERE column=value LIMIT 1; • Function mysql_affected_rows() returns the number of rows affected by an insert, delete or update query.
Deleting Table Information <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>Delete a Blog Entry</title> </head> <body> <?php // Script 12.7 - delete_entry.php // This script deletes a blog entry. // Address error handling. ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); // Connect and select. if ($dbc = @mysql_connect ('localhost', 'username', 'password')) { if (!@mysql_select_db ('myblog')) { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); }} else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');} if (isset ($_POST['submit'])) { // Handle the form.
Code Continued // Define the query. $query = "DELETE FROM blog_entries WHERE blog_id={$_POST['id']} LIMIT 1"; $r = mysql_query ($query); // Execute the query. // Report on the result. if (mysql_affected_rows() == 1) { print '<p>The blog entry has been deleted.</p>'; } else { print "<p>Could not delete the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>"; }} else { // Display the entry in a form. // Check for a valid entry ID in the URL. if (is_numeric ($_GET['id']) ) { // Define the query. $query = "SELECT * FROM blog_entries WHERE blog_id={$_GET['id']}"; if ($r = mysql_query ($query)) { // Run the query. $row = mysql_fetch_array ($r); // Retrieve the information.
Code Continued // Make the form. print '<form action="delete_entry.php" method="post"> <p>Are you sure you want to delete this entry?</p> <p><h3>' . $row['title'] . '</h3>' . $row['entry'] . '<br /> <input type="hidden" name="id" value="' . $_GET['id'] . '" /> <input type="submit" name="submit" value="Delete this Entry!" /></p> </form>'; } else { // Couldn't get the information. print "<p>Could not retrieve the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>"; } } else { // No ID set. print '<p><b>You must have made a mistake in using this page.</b></p>'; }} // End of main IF. mysql_close(); // Close the database connection. ?> </body> </html>
Updating Data in a Database • WHERE clause must be used or all the records in the database will be updated. • If the values are strings they should be placed between single quotation marks.
Updating Table Information <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>Edit a Blog Entry</title> </head> <body> <?php // Script 12.8 - edit_entry.php // This script edits a blog entry using an UPDATE query. // Address error handling. ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); // Connect and select. if ($dbc = @mysql_connect ('localhost', 'username', 'password')) { if (!@mysql_select_db ('myblog')) { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); }} else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');}
Code Continued if (isset ($_POST['submit'])) { // Handle the form. // Define the query. $query = "UPDATE blog_entries SET title='{$_POST['title']}', entry='{$_POST['entry']}' WHERE blog_id={$_POST['id']}"; $r = mysql_query ($query); // Execute the query. // Report on the result. if (mysql_affected_rows() == 1) { print '<p>The blog entry has been updated.</p>'; } else { print "<p>Could not update the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>"; }} else { // Display the entry in a form. // Check for a valid entry ID in the URL. if (is_numeric ($_GET['id']) ) { // Define the query. $query = "SELECT * FROM blog_entries WHERE blog_id={$_GET['id']}"; if ($r = mysql_query ($query)) { // Run the query. $row = mysql_fetch_array ($r); // Retrieve the information.
Code Continued // Make the form. print '<form action="edit_entry.php" method="post"> <p>Entry Title: <input type="text" name="title" size="40" maxsize="100" value="' . $row['title'] . '" /></p> <p>Entry Text: <textarea name="entry" columns="40" rows="5">' . $row['entry'] . '</textarea></p> <input type="hidden" name="id" value="' . $_GET['id'] . '" /> <input type="submit" name="submit" value="Update this Entry!" /> </form>'; } else { // Couldn't get the information. print "<p>Could not retrieve the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>"; } } else { // No ID set. print '<p><b>You must have made a mistake in using this page.</b></p>'; }} // End of main IF. mysql_close(); // Close the database connection. ?> </body> </html>
How to Create Users and Privileges in MySQL • Open the MySQL monitor and type the following: • mysql> GRANT privileges ON database.* TO username IDENTIFIED BY ‘password’; • Example: • mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON companydb.* TO areej IDENTIFIED BY ‘aa7097’;
