Working with MySQL

1. Working with MySQL • To build an application that uses databases you need to know the basics of: • 1. Creating database tables that can hold your data • 2. Writing SQL queries to manipulate that data • 3. Connecting to your MySQL database using PHP code • 4. Sending SQL queries to the database, and retrieving the results

2. Creating Database Tables • A data table is made up of columns (fields), and rows (records). When creating a data table you need to define its fields as follows, which can have various properties. • Primary Keys • Data Types (numerical types, character and string types, and date and time types) • NULL and NOT NULL columns • Default column values • auto_increment columns • Indexes • Example of a SQL command that creates a simple data table: CREATE TABLE users ( user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(32) NOT NULL, PRIMARY KEY (user_id) );

5. Manipulating DAta • Commands used to retrieve, add, modify, and delete records from data tables: SELECT <column list> FROM <table name(s)> [WHERE <restrictive condition(s)>] INSERT INTO <table name> [(column list)] VALUES (column values) UPDATE <table name> SET <column name> = <new value> [, <column name> = <new value> ... ] [WHERE <restrictive condition>] DELETE FROM <table name> [WHERE <restrictive condition>]

6. Basic things should be kept in mind • The SQL code can be written in one or more lines, however you feel it looks nicer. • If you want to execute several SQLcommands at once, you must separate them using the semicolon (;). • The values written between square brackets in the syntax are optional. (Be careful with the DELETE statement though; if you don't specify a restrictive condition, all elements will be deleted.) • With SELECT, you can specify *, instead of the column list, which includes all the existing table columns. • SQL is not case sensitive, but we will try to write the SQL statements in uppercase, and the table and field names in lowercase. Consistency is always good.

7. Example operations • INSERT INTO users (user_name) VALUES ('john'); • INSERT INTO users (user_name) VALUES ('sam'); • INSERT INTO users (user_name) VALUES ('ajax'); • SELECT user_id, user_name FROM users; • UPDATE users SET user_name='cristian' WHERE user_id=1; • SELECT user_id, user_name FROM users; • DELETE FROM users WHERE user_id=3; • SELECT * FROM users WHERE user_id>1;

8. Connecting to Database and Executing Queries

9. Preparing for MySQL • Connect to the ajax database, and create a table named users with the following code: CREATE TABLE users ( user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(32) NOT NULL, PRIMARY KEY (user_id) ); 2. Execute the following INSERT commands to populate your users table with some sample data: INSERT INTO users (user_name) VALUES ('bogdan'); INSERT INTO users (user_name) VALUES ('filip'); INSERT INTO users (user_name) VALUES ('mihai'); INSERT INTO users (user_name) VALUES ('emilian'); INSERT INTO users (user_name) VALUES ('paula'); INSERT INTO users (user_name) VALUES ('cristian');

10. MySQL methods of mysqli extension: • query(): this function returns true or an object containing the results of the SQL statement if it executes successfully. The result object is returned from SELECT, DESCRIBE OR SHOW statements. • fetch_object(result): return the result set as an object • fetch_row(result): returns an array which contains the current row. • fetch_array (result, type): This method can return data in an array and user either numeric or associative indices to access it. In the associative array the names of the fields are used as the keys. The type field can take one of the constants MYSQLI_NUM, MYSQLI_ASSOC or MYSQLI_BOTH

11. Config.php <?php // defines database connection data define('DB_HOST', 'localhost'); define('DB_USER', ‘root'); define('DB_PASSWORD', ‘asie'); define('DB_DATABASE', ‘test'); ?>

12. Error_handler.php <?php // set the user error handler method to be error_handler set_error_handler('error_handler', E_ALL); // error handler function function error_handler($errNo, $errStr, $errFile, $errLine) { // clear any output that has already been generated ob_clean(); // output the error message $error_message = 'ERRNO: ' . $errNo . chr(10) . 'TEXT: ' . $errStr . chr(10) . 'LOCATION: ' . $errFile . ', line ' . $errLine; echo $error_message; // prevent processing any more PHP scripts exit; } ?>

13. Index.php <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html> <head> <title>Practical AJAX: Working with PHP and MySQL</title> </head> <body> <?php // load configuration file require_once('error_handler.php'); require_once('config.php'); // connect to the database $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE); // the SQL query to execute $query = 'SELECT * FROM users'; // execute the query $result = $mysqli->query($query);

14. Index.php (cont.d) // loop through the results while ($row = $result->fetch_array(MYSQLI_ASSOC)) { // extract user id and name $user_id = $row['user_id']; $user_name = $row['user_name']; // do something with the data (here we output it) echo 'Name of user #' . $user_id . ' is ' . $user_name . '<br/>'; } // close the input stream $result->close(); // close the database connection $mysqli->close(); ?> </body> </html>

15. Steps for writing PHP for MySQL operation • Connecting to the database server: • $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE); • Generate a query by using either SELECT, DESCRIBE OR SHOW statements • Use query() method to put a query to MYSQL. • Process the data object by the PHP’s mysqli extension method. • Display it to clients.

16. Validating forms exampleThings happen after submitting • Index.php structure: require_once ('index_top.php'); <link href="validate.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="validate.js"></script> <form name="frmRegistration" method="post" action="validate.php?validationType=php">

17. // initialize some session variables to prevent PHP throwing Notices if (!isset($_SESSION['values'])) { $_SESSION['values']['txtUsername'] = ''; $_SESSION['values']['txtName'] = ''; $_SESSION['values']['selGender'] = ''; $_SESSION['values']['selBthMonth'] = ''; $_SESSION['values']['txtBthDay'] = ''; $_SESSION['values']['txtBthYear'] = ''; $_SESSION['values']['txtEmail'] = ''; $_SESSION['values']['txtPhone'] = ''; $_SESSION['values']['chkReadTerms'] = ''; } if (!isset($_SESSION['errors'])) { $_SESSION['errors']['txtUsername'] = 'hidden'; $_SESSION['errors']['txtName'] = 'hidden'; $_SESSION['errors']['selGender'] = 'hidden'; $_SESSION['errors']['selBthMonth'] = 'hidden'; $_SESSION['errors']['txtBthDay'] = 'hidden'; $_SESSION['errors']['txtBthYear'] = 'hidden'; $_SESSION['errors']['txtEmail'] = 'hidden'; $_SESSION['errors']['txtPhone'] = 'hidden'; $_SESSION['errors']['chkReadTerms'] = 'hidden'; } Sessions added to index_top.php

18. Validate.php structures <?php // start PHP session session_start(); // load error handling script and validation class require_once ('error_handler.php'); require_once ('validate.class.php'); // Create new validator object $validator = new Validate(); // read validation type (PHP or AJAX?) $validationType = ''; if (isset($_GET['validationType'])) { $validationType = $_GET['validationType']; } // AJAX validation or PHP validation? if ($validationType == 'php') { // PHP validation is performed by the ValidatePHP method, which returns // the page the visitor should be redirected to (which is allok.php if // all the data is valid, or back to index.php if not) header("Location:" . $validator->ValidatePHP()); } Else { ajax checking: next week;} ?>

19. Validate.class.php structures • The file declare the classed which used by validate.php: • Open database • Public function ValidateAJAX • Public function ValidatePHP • If no errors are found , point to a successful validation page allok.php • Otherwise return to index.php • Private functions about the field validation.