CU-BCA-SEM-V-Web Development using Php-Web Technologies-Second Draft

UNIT - 10: PHP MYSQL STRUCTURE 10.0 Learning Objectives 10.1 Introduction 10.2 Connection with MYSQL database 10.3 CRUD Operations 10.4 setting query parameter 10.5 executing query on MYSQL 10.6 PHP Joins operations 10.7 Summary 10.8 Keywords 10.9 Learning Activity 10.10 Unit End Questions 10.11 References 10.0 LEARNING OBJECTIVES After studying this unit, you will be able to:  Describe nature of human resource management  Identify scope of human resource  State the need and importance of HRM  List the functions of HRM 10.1 INTRODUCTION MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation.  The data in a MySQL database are stored in tables which consists of columns and rows.  MySQL is a database system that runs on a server.  MySQL is ideal for both small and large applications.  MySQL is very fast, reliable, and easy to use database system. It uses standard SQL 151 CU IDOL SELF LEARNING MATERIAL (SLM)

 MySQL compiles on a number of platforms. 10.2 CONNECTION WITH MYSQL DATABASE Before making a connection, we should have details like – Hostname, Database User Name, Database Password, Port (if application), etc. Every programming language has its own unique way of making a connection with the databases and playing with that. Database in PHP, not that much of a big task as we see in a programming language like JAVA. There is a very simple couple of lines code to connect with the database. In the PHP language, we can make database connection in a below-mentioned way: MySQL This will work with the MySQL database only. This extension follows the old traditional way of communicating with a database. Now, every coming PHP version has deprecated this approach. MySQLi Extension This will work with the MySQL database only, but this is an improved version of MySQL. PDO It works with various databases. Usually, we consider this as the best approach out of these three. This one is considered as an object-oriented way of communicating with the MySQL database. The moment we create a connection, it gives us the object to deal with the MySQL related. Examples Example 1 – PHP MYSQL Connection using MYSQL Code: $servername = \"localhost\"; $username = \"root\"; $password = \"\"; $link = mysql_connect($servername, $username, $password); if (!$link) { die('Connection failed: ' . mysql_error()); }else{ echo \"Database Connected successfully\"; // in case of success } The connection can be made successfully in the lower version of PHP. But, if we use this code, it says Deprecated: mysql_connect(): The MySQL extension is deprecated and will be removed in the future: use mysqli or PDO instead. 152 CU IDOL SELF LEARNING MATERIAL (SLM)

That’s why we should avoid using this technique to make a database connection in PHP language to the MySQL database. Example 2 – PHP MYSQL Connection Using MYSQLi We can make the connection using the MYSQLi in two ways. MYSQLi Object-Oriented Code: <?php $servername = \"localhost\"; $username = \"root\"; $password = \"\"; // Database Connection Code $conn = new mysqli($servername, $username, $password); // Check connection if ($conn->connect_error) { die(\"Connection failed: \" . $conn->connect_error); // in case of error }else{ echo \"Database Connected successfully\"; // in case of success } ?> Now, we have $conn, the database connection object. We can use this object for all the communication to the database. // selecting database \"test1\" mysqli_select_db($conn,\"test1\"); You can also pass the database as an argument at the time of connection establishment. $conn = new mysqli($servername, $username, $password, $databaseName); MYSQLi Function (procedural) Way $servername = \"localhost\"; $username = \"root\"; $password = \"\"; // Database Connection Code $conn = mysqli_connect($servername, $username, $password); // Check connection if (!$conn) { die(\"Connection failed: \" . mysqli_connect_error()); // in case of error }else{ 153 CU IDOL SELF LEARNING MATERIAL (SLM)

echo \"Database Connected successfully\"; // in case of success } We can also use the other operation like database connection and other as mentioned above. Example 3 – PDO PHP Database Connection Again this is an Object-Oriented way of database connection in PHP. Code: $servername = \"localhost\"; $username = \"root\"; $password = \"\"; try { // Database Connection Code $conn = new PDO(\"mysql:host=$servername;dbname=test1\", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // in case of success echo \"Connected successfully\"; } catch(PDOException $e) { // in case of error echo \"Connection failed: \" . $e->getMessage(); } 10.3 CRUD OPERATIONS CRUD refers to the four basic types of Database operations: Create, Read, Update, Delete. Most applications and projects perform some kind of CRUD functionality. Once you learn about these CRUD operations, you can use them for many projects. For an example, if you learn how to create student table with multiple columns, you can use similar approach to create employee table or customers table. config.php 154 <?php /** * using mysqli_connect for database connection */ CU IDOL SELF LEARNING MATERIAL (SLM)

$databaseHost = 'localhost'; $databaseName = 'crud_db'; $databaseUsername = 'root'; $databasePassword = ''; $mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName); ?> config.php file store information about the database host, username and password. Most of the local server works with given detail. You can change as per your host and database details. database.sql /* Create Database and Table */ create database crud_db; use crud_db; CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `name` varchar(100), `email` varchar(100), `mobile` varchar(15), PRIMARY KEY (`id`) ); database.sql is a database file. You can to create a database, after downloading the crud script. You can either run commands from above mysql file or simply import this ‘database.sql’ file into database (example using PHPMyAdmin). 155 CU IDOL SELF LEARNING MATERIAL (SLM)

