PHP and MySQLi Extension

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • PHP and MySQLi Extension

    PHP and MySQLi Extension

    In the open source community the most popular data base driven web application is comprised of the webserver Apache, the backend database MySQL and the scripting language PHP, which along with Linux, the operating system, have come to be known by the acronym LAMP. When a request is made to Apache for a particular webpage, it is the PHP engine that interprets the script, connects to the MySQL server, performs database queries, construct the HTML output and returns them to Apache.


    PHP database driven web application basically consists of the following steps:

    1. Open a connection to the server and the database.
    2. Perform a query, which may be in response to a user prompted input.
    3. Process the query results and present them to the user.
    4. Close the connection.


    To make use of new functionality in MySQL 5 (actually, in MySQL 4.1.2 and above) and to provide greater security and speed, PHP provides a new and improved methods (procedural as well as object oriented programming) for connecting to MySQL. This is known as the mysqli extension. To make this extension available, however, you must compile PHP by specifying the complete path to the mysql_config file.


    There are basically three pre-defined MySQL classes:

    1. mysqli, representing the PHP connection to MySQL
    This contains methods for connecting to the server and sending queries

    2. mysqli_result, representing the result set from a MySQL database query
    This contains methods for working with the result set, such as fetching rows and columns

    3. mysqli_stmt, representing a prepared statement
    This contains methods for constructing and executing a prepared statement


    Though class methods are, of course, for object oriented programming, similarly named functions are also available for use in procedural programming. Just note for now that in object oriented programming, you don't need to specify a connection handle or a result handle. The connection and result set information are stored in $mysqli and mysqli_$result objects. Shortly, we will present examples of both object oriented and procedural programing styles.


    Connecting to MySQL

    For object oriented style, a mysql connection resource is created with new and closed with the method close(). For example, the following code snippet will attempt to connect user joel to the Jolux database or terminate the script by calling the die() function if there is an error connecting, i.e., mysqli_connect_errno() greater than zero. (Note that there no object oriented style equivalent to mysqli_connect_errno()).

    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
       die(
    "<font color='red'>Eeeek! Cannot connect user $user, " .
               
    " password $password </font>");
    }

    echo 
    "Connected successfully";

    $connection->close(); 
    ?>
    For procedural programmming, the function mysqli_connect() is used to open a connection resource to MySQL and the function mysqli_close() is used to close a connection. The functions mysqli_connect() and mysqli_close() have the following prototypes:
    mysqli_connect (hostname, username, password, database)

    mysqli_close (resource)

    mysqli_connect() returns a connection resource on success or false otherwise. mysqli_close() closes a connection and returns true on success.

    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = @mysqli_connect($server$user$password$database )
        or die(
    "<font color='red'>Eeeek! Cannot connect user $user, " .
               
    " password $password </font>");

    echo 
    "Connected successfully";

    mysqli_close($connection); 
    ?>
    The @ in front of the function name is used to suppress any error messages on failure. For example, without the @, if we entered an incorrect password, we'd get the following error message when we point our browser to it (tester.php is what we named our file):
    Click image for larger version

