{"id":237,"date":"2018-07-13T06:12:06","date_gmt":"2018-07-13T06:12:06","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=237"},"modified":"2018-08-13T04:33:42","modified_gmt":"2018-08-13T04:33:42","slug":"php-and-mysql","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/php-and-mysql\/","title":{"rendered":"PHP and MySQL"},"content":{"raw":"
\r\n\r\n \r\n\r\nObjectives<\/strong>\r\n\r\n \r\n\r\n\u2022\u00a0Ways to connect PHP and MySQL<\/strong>\r\n\r\n\u2022\u00a0Useful methods of PDO<\/strong>\r\n\r\n\u2022\u00a0PHP and MySQL connectivity<\/strong>\r\n\r\n \r\n\r\nWeb Database Architecture<\/strong>\r\n\r\n \r\n

Web Application is a client\/server architecture. In typical web application client\/server architecture consist of two objects: 1. A web browser and 2. A web server. Web Browser and Web Server communicate via a communication link \u2013 generally internet or through http protocol. A web browser send request to web server. The Web server send response to the web server. This architecture is used for a server sends static web pages. which Following figure shows the simple client\/server architecture.<\/p>\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\nThe typical web database application consists of following stages.\r\n\r\n \r\n

1.Using browser, user send request to webserver using HTTP. For example, by entering city name, the user wants to get all the vendor details who belongs to city. By entering city name in HTML form and click submit button, he sends request to webserver. The search result page is fetch.php<\/p>\r\n

2. The web server \u2013 Apache \u2013 receives the request and retrieve the file fetch.php. The webserver passes the file to PHP engine for processing.<\/p>\r\n

3. The PHP engine starts parsing the script. Inside the script, there is a command to connect to the database and execute the query. For example, here the query is to search vendor details based on their city name. PHP opens a connection to MySQL server and send query to MySQL server.<\/p>\r\n

4. MySQL server receives the query, process it and send the result back to the PHP engine<\/p>\r\n

5. The PHP engine get the query result, finishes the script and formatting the query result in HTML. Then returns the resulting HTML to web server.<\/span><\/p>\r\n

6. The web server sends HTML back to the browser.<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n

\u00a0 \u00a0The php engine and database server can run on the same machine or different machine.<\/p>\r\n \r\n\r\nThree ways to connect PHP and MySQL<\/strong>\r\n

    \r\n \t
  • An Application Programming Interface (API) is used to communicate an application with other applications. MySQL provides an API which specifies how PHP can work with MySQL.<\/li>\r\n \t
  • PHP provides 3 extensions to work with MySQL API.<\/li>\r\n<\/ul>\r\n
      \r\n \t
    1. MySQL extension<\/li>\r\n \t
    2. MySQLi extension<\/li>\r\n \t
    3. PDO MySQL driver extension<\/li>\r\n<\/ol>\r\n
        \r\n \t
      • PDO (PHP Data Objects) is a database abstraction layer. It is used specifically for PHP applications. To communicate with MySQL, It uses PDO MySQL driver extension. PDO can use other drivers extension to communicate with other databases.<\/li>\r\n \t
      • Each extension has its own pros and cons.<\/li>\r\n<\/ul>\r\n \r\n\r\nPDO (PHP Data Objects) Pros<\/strong>\r\n
          \r\n \t
        • Available for PHP 5.0 and later<\/li>\r\n \t
        • Object Oriented Interface<\/li>\r\n \t
        • Consistent Interface which is portable between many database servers such as Oracle, DB2, MS SQL Server and Postgre SQL<\/li>\r\n \t
        • Use features available MySQL 4.1.3 and later<\/li>\r\n<\/ul>\r\n\u00a0 \u00a0 \u00a0PDO Cons<\/strong>\r\n
            \r\n \t
          • Does not work PHP 4.X or earlier<\/li>\r\n \t
          • Not use advances features of MySQL 4.1.3 and later such as multiple statements<\/li>\r\n<\/ul>\r\n \r\n\r\nMySQLi (MySQL improved extension) Pros<\/strong>\r\n
              \r\n \t
            • Included with PHP 5.X or later<\/li>\r\n \t
            • Provides Object-Oriented and Procedure Oriented Interface<\/li>\r\n \t
            • use all advances features of MySQL 4.1.3 and later<\/li>\r\n<\/ul>\r\n\u00a0 \u00a0 MySQLi Cons<\/strong>\r\n\r\n<\/div>\r\n
              \r\n
                \r\n \t
              • Cannot used with other database servers<\/li>\r\n<\/ul>\r\n\u00a0 \u00a0 MySQL (MySQL extension) pros<\/strong>\r\n\r\n \r\n\r\n\u2022\u00a0 Available for PHP 3.X, 4.X and 5.X\r\n\r\n \r\n\r\nMySQL cons<\/strong>\r\n
                  \r\n \t
                • Not use advances features of MySQL 4.1.3 and later such as multiple statements<\/li>\r\n \t
                • Not under active development<\/li>\r\n<\/ul>\r\nPDO is used in this module for demonstration.\r\n
                    \r\n \t
                  • To use PDO object for making connection with MySQL, you must create a PDO object with three arguments. The three arguments are \u2013 DSN, User Name and Password.<\/li>\r\n \t
                  • The general syntax is:<\/li>\r\n<\/ul>\r\n

                    new PDO($dsn, $username, $password)<\/p>\r\n\r\n

                      \r\n \t
                    • DSN is short form of Data Source Name. The DSN specifies host name and database name. If MySQL database runs on the same server as PHP, then host name is localhost. The syntax of DSN for MySQL database is<\/li>\r\n<\/ul>\r\n \r\n\r\nmysql:host=hostaddress;dbname=databasename\r\n\r\n \r\n\r\nor you can set the values in a variable.\r\n\r\n \r\n\r\n$dsn = 'mysql:host=localhost;dbname=account'\r\n
                        \r\n \t
                      • To call a method of the object, type object name, followed by object access operator \u2013 code as -> - and followed by method name with arguments required for method in parentheses. Multiple arguments are separated by comma. The general syntax is<\/li>\r\n<\/ul>\r\nobjectName->methodName(Argument1 [, Argument2 ..])\r\n
                          \r\n \t
                        • To execute the select statement of SQL, query method of PDO object is used. The query method takes one parameter.<\/li>\r\n<\/ul>\r\n \r\n\r\n\r\n\r\n\r\n
                          Method<\/strong><\/td>\r\nDescription<\/strong><\/td>\r\n<\/tr>\r\n
                          query($select)\r\n\r\n \r\n\r\n <\/td>\r\nExecute the $select statement and returns a PDO statement object. The PDO\r\n\r\nstatement object contains the result set of $select. If no result set is returned,\r\n\r\nthen it returns false value.<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n \r\n
                            \r\n \t
                          • The general syntax to use query method is\u00a0 $PDOobject->query($select)<\/li>\r\n \t
                          • PDOStatement class has many methods. Below table describe some methods of the PDOStatement Class<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n\r\n\r\n
                            Method<\/strong><\/td>\r\nDescription<\/strong><\/td>\r\n<\/td>\r\n<\/tr>\r\n
                            fetch( )\r\n\r\n <\/td>\r\nReturns an array having result set for next row. Array can be index by\r\n\r\nstring and number. String is used for column name and number for column<\/td>\r\n<\/td>\r\n<\/tr>\r\n
                            <\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/div>\r\n
                            \r\n\r\n\r\n\r\n\r\n\r\n\r\n
                            <\/td>\r\nposition. If no array is available , it returns false.<\/td>\r\n<\/tr>\r\n
                            fetchall( )<\/td>\r\nReturns an array having all the rows of the result set.<\/td>\r\n<\/tr>\r\n
                            rowcount( )<\/td>\r\nReturns the number of rows affected by the executing of last statement.<\/td>\r\n<\/tr>\r\n
                            closeCursor( )\r\n\r\n <\/td>\r\nClose the cursor and frees the connection to the server so other SQL\r\n\r\nstatement can be issued.<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n
                              \r\n \t
                            • The following code shows one example using the query and fetch method. There is a GUI which ask user to enter city name and click on submit button. The output is displayed all the details of vendor who lives in the city entered by user.<\/li>\r\n \t
                            • The HTML code for GUI is shown below.<\/li>\r\n<\/ul>\r\n<html>\r\n\r\n<body>\r\n\r\n<form action=\"fetch.php\" method=\"post\"> Enter City Name:\r\n\r\n<input type=\"text\" name=\"city\"> <br><br>\r\n\r\n<input type=\"submit\" name=\"submit\" value=\"Submit\"> <input type=\"reset\" name=\"reset\" value=\"Reset\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/form>\r\n\r\n<\/body>\r\n\r\n<\/html>\r\n
                                \r\n \t
                              • The output of HTML code is shown below:<\/li>\r\n<\/ul>\r\n \r\n\r\n\"\"\r\n
                                  \r\n \t
                                • The PHP Code for file fetch.php is shown below<\/li>\r\n<\/ul>\r\n \r\n\r\n<?php\r\n\r\n$dsn = 'mysql:host=localhost;dbname=account'; $uname = 'root';\r\n\r\n$pwd = '';\r\n\r\n$db = new PDO($dsn,$uname,$pwd); $vcity = $_POST['city'];\r\n\r\n$query = \"select * from vendor\r\n\r\nwhere vcity = '$vcity'\";\r\n\r\necho \"<br> Query : $query <br>\";<\/span>\r\n\r\n$result = $db->query($query);<\/span>\r\n\r\n$row = $result->fetch();<\/span>\r\n\r\nwhile($row != null)<\/span>\r\n\r\n{<\/span>\r\n\r\n$vid = $row['vid'];<\/span>\r\n\r\n$vname = $row['vname'];<\/span>\r\n\r\n$vaddress = $row['vaddress'];<\/span>\r\n\r\n$vcity = $row['vcity'];<\/span>\r\n\r\n$vmobile = $row['vmobile'];<\/span>\r\n\r\necho \"<br>\";<\/span>\r\n\r\necho \"<br> Vendor ID : $vid\";<\/span>\r\n\r\necho \"<br> Vendor Name: $vname\";<\/span>\r\n\r\necho \"<br> Vendor Address: $vaddress\";<\/span>\r\n\r\necho \"<br> Vendor City : $vcity\";<\/span>\r\n\r\necho \"<br> Vendor Mobile : $vmobile\";<\/span>\r\n\r\n$row = $result->fetch();<\/span>\r\n\r\n}<\/span>\r\n\r\n$result->closeCursor();<\/span>\r\n\r\n?><\/span>\r\n\r\n<\/div>\r\n
                                  \r\n
                                    \r\n \t
                                  • The output is given below:<\/li>\r\n \t
                                  • Query : select * from vendor where vcity = 'Rajkot'<\/li>\r\n<\/ul>\r\n

                                    \u00a0 \u00a0 Vendor ID : 1<\/p>\r\n

                                    Vendor Name: Anand<\/p>\r\n

                                    Vendor Address: Ravi Park<\/p>\r\n

                                    Vendor City : Rajkot<\/p>\r\n

                                    Vendor Mobile : 9876034521<\/p>\r\n

                                    Vendor ID : 2<\/p>\r\n

                                    Vendor Name: Dharmesh<\/p>\r\n

                                    Vendor Address: 19, Park Avenue<\/span><\/p>\r\n

                                    Vendor City : Rajkot<\/span><\/p>\r\n

                                    Vendor Mobile : 1234567890<\/span><\/p>\r\n

                                    Vendor ID : 7<\/span><\/p>\r\n

                                    Vendor Name: Manish<\/span><\/p>\r\n

                                    Vendor Address: University Road<\/span><\/p>\r\n

                                    Vendor City : Rajkot<\/span><\/p>\r\n

                                    Vendor Mobile : 9087654321<\/span><\/p>\r\n\r\n<\/div>\r\n

                                    \r\n
                                      \r\n \t
                                    • In the above code, fetch() method is used. The same code be written using fetchall() method. Following code shows HTML and PHP code of the above program using fetchall() method.<\/li>\r\n \t
                                    • HTML code<\/li>\r\n<\/ul>\r\n<html>\r\n\r\n<body>\r\n\r\n<form action=\"fetchall.php\" method=\"post\">\r\n\r\nEnter City Name:\r\n\r\n<input type=\"text\" name=\"city\">\r\n\r\n<br><br>\r\n\r\n<input type=\"submit\" name=\"submit\" value=\"Submit\">\r\n\r\n<input type=\"reset\" name=\"reset\" value=\"Reset\">\r\n\r\n<\/form>\r\n\r\n<\/body>\r\n\r\n<\/html>\r\n
                                        \r\n \t
                                      • PHP Code<\/li>\r\n<\/ul>\r\n<?php\r\n\r\n$dsn = 'mysql:host=localhost;dbname=account'; $uname = 'root';\r\n\r\n$pwd = '';\r\n\r\n$db = new PDO($dsn,$uname,$pwd); $vcity = $_POST['city'];\r\n\r\n$query = \"select *\u00a0from vendor\u00a0<\/span>where vcity = '$vcity'\";<\/span>\r\n\r\n<\/div>\r\n
                                        \r\n\r\n\/\/echo \"<br> Query : $query <br>\";\r\n\r\n$result = $db->query($query);\r\n\r\n$rows = $result->fetchall();\r\n\r\nforeach($rows as $row)\r\n\r\n{\r\n\r\n$vid = $row['vid'];\r\n\r\n$vname = $row['vname'];\r\n\r\n$vaddress = $row['vaddress'];\r\n\r\n$vcity = $row['vcity'];\r\n\r\n$vmobile = $row['vmobile'];\r\n\r\necho \"<br>\";\r\n\r\necho \"<br> Vendor ID : $vid\";\r\n\r\necho \"<br> Vendor Name: $vname\";\r\n\r\necho \"<br> Vendor Address: $vaddress\";\r\n\r\necho \"<br> Vendor City : $vcity\";\r\n\r\necho \"<br> Vendor Mobile : $vmobile\";\r\n\r\n}\r\n\r\n$result->closeCursor();\r\n\r\n?>\r\n
                                          \r\n \t
                                        • To execute insert, update or delete statement exec method of PDO object is used. Exec method takes insert, update or delete SQL statement as argument.<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n\r\n
                                          Method<\/td>\r\nDescription<\/td>\r\n<\/tr>\r\n
                                          exec($SQLSelect)\r\n\r\n <\/td>\r\nExecute the $select and returns a number indicate how many rows are\r\n\r\naffected. If no rows are affected, then return zero.<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n
                                            \r\n \t
                                          • For example, to insert a new record in vendor table first create GUI. Following code shows<\/li>\r\n<\/ul>\r\n \r\n

                                            <html><\/p>\r\n

                                            <body><\/p>\r\n

                                            <form action='exec.php' method='post'> <table><\/p>\r\n

                                            <!-- vid --> <tr><\/p>\r\n

                                            <td>Vendor ID:<\/td><\/span><\/p>\r\n

                                            <td> <input type='text' name='vid'> <\/td><\/span><\/p>\r\n

                                            <\/tr><\/span><\/p>\r\n

                                            <!-- vname --><\/span><\/p>\r\n

                                            <tr><\/span><\/p>\r\n

                                            <td>Vendor Name:<\/td><\/span><\/p>\r\n

                                            <td> <input type='text' name='vname'> <\/td><\/span><\/p>\r\n

                                            <\/tr><\/span><\/p>\r\n

                                            <!-- vaddress\u00a0 --><\/span><\/p>\r\n

                                            <tr><\/span><\/p>\r\n

                                            <td>Vendor Address:<\/td><\/span><\/p>\r\n

                                            <td> <input type='text' name='vaddress'> <\/td><\/span><\/p>\r\n

                                            <\/tr><\/span><\/p>\r\n

                                            <!-- vcity --><\/span><\/p>\r\n

                                            <tr><\/span><\/p>\r\n

                                            <td> Vendor City: <\/td><\/span><\/p>\r\n

                                            <td> <input type='text' name='vcity'> <\/td><\/span><\/p>\r\n

                                            <\/tr><\/span><\/p>\r\n

                                            <!-- vmobile --><\/span><\/p>\r\n

                                            <tr><\/span><\/p>\r\n

                                            <td> Vendor Mobile: <\/td><\/span><\/p>\r\n

                                            <td> <input type='text' name='vmobile'> <\/td><\/span><\/p>\r\n

                                            <\/tr><\/span><\/p>\r\n

                                            <!-- submit and reset --><\/span><\/p>\r\n

                                            <tr><\/span><\/p>\r\n

                                            <td> <input type='submit' name='submit' value='Submit'> <\/td> <td> <input type='reset' name='reset' value='Reset'> <\/td> <\/tr><\/span><\/p>\r\n

                                            <\/table><\/span><\/p>\r\n

                                            <\/form><\/span><\/p>\r\n

                                            <\/body><\/span><\/p>\r\n

                                            <\/html><\/span><\/p>\r\n\r\n<\/div>\r\n

                                            \r\n

                                            <\/p>\r\nThe output of the HTML code is :\r\n\r\n \r\n\r\n \r\n\r\n\"\"\r\n\r\n<\/div>\r\n

                                              \r\n \t
                                            • The PHP code for file exec.php is given below.<\/span><\/li>\r\n<\/ul>\r\n
                                              \r\n\r\n\u00a0 \u00a0<?php\r\n\r\n$dsn = 'mysql:host=localhost;dbname=account'; $uname = 'root';\r\n\r\n$pwd = '';\r\n\r\n$db = new PDO($dsn,$uname,$pwd);\r\n\r\n$vid = $_POST['vid'];\r\n\r\n$vname = $_POST['vname'];\r\n\r\n$vaddress = $_POST['vaddress']; $vcity = $_POST['vcity']; $vmobile = $_POST['vmobile'];\r\n\r\n$query = \"insert into vendor\r\n\r\nvalues($vid,'$vname','$vaddress','$vcity','$vmobile') \";\r\n\r\necho \"<br> Query : $query <br>\"; $result = $db->exec($query);\r\n\r\necho \"<br> Result is : $result\"; if($result > 0)\r\n\r\n{\r\n\r\necho \"<br> Record Insrted Successfully\";\r\n\r\n}\r\n\r\n?>\r\n
                                                \r\n \t
                                              • The output is<\/li>\r\n<\/ul>\r\n<\/div>\r\n
                                                \r\n

                                                \u00a0 \u00a0Query : insert into vendor values(22,'Kalpesh','Bopal','Ahmedabad','9753124680')<\/p>\r\n

                                                Result is : 1<\/p>\r\n

                                                Record Insrted Successfully<\/p>\r\n\r\n

                                                  \r\n \t
                                                • For live application development, prepare method is used. Prepare method is a method of PDO object. Prepare statement improves database performance and security.<\/li>\r\n \t
                                                • When a statement is executed multiple times, the statements allow the database server to reuse of the work which is done when prepare a statement.<\/li>\r\n \t
                                                • Prepare statement also improves security because it can prevent most types of the SQL injection attacks.<\/li>\r\n<\/ul>\r\n\"\"\r\n\r\nLet\u2019s take one example using prepare statement. The HTML code is shown below\r\n\r\n \r\n\r\n<html>\r\n\r\n<body>\r\n\r\n<form action='prepareselect.php' method='post'> Enter city name :\r\n\r\n<input type=\"text\" name=\"vcity\"> <\/input>\r\n\r\n<br><br>\r\n\r\n<input type='submit' name='submit' value='Submit'> <input type='reset' name='reset' value='Reset'> <\/form>\r\n\r\n<\/body>\r\n\r\n<\/html>\r\n\r\n\"\"\r\n\r\n<\/div>\r\n
                                                  \r\n\r\n\u00a0 \u00a0The PHP code for the file prepareselect.php is shown below.\r\n\r\n \r\n\r\n<?php\r\n\r\n$dsn='mysql:host=localhost;dbname=account';\r\n\r\n$uname = 'root';\r\n\r\n$pwd = '';\r\n\r\n$db = new PDO($dsn,$uname,$pwd); $vcity = $_POST['vcity'];\r\n\r\n$query = \"select * from vendor\r\n\r\nwhere vcity = :vcity\";\r\n\r\n$result = $db->prepare($query);\r\n\r\n$result->bindValue(':vcity',$vcity); $result->execute();\r\n\r\n$rows = $result->fetchAll();\r\n\r\nforeach($rows as $r)\r\n\r\n{\r\n\r\necho '<br> Vendor ID :'. $r['vid'];\r\n\r\necho '<br> Vendor Name :'. $r['vname'];\r\n\r\necho '<br> Vendor Address :'. $r['vaddress'];\r\n\r\necho '<br> Vendor City :'. $r['vcity'];\r\n\r\necho '<br> Vendor Mobile :'. $r['vmobile'];\r\n\r\necho \"<br>\";\r\n\r\n}\r\n\r\n$result->closeCursor();\r\n\r\n?>\r\n\r\n \r\n\r\nAs the user clicks on submit button, he\/she will get the output as shown below.\r\n\r\n<\/div>\r\n
                                                  \r\n\r\n\u00a0 \u00a0 Vendor ID :1\r\n\r\nVendor Name :Anand\r\n\r\nVendor Address :Ravi Park\r\n\r\nVendor City :Rajkot\r\n\r\nVendor Mobile :9876034521\r\n\r\n \r\n\r\nVendor ID :2\r\n\r\nVendor Name :Dharmesh\r\n\r\nVendor Address :19, Park Avenue\r\n\r\nVendor City :Rajkot\r\n\r\nVendor Mobile :1234567890\r\n\r\n \r\n\r\nVendor ID :7\r\n\r\nVendor Name :Manish\r\n\r\nVendor Address :University Road\r\n\r\nVendor City :Rajkot\r\n\r\nVendor Mobile :9087654321\r\n\r\n \r\n

                                                  In the similar way, prepare and bindValue method can be used to insert,update or delete statement. Following code shows to insert a record using prpare and bindValue.<\/p>\r\n \r\n\r\n<html>\r\n\r\n<body>\r\n\r\n<form action='prepareinsert.php' method='post'>\r\n\r\n<table>\r\n\r\n<!-- vid --> <tr>\r\n\r\n<td>Vendor ID:<\/td>\r\n\r\n<td> <input type='text' name='vid'> <\/td> <\/tr>\r\n\r\n<!-- vname --> <tr>\r\n\r\n<td>Vendor Name:<\/td>\r\n\r\n<td> <input type='text' name='vname'> <\/td> <\/tr>\r\n\r\n<!-- vaddress\u00a0 --><\/span>\r\n\r\n<tr><\/span>\r\n\r\n<td>Vendor Address:<\/td><\/span>\r\n\r\n<td> <input type='text' name='vaddress'> <\/td><\/span>\r\n\r\n<\/tr><\/span>\r\n\r\n<!-- vcity --><\/span>\r\n\r\n<tr><\/span>\r\n\r\n<td> Vendor City: <\/td><\/span>\r\n\r\n<td> <input type='text' name='vcity'> <\/td><\/span>\r\n\r\n<\/tr><\/span>\r\n\r\n<!-- vmobile --><\/span>\r\n\r\n<tr><\/span>\r\n\r\n<td> Vendor Mobile: <\/td><\/span>\r\n\r\n<td> <input type='text' name='vmobile'> <\/td><\/span>\r\n\r\n<\/tr><\/span>\r\n\r\n<!-- submit and reset --><\/span>\r\n\r\n<tr><\/span>\r\n\r\n<td> <input type='submit' name='submit' value='Submit'> <\/td> <td> <input type='reset' name='reset' value='Reset'> <\/td> <\/tr><\/span>\r\n\r\n<\/table><\/span>\r\n\r\n<\/form><\/span>\r\n\r\n<\/body><\/span>\r\n\r\n<\/html><\/span>\r\n\r\n<\/div>\r\n

                                                  <\/div>\r\n
                                                  \r\n\r\n\"\"\r\n\r\n \r\n\r\n \r\n\r\nThe code for prepareinsert.php is shown below.\r\n\r\n \r\n\r\n\u00a0<?php<\/span>\r\n\r\n$dsn='mysql:host=localhost;dbname=account';<\/span>\r\n\r\n$uname = 'root';<\/span>\r\n\r\n$pwd = '';<\/span>\r\n\r\n$db = new PDO($dsn,$uname,$pwd);<\/span>\r\n\r\n$vid = $_POST['vid'];<\/span>\r\n\r\n$vname = $_POST['vname'];<\/span>\r\n\r\n$vaddress = $_POST['vaddress'];<\/span>\r\n\r\n$vcity = $_POST['vcity'];<\/span>\r\n\r\n$vmobile = $_POST['vmobile'];<\/span>\r\n\r\n$query = \"insert into vendor values (:vid, :vname, :vaddress, :vcity, :vmobile)\";<\/span>\r\n\r\n$result = $db->prepare($query);<\/span>\r\n\r\n$result->bindValue(':vid',$vid);<\/span>\r\n\r\n$result->bindValue(':vname',$vname);<\/span>\r\n\r\n$result->bindValue(':vaddress',$vaddress);<\/span>\r\n\r\n$result->bindValue(':vcity',$vcity);<\/span>\r\n\r\n$result->bindValue(':vmobile',$vmobile);<\/span>\r\n\r\n$success = $result->execute();<\/span>\r\n\r\nif($success > 0)<\/span>\r\n\r\n{<\/span>\r\n\r\necho \"<br> Record Inserted Successfully \";<\/span>\r\n\r\n}<\/span>\r\n\r\n$result->closeCursor();<\/span>\r\n\r\n?><\/span>\r\n\r\n \r\n\r\n\u00a0Output for the program is<\/span>\r\n\r\n \r\n\r\nRecord Inserted Successfully<\/strong>\r\n\r\n<\/div>\r\n
                                                  \r\n
                                                    \r\n \t
                                                  • An exception is an object which contains information about error which has occurred. Some PHP statements throw exception when an error is occurred. If an exception is not handled, then the applications terminate with error. To handle exception, try catch statement is used.<\/li>\r\n \t
                                                  • To handle the errors thrown by the PDO library, the PDO Exception class is used.<\/li>\r\n \t
                                                  • The below code shows an example of PDO Exception.<\/li>\r\n<\/ul>\r\n \r\n\r\n<\/div>\r\n
                                                    \r\n\r\n\u00a0 \u00a0 <?php\r\n\r\n\/\/ PDO Database Object created\r\n\r\n$dsn ='mysql:host=localhost;dbname=account';\r\n\r\n$uname = 'root';\r\n\r\n$pwd = 'pwd';\r\n\r\ntry\r\n\r\n{\r\n\r\n$db = new PDO($dsn,$uname,$pwd);\r\n\r\necho \"<br> Connection established successfully\";\r\n\r\n\/\/\r\n\r\n$query = \"select * from vendor\";\r\n\r\necho '<br>'. $query . '<br>';\r\n\r\n$rows = $db->query($query);\r\n\r\n$row = $rows->fetch();\r\n\r\nwhile ($row != null)\r\n\r\n{\r\n\r\n$vid = $row['vid'];\r\n\r\n$vname = $row['vname'];\r\n\r\n$vaddress = $row['vaddress'];\r\n\r\n$vcity = $row['vcity'];\r\n\r\n$vmobile = $row['vmobile'];\r\n\r\necho '<br>';\r\n\r\necho \"vid : $vid <br>\";\r\n\r\necho \"vname : $vname <br>\";\r\n\r\necho \"vaddress : $vaddress <br> \";\r\n\r\necho \"vcity : $vcity <br> \";\r\n\r\necho \"vmobile : $vmobile <br> \";\r\n\r\n\/\/ fetch next record\r\n\r\n$row = $rows->fetch();\r\n\r\n}\r\n\r\n}\r\n\r\ncatch(PDOException $e)\r\n\r\n{\r\n\r\n$errormessage = $e->getMessage();\r\n\r\necho \"<br> An error occured when connecting with database. The error is :<\/span>\r\n\r\n$errormessage\";<\/span>\r\n\r\n}<\/span>\r\n\r\n?><\/span>\r\n\r\n<\/div>\r\n
                                                      \r\n \t
                                                    • The output of the program is<\/li>\r\n<\/ul>\r\n \r\n

                                                      An error occured when connecting with database. The error is : SQLSTATE[28000]<\/p>\r\n[1045] Access denied for user 'root'@'localhost' (using password: YES)\r\n\r\n \r\n\r\nReferences:<\/strong>\r\n\r\n \r\n

                                                      1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,<\/p>\r\n

                                                      2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress<\/p>\r\n

                                                      3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,<\/p>\r\n

                                                      4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O'Reilly Media<\/p>\r\n

                                                      5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox<\/p>\r\n

                                                      6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible<\/p>\r\n

                                                      7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach<\/p>\r\n

                                                      8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML\/Javascript\/DHTML\/PHP , BPB Publications<\/p>\r\n

                                                      9. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams<\/p>\r\n

                                                      10. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education<\/p>\r\n

                                                      11. http:\/\/www.php.net\/<\/p>\r\n

                                                      12. http:\/\/www.w3schools.com\/<\/p>\r\n

                                                      13. http:\/\/www.tutorialspoint.com\/<\/p>","rendered":"

                                                      \n

                                                       <\/p>\n

                                                      Objectives<\/strong><\/p>\n

                                                       <\/p>\n

                                                      \u2022\u00a0Ways to connect PHP and MySQL<\/strong><\/p>\n

                                                      \u2022\u00a0Useful methods of PDO<\/strong><\/p>\n

                                                      \u2022\u00a0PHP and MySQL connectivity<\/strong><\/p>\n

                                                       <\/p>\n

                                                      Web Database Architecture<\/strong><\/p>\n

                                                       <\/p>\n

                                                      Web Application is a client\/server architecture. In typical web application client\/server architecture consist of two objects: 1. A web browser and 2. A web server. Web Browser and Web Server communicate via a communication link \u2013 generally internet or through http protocol. A web browser send request to web server. The Web server send response to the web server. This architecture is used for a server sends static web pages. which Following figure shows the simple client\/server architecture.<\/p>\n

                                                       <\/p>\n

                                                      \"\"<\/p>\n

                                                       <\/p>\n

                                                      The typical web database application consists of following stages.<\/p>\n

                                                       <\/p>\n

                                                      1.Using browser, user send request to webserver using HTTP. For example, by entering city name, the user wants to get all the vendor details who belongs to city. By entering city name in HTML form and click submit button, he sends request to webserver. The search result page is fetch.php<\/p>\n

                                                      2. The web server \u2013 Apache \u2013 receives the request and retrieve the file fetch.php. The webserver passes the file to PHP engine for processing.<\/p>\n

                                                      3. The PHP engine starts parsing the script. Inside the script, there is a command to connect to the database and execute the query. For example, here the query is to search vendor details based on their city name. PHP opens a connection to MySQL server and send query to MySQL server.<\/p>\n

                                                      4. MySQL server receives the query, process it and send the result back to the PHP engine<\/p>\n

                                                      5. The PHP engine get the query result, finishes the script and formatting the query result in HTML. Then returns the resulting HTML to web server.<\/span><\/p>\n

                                                      6. The web server sends HTML back to the browser.<\/span><\/p>\n<\/div>\n

                                                      \n

                                                      \u00a0 \u00a0The php engine and database server can run on the same machine or different machine.<\/p>\n

                                                       <\/p>\n

                                                      Three ways to connect PHP and MySQL<\/strong><\/p>\n

                                                        \n
                                                      • An Application Programming Interface (API) is used to communicate an application with other applications. MySQL provides an API which specifies how PHP can work with MySQL.<\/li>\n
                                                      • PHP provides 3 extensions to work with MySQL API.<\/li>\n<\/ul>\n
                                                          \n
                                                        1. MySQL extension<\/li>\n
                                                        2. MySQLi extension<\/li>\n
                                                        3. PDO MySQL driver extension<\/li>\n<\/ol>\n
                                                            \n
                                                          • PDO (PHP Data Objects) is a database abstraction layer. It is used specifically for PHP applications. To communicate with MySQL, It uses PDO MySQL driver extension. PDO can use other drivers extension to communicate with other databases.<\/li>\n
                                                          • Each extension has its own pros and cons.<\/li>\n<\/ul>\n

                                                             <\/p>\n

                                                            PDO (PHP Data Objects) Pros<\/strong><\/p>\n

                                                              \n
                                                            • Available for PHP 5.0 and later<\/li>\n
                                                            • Object Oriented Interface<\/li>\n
                                                            • Consistent Interface which is portable between many database servers such as Oracle, DB2, MS SQL Server and Postgre SQL<\/li>\n
                                                            • Use features available MySQL 4.1.3 and later<\/li>\n<\/ul>\n

                                                              \u00a0 \u00a0 \u00a0PDO Cons<\/strong><\/p>\n

                                                                \n
                                                              • Does not work PHP 4.X or earlier<\/li>\n
                                                              • Not use advances features of MySQL 4.1.3 and later such as multiple statements<\/li>\n<\/ul>\n

                                                                 <\/p>\n

                                                                MySQLi (MySQL improved extension) Pros<\/strong><\/p>\n

                                                                  \n
                                                                • Included with PHP 5.X or later<\/li>\n
                                                                • Provides Object-Oriented and Procedure Oriented Interface<\/li>\n
                                                                • use all advances features of MySQL 4.1.3 and later<\/li>\n<\/ul>\n

                                                                  \u00a0 \u00a0 MySQLi Cons<\/strong><\/p>\n<\/div>\n

                                                                  \n
                                                                    \n
                                                                  • Cannot used with other database servers<\/li>\n<\/ul>\n

                                                                    \u00a0 \u00a0 MySQL (MySQL extension) pros<\/strong><\/p>\n

                                                                     <\/p>\n

                                                                    \u2022\u00a0 Available for PHP 3.X, 4.X and 5.X<\/p>\n

                                                                     <\/p>\n

                                                                    MySQL cons<\/strong><\/p>\n

                                                                      \n
                                                                    • Not use advances features of MySQL 4.1.3 and later such as multiple statements<\/li>\n
                                                                    • Not under active development<\/li>\n<\/ul>\n

                                                                      PDO is used in this module for demonstration.<\/p>\n

                                                                        \n
                                                                      • To use PDO object for making connection with MySQL, you must create a PDO object with three arguments. The three arguments are \u2013 DSN, User Name and Password.<\/li>\n
                                                                      • The general syntax is:<\/li>\n<\/ul>\n

                                                                        new PDO($dsn, $username, $password)<\/p>\n

                                                                          \n
                                                                        • DSN is short form of Data Source Name. The DSN specifies host name and database name. If MySQL database runs on the same server as PHP, then host name is localhost. The syntax of DSN for MySQL database is<\/li>\n<\/ul>\n

                                                                           <\/p>\n

                                                                          mysql:host=hostaddress;dbname=databasename<\/p>\n

                                                                           <\/p>\n

                                                                          or you can set the values in a variable.<\/p>\n

                                                                           <\/p>\n

                                                                          $dsn = ‘mysql:host=localhost;dbname=account’<\/p>\n

                                                                            \n
                                                                          • To call a method of the object, type object name, followed by object access operator \u2013 code as -> – and followed by method name with arguments required for method in parentheses. Multiple arguments are separated by comma. The general syntax is<\/li>\n<\/ul>\n

                                                                            objectName->methodName(Argument1 [, Argument2 ..])<\/p>\n

                                                                              \n
                                                                            • To execute the select statement of SQL, query method of PDO object is used. The query method takes one parameter.<\/li>\n<\/ul>\n

                                                                               <\/p>\n\n\n\n\n
                                                                              Method<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
                                                                              query($select)<\/p>\n

                                                                               <\/p>\n

                                                                               <\/td>\n

                                                                              		Execute the $select statement and returns a PDO statement object. The PDO<\/p>\n

                                                                              statement object contains the result set of $select. If no result set is returned,<\/p>\n

                                                                              then it returns false value.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                                                               <\/p>\n

                                                                                \n
                                                                              • The general syntax to use query method is\u00a0 $PDOobject->query($select)<\/li>\n
                                                                              • PDOStatement class has many methods. Below table describe some methods of the PDOStatement Class<\/li>\n<\/ul>\n\n\n\n\n\n
                                                                                Method<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/td>\n<\/tr>\n
                                                                                fetch( )<\/p>\n

                                                                                 <\/td>\n

                                                                                		Returns an array having result set for next row. Array can be index by<\/p>\n

                                                                                string and number. String is used for column name and number for column<\/td>\n

                                                                                		<\/td>\n<\/tr>\n
                                                                                <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
                                                                                \n\n\n\n\n\n\n
                                                                                <\/td>\nposition. If no array is available , it returns false.<\/td>\n<\/tr>\n
                                                                                fetchall( )<\/td>\nReturns an array having all the rows of the result set.<\/td>\n<\/tr>\n
                                                                                rowcount( )<\/td>\nReturns the number of rows affected by the executing of last statement.<\/td>\n<\/tr>\n
                                                                                closeCursor( )<\/p>\n

                                                                                 <\/td>\n

                                                                                		Close the cursor and frees the connection to the server so other SQL<\/p>\n

                                                                                statement can be issued.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                                                                  \n
                                                                                • The following code shows one example using the query and fetch method. There is a GUI which ask user to enter city name and click on submit button. The output is displayed all the details of vendor who lives in the city entered by user.<\/li>\n
                                                                                • The HTML code for GUI is shown below.<\/li>\n<\/ul>\n

                                                                                  <html><\/p>\n

                                                                                  <body><\/p>\n

                                                                                  <form action=”fetch.php” method=”post”> Enter City Name:<\/p>\n

                                                                                  <input type=”text” name=”city”> <br><br><\/p>\n

                                                                                  <input type=”submit” name=”submit” value=”Submit”> <input type=”reset” name=”reset” value=”Reset”>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/form><\/p>\n

                                                                                  <\/body><\/p>\n

                                                                                  <\/html><\/p>\n

                                                                                    \n
                                                                                  • The output of HTML code is shown below:<\/li>\n<\/ul>\n

                                                                                     <\/p>\n

                                                                                    \"\"<\/p>\n

                                                                                      \n
                                                                                    • The PHP Code for file fetch.php is shown below<\/li>\n<\/ul>\n

                                                                                       <\/p>\n

                                                                                      <?php<\/p>\n

                                                                                      $dsn = ‘mysql:host=localhost;dbname=account’; $uname = ‘root’;<\/p>\n

                                                                                      $pwd = ”;<\/p>\n

                                                                                      $db = new PDO($dsn,$uname,$pwd); $vcity = $_POST[‘city’];<\/p>\n

                                                                                      $query = “select * from vendor<\/p>\n

                                                                                      where vcity = ‘$vcity'”;<\/p>\n

                                                                                      echo “<br> Query : $query <br>”;<\/span><\/p>\n

                                                                                      $result = $db->query($query);<\/span><\/p>\n

                                                                                      $row = $result->fetch();<\/span><\/p>\n

                                                                                      while($row != null)<\/span><\/p>\n

                                                                                      {<\/span><\/p>\n

                                                                                      $vid = $row[‘vid’];<\/span><\/p>\n

                                                                                      $vname = $row[‘vname’];<\/span><\/p>\n

                                                                                      $vaddress = $row[‘vaddress’];<\/span><\/p>\n

                                                                                      $vcity = $row[‘vcity’];<\/span><\/p>\n

                                                                                      $vmobile = $row[‘vmobile’];<\/span><\/p>\n

                                                                                      echo “<br>”;<\/span><\/p>\n

                                                                                      echo “<br> Vendor ID : $vid”;<\/span><\/p>\n

                                                                                      echo “<br> Vendor Name: $vname”;<\/span><\/p>\n

                                                                                      echo “<br> Vendor Address: $vaddress”;<\/span><\/p>\n

                                                                                      echo “<br> Vendor City : $vcity”;<\/span><\/p>\n

                                                                                      echo “<br> Vendor Mobile : $vmobile”;<\/span><\/p>\n

                                                                                      $row = $result->fetch();<\/span><\/p>\n

                                                                                      }<\/span><\/p>\n

                                                                                      $result->closeCursor();<\/span><\/p>\n

                                                                                      ?><\/span><\/p>\n<\/div>\n

                                                                                      \n
                                                                                        \n
                                                                                      • The output is given below:<\/li>\n
                                                                                      • Query : select * from vendor where vcity = ‘Rajkot’<\/li>\n<\/ul>\n

                                                                                        \u00a0 \u00a0 Vendor ID : 1<\/p>\n

                                                                                        Vendor Name: Anand<\/p>\n

                                                                                        Vendor Address: Ravi Park<\/p>\n

                                                                                        Vendor City : Rajkot<\/p>\n

                                                                                        Vendor Mobile : 9876034521<\/p>\n

                                                                                        Vendor ID : 2<\/p>\n

                                                                                        Vendor Name: Dharmesh<\/p>\n

                                                                                        Vendor Address: 19, Park Avenue<\/span><\/p>\n

                                                                                        Vendor City : Rajkot<\/span><\/p>\n

                                                                                        Vendor Mobile : 1234567890<\/span><\/p>\n

                                                                                        Vendor ID : 7<\/span><\/p>\n

                                                                                        Vendor Name: Manish<\/span><\/p>\n

                                                                                        Vendor Address: University Road<\/span><\/p>\n

                                                                                        Vendor City : Rajkot<\/span><\/p>\n

                                                                                        Vendor Mobile : 9087654321<\/span><\/p>\n<\/div>\n

                                                                                        \n
                                                                                          \n
                                                                                        • In the above code, fetch() method is used. The same code be written using fetchall() method. Following code shows HTML and PHP code of the above program using fetchall() method.<\/li>\n
                                                                                        • HTML code<\/li>\n<\/ul>\n

                                                                                          <html><\/p>\n

                                                                                          <body><\/p>\n

                                                                                          <form action=”fetchall.php” method=”post”><\/p>\n

                                                                                          Enter City Name:<\/p>\n

                                                                                          <input type=”text” name=”city”><\/p>\n

                                                                                          <br><br><\/p>\n

                                                                                          <input type=”submit” name=”submit” value=”Submit”><\/p>\n

                                                                                          <input type=”reset” name=”reset” value=”Reset”><\/p>\n

                                                                                          <\/form><\/p>\n

                                                                                          <\/body><\/p>\n

                                                                                          <\/html><\/p>\n

                                                                                            \n
                                                                                          • PHP Code<\/li>\n<\/ul>\n

                                                                                            <?php<\/p>\n

                                                                                            $dsn = ‘mysql:host=localhost;dbname=account’; $uname = ‘root’;<\/p>\n

                                                                                            $pwd = ”;<\/p>\n

                                                                                            $db = new PDO($dsn,$uname,$pwd); $vcity = $_POST[‘city’];<\/p>\n

                                                                                            $query = “select *\u00a0from vendor\u00a0<\/span>where vcity = ‘$vcity'”;<\/span><\/p>\n<\/div>\n

                                                                                            \n

                                                                                            \/\/echo “<br> Query : $query <br>”;<\/p>\n

                                                                                            $result = $db->query($query);<\/p>\n

                                                                                            $rows = $result->fetchall();<\/p>\n

                                                                                            foreach($rows as $row)<\/p>\n

                                                                                            {<\/p>\n

                                                                                            $vid = $row[‘vid’];<\/p>\n

                                                                                            $vname = $row[‘vname’];<\/p>\n

                                                                                            $vaddress = $row[‘vaddress’];<\/p>\n

                                                                                            $vcity = $row[‘vcity’];<\/p>\n

                                                                                            $vmobile = $row[‘vmobile’];<\/p>\n

                                                                                            echo “<br>”;<\/p>\n

                                                                                            echo “<br> Vendor ID : $vid”;<\/p>\n

                                                                                            echo “<br> Vendor Name: $vname”;<\/p>\n

                                                                                            echo “<br> Vendor Address: $vaddress”;<\/p>\n

                                                                                            echo “<br> Vendor City : $vcity”;<\/p>\n

                                                                                            echo “<br> Vendor Mobile : $vmobile”;<\/p>\n

                                                                                            }<\/p>\n

                                                                                            $result->closeCursor();<\/p>\n

                                                                                            ?><\/p>\n

                                                                                              \n
                                                                                            • To execute insert, update or delete statement exec method of PDO object is used. Exec method takes insert, update or delete SQL statement as argument.<\/li>\n<\/ul>\n\n\n\n\n
                                                                                              Method<\/td>\nDescription<\/td>\n<\/tr>\n
                                                                                              exec($SQLSelect)<\/p>\n

                                                                                               <\/td>\n

                                                                                              		Execute the $select and returns a number indicate how many rows are<\/p>\n

                                                                                              affected. If no rows are affected, then return zero.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                                                                                \n
                                                                                              • For example, to insert a new record in vendor table first create GUI. Following code shows<\/li>\n<\/ul>\n

                                                                                                 <\/p>\n

                                                                                                <html><\/p>\n

                                                                                                <body><\/p>\n

                                                                                                <form action=’exec.php’ method=’post’> <table><\/p>\n

                                                                                                <!– vid –> <tr><\/p>\n

                                                                                                <td>Vendor ID:<\/td><\/span><\/p>\n

                                                                                                <td> <input type=’text’ name=’vid’> <\/td><\/span><\/p>\n

                                                                                                <\/tr><\/span><\/p>\n

                                                                                                <!– vname –><\/span><\/p>\n

                                                                                                <tr><\/span><\/p>\n

                                                                                                <td>Vendor Name:<\/td><\/span><\/p>\n

                                                                                                <td> <input type=’text’ name=’vname’> <\/td><\/span><\/p>\n

                                                                                                <\/tr><\/span><\/p>\n

                                                                                                <!– vaddress\u00a0 –><\/span><\/p>\n

                                                                                                <tr><\/span><\/p>\n

                                                                                                <td>Vendor Address:<\/td><\/span><\/p>\n

                                                                                                <td> <input type=’text’ name=’vaddress’> <\/td><\/span><\/p>\n

                                                                                                <\/tr><\/span><\/p>\n

                                                                                                <!– vcity –><\/span><\/p>\n

                                                                                                <tr><\/span><\/p>\n

                                                                                                <td> Vendor City: <\/td><\/span><\/p>\n

                                                                                                <td> <input type=’text’ name=’vcity’> <\/td><\/span><\/p>\n

                                                                                                <\/tr><\/span><\/p>\n

                                                                                                <!– vmobile –><\/span><\/p>\n

                                                                                                <tr><\/span><\/p>\n

                                                                                                <td> Vendor Mobile: <\/td><\/span><\/p>\n

                                                                                                <td> <input type=’text’ name=’vmobile’> <\/td><\/span><\/p>\n

                                                                                                <\/tr><\/span><\/p>\n

                                                                                                <!– submit and reset –><\/span><\/p>\n

                                                                                                <tr><\/span><\/p>\n

                                                                                                <td> <input type=’submit’ name=’submit’ value=’Submit’> <\/td> <td> <input type=’reset’ name=’reset’ value=’Reset’> <\/td> <\/tr><\/span><\/p>\n

                                                                                                <\/table><\/span><\/p>\n

                                                                                                <\/form><\/span><\/p>\n

                                                                                                <\/body><\/span><\/p>\n

                                                                                                <\/html><\/span><\/p>\n<\/div>\n

                                                                                                \n

                                                                                                \n

                                                                                                The output of the HTML code is :<\/p>\n

                                                                                                 <\/p>\n

                                                                                                 <\/p>\n

                                                                                                \"\"<\/p>\n<\/div>\n

                                                                                                  \n
                                                                                                • The PHP code for file exec.php is given below.<\/span><\/li>\n<\/ul>\n
                                                                                                  \n

                                                                                                  \u00a0 \u00a0<?php<\/p>\n

                                                                                                  $dsn = ‘mysql:host=localhost;dbname=account’; $uname = ‘root’;<\/p>\n

                                                                                                  $pwd = ”;<\/p>\n

                                                                                                  $db = new PDO($dsn,$uname,$pwd);<\/p>\n

                                                                                                  $vid = $_POST[‘vid’];<\/p>\n

                                                                                                  $vname = $_POST[‘vname’];<\/p>\n

                                                                                                  $vaddress = $_POST[‘vaddress’]; $vcity = $_POST[‘vcity’]; $vmobile = $_POST[‘vmobile’];<\/p>\n

                                                                                                  $query = “insert into vendor<\/p>\n

                                                                                                  values($vid,’$vname’,’$vaddress’,’$vcity’,’$vmobile’) “;<\/p>\n

                                                                                                  echo “<br> Query : $query <br>”; $result = $db->exec($query);<\/p>\n

                                                                                                  echo “<br> Result is : $result”; if($result > 0)<\/p>\n

                                                                                                  {<\/p>\n

                                                                                                  echo “<br> Record Insrted Successfully”;<\/p>\n

                                                                                                  }<\/p>\n

                                                                                                  ?><\/p>\n

                                                                                                    \n
                                                                                                  • The output is<\/li>\n<\/ul>\n<\/div>\n
                                                                                                    \n

                                                                                                    \u00a0 \u00a0Query : insert into vendor values(22,’Kalpesh’,’Bopal’,’Ahmedabad’,’9753124680′)<\/p>\n

                                                                                                    Result is : 1<\/p>\n

                                                                                                    Record Insrted Successfully<\/p>\n

                                                                                                      \n
                                                                                                    • For live application development, prepare method is used. Prepare method is a method of PDO object. Prepare statement improves database performance and security.<\/li>\n
                                                                                                    • When a statement is executed multiple times, the statements allow the database server to reuse of the work which is done when prepare a statement.<\/li>\n
                                                                                                    • Prepare statement also improves security because it can prevent most types of the SQL injection attacks.<\/li>\n<\/ul>\n

                                                                                                      \"\"<\/p>\n

                                                                                                      Let\u2019s take one example using prepare statement. The HTML code is shown below<\/p>\n

                                                                                                       <\/p>\n

                                                                                                      <html><\/p>\n

                                                                                                      <body><\/p>\n

                                                                                                      <form action=’prepareselect.php’ method=’post’> Enter city name :<\/p>\n

                                                                                                      <input type=”text” name=”vcity”> <\/input><\/p>\n

                                                                                                      <br><br><\/p>\n

                                                                                                      <input type=’submit’ name=’submit’ value=’Submit’> <input type=’reset’ name=’reset’ value=’Reset’> <\/form><\/p>\n

                                                                                                      <\/body><\/p>\n

                                                                                                      <\/html><\/p>\n

                                                                                                      \"\"<\/p>\n<\/div>\n

                                                                                                      \n

                                                                                                      \u00a0 \u00a0The PHP code for the file prepareselect.php is shown below.<\/p>\n

                                                                                                       <\/p>\n

                                                                                                      <?php<\/p>\n

                                                                                                      $dsn=’mysql:host=localhost;dbname=account’;<\/p>\n

                                                                                                      $uname = ‘root’;<\/p>\n

                                                                                                      $pwd = ”;<\/p>\n

                                                                                                      $db = new PDO($dsn,$uname,$pwd); $vcity = $_POST[‘vcity’];<\/p>\n

                                                                                                      $query = “select * from vendor<\/p>\n

                                                                                                      where vcity = :vcity”;<\/p>\n

                                                                                                      $result = $db->prepare($query);<\/p>\n

                                                                                                      $result->bindValue(‘:vcity’,$vcity); $result->execute();<\/p>\n

                                                                                                      $rows = $result->fetchAll();<\/p>\n

                                                                                                      foreach($rows as $r)<\/p>\n

                                                                                                      {<\/p>\n

                                                                                                      echo ‘<br> Vendor ID :’. $r[‘vid’];<\/p>\n

                                                                                                      echo ‘<br> Vendor Name :’. $r[‘vname’];<\/p>\n

                                                                                                      echo ‘<br> Vendor Address :’. $r[‘vaddress’];<\/p>\n

                                                                                                      echo ‘<br> Vendor City :’. $r[‘vcity’];<\/p>\n

                                                                                                      echo ‘<br> Vendor Mobile :’. $r[‘vmobile’];<\/p>\n

                                                                                                      echo “<br>”;<\/p>\n

                                                                                                      }<\/p>\n

                                                                                                      $result->closeCursor();<\/p>\n

                                                                                                      ?><\/p>\n

                                                                                                       <\/p>\n

                                                                                                      As the user clicks on submit button, he\/she will get the output as shown below.<\/p>\n<\/div>\n

                                                                                                      \n

                                                                                                      \u00a0 \u00a0 Vendor ID :1<\/p>\n

                                                                                                      Vendor Name :Anand<\/p>\n

                                                                                                      Vendor Address :Ravi Park<\/p>\n

                                                                                                      Vendor City :Rajkot<\/p>\n

                                                                                                      Vendor Mobile :9876034521<\/p>\n

                                                                                                       <\/p>\n

                                                                                                      Vendor ID :2<\/p>\n

                                                                                                      Vendor Name :Dharmesh<\/p>\n

                                                                                                      Vendor Address :19, Park Avenue<\/p>\n

                                                                                                      Vendor City :Rajkot<\/p>\n

                                                                                                      Vendor Mobile :1234567890<\/p>\n

                                                                                                       <\/p>\n

                                                                                                      Vendor ID :7<\/p>\n

                                                                                                      Vendor Name :Manish<\/p>\n

                                                                                                      Vendor Address :University Road<\/p>\n

                                                                                                      Vendor City :Rajkot<\/p>\n

                                                                                                      Vendor Mobile :9087654321<\/p>\n

                                                                                                       <\/p>\n

                                                                                                      In the similar way, prepare and bindValue method can be used to insert,update or delete statement. Following code shows to insert a record using prpare and bindValue.<\/p>\n

                                                                                                       <\/p>\n

                                                                                                      <html><\/p>\n

                                                                                                      <body><\/p>\n

                                                                                                      <form action=’prepareinsert.php’ method=’post’><\/p>\n

                                                                                                      <table><\/p>\n

                                                                                                      <!– vid –> <tr><\/p>\n

                                                                                                      <td>Vendor ID:<\/td><\/p>\n

                                                                                                      <td> <input type=’text’ name=’vid’> <\/td> <\/tr><\/p>\n

                                                                                                      <!– vname –> <tr><\/p>\n

                                                                                                      <td>Vendor Name:<\/td><\/p>\n

                                                                                                      <td> <input type=’text’ name=’vname’> <\/td> <\/tr><\/p>\n

                                                                                                      <!– vaddress\u00a0 –><\/span><\/p>\n

                                                                                                      <tr><\/span><\/p>\n

                                                                                                      <td>Vendor Address:<\/td><\/span><\/p>\n

                                                                                                      <td> <input type=’text’ name=’vaddress’> <\/td><\/span><\/p>\n

                                                                                                      <\/tr><\/span><\/p>\n

                                                                                                      <!– vcity –><\/span><\/p>\n

                                                                                                      <tr><\/span><\/p>\n

                                                                                                      <td> Vendor City: <\/td><\/span><\/p>\n

                                                                                                      <td> <input type=’text’ name=’vcity’> <\/td><\/span><\/p>\n

                                                                                                      <\/tr><\/span><\/p>\n

                                                                                                      <!– vmobile –><\/span><\/p>\n

                                                                                                      <tr><\/span><\/p>\n

                                                                                                      <td> Vendor Mobile: <\/td><\/span><\/p>\n

                                                                                                      <td> <input type=’text’ name=’vmobile’> <\/td><\/span><\/p>\n

                                                                                                      <\/tr><\/span><\/p>\n

                                                                                                      <!– submit and reset –><\/span><\/p>\n

                                                                                                      <tr><\/span><\/p>\n

                                                                                                      <td> <input type=’submit’ name=’submit’ value=’Submit’> <\/td> <td> <input type=’reset’ name=’reset’ value=’Reset’> <\/td> <\/tr><\/span><\/p>\n

                                                                                                      <\/table><\/span><\/p>\n

                                                                                                      <\/form><\/span><\/p>\n

                                                                                                      <\/body><\/span><\/p>\n

                                                                                                      <\/html><\/span><\/p>\n<\/div>\n

                                                                                                      <\/div>\n
                                                                                                      \n

                                                                                                      \"\"<\/p>\n

                                                                                                       <\/p>\n

                                                                                                       <\/p>\n

                                                                                                      The code for prepareinsert.php is shown below.<\/p>\n

                                                                                                       <\/p>\n

                                                                                                      \u00a0<?php<\/span><\/p>\n

                                                                                                      $dsn=’mysql:host=localhost;dbname=account’;<\/span><\/p>\n

                                                                                                      $uname = ‘root’;<\/span><\/p>\n

                                                                                                      $pwd = ”;<\/span><\/p>\n

                                                                                                      $db = new PDO($dsn,$uname,$pwd);<\/span><\/p>\n

                                                                                                      $vid = $_POST[‘vid’];<\/span><\/p>\n

                                                                                                      $vname = $_POST[‘vname’];<\/span><\/p>\n

                                                                                                      $vaddress = $_POST[‘vaddress’];<\/span><\/p>\n

                                                                                                      $vcity = $_POST[‘vcity’];<\/span><\/p>\n

                                                                                                      $vmobile = $_POST[‘vmobile’];<\/span><\/p>\n

                                                                                                      $query = “insert into vendor values (:vid, :vname, :vaddress, :vcity, :vmobile)”;<\/span><\/p>\n

                                                                                                      $result = $db->prepare($query);<\/span><\/p>\n

                                                                                                      $result->bindValue(‘:vid’,$vid);<\/span><\/p>\n

                                                                                                      $result->bindValue(‘:vname’,$vname);<\/span><\/p>\n

                                                                                                      $result->bindValue(‘:vaddress’,$vaddress);<\/span><\/p>\n

                                                                                                      $result->bindValue(‘:vcity’,$vcity);<\/span><\/p>\n

                                                                                                      $result->bindValue(‘:vmobile’,$vmobile);<\/span><\/p>\n

                                                                                                      $success = $result->execute();<\/span><\/p>\n

                                                                                                      if($success > 0)<\/span><\/p>\n

                                                                                                      {<\/span><\/p>\n

                                                                                                      echo “<br> Record Inserted Successfully “;<\/span><\/p>\n

                                                                                                      }<\/span><\/p>\n

                                                                                                      $result->closeCursor();<\/span><\/p>\n

                                                                                                      ?><\/span><\/p>\n

                                                                                                       <\/p>\n

                                                                                                      \u00a0Output for the program is<\/span><\/p>\n

                                                                                                       <\/p>\n

                                                                                                      Record Inserted Successfully<\/strong><\/p>\n<\/div>\n

                                                                                                      \n
                                                                                                        \n
                                                                                                      • An exception is an object which contains information about error which has occurred. Some PHP statements throw exception when an error is occurred. If an exception is not handled, then the applications terminate with error. To handle exception, try catch statement is used.<\/li>\n
                                                                                                      • To handle the errors thrown by the PDO library, the PDO Exception class is used.<\/li>\n
                                                                                                      • The below code shows an example of PDO Exception.<\/li>\n<\/ul>\n

                                                                                                         <\/p>\n<\/div>\n

                                                                                                        \n

                                                                                                        \u00a0 \u00a0 <?php<\/p>\n

                                                                                                        \/\/ PDO Database Object created<\/p>\n

                                                                                                        $dsn =’mysql:host=localhost;dbname=account’;<\/p>\n

                                                                                                        $uname = ‘root’;<\/p>\n

                                                                                                        $pwd = ‘pwd’;<\/p>\n

                                                                                                        try<\/p>\n

                                                                                                        {<\/p>\n

                                                                                                        $db = new PDO($dsn,$uname,$pwd);<\/p>\n

                                                                                                        echo “<br> Connection established successfully”;<\/p>\n

                                                                                                        \/\/<\/p>\n

                                                                                                        $query = “select * from vendor”;<\/p>\n

                                                                                                        echo ‘<br>’. $query . ‘<br>’;<\/p>\n

                                                                                                        $rows = $db->query($query);<\/p>\n

                                                                                                        $row = $rows->fetch();<\/p>\n

                                                                                                        while ($row != null)<\/p>\n

                                                                                                        {<\/p>\n

                                                                                                        $vid = $row[‘vid’];<\/p>\n

                                                                                                        $vname = $row[‘vname’];<\/p>\n

                                                                                                        $vaddress = $row[‘vaddress’];<\/p>\n

                                                                                                        $vcity = $row[‘vcity’];<\/p>\n

                                                                                                        $vmobile = $row[‘vmobile’];<\/p>\n

                                                                                                        echo ‘<br>’;<\/p>\n

                                                                                                        echo “vid : $vid <br>”;<\/p>\n

                                                                                                        echo “vname : $vname <br>”;<\/p>\n

                                                                                                        echo “vaddress : $vaddress <br> “;<\/p>\n

                                                                                                        echo “vcity : $vcity <br> “;<\/p>\n

                                                                                                        echo “vmobile : $vmobile <br> “;<\/p>\n

                                                                                                        \/\/ fetch next record<\/p>\n

                                                                                                        $row = $rows->fetch();<\/p>\n

                                                                                                        }<\/p>\n

                                                                                                        }<\/p>\n

                                                                                                        catch(PDOException $e)<\/p>\n

                                                                                                        {<\/p>\n

                                                                                                        $errormessage = $e->getMessage();<\/p>\n

                                                                                                        echo “<br> An error occured when connecting with database. The error is :<\/span><\/p>\n

                                                                                                        $errormessage”;<\/span><\/p>\n

                                                                                                        }<\/span><\/p>\n

                                                                                                        ?><\/span><\/p>\n<\/div>\n

                                                                                                          \n
                                                                                                        • The output of the program is<\/li>\n<\/ul>\n

                                                                                                           <\/p>\n

                                                                                                          An error occured when connecting with database. The error is : SQLSTATE[28000]<\/p>\n

                                                                                                          [1045] Access denied for user ‘root’@’localhost’ (using password: YES)<\/p>\n

                                                                                                           <\/p>\n

                                                                                                          References:<\/strong><\/p>\n

                                                                                                           <\/p>\n

                                                                                                          1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,<\/p>\n

                                                                                                          2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress<\/p>\n

                                                                                                          3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,<\/p>\n

                                                                                                          4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O’Reilly Media<\/p>\n

                                                                                                          5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox<\/p>\n

                                                                                                          6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible<\/p>\n

                                                                                                          7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach<\/p>\n

                                                                                                          8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML\/Javascript\/DHTML\/PHP , BPB Publications<\/p>\n

                                                                                                          9. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams<\/p>\n

                                                                                                          10. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education<\/p>\n

                                                                                                          11. http:\/\/www.php.net\/<\/p>\n

                                                                                                          12. http:\/\/www.w3schools.com\/<\/p>\n

                                                                                                          13. http:\/\/www.tutorialspoint.com\/<\/p>\n","protected":false},"author":3,"menu_order":25,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["dr-hiren-joshi"],"pb_section_license":""},"chapter-type":[],"contributor":[59],"license":[],"class_list":["post-237","chapter","type-chapter","status-publish","hentry","contributor-dr-hiren-joshi"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/237","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/users\/3"}],"version-history":[{"count":8,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/237\/revisions"}],"predecessor-version":[{"id":425,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/237\/revisions\/425"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/237\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/media?parent=237"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapter-type?post=237"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/contributor?post=237"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/license?post=237"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}