index.php <?php // Create database connection using config file include_once(\"config.php\"); // Fetch all users data from database $result = mysqli_query($mysqli, \"SELECT * FROM users ORDER BY id DESC\"); ?> <html> <head> <title>Homepage</title> </head> <body> <a href=\"add.php\">Add New User</a><br/><br/> <table width='80%' border=1> <tr> 156 <th>Name</th><th>Mobile</th><th>Email</th><th>Update</th> </tr> <?php while($user_data = mysqli_fetch_array($result)) { echo \"<tr>\"; echo \"<td>\".$user_data['name'].\"</td>\"; echo \"<td>\".$user_data['mobile'].\"</td>\"; echo \"<td>\".$user_data['email'].\"</td>\"; echo \"<td><a href='edit.php?id=$user_data[id]'>Edit</a> | <a href='delete.php?id=$user_data[id]'>Delete</a></td></tr>\"; CU IDOL SELF LEARNING MATERIAL (SLM)

} ?> </table> </body> </html> index.php file is the main file which include configuration file for database connection. Then it display all users list using MySQL Select Query. However, you need to add some users first using ‘Add New User’ link. add.php <html> <head> <title>Add Users</title> </head> <body> <a href=\"index.php\">Go to Home</a> <br/><br/> <form action=\"add.php\" method=\"post\" name=\"form1\"> 157 <table width=\"25%\" border=\"0\"> <tr> <td>Name</td> <td><input type=\"text\" name=\"name\"></td> </tr> <tr> <td>Email</td> <td><input type=\"text\" name=\"email\"></td> </tr> <tr> CU IDOL SELF LEARNING MATERIAL (SLM)

<td>Mobile</td> <td><input type=\"text\" name=\"mobile\"></td> </tr> <tr> <td></td> <td><input type=\"submit\" name=\"Submit\" value=\"Add\"></td> </tr> </table> </form> <?php // Check If form submitted, insert form data into users table. if(isset($_POST['Submit'])) { $name = $_POST['name']; $email = $_POST['email']; $mobile = $_POST['mobile']; // include database connection file include_once(\"config.php\"); // Insert user data into table $result = mysqli_query($mysqli, \"INSERT INTO users(name,email,mobile) VALUES('$name','$email','$mobile')\"); // Show message when user added echo \"User added successfully. <a href='index.php'>View Users</a>\"; } ?> </body> 158 CU IDOL SELF LEARNING MATERIAL (SLM)

</html> Markup Copy add.php file is responsible to add new users. HTML Form is used to capture user data. After User data is submitted, MySQL INSERT Query is used to insert user data into database. You can “View User” after user added. edit.php <?php // include database connection file include_once(\"config.php\"); // Check if form is submitted for user update, then redirect to homepage after update if(isset($_POST['update'])) { $id = $_POST['id']; $name=$_POST['name']; $mobile=$_POST['mobile']; $email=$_POST['email']; // update user data $result = mysqli_query($mysqli, \"UPDATE users SET name='$name',email='$email',mobile='$mobile' WHERE id=$id\"); // Redirect to homepage to display updated user in list 159 header(\"Location: index.php\"); } ?> <?php // Display selected user data based on id CU IDOL SELF LEARNING MATERIAL (SLM)

// Getting id from url $id = $_GET['id']; // Fetech user data based on id $result = mysqli_query($mysqli, \"SELECT * FROM users WHERE id=$id\"); while($user_data = mysqli_fetch_array($result)) { $name = $user_data['name']; $email = $user_data['email']; $mobile = $user_data['mobile']; } ?> <html> <head> <title>Edit User Data</title> </head> <body> <a href=\"index.php\">Home</a> <br/><br/> <form name=\"update_user\" method=\"post\" action=\"edit.php\"> <table border=\"0\"> <tr> <td>Name</td> <td><input type=\"text\" name=\"name\" value=<?php echo $name;?>></td> </tr> <tr> <td>Email</td> 160 CU IDOL SELF LEARNING MATERIAL (SLM)

<td><input type=\"text\" name=\"email\" value=<?php echo $email;?>></td> </tr> <tr> <td>Mobile</td> <td><input type=\"text\" name=\"mobile\" value=<?php echo $mobile;?>></td> </tr> <tr> <td><input type=\"hidden\" name=\"id\" value=<?php echo $_GET['id'];?>></td> <td><input type=\"submit\" name=\"update\" value=\"Update\"></td> </tr> </table> </form> </body> </html> edit.php is used to edit/update user data once user click on’Edit’ link. It first fetch user’s current data in form. You can change user data and update it. It will redirect to homepage, after successful update. delete.php <?php // include database connection file include_once(\"config.php\"); // Get id from URL to delete that user $id = $_GET['id']; // Delete user row from table based on given id $result = mysqli_query($mysqli, \"DELETE FROM users WHERE id=$id\"); // After delete redirect to Home, so that latest user list will be displayed. 161 CU IDOL SELF LEARNING MATERIAL (SLM)

header(\"Location:index.php\"); ?> PHP Copy delete.php file simply called when we click on ‘Delete’ link for any user. It will delete selected user. Delete/edit/update operations user particular user_id to identify users. 10.4 SETTING QUERY PARAMETER In PHP you can easily do this using the mysqli_connect() function. All communication between PHP and the MySQL database server takes place through this connection. Here're the basic syntaxes for connecting to MySQL using MySQLi and PDO extensions: Syntax: MySQLi, Procedural way $link = mysqli_connect(\"hostname\", \"username\", \"password\", \"database\"); Syntax: MySQLi, Object Oriented way $mysqli = new mysqli(\"hostname\", \"username\", \"password\", \"database\"); Syntax: PHP Data Objects (PDO) way $pdo = new PDO(\"mysql:host=hostname;dbname=database\", \"username\", \"password\"); The hostname parameter in the above syntax specify the host name (e.g. localhost), or IP address of the MySQL server, whereas the username and password parameters specifies the credentials to access MySQL server, and the database parameter, if provided will specify the default MySQL database to be used when performing queries. The following example shows how to connect to MySQL database server using MySQLi (both procedural and object oriented way) and PDO extension. <?php /* Attempt MySQL server connection. Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect(\"localhost\", \"root\", \"\"); // Check connection 162 if($link === false){ die(\"ERROR: Could not connect. \" . mysqli_connect_error()); CU IDOL SELF LEARNING MATERIAL (SLM)

} // Print host information echo \"Connect Successfully. Host info: \" . mysqli_get_host_info($link); ?> Closing the MySQL Database Server Connection The connection to the MySQL database server will be closed automatically as soon as the execution of the script ends. However, if you want to close it earlier you can do this by simply calling the PHP mysqli_close() function. <?php /* Attempt MySQL server connection. Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect(\"localhost\", \"root\", \"\"); // Check connection if($link === false){ die(\"ERROR: Could not connect. \" . mysqli_connect_error()); } // Print host information echo \"Connect Successfully. Host info: \" . mysqli_get_host_info($link); // Close connection mysqli_close($link); ?> 10.5 EXECUTING QUERY ON MYSQL you can execute MySQL queries from the PHP code itself. Store the query in a variable as a string. Then, you can use mysqli_query() to perform queries against the database. 163 CU IDOL SELF LEARNING MATERIAL (SLM)