Name:	connect_err.png
Views:	1
Size:	51.7 KB
ID:	113015


    Performing Queries

    The object method query(), or its procedural style equivalent function mysqli_query(), is used to send a query statement to the currently active database. It has the following prototype:
    query( string)

    mysqli_query (connection, string)

    mysqli_query() returns false on error or it returns a resource handle. The query string must NOT end in a semicolon. For example, the following script connects to mysql and creates a table.

    Object Oriented Style.
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<p><font color='red'>Eeeeeeeek. Errrrrror!</font>"); 
    }

    $sql "CREATE TABLE my_table (name varchar(32), price decimal(7,2))";
    if (
    $connection->query($sql)) {
       echo (
    "Table successfully created.");
    } else {
       echo (
    "Eeeeek! Cannot create table");
    }

    $connection->close(); 
    ?>
    Procedural Programming Style.
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = @mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font>");

    $sql "CREATE TABLE my_table (name varchar(32), price decimal(7,2))";
    $result mysqli_query ($connection$sql
           or die(
    "Eeeeek! Cannot create table");
    echo 
    "Table successfully created";

    mysqli_close($connection); 
    ?>
    For queries that do not return a result set (for example, INSERT, UPDATE, DELETE), use the object property affected_rows or the procedural function mysqli_affected_rows() which returns the number of rows that have been affected. The procedural function has the following prototype
    mysqli_affected_rows(connection)

    Here's a script that inserts a row into the table.

    Object Oriented Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<p><font color='red'>Eeeeeeeek. Errrrrror!!</font>"); 
    }

    $sql "INSERT INTO my_table (name, price) values ('Syoktong', 155.25)";
    if (
    $connection->query($sql)) {
       echo 
    "Rows inserted: "$connection->affected_rows;
    } else {
       echo 
    "Eeeeek! Problem executing query";   
    }
    $connection->close(); 
    ?>
    Procedural Programming Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = @mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font>");

    $sql "INSERT INTO my_table (name, price) values ('Syoktong', 155.25)";
    mysqli_query ($connection$sql) or die("Problem executing query");
    echo 
    "Rows inserted: "mysqli_affected_rows($connection);

    mysqli_close($connection); 
    ?>
    But for queries that return a result set (for example, SELECT statements), mysqli_query() or its equivalent object method query() will return a resource handle on success which you could then pass on to another function that would manipulate the result set. On failure, i.e., when the query cannot be executed correctly, mysqli_query() will return boolean FALSE. But success or non-false does not necessarily indicate that there are rows in the result set. To find out how many rows are in the result set, pass the handle to the function mysqli_num_rows() (or its equivalent, the object property num_rows). To find the number of fields in the result set, pass the handle to the function mysqli_num_fields() (or its equivalent, the object property field_count).

    Example: Object Oriented Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<p><font color='red'>Eeeeeeeek. Errrrrror!</font>"); 
    }

    $sql "SELECT name, phone FROM customers";
    $result $connection->query($sql
        or die(
    "Eeeeek! Cannot execute query");

    $rows $result->num_rows;
    $fields $result->field_count;
    echo 
    "Number of rows in result set: $rows";
    echo 
    "<br>Number of fields in result set: $fields";

    $connection->close(); 
    ?>
    Example: Procedural Function Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = @mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font>");

    $sql "SELECT name, phone FROM customers";
    $result mysqli_query ($connection$sql
           or die(
    "Eeeeek! Cannot execute query");

    $rows mysqli_num_rows($result);
    $fields mysqli_num_fields($result);
    echo 
    "Number of rows in result set: $rows";
    echo 
    "<br>Number of fields in result set: $fields";

    mysqli_close($connection); 
    ?>



    There are a number of functions that can be used to manipulate the result set in addition to the mysqli_num_rows(), mysqli_num_fields() and mysqli_affected_rows() we saw in the previous section.
    Method fetch_array() or function mysqli_fetch_array(result, option) — Perhaps the most important function for manipulating the result set, mysqli_fetch_array() returns an associative array corresponding to a row that is fetched, or it returns boolean false if there are no more rows or when there's an error. The optional second argument is a constant which can be any of the following: MYSQLI_NUM (meaning, the array be indexed numerically starting with zero), MYSQLI_ASSOC (as an associative array using the field names as keys), or MYSQLI_BOTH (the default, meaning both).

    Example: Object Oriented Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<p><font color='red'>Eeeeeeeek. Errrrrror!</font>"); 
    }

    $result $connection->query("SELECT name, phone FROM customers"
       or die(
    "Eeeek! Problem executing query");
    print 
    "<table border=1>\n";
    print 
    "<tr><td><b>NAME</b></td><td><b>PHONE</b></td></tr>";
    while (
    $row $result->fetch_array()) {
       echo 
    "<td>"$row['name'], "</td><td>"$row['phone'], "</td></tr>\n"
    }
    echo 
    "</table>\n";

    $connection->close(); 
    ?>
    Example: Procedural Programming Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = @mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font>");

    $result mysqli_query ($connection"SELECT name, phone FROM customers") or die();
    print 
    "<table border=1>\n";
    print 
    "<tr><td><b>NAME</b></td><td><b>PHONE</b></td></tr>";
    while (
    $row mysqli_fetch_array($result)) {
       echo 
    "<td>"$row['name'], "</td><td>"$row['phone'], "</td></tr>\n"
    }
    echo 
    "</table>\n";

    mysqli_close($connection); 
    ?>
    If you saved this script to a file (say, tester.php) and point your browser to it, you should see something similar to the following.
    Click image for larger version

Name:	select.png
Views:	1
Size:	41.4 KB
ID:	113016

    The method fetch_assoc() or the procedural function mysqli_fetch_assoc(result) and the method fetch_row() or the function mysqli_fetch_row(result) — These are similar to mysqli_fetch_array(result, MYSQLI_ASSOC) and mysqli_fetch_array(result, MYSQLI_NUM), respectively. For example, here's a code snippet that uses mysqli_fetch_assoc() to print the customer name and phone number.
    PHP Code:
    while ($row $result->fetch_assoc()) {
       echo 
    "<td>"$row['name'], "</td><td>"$row['phone'], "</td></tr>\n"

    Or, in procedural programming style,
    PHP Code:
    while ($row mysqli_fetch_assoc($result)) {
       echo 
    "<td>"$row['name'], "</td><td>"$row['phone'], "</td></tr>\n"

    And here's one that uses mysqli_fetch_row() to also print the customer name and phone number.
    PHP Code:
    while ($row $result->fetch_row()) {
       echo 
    "<td>"$row[0], "</td><td>"$row[1], "</td></tr>\n"

    Or, in procedural programming style,
    PHP Code:
    while ($row mysqli_fetch_row($result)) {
       echo 
    "<td>"$row[0], "</td><td>"$row[1], "</td></tr>\n"


    The method fetch_object() or the procedural function mysqli_fetch_object(result) — Returns an object corresponding to the fetched row, or boolean false if there are no more rows or there's an error. The following uses mysqli_fetch_object() to also print the customer name and phone number.
    PHP Code:
    <?php;
    while (
    $row $result->fetch_object()) {
       echo 
    "<td>"$row->name"</td><td>"$row->phone"</td></tr>\n"
    }
    ?>
    Or, in procedural programming style,
    PHP Code:
    <?php;
    while (
    $row mysqli_fetch_object($result)) {
       echo 
    "<td>"$row->name"</td><td>"$row->phone"</td></tr>\n"
    }
    ?>

    mysqli_fetch_field_direct(field_number), mysqli_fetch_field() and mysqli_fetch_fields() — Returns various kinds of information about a column. fetch_field_direct() accepts the field number offset (zero-based) as argument and returns information for that specified column. fetch_field() and fetch_fields() are identical, the single difference is that the singular fetch_field() returns a single column whereas the plural fetch_fields() returns an array of columns.
    Example: Object Oriented Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<p><font color='red'>Eeeeeeeek. Errrrrror!</font>"); 
    }

    $sql "SELECT name, phone FROM customers";
    $result $connection->query ($connection$sql
            OR die(
    "Eeeek! Query failed.");
    $f $result->fetch_field_direct(1);
    echo 
    "Column Name: $f->name<br>";
    echo 
    "Table Name: $f->table<br>";
    echo 
    "Data Type: $f->type<br>";
    echo 
    "Max Length: $f->max_length<br>";

    $connection-> close
    ?>
    Example: Procedural Programming Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = @mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font>");

    $sql "SELECT name, phone FROM customers";
    $result mysqli_query ($connection$sql) or die("Eeeek! Query failed.");
    $f mysqli_fetch_field_direct($result,1);
    echo 
    "Column Name: $f->name<br>";
    echo 
    "Table Name: $f->table<br>";
    echo 
    "Data Type: $f->type<br>";
    echo 
    "Max Length: $f->max_length<br>";

    mysqli_close($connection); 
    ?>
    The following example uses fetch_fields() to print metadata about columns.

    Example: Object Oriented Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<p><font color='red'>Eeeeeeeek. Errrrrror!</font>"); 
    }


    $sql "SELECT name, income FROM customers";
    $result $connection->query ($sql
            OR die(
    "Eeeek! Query failed.");
    $fields $result->fetch_fields();
    foreach(
    $fields as $f) {
      echo 
    "-------------------------<br>";
      echo 
    "Column Name: $f->name<br>";
      echo 
    "Table Name: $f->table<br>";
      echo 
    "Data Type: $f->type<br>";
      echo 
    "Max Length: $f->max_length<br>";
    }

    $connection-> close
    ?>
    Example: Procedural Programming Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font");

    $sql "SELECT name, income FROM customers";
    $result mysqli_query ($connection$sql
            OR die(
    "Eeeek! Query failed.");
    $fields mysqli_fetch_fields($result);
    foreach(
    $fields as $f) {
      echo 
    "-------------------------<br>";
      echo 
    "Column Name: $f->name<br>";
      echo 
    "Table Name: $f->table<br>";
      echo 
    "Data Type: $f->type<br>";
      echo 
    "Max Length: $f->max_length<br>";
    }

    mysqli_close($connection);
    ?>

    Multiple Queries

    The mysqli_multi_query() function executes one or more queries which are concatenated by a semicolon. To retrieve the result set from multi_query() use mysqli_store_result() and mysqli_next_result() for all subsequent result set retrieval. To check if there are more rows in the result set, use mysqli_more_results(). Use mysqli_free_result() to free up resources used by mysqli_store_result(), especially if you're concerned with the memory occupied by a large result set.

    For queries that do not return a result set (for example, an INSERT), mysqli_store_result() will return FALSE, in which case, check that the function mysqli_errno() (or the object property errno) is zero, meaning that everything's okay. For example, the following script inserts multiple records into a table. An error number of zero indicates success.

    Object Oriented Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<p><font color='red'><font</font>"); 
    }

    $sql "INSERT INTO my_table values ('Bahal', 135.90);"
        
    "INSERT INTO my_table values ('Tanduay', 350.75);"
        
    "INSERT INTO my_table values ('Kulafu', 295.85)";

    $connection->multi_query($sql);
    if (
    $connection->errno 0) {
       die(
    "Eeeeek! Problem executing multi query");   
    }

    $connection->close();
    ?>
    Procedural Programming Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'><font");

    $sql "INSERT INTO my_table values ('Bahal', 135.90);"
        
    "INSERT INTO my_table values ('Tanduay', 350.75);"
        
    "INSERT INTO my_table values ('Kulafu', 295.85)";

    mysqli_multi_query($connection$sql);
    if (
    mysqli_errno($connection) > 0) {
       die(
    "Eeeeek! Problem executing multi query");   
    }

    mysqli_close($connection);
    ?>
    In the following script, two SELECT statements are passed to multi_query(). The store_result() function returns the result set pointed to by next_result() (which, of course, is the first set at the very beginning). The individual rows in the set are then fetched with fetch_row() and its contents printed out. If there are more result set in the store (i.e., the function mysqli_more_results() returning TRUE), then a dividing line is printed. The next_result() method then moves the pointer to the next result set in the store.

    Object Oriented Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font");
    }

    $sql "SELECT name, phone FROM customers;"
        
    "SELECT name, price FROM my_table";
      
    if (
    $connection->multi_query($sql)) {
      do {
           if (
    $result $connection->store_result()) {   // get a result set
              
    while ($row $result->fetch_row()) {
                echo 
    $row[0], " => "$row[1], "<BR>";
              }
              
    $result->free();                           // free result resource
            
    }
            if (
    $connection->more_results()) {
               echo 
    "---------------------------------<BR>";
            }
        } while (
    $connection->next_result());

    }
    $connection->close();
    ?>
    Procedural Programming Style
    PHP Code:
    <?php
    $server 
    'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection mysqli_connect($server$user$password$database )
       or die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font");

    $sql "SELECT name, phone FROM customers;"
        
    "SELECT name, price FROM my_table";
      
    if (
    mysqli_multi_query($connection$sql)) {
      do {
           if (
    $result mysqli_store_result($connection)) {   // get result set
              
    while ($row mysqli_fetch_row($result)) {
                echo 
    $row[0], " => "$row[1], "<BR>";
              }
              
    mysqli_free_result($result);               // free result resource
            
    }
            if (
    mysqli_more_results($connection)) {
               echo 
    "---------------------------------<BR>";
            }
        } while (
    mysqli_next_result($connection));

    }

    mysqli_close($connection);
    ?>
    Here's what the script produces.
    Code:
    Magno Alda => 233-1207
    Aning Bulhog => 564-1367
    Mina Tai => 345-1908
    ---------------------------------
    Syoktong => 155.25
    Bahal => 135.90
    Tanduay => 350.75
    Kulafu => 295.85

    Prepared Statements

    PHP supports two types of prepared statements: bound parameters and bound results.

    In a bound parameter prepared statement, a query template is constructed with parameter markers indicated by a question mark (?) using mysqli_stmt_prepare() which returns TRUE on success or FALSE on failure. This template is sent to the MySQL server once. On requests to execute the query, the parameter markers are filled with data and delivered to the MySQL server where a complete query is formed and then executed.

    You can use markers in the VALUES() list of an INSERT statement or in a comparison with a column in a WHERE clause to specify a comparison value, but you can't use them for identifiers such as column names or for operators such as equals (=). For example, the following are valid statements.
    PHP Code:
       $stmt->prepare("INSERT INTO my_table (name, price) VALUES (?,?)");
     
       
    $stmt->prepare("SELECT name FROM my_table WHERE price < ?"); 
    Before you execute a prepared statement, you must bound the parameter markers to the variables using mysqli_stmt_bind_param() which accepts a string as its first argument. Each variable in bind_param() must have a corresponding letter in the string that specifies the variable's data type. The letters are
    i to indicate an integer
    d to indicate a double or a float
    b to indicate a blob
    s to indicate all other data types

    For example, the following specifies $name as data type string and $price as data type double.
    PHP Code:
        $stmt->bind_param('sd'$name$price); 
    Essentially, there are five steps in a bound prepared statement:
    1. Initialize a statement handle using mysqli_stmt_init()
    2. Set up the prepared statement using mysqli_stmt_prepare()
    3. Associate or bind the parameter markers to the variables using mysqli_stmt_bind_param()
    4. Execute the prepared statement using mysqli_stmt_execute(). All parameter markers will be replaced with the appropiate data.
    5. Deallocate the statement handle using mysqli_stmt_close()

    Here's a function in object oriented style that uses a bound prepared statement. The mysqli_stmt_affected_rows() is used to determine how many rows have been affected (i.e., inserted).
    PHP Code:
    <?php
    function insert_my_table($connection$name$price) {
      
    $sql "INSERT INTO my_table (name, price) VALUES (?, ?)";
      
    $stmt =  $connection->stmt_init();    // initialize statement handle

      
    if ($stmt->prepare($sql))  {               // set up prepared statement
         
    $stmt->bind_param('sd'$name$price); // bind variables to markers
         
    if ($stmt->execute())                   // execute prepared statement
            
    echo "Inserted rows: ",  $stmt->affected_rows"<br>"
      }
      
    $stmt->close();                    // deallocate statement handle
    }
    ?>
    The procedural programming version is, of course, very similar. Just replace the object-> notation with mysqli_stmt_ and pass the statement handle as the first parameter to the function. Thus, the same function in procedural programming style.
    PHP Code:
    <?php
    function insert_my_table($connection$name$price) {
      
    $sql "INSERT INTO my_table (name, price) VALUES (?, ?)";
      
    $stmt mysqli_stmt_init($connection);    // initialize statement handle 

      
    if (mysqli_stmt_prepare($stmt$sql))  {               // set up prepared statement
         
    mysqli_stmt_bind_param($stmt'sd'$name$price); // bind variables to markers
         
    if (mysqli_stmt_execute($stmt))           // execute prepared statement
            
    echo "Inserted rows: ",  mysqli_stmt_affected_rows($stmt), "<br>"
      }
      
    mysqli_stmt_close($stmt);                    // deallocate statement handle
    }
    ?>
    In a bound result prepared statement the value of variables is associated with the value of the columns in a query result set from a prepared statement. That is, after the prepared statement query is executed, you can then
    1. associate a variable to a column in the result set using mysqli_stmt_bind_result(), and
    2. place the data for the bound column into the variable using mysqli_stmt_fetch()

    To illustrate, here's a function written in object oriented style that uses a prepared query statement (in this case, a bound parameter statement) and then binds the values of the columns of the query result set to the specified variables.

    PHP Code:
    <?php
    function price_above($connection$price) {
       
    $sql "SELECT name, price FROM my_table WHERE price > ?";
       
    $stmt =  $connection->stmt_init();    // initialize prepared statement 
       
    if ($stmt->prepare($sql))  {          // set up prepared statement
          
    $stmt->bind_param('d'$price);    // bind variable to marker
          
    $stmt->execute();                  // execute the statement

          
    $stmt->bind_result($name$price); // bind columns to variables
          
    while ($stmt->fetch())             // fetch result into variables
             
    echo "$name is $price<br>";     
       }
       
    $stmt->close();                       // deallocate statement handle
    }
    ?>
    For procedural programming version, just replace the object-> notation with mysqli_stmt_ and pass the statement handle as the first parameter to the function. For example, $stmt->bind_result($name, $price) becomes mysqli_stmt_bind_result($stmt, $name, $price) and $stmt->fetch() becomes mysqli_stmt_fetch($stmt).

    In our case, we've combined the two functions in one file, testfunc.php, and included it in a "require" statement in our main script, tester.php, that calls the functions. Here's what tester.php contains.
    PHP Code:
    <?php
    // File name: tester.php

    require "testfunc.php";

    $server 'localhost';
    $user 'joel';
    $password 'Eeeeeek';
    $database 'Jolux'

    $connection = new mysqli($server$user$password$database );
    if (
    mysqli_connect_errno()) {
        die(
    "<font color='red'>Eeeeeeeek. Errrrrror!</font");
    }

    insert_my_table($connection'Bahalina'345.85);
    price_above($connection300.00);

    $connection->close();
    ?>
    And here's what tester.php prints out when we point our browser to it.
    Code:
    Inserted rows: 1
    Tanduay is 350.75
    Bahalina is 345.85
    PHP and MySQLi
    Last edited by arnage; 10.01.13, 15:01.
Working...
X