{"id":412,"date":"2021-02-13T22:31:00","date_gmt":"2021-02-13T22:31:00","guid":{"rendered":"https:\/\/rishikantsri.in\/blog\/?p=412"},"modified":"2023-10-12T09:44:54","modified_gmt":"2023-10-12T09:44:54","slug":"basic-and-advance-feature-of-mysqli","status":"publish","type":"post","link":"https:\/\/rishikantsri.in\/blog\/basic-and-advance-feature-of-mysqli\/","title":{"rendered":"Basic and Advance feature of MySqli"},"content":{"rendered":"\n

MySQLi (MySQL Improved)<\/strong> is a PHP extension for interacting with MySQL databases. It provides both procedural and object-oriented programming interfaces and offers a wide range of features for database operations. Below, I’ll cover some basic and advanced features of MySQLi along with examples.<\/p>\n\n\n\n

Basic Features:<\/h3>\n\n\n\n
    \n
  1. Connection<\/strong>: To establish a connection to a MySQL database, use mysqli_connect()<\/code> (procedural) or new mysqli()<\/code> (object-oriented).<\/li>\n\n\n\n
  2. <\/li>\n<\/ol>\n\n\n\n

    Procedural Example:<\/strong><\/p>\n\n\n\n

    PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
       $conn <\/span>=<\/span> mysqli_connect<\/span>(<\/span>"localhost"<\/span>,<\/span> <\/span>"username"<\/span>,<\/span> <\/span>"password"<\/span>,<\/span> <\/span>"database_name"<\/span>)<\/span>;<\/span><\/span>\n   <\/span>if<\/span> (<\/span>!<\/span>$conn) {<\/span><\/span>\n       <\/span>die<\/span>(<\/span>"Connection failed: "<\/span> <\/span>.<\/span> mysqli_connect_error<\/span>()<\/span>);<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Object-Oriented Example:<\/strong><\/p>\n\n\n\n
    PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
       $conn <\/span>=<\/span> <\/span>new<\/span> <\/span>mysqli<\/span>(<\/span>"localhost"<\/span>,<\/span> <\/span>"username"<\/span>,<\/span> <\/span>"password"<\/span>,<\/span> <\/span>"database_name"<\/span>);<\/span><\/span>\n   <\/span>if<\/span> ($conn<\/span>-><\/span>connect_error) {<\/span><\/span>\n       <\/span>die<\/span>(<\/span>"Connection failed: "<\/span> <\/span>.<\/span> $conn<\/span>-><\/span>connect_error);<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      \n
    1. Query Execution<\/strong>: You can execute SQL queries using mysqli_query()<\/code> for procedural style and $conn->query()<\/code> for object-oriented style. <\/li>\n<\/ol>\n\n\n\n
      Procedural Example:<\/strong><\/p>\n\n\n\n
      PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
         $sql <\/span>=<\/span> <\/span>"<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> users"<\/span>;<\/span><\/span>\n   $result <\/span>=<\/span> mysqli_query<\/span>(<\/span>$conn<\/span>,<\/span> $sql<\/span>)<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      Object-Oriented Example:<\/strong><\/p>\n\n\n\n
      PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
         $sql <\/span>=<\/span> <\/span>"<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> users"<\/span>;<\/span><\/span>\n   $result <\/span>=<\/span> $conn<\/span>-><\/span>query<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        \n
      1. Fetching Data<\/strong>: You can fetch data using functions like mysqli_fetch_assoc()<\/code>, mysqli_fetch_row()<\/code>, and others for the procedural style, or $result->fetch_assoc()<\/code>, $result->fetch_row()<\/code>, etc., for the object-oriented style. <\/li>\n<\/ol>\n\n\n\n
        Procedural Example:<\/strong><\/p>\n\n\n\n
        PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
           <\/span>while<\/span> ($row <\/span>=<\/span> mysqli_fetch_assoc<\/span>(<\/span>$result<\/span>)<\/span>) {<\/span><\/span>\n       echo <\/span>"Username: "<\/span> <\/span>.<\/span> $row[<\/span>'username'<\/span>] <\/span>.<\/span> <\/span>"<br>"<\/span>;<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        Object-Oriented Example:<\/strong><\/p>\n\n\n\n
        PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
           <\/span>while<\/span> ($row <\/span>=<\/span> $result<\/span>-><\/span>fetch_assoc<\/span>()<\/span>) {<\/span><\/span>\n       echo <\/span>"Username: "<\/span> <\/span>.<\/span> $row[<\/span>'username'<\/span>] <\/span>.<\/span> <\/span>"<br>"<\/span>;<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
          \n
        1. Prepared Statements<\/strong>: MySQLi supports prepared statements for safe and efficient queries. Here’s an example of a prepared statement for selecting data:<\/li>\n<\/ol>\n\n\n\n
          PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
             $sql <\/span>=<\/span> <\/span>"<\/span>SELECT<\/span> username, email <\/span>FROM<\/span> users <\/span>WHERE<\/span> id <\/span>=<\/span> ?"<\/span>;<\/span><\/span>\n   $stmt <\/span>=<\/span> $conn<\/span>-><\/span>prepare<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n   $id <\/span>=<\/span> <\/span>1<\/span>;<\/span><\/span>\n   $stmt<\/span>-><\/span>bind_param<\/span>(<\/span>"i"<\/span>,<\/span> $id<\/span>)<\/span>; <\/span>\/\/ "i" represents an integer<\/span><\/span>\n   $stmt<\/span>-><\/span>execute<\/span>()<\/span>;<\/span><\/span>\n   $stmt<\/span>-><\/span>bind_result<\/span>(<\/span>$username<\/span>,<\/span> $email<\/span>)<\/span>;<\/span><\/span>\n   $stmt<\/span>-><\/span>fetch<\/span>()<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
          Advanced Features:<\/h3>\n\n\n\n
            \n
          1. Error Handling<\/strong>: MySQLi provides detailed error handling through functions like mysqli_error()<\/code> and mysqli_errno()<\/code> for procedural style and via $conn->error<\/code> and $conn->errno<\/code> for object-oriented style.<\/li>\n<\/ol>\n\n\n\n
            PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
               <\/span>if<\/span> ($result <\/span>===<\/span> <\/span>false<\/span>) {<\/span><\/span>\n       echo <\/span>"Error: "<\/span> <\/span>.<\/span> mysqli_error<\/span>(<\/span>$conn<\/span>)<\/span>;<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
              \n
            1. Transactions<\/strong>: MySQLi supports transactions. You can begin a transaction with mysqli_begin_transaction()<\/code> (procedural) or $conn->begin_transaction()<\/code> (object-oriented) and commit or rollback as needed.<\/li>\n<\/ol>\n\n\n\n
              PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
                 mysqli_begin_transaction<\/span>(<\/span>$conn<\/span>)<\/span>;<\/span><\/span>\n   <\/span>\/\/ Perform database operations<\/span><\/span>\n   <\/span>if<\/span> (<\/span>\/* Operation successful *\/<\/span>) {<\/span><\/span>\n       mysqli_commit<\/span>(<\/span>$conn<\/span>)<\/span>;<\/span><\/span>\n   } <\/span>else<\/span> {<\/span><\/span>\n       mysqli_rollback<\/span>(<\/span>$conn<\/span>)<\/span>;<\/span><\/span>\n   }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                \n
              1. Stored Procedures<\/strong>: You can call stored procedures using MySQLi, both for prepared statements and regular queries.<\/li>\n<\/ol>\n\n\n\n
                PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
                   $sql <\/span>=<\/span> <\/span>"CALL procedure_name(?, ?)"<\/span>;<\/span><\/span>\n   $stmt <\/span>=<\/span> $conn<\/span>-><\/span>prepare<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span>\n   $param1 <\/span>=<\/span> <\/span>1<\/span>;<\/span><\/span>\n   $param2 <\/span>=<\/span> <\/span>"example"<\/span>;<\/span><\/span>\n   $stmt<\/span>-><\/span>bind_param<\/span>(<\/span>"is"<\/span>,<\/span> $param1<\/span>,<\/span> $param2<\/span>)<\/span>;<\/span><\/span>\n   $stmt<\/span>-><\/span>execute<\/span>()<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                  \n
                1. Multiple Statements<\/strong>: MySQLi allows executing multiple SQL statements in a single call. Be cautious and validate input to prevent SQL injection in such cases.<\/li>\n<\/ol>\n\n\n\n
                  PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
                     $sql <\/span>=<\/span> <\/span>"<\/span>INSERT INTO<\/span> table1 <\/span>VALUES<\/span> ('value1'); <\/span>INSERT INTO<\/span> table2 <\/span>VALUES<\/span> ('value2')"<\/span>;<\/span><\/span>\n   $conn<\/span>-><\/span>multi_query<\/span>(<\/span>$sql<\/span>)<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                    \n
                  1. Connection Handling<\/strong>: MySQLi supports managing multiple database connections and connection pooling.<\/li>\n<\/ol>\n\n\n\n
                    PHP<\/span><\/span><\/path><\/path><\/svg><\/span>
                       $conn1 <\/span>=<\/span> <\/span>new<\/span> <\/span>mysqli<\/span>(<\/span>"host1"<\/span>,<\/span> <\/span>"user"<\/span>,<\/span> <\/span>"pass"<\/span>,<\/span> <\/span>"db1"<\/span>);<\/span><\/span>\n   $conn2 <\/span>=<\/span> <\/span>new<\/span> <\/span>mysqli<\/span>(<\/span>"host2"<\/span>,<\/span> <\/span>"user"<\/span>,<\/span> <\/span>"pass"<\/span>,<\/span> <\/span>"db2"<\/span>);<\/span><\/span>\n   <\/span>\/\/ Perform operations with $conn1 and $conn2<\/span><\/span>\n   $conn1<\/span>-><\/span>close<\/span>()<\/span>;<\/span><\/span>\n   $conn2<\/span>-><\/span>close<\/span>()<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                    This is just an overview of some basic and advanced features of MySQLi. In real-world applications, you’ll often combine these features to interact with your database efficiently and securely. MySQLi is a powerful extension for PHP that is widely used for working with MySQL databases.<\/p>\n","protected":false},"excerpt":{"rendered":"
                    MySQLi (MySQL Improved) is a PHP extension for interacting with MySQL databases. It provides both procedural and object-oriented programming interfaces and offers a wide range of features for database operations. Below, I’ll cover some basic and advanced features of MySQLi along with examples. Basic Features: Procedural Example: Object-Oriented Example: Procedural Example: Object-Oriented Example: Procedural Example: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[50,10],"tags":[51,4],"_links":{"self":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts\/412"}],"collection":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/comments?post=412"}],"version-history":[{"count":2,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts\/412\/revisions"}],"predecessor-version":[{"id":415,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/posts\/412\/revisions\/415"}],"wp:attachment":[{"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/media?parent=412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/categories?post=412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rishikantsri.in\/blog\/wp-json\/wp\/v2\/tags?post=412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}