1. Create Database // Create database $sql = \"CREATE DATABASE myDB\"; if ($conn->query($sql) === TRUE) { echo \"Database created successfully\"; } else { echo \"Error creating database: \" . $conn->error; } mysqli_error() returns the last error description for the most recent function call. 2. Insert Data $sql = \"INSERT INTO users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')\"; if (mysqli_query($conn, $sql)) { echo \"New record created successfully\"; } else { echo \"Error: \" . $sql . \"<br>\" . mysqli_error($conn); } 3. Get ID of Last Inserted Record If you perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, you can get the ID of the last inserted or updated record immediately. $sql = \"INSERT INTO users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')\"; if (mysqli_query($conn, $sql)) { $last_id = mysqli_insert_id($conn); echo \"New record created successfully. Last inserted ID is: \" . $last_id; } else { echo \"Error: \" . $sql . \"<br>\" . mysqli_error($conn); } 4. Select Data First, you make an SQL query that selects the id, firstname and lastname columns from the users table. The next line of code runs the query and puts the resulting data into a variable called $result. Then, the function num_rows() checks if there are more than zero rows returned. If there are more than zero rows returned, the function fetch_assoc() puts all the results into an 164 CU IDOL SELF LEARNING MATERIAL (SLM)

associative array that you can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns. $sql = \"SELECT id, firstname, lastname FROM users\"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { // output data of each row while($row = mysqli_fetch_assoc($result)) { echo \"id: \" . $row[\"id\"]. \" - Name: \" . $row[\"firstname\"]. \" \" . $row[\"lastname\"]. \"<br>\"; } } else { echo \"0 results\"; } 5. Delete Data The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted. // sql to delete a record $sql = \"DELETE FROM users WHERE id=3\"; if (mysqli_query($conn, $sql)) { echo \"Record deleted successfully\"; } else { echo \"Error deleting record: \" . mysqli_error($conn); } 6. Update Data The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated. $sql = \"UPDATE users SET lastname='Doe' WHERE id=2\"; if (mysqli_query($conn, $sql)) { echo \"Record updated successfully\"; } else { echo \"Error updating record: \" . mysqli_error($conn); } 165 CU IDOL SELF LEARNING MATERIAL (SLM)

