FYBSC IT Web Programming Unit V Advanced PHP and MySQL

PHP MySQL Database  With PHP, you can connect to and manipulate databases.  MySQL is the most popular database system used with PHP.  The data in a MySQL database are stored in tables.  A table is a collection of related data, and it consists of columns and rows.

relational databases  In relational databases and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows.

Database Queries  A query is a question or a request.  We can query a database for specific information and have a recordset returned.  Look at the following query (using standard SQL): SELECT Last_Name FROM Employee  The query above selects all the data in the "LastName" column from the "Employees" table.

PHP Connect to MySQL  PHP 5 can work with a MySQL database using:  MySQLi extension (the "i" stands for improved)  MySQLi (object-oriented)  MySQLi (procedural)  PDO (PHP Data Objects)

Open a Connection to MySQL Example (MySQLi Object-Oriented) <?php $servername = "localhost"; $username = “username"; $password = "password"; // Create connection $conn = new mysqli($servername, $username, $password); // Check connection if ($conn->connect_error){ die("Connection failed: " . $conn->connect_error); } echo "Connected successfully"; ?>

Example (MySQLi Procedural) <?php $servername = "localhost"; $username = "username"; $password = "password"; // Create connection $conn = mysqli_connect($servername, $username, $password); // Check connection if (!$conn){ die("Connection failed: " . mysqli_connect_error()); } echo "Connected successfully"; ?>

Close the Connection  The connection will be closed automatically when the script ends.To close the connection before, use the following:  Example (MySQLi Object-Oriented) $conn->close();  Example (MySQLi Procedural) mysqli_close($conn);

PHP Create a MySQL Database  A database consists of one or more tables.  The CREATE DATABASE statement is used to create a database in MySQL.  If you are using windows by default username is "root" and password is "" (empty),  My Localhost is configured with  Username: root  Password: password

Example (MySQLi Object- oriented) <?php $servername = "localhost"; $username = “root"; $password = "password"; // Create connection $conn = new mysqli($servername, $username, $password); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Create database $sql = "CREATE DATABASE myDB"; if ($conn->query($sql) ===TRUE) { echo "Database created successfully"; } else { echo "Error creating database: " . $conn->error; } $conn->close(); ?>

Example (MySQLi Procedural) <?php $servername = "localhost"; $username = “root"; $password = "password"; // Create connection $conn = mysqli_connect($servername, $username, $password); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } // Create database $sql = "CREATE DATABASE myDB"; if (mysqli_query($conn, $sql)) { echo "Database created successfully"; } else { echo "Error creating database: " . mysqli_error($conn); } mysqli_close($conn); ?>

Create a MySQL Table Using MySQLi  A database table has its own unique name and consists of columns and rows.  The CREATETABLE statement is used to create a table in MySQL.  Example: create a table named "MyGuests", with five columns: "id", "firstname", "lastname", "email" and "reg_date"

Example (MySQLi Object-oriented)  <?php $servername = "localhost"; $username = “root"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); }  // sql to create table $sql = "CREATETABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstnameVARCHAR(30) NOT NULL, lastnameVARCHAR(30) NOT NULL, emailVARCHAR(50), reg_dateTIMESTAMP )"; if ($conn->query($sql) ===TRUE) { echo "Table MyGuests created successfully"; } else { echo "Error creating table: " . $conn- >error; } $conn->close(); ?> 

Insert Data Into MySQL Using MySQLi  After a database and a table have been created, we can start adding data in them.  Here are some syntax rules to follow:  The SQL query must be quoted in PHP  String values inside the SQL query must be quoted  Numeric values must not be quoted  The word NULL must not be quoted  The INSERT INTO statement is used to add new records to a MySQL table: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Example (MySQLi Object-oriented)  <?php $servername = "localhost"; $username = “root"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?>

Insert Multiple Records Into MySQL Using MySQLi  Multiple SQL statements must be executed with the mysqli_multi_query() function.

Example (MySQLi Object-oriented)  <?php $servername = "localhost"; $username = “root"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername , $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')"; if ($conn->multi_query($sql) === TRUE) { echo "New records created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?>

Select Data From a MySQL Database  The SELECT statement is used to select data from one or more tables: SELECT column_name(s) FROM table_name  or we can use the * character to select ALL columns from a table: SELECT * FROM table_name

Example (MySQLi Object- oriented) <!DOCTYPE html> <html> <head> <title>PHP Demo</title> </head> <body> <?php $servername = "localhost"; $username = "root"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn- >connect_error); } $sql = "SELECT id, firstname, lastname FROM MyGuests"; $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>"; } } else { echo "0 results"; } $conn->close(); ?> </body> </html>

