PHP5.5 Connecting MySQL database and reading data based on mysqli

This article describes the PHP 5.5 based on mysqli connection MySQL database and read data operations. Share it for your reference, as follows:

In Learning 1. Turn on API support for PHP

(1) First modify your php. ini configuration file.

Find the following statement:

;extension=php_mysqli.dll

Modify it to read:

extension=php_mysqli.dll

(2) Restart Apache/IIS.

(3) Explanation: PHP needs a separate file to support this extension library. php_mysqli. dll file (PHP) can be found in ext directory under PHP directory < = 5.0. 2 is libmysqli. dll), of course, in the PHP configuration file should have the correct information pointing to ext (extension_dir). If your PHP does not have this file, you can download the source package of PHP5. In addition, this API extension can only be used in PHP5 and above. For other specific information, please see below.

2. PHP mysqli ID card

mysqli is the abbreviation of "MySQL, Improved", and this extension only applies to PHP 5. It can be used in MySQL 4.1. 1 and later. The extension fully supports the authentication protocol adopted in MySQL 5.1, as well as pre-processing statements and multi-statement API. In addition, the extension provides an advanced, object-oriented programming interface

<?php
  /* Connect to a MySQL server  Connect to the database server  */
  $link = mysqli_connect(
        'localhost', /* The host to connect to  Connect MySQL Address  */
        'user',   /* The user to connect as  Connect MySQL User name  */
        'password', /* The password to use  Connect MySQL Password  */
        'world');  /* The default database to query  Connection database name */
  if (!$link) {
    printf("Can't connect to MySQL Server. Errorcode: %s ", mysqli_connect_error());
    exit;
  }
  /* Send a query to the server  Send a query request to the server */
  if ($result = mysqli_query($link, 'SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {
    //print("Very large cities are: ");
    /* Fetch the results of the query  Returns the results of a query  */
    while( $row = mysqli_fetch_assoc($result) ){
      printf("%s (%s) ", $row['Name'], $row['Population']);
    }
    /* Destroy the result set and free the memory used for it  End query to free memory  */
    mysqli_free_result($result);
  }
  /* Close the connection  Close the connection */
  mysqli_close($link);
?>

Using MySQLi

In the following example, we read the id, firstname, and lastname columns from the MyGuests table of the myDB database and display them on the page:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
//  Create a connection 
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die(" Connection failed : " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
  //  Output data 
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0  Results ";
}
$conn->close();
?>

The above code is parsed as follows:

First, we set the SQL statement to read the id, firstname, and lastname fields from the MyGuests data table. We then use the modified SQL statement to fetch the result set from the database and assign it to the copied variable $result.

Function num_rows() Determine the returned data.

If multiple pieces of data are returned, the function fetch_assoc() Places the union set into an associative array and loops it out. while() Loop out the result set, and output three field values of id, firstname and lastname.

The following example uses the MySQLi process-oriented approach, and the effect is similar to the above code:

Example (MySQLi-Process Oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
//  Create a connection 
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die(" Connection failed : " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
  //  Output data 
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0  Results ";
}
mysqli_close($conn);
?>

Use PDO (+ preprocessing)

The following example uses preprocessing statements.

Select the id, firstname, and lastname fields in the MyGuests table and place them in the HTML table:

<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";
class TableRows extends RecursiveIteratorIterator {
  function __construct($it) {
    parent::__construct($it, self::LEAVES_ONLY);
  }
  function current() {
    return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
  }
  function beginChildren() {
    echo "<tr>";
  }
  function endChildren() {
    echo "</tr>" . "\n";
  }
}
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests");
  $stmt->execute();
  //  Set the result set to an associative array 
  $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
  foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
    echo $v;
  }
}
catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

For more readers interested in PHP related content, please check the topics on this site: "Summary of php+mysqli Database Programming Skills", "Introduction to php Object-Oriented Programming", "Encyclopedia of PHP Array (Array) Operation Skills", "Introduction to PHP Basic Syntax", "Summary of php String (string) Usage", "Introduction to php+mysql Database Operation Skills" and "Summary of php Common Database Operation Skills"

I hope this article is helpful to everyone's PHP programming.