10.6PHP JOINS OPERATIONS A join lets you combine columns from two or more tables into a single result or say a SQL statement that retrieves data from two tables is called a join. Types of join  INNER JOIN  OUTER JOIN  SELF JOIN I am describing here each of those one-by-one. INNER JOIN The inner join returns those rows with at least one match in both tables. If two columns in a join condition have the same name then you must qualify them with the table name so MySQL distinguishes between them. This Example illustrates how to use an inner join clause in the PHP code. Example <?php $con=mysql_connect(\"localhost\",\"sharad\",\"gupta\"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db(\"Employees\", $con); $qry=\"SELECT emp.id,emp.FirstName, dept.dept_name FROM emp INNER JOIN dept on emp.id = dept.dept_id\"; $result=mysql_query($qry)ordie(mysql_error()); echo\"<tableborder='1'> <tr> <th>EmpId</th> <th>Firstname</th> 166 CU IDOL SELF LEARNING MATERIAL (SLM)

<th>DeptName</th> </tr>\"; while($row = mysql_fetch_array($result)) { echo \"<tr>\"; echo \"<td>\" . $row['id'] . \"</td>\"; echo \"<td>\" . $row['FirstName'] . \"</td>\"; echo \"<td>\" . $row['dept_name'] . \"</td>\"; echo \"</tr>\"; } echo \"</table>\"; mysql_close($con); ?> I show here the emp table data and dept table data separately. Emp table data and dept table data Output 167 CU IDOL SELF LEARNING MATERIAL (SLM)

Note: As you see above, the emp and dept tables contain different columns, so in the above example I use a join clause between the two tables. I use \"emp\" and \"dept\" table and find the first name from the dept table, id and last name from the emp table where id in the emp table dept_id in the dept table is equal. OUTER JOIN Previously, I also described INNER JOIN. Now I am going to explain, how to use OUTER JOIN in PHP. Unlike an inner join, outer joins return all of the rows from one of the tables involved in the join. An Outer join contains the following types, I am explaining each one-by-one.  Left outer join  Right outer join LEFT OUTER JOIN Left outer join is the join based on the left table, and displays a null for the right table if the join condition is not true. I show here emp table data and dept table data separately. Emp table data and dept table data 168 CU IDOL SELF LEARNING MATERIAL (SLM)

Output Note: Left outer join returns all the rows from the left table (emp), and at the right-side table, those values which do not match, represents NULL. RIGHT OUTER JOIN A right outer join is pretty much the same thing as a left outer join, except that all the rows from the right table are displayed in the result table, regardless of whether or not they have matching values in the left table. Example <?php $con=mysql_connect(\"localhost\",\"sharad\",\"gupta\"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db(\"Employees\", $con); $qry=\"SELECT emp.id, emp.FirstName,dept.dept_name from emp RIGHT OUTER JOIN dept on emp.id=dept.dept_id\"; $result=mysql_query($qry)ordie(mysql_error()); echo\"<tableborder='1'> <tr> 169 CU IDOL SELF LEARNING MATERIAL (SLM)

<th>EmpId</th> <th>Firstname</th> <th>DeptName</th> </tr>\"; while($row = mysql_fetch_array($result)) { echo \"<tr>\"; echo \"<td>\" . $row['id'] . \"</td>\"; echo \"<td>\" . $row['FirstName'] . \"</td>\"; echo \"<td>\" . $row['dept_name'] . \"</td>\"; echo \"</tr>\"; } echo \"</table>\"; mysql_close($con); ?> I show here the emp table data and dept table data separately. Emp table data and dept table data Output 170 CU IDOL SELF LEARNING MATERIAL (SLM)

Note: A right outer join returns all the rows from the right table (dept), and at the left side table, those values which do not match, represents NULL. SELF JOIN A self join is another type of join and it is used to join a table to itself. Especially when the table has a foreign key which references its own primary key. Example <?php $con=mysql_connect(\"localhost\",\"sharad\",\"gupta\"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db(\"Employees\", $con); $qry=\"select a.comp_name,b.comp_name,a.comp_city from company a, company b where a.comp_city=b. comp_city and a.comp_name<>b.comp_name\"; $result=mysql_query($qry)ordie(mysql_error()); echo\"<tableborder='1'> <tr> <th>CompanyName</th> <th>CompanyCity</th> 171 CU IDOL SELF LEARNING MATERIAL (SLM)

</tr>\"; while($row = mysql_fetch_array($result)) { echo \"<tr>\"; echo \"<td>\" . $row['comp_name'] . \"</td>\"; echo \"<td>\" . $row['comp_city'] . \"</td>\"; echo \"</tr>\"; } echo \"</table>\"; mysql_close($con); ?> I show here company table data. Output 172 CU IDOL SELF LEARNING MATERIAL (SLM)

10.7 SUMMARY  MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation.  The data in a MySQL database are stored in tables which consists of columns and rows.  MySQL is a database system that runs on a server.  MySQL is ideal for both small and large applications.  MySQL is very fast, reliable, and easy to use database system.It uses standard SQL  MySQL compiles on a number of platforms. 10.8 KEYWORD  SQL- Structured Query Language  SELF JOIN- A self join is another type of join and it is used to join a table to itself.  CRUD -Create, Read, Update, Delete 10.9 LEARNING ACTIVITY 1. Create a connection with MYSQL. ___________________________________________________________________________ ___________________________________________________________________________ 2. Create a table in MYSQL for student details. ___________________________________________________________________________ ___________________________________________________________________________ 10.10 UNIT END QUESTIONS A. Descriptive Questions 173 CU IDOL SELF LEARNING MATERIAL (SLM)

Short Questions 1. What is SQL? 2. Give syntax for connection with MYSQL. 3. Give syntax for closing a connection with MYSQL. 4. Give syntax for creating a database. 5. Give syntax for inserting values in a database. Long Questions 1. Which MYSQL function would you use to select a database? 2. How many types of tables exists in mysql? 3. Describe Join operations in PHP? 4. How can we find the number of rows in a table using MySQL? 5. Write a program to perform CRUD operations. B. Multiple Choice Questions 1. Use the.............. to delete the data inside the table, and not the table itself? a. Drop table b. Delete table c. Truncate table d. Remove table 2. The ............. statement is used to delete a table. a. Drop table b. Delete table c. Del table d. Remove table 3. The datatype best suited to store currency values is __________ a. Int b. Float c. Double d. Decimal 4. Which one of the following databases has PHP supported almost since the beginning? a. Oracle Database b. SQL c. SQL+ 174 CU IDOL SELF LEARNING MATERIAL (SLM)

d. MySQL 5. Which one of the following statements is used to create a table? a. CREATE TABLE table_name (column_name column_type); b. CREATE table_name (column_type column_name); c. CREATE table_name (column_name column_type); d. CREATE TABLE table_name (column_type column_name); Answers 1-c, 2-a, 3-d. 4-d, 5-a 10.11 REFERENCES Textbooks  T1: Steven Holzner, Wordpress-The Complete Reference, McGraw-Hill.  T2: Robin Nixon, Wordpress-MySQL-JavaScript, O’Reilly. Reference Books  R1: Rasmus Lerdorf, Kevin Tatroe, Bob Kaehms, Ric McGredy, Wordpress, O’Reilly, Shroff Publishers. 175 CU IDOL SELF LEARNING MATERIAL (SLM)

UNIT - 11: FETCHING FUNCTIONS STRUCTURE 11.0 Learning Objectives 11.1 Introduction 11.2 mysqli_query 11.3 mysqli_fetch_array 11.4 mysqli_fetch_assoc 11.5 mysqli_fetch_row 11.6 mysqli_fetch _object 11.7 mysql_insert_id(). 11.8 Summary 11.9 Keywords 11.10 Learning Activity 11.11 Unit End Questions 11.12 References 11.0 LEARNING OBJECTIVES After studying this unit, you will be able to:  Describe nature of human resource management  Identify scope of human resource  State the need and importance of HRM  List the functions of HRM 11.1 INTRODUCTION The mysqli functions are designed to communicate with MySQL 4.1 or later versions. Using the mysqli functions you can take advantage of all the latest and advanced features of MySQL, which you may not be able to do with the earlier MySQL functions. 11.2 MYSQLI_QUERY The mysqli_query() function / mysqli::query performs a query against the database. Syntax: 176 CU IDOL SELF LEARNING MATERIAL (SLM)

Object oriented style mixed mysqli::query ( string $query [, int $resultmode = MYSQLI_STORE_RESULT ] ) Procedural style mixed mysqli_query ( mysqli $link , string $query [, int $resultmode = MYSQLI_STORE_RESULT ] ) Parameter: Name Description Required/Optional Link A link identifier returned by mysqli_connect() Required for procedural or mysqli_init() style only and Optional for Object oriented style Query The query string. Required for procedural Data inside the query should be properly style only and Optional for escaped. Object oriented style Resultmode Either the constant MYSQLI_USE_RESULT Optional or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result() With MYSQLI_ASYNC (available with mysqlnd), it is possible to perform query asynchronously. mysqli_poll() is then used to get results from such queries. Usage: Procedural style mysqli_query(connection,query,resultmode); Parameter: Name Description Required / 177 CU IDOL SELF LEARNING MATERIAL (SLM)

Optional Connection Specifies the MySQL connection to use Required Query Specifies the query string Required Resultmode A constant. Either: Optional  MYSQLI_USE_RESULT (Use this if we have to retrieve large amount of data)  MYSQLI_STORE_RESULT (This is default) Return value: Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE. Version: PHP 5, PHP 7 Example of object oriented style: <?php $mysqli = new mysqli(\"localhost\", \"user1\", \"datasoft123\", \"hr\"); /* check connection */ if ($mysqli->connect_errno) { printf(\"Connect failed: %s\\n\", $mysqli->connect_error); exit(); } /* Create table doesn't return a resultset */ if ($mysqli->query(\"CREATE TEMPORARY TABLE myCity LIKE City\") === TRUE) { printf(\"Table myCity successfully created.\\n\"); } 178 CU IDOL SELF LEARNING MATERIAL (SLM)

/* Select queries return a resultset */ if ($result = $mysqli->query(\"SELECT Name FROM City LIMIT 10\")) { printf(\"Select returned %d rows.\\n\", $result->num_rows); /* free result set */ $result->close(); } /* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */ if ($result = $mysqli->query(\"SELECT * FROM City\", MYSQLI_USE_RESULT)) { /* Note, that we can't execute any functions which interact with the server until result set was closed. All calls will return an 'out of sync' error */ if (!$mysqli->query(\"SET @a:='this will not work'\")) { printf(\"Error: %s\\n\", $mysqli->error); } $result->close(); } $mysqli->close(); ?> Copy Example of procedural style: <?php $link = mysqli_connect(\"localhost\", \"user1\", \"datasoft123\", \"hr\"); /* check connection */ 179 if (mysqli_connect_errno()) { printf(\"Connect failed: %s\\n\", mysqli_connect_error()); CU IDOL SELF LEARNING MATERIAL (SLM)

exit(); } /* Create table doesn't return a resultset */ if (mysqli_query($link, \"CREATE TEMPORARY TABLE myCity LIKE City\") === TRUE) { printf(\"Table myCity successfully created.\\n\"); } /* Select queries return a resultset */ if ($result = mysqli_query($link, \"SELECT Name FROM City LIMIT 10\")) { printf(\"Select returned %d rows.\\n\", mysqli_num_rows($result)); /* free result set */ mysqli_free_result($result); } /* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */ if ($result = mysqli_query($link, \"SELECT * FROM City\", MYSQLI_USE_RESULT)) { /* Note, that we can't execute any functions which interact with the server until result set was closed. All calls will return an 'out of sync' error */ if (!mysqli_query($link, \"SET @a:='this will not work'\")) { printf(\"Error: %s\\n\", mysqli_error($link)); } mysqli_free_result($result); } mysqli_close($link); 180 CU IDOL SELF LEARNING MATERIAL (SLM)

?> Output: Table myCity successfully created. Select returned 10 rows. Error: Commands out of sync; You can't run this command no 11.3 MYSQLI_FETCH_ARRAY The mysqli_fetch_array() function is used to fetch rows from the database and store them as an array. The array can be fetched as an associative array, as a numeric array or both. Associative arrays are the arrays where the indexes are the names of the individual columns of the table. On the other hand, numeric arrays are arrays where indexes are numbers, with 0 representing the first column and n-1 representing the last column of an n-column table. Syntax: mysqli_fetch_array (\"database_name\", \"mode\") Parameters: This function accepts two parameters as mentioned above and described below:  database_name: It is the database on which operations are being performed. It is a mandatory parameter.  mode: It can have three values – MYSQLI_ASSOC, MYSQLI_NUM, and MYSQLI_BOTH. MYSQLI_ASSOC makes the function behave like mysqli_fetch_assoc() function, fetching an associative array, MYSQLI_NUM makes the function behave like mysqli_fetch_row() function, fetching a numeric array while MYSQLI_BOTH stores the data fetched in an array that can be accessed using both column indexes as well as column names. Program: <?php $conn = mysqli_connect( \"localhost\", \"root\", \"\", \"Persons\"); // Check connection 181 if (mysqli_connect_errno()) { echo \"Database connection failed.\"; } CU IDOL SELF LEARNING MATERIAL (SLM)

$sql = \"SELECT Lastname, Age FROM Persons ORDER BY Lastname\"; $result -> $mysqli -> query($sql); // Numeric array $row = mysqli_fetch_array($conn, MYSQLI_NUM); printf (\"%s (%s)\\n\", $row[0], $row[1]); printf(\"\\n\"); // Associative array $row = mysqli_fetch_array($conn, MYSQLI_ASSOC); printf (\"%s (%s)\\n\", $row[\"Firstname\"], $row[\"Lastname\"]); mysqli_close($conn); ?> For the above table, the output will be: 182 Output: AB CD EF GH AB CD CU IDOL SELF LEARNING MATERIAL (SLM)

EF GH 11.4 MYSQLI_FETCH_ASSOC Returns an associative array that corresponds to the fetched row or FALSE if there are no more rows. The mysqli_fetch_assoc() function is used to return an associative array representing the next row in the result set for the result represented by the result parameter, where each key in the array represents the name of one of the result set's columns. If two or more columns in the result set have the same column name, the associative array returned by the mysqli_fetch_assoc() function will contain the value of the last column of that name. If you must work with result sets with this property, the mysqli_fetch_row() should be used which returns an numerically-indexed array instead. Note: Field names returned by this function are case-sensitive. Example 1. An expanded mysqli_fetch_assoc() example <?php $conn = mysqli_connect(\"localhost\", \"mysql_user\", \"mysql_password\"); if (!$conn) { echo \"Unable to connect to DB: \" . mysqli_error(); exit; } if (!mysqli_select_db(\"mydbname\")) { echo \"Unable to select mydbname: \" . mysqli_error(); exit; } $sql = \"SELECT id as userid, fullname, userstatus FROM sometable WHERE userstatus = 1\"; $result = mysqli_query($sql); if (!$result) { 183 echo \"Could not successfully run query ($sql) from DB: \" . mysqli_error(); exit; CU IDOL SELF LEARNING MATERIAL (SLM)

} if (mysqli_num_rows($result) == 0) { echo \"No rows found, nothing to print so am exiting\"; exit; } // While a row of data exists, put that row in $row as an associative array // Note: If you're expecting just one row, no need to use a loop // Note: If you put extract($row); inside the following loop, you'll // then create $userid, $fullname, and $userstatus while ($row = mysqli_fetch_assoc($result)) { echo $row[\"userid\"]; echo $row[\"fullname\"]; echo $row[\"userstatus\"]; } mysqli_free_result($result); ?> 11.5MYSQLI_FETCH_ROW The mysqli_fetch_row() function returns a row from a recordset as a numeric array. mysqli_fetch_row() return a single row from the number of records available in the database. at a time it return only the first row of the result set. if we want to retrieve all the rows of the table then we must put this function inside the while loop. Syntax mysqli_fetch_row(data) Display the records using mysqli_fetch_row( ) <?php //connect database $con=mysqli_connect(\"localhost\",\"root\",\"\",\"Employee\") or die(mysqli_error()); //select values from empInfo table $sql = \"SELECT * from empInfo WHERE email='devesh@gmail.com'\"; 184 CU IDOL SELF LEARNING MATERIAL (SLM)

$result = mysqli_query($con,$sql); print_r(mysqli_fetch_row($result)); mysqli_close($con); ?> Output : Array( [0] =>1 [1] => devesh [2] => devesh@gmail.com [3] => 9910099100 ) In the above example first connection to the database is created after that the result provided by mysqli_query() is passed to mysqli_fetch_row() and the data of the row is displayed using print_r() function. Retrieve all records from empInfo table using mysqli_fetch_row( ) function. <?php //connect database $con=mysqli_connect(\"localhost\",\"root\",\"\",\"Employee\") or die(mysqli_error()); //select all values from empInfo table $data=\"SELECT * FROM empInfo\"; $val=mysqli_query($con,$data); while($r=mysqli_fetch_row($val)) { echo $r[0].\" \".$r[1].\" \".$r[2].\" \".$r[3].\"<br/>\"; } ?> Output : Emp_id Name Email Mobile 185 CU IDOL SELF LEARNING MATERIAL (SLM)

1 devesh devesh@gmail.com 9910099100 2 deepak deepak@gmail.com 9210053520 3 ravi ravi@gmail.com 9810098100 In the above example first connection to the database is created after that the result provided by mysqli_query() is passed to mysqli_fetch_row() and row returned is stored in 'r' variable and it put in while loop to fetch all records . after that the data is displayed in tabular form. 11.6MYSQLI_FETCH _OBJECT Syntax mysqli_fetch_object(result,classname,params); Definition and Usage It returns the current row of a result set, as an object. Return Values It returns string properties for the fetched row or NULL if there are no more rows in the result set Parameters Sr.No Parameters & Description 1 result It specifies the result set identifier returned by mysqli_query(), mysqli_store_result() or mysqli_use_result() 2 classname It specifies the name of the class to instantiate 3 params It specifies an array of parameters to pass to the constructor for classname objects Example 186 Try out the following example <?php CU IDOL SELF LEARNING MATERIAL (SLM)

$connection_mysql = mysqli_connect(\"localhost\",\"user\",\"password\",\"db\"); if (mysqli_connect_errno($connection_mysql)){ echo \"Failed to connect to MySQL: \" . mysqli_connect_error(); } $sql = \"SELECT name FROM emp\"; if ($result = mysqli_query($connection_mysql,$sql)){ while ($obj = mysqli_fetch_object($result)){ print $obj->name; print \"\\n\"; } mysqli_free_result($result); } mysqli_close($connection_mysql); 11.7MYSQL_INSERT_ID(). Description int mysql_insert_id ( [resource link_identifier]) mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query using the given link_identifier. If link_identifier isn't specified, the last opened link is assumed. mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value. Note: The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries. 187 CU IDOL SELF LEARNING MATERIAL (SLM)

Warning mysql_insert_id() converts the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id() will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query. Example 1. mysql_insert_id() example <?php mysql_connect(\"localhost\", \"mysql_user\", \"mysql_password\") or die(\"Could not connect: \" . mysql_error()); mysql_select_db(\"mydb\"); mysql_query(\"INSERT INTO mytable (product) values ('kossu')\"); printf(\"Last inserted record has id %d\\n\", mysql_insert_id()); ?> 11.8 SUMMARY  If you are using MySQL database management system via MySQLi extension, you can find useful PHP inbuilt functions (such as MySQLi fetch array) meant for handling MySQL queries.  If you're not using MySQLi at this moment, you can download the installation package here free of charge.  PHP MySQL functions let you access MySQL databases and send queries using less code lines than usual.  Unlike MySQL extension that is now deprecated, MySQLi has no mysqli_result function. 11.9 KEYWORD  mysqli_query- function accepts a string value representing a query as one of the parameters and, executes/performs the given query on the database. 188 CU IDOL SELF LEARNING MATERIAL (SLM)

 mysqli_fetch_array-function is used to fetch rows from the database and store them as an array.  mysqli_fetch_assoc- is used to return an associative array representing the next row in the result set for the result represented by the result parameter, where each key in the array represents the name of one of the result set's columns  mysqli_fetch_row-returns an array of values that corresponds to the fetched row or null if there are no more rows in result set.  mysqli_fetch _object-function returns an object (with string properties) which holds the current row of the result object. 11.10 LEARNING ACTIVITY 1. Write a program to display all the records from employee table. ___________________________________________________________________________ ___________________________________________________________________________ 2. Write a program to display all the records from employee table using mysql_fetch_assoc() function. ___________________________________________________________________________ ___________________________________________________________________________ 11.11 UNIT END QUESTIONS A. Descriptive Questions Short Questions 1. What is fetch () in PHP? 2. What is use of Mysqli_query in PHP? 3. What is query function PHP? 4. What is the use of die in PHP? 5. What is the use of Fetch_assoc () in PHP? Long Questions 1. What is the difference between Mysqli_fetch_assoc and Mysqli_fetch_array? 2. What is difference between Mysqli_fetch_array () and Mysqli_fetch_rows () function? 3. What is the use of Mysql_fetch_assoc in PHP? 4. What is difference between Mysqli_fetch_array () Mysqli_fetch_row () and Mysqli_fetch_object ()? 5. When fetching a record using the Mysqli_fetch_row () function What is the format of the data? B. Multiple Choice Questions 189 CU IDOL SELF LEARNING MATERIAL (SLM)

1. Which one of the following methods is responsible for sending the query to the database? a. query() b. send_query() c. sendquery() d. mysqli_query() 2. Which one of the following methods recuperates any memory consumed by a result set? a. destroy() b. mysqli_free_result() c. alloc() d. free() 3. Which of the methods are used to manage result sets using both associative and indexed arrays? a. get_array() and get_row() b. get_array() and get_column() c. fetch_array() and fetch_row() d. mysqli_fetch_array() and mysqli_fetch_row() 4. Which one of the following method is used to retrieve the number of rows affected by an INSERT, UPDATE, or DELETE query? a. num_rows() b. affected_rows() c. changed_rows() d. mysqli_affected_rows() 5. Which version of MySQL introduced the prepared statements? 190 a. MySQL 4.0 b. MySQL 4.1 c. MySQL 4.2 d. MySQL 4.3 Answers 1-d, 2-b, 3-d. 4-d, 5-b CU IDOL SELF LEARNING MATERIAL (SLM)

11.12 REFERENCES Textbooks  T1: Steven Holzner, WordPress-The Complete Reference, McGraw-Hill.  T2: Robin Nixon, WordPress-MySQL-JavaScript, O’Reilly. Reference Books  R1: Rasmus Lerdorf, Kevin Tatroe, Bob Kaehms, Ric McGredy, WordPress, O’Reilly, Shroff Publishers. 191 CU IDOL SELF LEARNING MATERIAL (SLM)

UNIT - 12: AJAX STRUCTURE 12.0 Learning Objectives 12.1 Introduction 12.2 AJAX Model 12.3 Implementation of Ajax 12.4 Summary 12.5 Keywords 12.6 Learning Activity 12.7 Unit End Questions 12.8 References 12.0 LEARNING OBJECTIVES After studying this unit, you will be able to:  Know about AJAX  Use of AJAX  Various techniques of AJAX  Work on AJAX model 12.1 INTRODUCTION AJAX = Asynchronous JavaScript and XML. AJAX is a technique for creating fast and dynamic web pages. AJAX allows web pages to be updated asynchronously by exchanging small amounts of data with the server behind the scenes. This means that it is possible to update parts of a web page, without reloading the whole page. Classic web pages, (which do not use AJAX) must reload the entire page if the content should change. 12.2 AJAX MODEL AJAX communicates with the server using XMLHttpRequest object. Let's try to understand the flow of ajax or how ajax works by the image displayed below. 192 CU IDOL SELF LEARNING MATERIAL (SLM)

As you can see in the above example, XMLHttpRequest object plays an important role. 1. User sends a request from the UI and a javascript call goes to XMLHttpRequest object. 2. HTTP Request is sent to the server by XMLHttpRequest object. 3. Server interacts with the database using JSP, PHP, Servlet, ASP.net etc. 4. Data is retrieved. 5. Server sends XML data or JSON data to the XMLHttpRequest callback function. 6. HTML and CSS data is displayed on the browser. 12.3 IMPLEMENTATION OF AJAX Step 1) Creating the index page Index.php <html> <head> <title>PHP MVC Frameworks - Search Engine</title> 193 CU IDOL SELF LEARNING MATERIAL (SLM)

<script type=\"text/javascript\" src=\"/auto_complete.js\"></script> </head> <body> <h2>PHP MVC Frameworks - Search Engine</h2> <p><b>Type the first letter of the PHP MVC Framework</b></p> <form method=\"POST\" action=\"index.php\"> <p><input type=\"text\" size=\"40\" id=\"txtHint\" onkeyup=\"showName(this.value)\"></p> </form> <p>Matches: <span id=\"txtName\"></span></p> </body> </html> HERE,  “onkeyup=\"showName(this.value)\"” executes the JavaScript function showName everytime a key is typed in the textbox. This feature is called auto complete Step 2) Creating the frameworks page frameworks.php <?php 194 CU IDOL SELF LEARNING MATERIAL (SLM)

$frameworks = array(\"CodeIgniter\",\"Zend Framework\",\"Cake PHP\",\"Kohana\") ; $name = $_GET[\"name\"]; if (strlen($name) > 0) { $match = \"\"; for ($i = 0; $i < count($frameworks); $i++) { if (strtolower($name) == strtolower(substr($frameworks[$i], 0, strlen($name)))) { if ($match == \"\") { $match = $frameworks[$i]; } else { $match = $match . \" , \" . $frameworks[$i]; } } } } echo ($match == \"\") ? 'no match found' : $match; 195 CU IDOL SELF LEARNING MATERIAL (SLM)

?> Step 3) Creating the JS script auto_complete.js function showName(str){ if (str.length == 0){ //exit function if nothing has been typed in the textbox document.getElementById(\"txtName\").innerHTML=\"\"; //clear previous results return; } if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp=new XMLHttpRequest(); } else {// code for IE6, IE5 xmlhttp=new ActiveXObject(\"Microsoft.XMLHTTP\"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState == 4 && xmlhttp.status == 200){ document.getElementById(\"txtName\").innerHTML=xmlhttp.responseText; 196 CU IDOL SELF LEARNING MATERIAL (SLM)

} } xmlhttp.open(\"GET\",\"frameworks.php?name=\"+str,true); xmlhttp.send(); } HERE,  “if (str.length == 0)” check the length of the string. If it is 0, then the rest of the script is not executed.  “if (window.XMLHttpRequest)…” Internet Explorer versions 5 and 6 use ActiveXObject for AJAX implementation. Other versions and browsers such as Chrome, FireFox use XMLHttpRequest. This code will ensure that our application works in both IE 5 & 6 and other high versions of IE and browsers.  “xmlhttp.onreadystatechange=function…” checks if the AJAX interaction is complete and the status is 200 then updates the txtName span with the returned results. Step 4) Testing our PHP Ajax application Assuming you have saved the file index.php In phututs/ajax, browse to the URL http://localhost/phptuts/ajax/index.php 12.4 SUMMARY  AJAX is the acronym for Asynchronous JavaScript and XML  AJAX is a technology used to create rich interaction applications that reduce the interactions between the client and the server by updating only parts of the web page.  Internet Explorer version 5 and 6 use ActiveXObject to implement AJAX operations. 197 CU IDOL SELF LEARNING MATERIAL (SLM)

 Internet explorer version 7 and above and browsers Chrome, Firefox, Opera, and Safari use XMLHttpRequest. 12.5 KEYWORD  XMLHttpRequest -provides the ability to listen to various events that can occur while the request is being processed.  UI -user interface (UI) is the point of human-computer interaction and communication in a device.  javascript - JavaScript is the world's most popular programming language. JavaScript is the programming language of the Web  JSON -(JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write.  CSS-CSS is the language we use to style an HTML document. 12.6 LEARNING ACTIVITY 1. Draw AJAX model. ___________________________________________________________________________ ___________________________________________________________________________ 2. State the working of AJAX. ___________________________________________________________________________ ___________________________________________________________________________ 12.7UNIT END QUESTIONS A. Descriptive Questions Short Questions 1. What is Ajax? 2. What are Ajax applications? 3. How many types of triggers are present in update panel? 4. What are the disadvantages of AJAX? 5. What are the security issues with AJAX? Long Questions 1. What is the difference between synchronous and asynchronous requests? 2. What are the technologies used by AJAX? 3. What is the purpose of XMLHttpRequest? 4. What are the properties of XMLHttpRequest? 5. What are the important methods of XMLHttpRequest? 198 CU IDOL SELF LEARNING MATERIAL (SLM)

B. Multiple Choice Questions 1. What is AJAX? a. is a programme b. is a country name c. is a football club name 2. What sever support AJAX? a. SMTP b. WWW c. HTTP 3. AJAX Stands for: a. Asynchronous Javascript and XML b. Abstract JSON and XML c. Another Java Abstraction for X-Windows d. Another Java and XML Library 4. What makes Ajax unique? a. It works as a stand-alone Web-development tool. b. It works the same with all Web browsers. c. It uses C++ as its programming language. d. It makes data requests asynchronously. 5. What does the XMLHttpRequest object accomplish in Ajax? a. It's the programming language used to develop Ajax applications. b. It provides a means of exchanging structured data between the Web server and client. c. It provides the ability to asynchronously exchange data between Web browsers and a Web server. d. It provides the ability to mark up and style the display of Web-page text. Answers 1-a, 2-c, 3-a. 4-d, 5-c 199 CU IDOL SELF LEARNING MATERIAL (SLM)

12.8 REFERENCES Textbooks  T1: Steven Holzner, WordPress-The Complete Reference, McGraw-Hill.  T2: Robin Nixon, WordPress-MySQL-JavaScript, O’Reilly. Reference Books  R1: Rasmus Lerdorf, Kevin Tatroe, Bob Kaehms, Ric McGredy, WordPress, O’Reilly, Shroff Publishers. 200 CU IDOL SELF LEARNING MATERIAL (SLM)