PHP/MySQL Database/mysqli

Multiple Queries Using MySQLi

   <source lang="html4strict">

<?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
\n";        
                   }
               }
               mysqli_free_result($result);        
           }
           if(mysqli_more_results($mysqli)) {                
               echo "
\nNext result set
\n";
           }
       } while(mysqli_next_result($mysqli));
   }
   mysqli_close($mysqli);

?>

 </source>
   
  

mysqli_fetch_object.php

   <source lang="html4strict">

<?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 
";

} ... ?>

 </source>
   
  

mysqli_fetch_result-2.php

   <source lang="html4strict">

<?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 
";

} ?>

 </source>
   
  

mysqli_fetch_result.php

   <source lang="html4strict">

<?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) 
";

} ?>

 </source>
   
  

mysqli_fetch_row.php

   <source lang="html4strict">

<?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 
";
  }
  ...

?>

 </source>
   
  

mysqli_free_result.php

   <source lang="html4strict">

<?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();

?>

 </source>
   
  

mysqli_multi_query.php

   <source lang="html4strict">

<?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] 
";
        if ($mysqli->more_results())
           echo "
";
     } while ($mysqli->next_result());
  }

?>

 </source>
   
  

mysqli_stmt_prepare.php

   <source lang="html4strict">

<?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();

?>

 </source>
   
  

mysqli_store_result.php

   <source lang="html4strict">

<?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 
";
  $result->free();

?>

 </source>
   
  

Parameter Binding in MySQLi

   <source lang="html4strict">

<?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);

?>

 </source>
   
  

retrieving_multiple_rows.php

   <source lang="html4strict">

<?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 
";
 }

?>

 </source>
   
  

Use mysqli to fetch data from mysql

   <source lang="html4strict">

<?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 
";
  }
  $stmt->close();
  $mysqli->close();

?>

 </source>
   
  

Using the mysqli Object-Oriented API

   <source lang="html4strict">

<?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"] . "
"; 
               echo "Title: " . stripslashes ($cddata["title"]) . "
"; 
               echo "MyValue: " . stripslashes ($cddata["MyValue"]) . "
"; 
           } 
           $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) . "
"; 
               echo "MyValue: " . stripslashes ($myvalue) . "
"; 
           } 
           $result->close (); 
       } else { 
           echo $mysqli->errno . " - " . $mysqli->error; 
       } 
       $mysqli->close(); 
   } 

} catch (exception $e) { echo $e->getmessage(); } ?>

 </source>
   
  

Using the Object-Oriented Syntax in MySQLi

   <source lang="html4strict">

<?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
\n";
       }
   }
   $result->close();
   $mysqli->close();

?>

 </source>