Delete Data From a MySQL Table Using MySQLi and PDO  The DELETE statement is used to delete records from a table: DELETE FROM table_name WHERE some_column = some_value  WHERE clause in the DELETE syntax:  TheWHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

Example (MySQLi Object- oriented) <?php $servername = "localhost"; $username = “root"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // sql to delete a record $sql = "DELETE FROM MyGuests WHERE id=3"; if ($conn->query($sql) === TRUE) { echo "Record deleted successfully"; } else { echo "Error deleting record: " . $conn->error; } $conn->close(); ?>

Update Data In a MySQL Table Using MySQLi  The UPDATE statement is used to update existing records in a table: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value  Notice theWHERE clause in the UPDATE syntax:TheWHERE clause specifies which record or records that should be updated.  If you omit theWHERE clause, all records will be updated!

Update Data In a MySQL Table <!DOCTYPE html> <html> <head> <title>PHP Demo</title> </head> <body> <?php $servername = "localhost"; $username = "root"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn- >connect_error); } $sql = "UPDATE MyGuests SET lastname='Dev' WHERE id=2"; if ($conn->query($sql) === TRUE) { echo "Record updated successfully"; } else { echo "Error updating record: " . $conn- >error; } $conn->close(); ?> </body> </html>

PHP Form Filling <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Add Record Form</title> </head> <body> <form action="insert.php" method="post"> <p> <label for="firstName">First Name:</label> <input type="text" name="firstname" id="firstName"> </p> <p> <label for="lastName">Last Name:</label> <input type="text" name="lastname" id="lastName"> </p> <p> <label for="emailAddress">Email Address:</label> <input type="text" name="email" id="emailAddress"> </p> <input type="submit" value="Submit"> </form> </body> </html>

PHP 5 MySQLi Functions Function Description mysqli_affected_rows() Returns the number of affected rows in the previous MySQL operation mysqli_autocommit() Turns on or off auto-committing database modifications mysqli_change_user() Changes the user of the specified database connection mysqli_character_set_n ame() Returns the default character set for the database connection

PHP 5 MySQLi Functions Function Description mysqli_close() Closes a previously opened database connection mysqli_commit() Commits the current transaction mysqli_connect_errno() Returns the error code from the last connection error mysqli_connect_error() Returns the error description from the last connection error mysqli_connect() Opens a new connection to the MySQL server

PHP 5 MySQLi Functions Function Description mysqli_data_seek() Adjusts the result pointer to an arbitrary row in the result-set mysqli_debug() Performs debugging operations mysqli_dump_debug_info() Dumps debugging info into the log mysqli_errno() Returns the last error code for the most recent function call mysqli_error_list() Returns a list of errors for the most recent function call mysqli_error() Returns the last error description for the most recent function call

PHP 5 MySQLi Functions Function Description mysqli_fetch_all() Fetches all result rows as an associative array, a numeric array, or both mysqli_fetch_array() Fetches a result row as an associative, a numeric array, or both mysqli_fetch_assoc() Fetches a result row as an associative array mysqli_fetch_field_direct() Returns meta-data for a single field in the result set, as an object mysqli_fetch_field() Returns the next field in the result set, as an object mysqli_fetch_fields() Returns an array of objects that represent the fields in a result set

PHP 5 MySQLi Functions Function Description mysqli_fetch_lengths() Returns the lengths of the columns of the current row in the result set mysqli_fetch_object() Returns the current row of a result set, as an object mysqli_fetch_row() Fetches one row from a result-set and returns it as an enumerated array mysqli_field_count() Returns the number of columns for the most recent query mysqli_field_seek() Sets the field cursor to the given field offset mysqli_field_tell() Returns the position of the field cursor

PHP 5 MySQLi Functions Function Description mysqli_free_result() Frees the memory associated with a result mysqli_get_charset() Returns a character set object mysqli_get_client_info() Returns the MySQL client library version mysqli_get_client_stats() Returns statistics about client per-process mysqli_get_client_version() Returns the MySQL client library version as an integer mysqli_get_connection_sta ts() Returns statistics about the client connection

PHP 5 MySQLi Functions Function Description mysqli_get_host_info() Returns the MySQL server hostname and the connection type mysqli_get_proto_info() Returns the MySQL protocol version mysqli_get_server_info() Returns the MySQL server version mysqli_get_server_version() Returns the MySQL server version as an integer mysqli_info() Returns information about the most recently executed query mysqli_init() Initializes MySQLi and returns a resource for use with mysqli_real_connect()

PHP 5 MySQLi Functions Function Description mysqli_insert_id() Returns the auto-generated id used in the last query mysqli_kill() Asks the server to kill a MySQL thread mysqli_more_results() Checks if there are more results from a multi query mysqli_multi_query() Performs one or more queries on the database mysqli_next_result() Prepares the next result set from mysqli_multi_query() mysqli_num_fields() Returns the number of fields in a result set

