PHP/MySQL Database/mysqli

Multiple Queries Using MySQLi

 
<?php
    $mysqli = new mysqli("localhost", "username", "password","mydatabase", 3306);
    $queries = "SELECT * FROM mytable; SELECT * FROM anothertable";
    if(mysqli_multi_query($mysqli, $queries)) {
        do {
            if($result = mysqli_store_result($mysqli)) {
                while($row = mysqli_fetch_row($result)) {
                    foreach($row as $key => $value) {
                        echo "$key => $value<BR/>\n";        
                    }
                }
                mysqli_free_result($result);        
            }
            if(mysqli_more_results($mysqli)) {                
                echo "<BR/>\nNext result set<BR/>\n";
            }
        } while(mysqli_next_result($mysqli));
    }
    mysqli_close($mysqli);
?>

mysqli_fetch_object.php

 
<?php
...
$query = "SELECT productid, name, price FROM product ORDER BY name";
$result = $mysqli->query($query);
while ($row = $result->fetch_object())
{
    $name = $row->name;
    $productid = $row->productid;
    $price = $row->price;
    echo "($productid) $name: $price <br />";
}
...
?>

mysqli_fetch_result-2.php

 
<?php
$query = "SELECT productid, name, price FROM product ORDER BY name";
$result = $mysqli->query($query);
while ($row = $result->fetch_array(MYSQLI_NUM))
{
   $productid = $row[0];
   $name = $row[1];
   $price = $row[2];
   echo "($productid) $name: $price <br />";
}
?>

mysqli_fetch_result.php

 
<?php
$query = "SELECT productid, name FROM product ORDER BY name";
$result = $mysqli->query($query);
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
    $name = $row["name"];
    $productid = $row["productid"];
    echo "Product:  $name ($productid) <br />";
}
?>

mysqli_fetch_row.php

 
<?php
   ...
   $query = "SELECT productid, name FROM product ORDER BY name";
   $result = $mysqli->mysqli_query($query);
   while (list($productid, $name) = $result->fetch_row())
   {
      echo "($productid) $name: $price <br />";
   }
   ...
?>

mysqli_free_result.php

 
<?php
   $mysqli = new mysqli();
   $mysqli->connect("127.0.0.1", "root","", "mydatabase");
   $query = "SELECT productid, name, price FROM product ORDER by name";
   $mysqli->query($query);
   $result->free();

?>

mysqli_multi_query.php

 
<?php
   $mysqli = new mysqli("127.0.0.1", "root","", "mydatabase");
   $userid = $_SESSION["userid"];
   $query = "SELECT lastname, firstname FROM user WHERE userID="$userid";";
   $query .= "SELECT product_count, CONCAT("$",total_cost) FROM sales WHERE userID="$userid"";
   if($mysqli->multi_query($query)) {
      do {
         $result = $mysqli->store_result();
         while ($row = $result->fetch_row())
            echo "$row[0], $row[1] <br />";
         if ($mysqli->more_results())
            echo "<br />";
      } while ($mysqli->next_result());
   }
?>

mysqli_stmt_prepare.php

 
<?php
   $mysqli = new mysqli("127.0.0.1", "root","", "mydatabase");
   $query = "SELECT productid, name, price, description FROM product ORDER BY productid";
   $stmt = $mysqli->prepare($query);
   $stmt->close();
   $mysqli->close();
?>

mysqli_store_result.php

 
<?php   
   $mysqli = new mysqli();
   $mysqli->connect("127.0.0.1", "root","", "mydatabase");
   $query = "SELECT productid, name, price FROM product ORDER by name";
   $mysqli->real_query($query);
   $result = $mysqli->store_result();
   while(list($productid, $name, $price) = $result->fetch_row())
      echo "($productid) $name: $price <br />";
   $result->free();
?>

Parameter Binding in MySQLi

 
<?php
    $mysqli = mysqli_connect("hostname", "user", "pass", "database");
    if(mysqli_connect_errno()) {
        die("Could not connect: ".mysqli_connect_error());
    }
    /* 
        CREATE TABLE books(name VARCHAR(255),
                           isbn VARCHAR(10),
                           price FLOAT)
    */
    $bookname = "PHP";
    $bookisbn = "11111111111";
    $bookprice = 49.95;
    $stmt = mysqli_prepare($mysqli, "INSERT INTO books VALUES(?, ?, ?)");
    mysqli_bind_param($stmt, "ssd", $bookname, $bookisbn, $bookprice);
    mysqli_execute($stmt);
    mysqli_stmt_close($stmt);
    mysqli_close($mysqli);
?>

retrieving_multiple_rows.php

 
<?php
  $db = new mysqli("localhost", "root","", "mydatabase");
  $result = $db->query("CALL get_employees()");
  while (list($employee_id, $name, $position) = $result->fetch_row()) {
     echo "$employeeid, $name, $position <br />";
  }
?>

Use mysqli to fetch data from mysql

 
<?php
   $mysqli = new mysqli("127.0.0.1", "root","", "mydatabase");
   $query = "SELECT productid, name, price, description FROM product ORDER BY productid";
   $stmt = $mysqli->prepare($query);
   $stmt->execute();
   $stmt->bind_result($productid, $name, $price, $description);
   while($stmt->fetch()) {
      echo "$productid, $name, $price, $description <br />";
   }
   $stmt->close();
   $mysqli->close();
?>

Using the mysqli Object-Oriented API

 
<?php 
$mysqli = new mysqli ("localhost","root","","mydatabase"); 
try { 
    if (mysqli_connect_errno()){ 
        throw new exception ("Error: " . mysqli_connect_errno() . " - ". mysqli_connect_error()); 
    
    } else { 
        if ($cdquery = $mysqli->query ("SELECT * FROM mytable ORDER BY id ASC")){ 
    
            while ($cddata = $cdquery->fetch_array ()){ 
                echo "ID: " . $cddata["id"] . "<br />"; 
                echo "Title: " . stripslashes ($cddata["title"]) . "<br />"; 
                echo "MyValue: " . stripslashes ($cddata["MyValue"]) . "<br />"; 
            } 
            $cdquery->close(); 
        } else { 
            echo $mysqli->errno . " - " . $mysqli->error; 
        } 
    
        $prep = $mysqli->prepare ("INSERT INTO mytable(id,title,myvalue) VALUES ("0",?,?)"); 
        $prep->bind_param ("ss",$title,$myvalue); 
        
        $title = "AA"; 
        $myvalue = 20; 
        
        $prep->execute(); 
        
        echo $prep->affected_rows . " row(s) affected."; 
        $prep->close(); 
        if ($result = $mysqli->prepare ("SELECT title, myvalue FROM mytable WHERE id > 2")){ 
            $result->execute (); 
            $result->bind_result ($title,$myvalue); 
            while ($result->fetch ()){ 
                echo "Title: " . stripslashes ($title) . "<br />"; 
                echo "MyValue: " . stripslashes ($myvalue) . "<br />"; 
            } 
            $result->close (); 
        } else { 
            echo $mysqli->errno . " - " . $mysqli->error; 
        } 
        $mysqli->close(); 
    } 
} catch (exception $e) { 
echo $e->getmessage(); 
} 
?>

Using the Object-Oriented Syntax in MySQLi

 
<?php
    $mysqli = new mysqli("localhost", "username", "password","mydatabase", 3306);
    $result = $mysqli->query("SELECT * FROM mytable");
    while($row = $result->fetch_array()) {
        foreach($row as $key => $value) {
            echo "$key = $value<BR/>\n";
        }
    }
    $result->close();
    $mysqli->close();
?>