PHP 5 MySQLi Functions Function Description mysqli_num_rows() Returns the number of rows in a result set mysqli_options() Sets extra connect options and affect behavior for a connection mysqli_ping() Pings a server connection, or tries to reconnect if the connection has gone down mysqli_prepare() Prepares an SQL statement for execution mysqli_query() Performs a query against the database mysqli_real_connect() Opens a new connection to the MySQL server

PHP 5 MySQLi Functions Function Description mysqli_real_escape_string() Escapes special characters in a string for use in an SQL statement mysqli_real_query() Executes an SQL query mysqli_reap_async_query() Returns the result from async query mysqli_refresh() Refreshes tables or caches, or resets the replication server information mysqli_rollback() Rolls back the current transaction for the database mysqli_select_db() Changes the default database for the connection

PHP 5 MySQLi Functions Function Description mysqli_set_charset() Sets the default client character set mysqli_set_local_infile_def ault() Unsets user defined handler for load local infile command mysqli_set_local_infile_han dler() Set callback function for LOAD DATA LOCAL INFILE command mysqli_sqlstate() Returns the SQLSTATE error code for the last MySQL operation mysqli_ssl_set() Used to establish secure connections using SSL mysqli_stat() Returns the current system status

PHP 5 MySQLi Functions Function Description mysqli_stmt_init() Initializes a statement and returns an object for use with mysqli_stmt_prepare() mysqli_store_result() Transfers a result set from the last query mysqli_thread_id() Returns the thread ID for the current connection mysqli_thread_safe() Returns whether the client library is compiled as thread-safe mysqli_use_result() Initiates the retrieval of a result set from the last query executed using the mysqli_real_query() mysqli_warning_count() Returns the number of warnings from the last query in the connection

PHP Cookies:What is a Cookie?  A cookie is often used to identify a user.  A cookie is a small file that the server embeds on the user's computer.  Each time the same computer requests a page with a browser, it will send the cookie too.  With PHP, you can both create and retrieve cookie values.  A cookie is created with the setcookie() function.  Syntax setcookie(name, value, expire, path, domain, secure, httponly);  Only the name parameter is required.All other parameters are optional.

Create Cookies With PHP <!DOCTYPE html> <?php $cookie_name = "user"; $cookie_value = "John Doe"; setcookie($cookie_name, $cookie_value, time() + (86400 * 30), "/"); // 86400 = 1 day ?> <html> <body> <?php if(!isset($_COOKIE[$cookie_name])) { echo "Cookie named '" . $cookie_name . "' is not set!"; } else { echo "Cookie '" . $cookie_name . "' is set!<br>"; echo "Value is: " . $_COOKIE[$cookie_name];} ?> <p><strong>Note:</strong>You might have to reload the page to see the value of the cookie.</p> </body></html>

PHP Sessions:What is a PHP Session?  A session is a way to store information (in variables) to be used across multiple pages.  Unlike a cookie, the information is not stored on the users computer.  When you work with an application, you open it, do some changes, and then you close it.This is much like a Session.The computer knows who you are. It knows when you start the application and when you end. But on the internet there is one problem: the web server does not know who you are or what you do, because the HTTP address doesn't maintain state.  Session variables solve this problem by storing user information to be used across multiple pages (e.g. username, favorite color, etc). By default, session variables last until the user closes the browser.  So; Session variables hold information about one single user, and are available to all pages in one application.

Start a PHP Session <?php // Start the session session_start(); ?> <!DOCTYPE html> <html> <body> <?php // Set session variables $_SESSION["favcolor"] = "green"; $_SESSION["favanimal"] = "cat"; echo "Session variables are set."; ?> </body> </html>

PHP HTTP Functions  The HTTP functions let you manipulate information sent to the browser by theWeb server, before any other output has been sent.  The HTTP functions are part of the PHP core.There is no installation needed to use these functions. Function Description header() Sends a raw HTTP header to a client headers_list() Returns a list of response headers sent (or ready to send) headers_sent() Checks if / where the HTTP headers have been sent setcookie() Defines a cookie to be sent along with the rest of the HTTP headers setrawcookie() Defines a cookie (without URL encoding) to be sent along with the rest of the HTTP headers

FYBSC IT Web Programming Unit V Advanced PHP and MySQL

More Related Content

What's hot

Similar to FYBSC IT Web Programming Unit V Advanced PHP and MySQL

More from Arti Parab Academics

Recently uploaded

In this document

FYBSC IT Web Programming Unit V Advanced PHP and MySQL