DIGITALREP

A quick look at the PHP MySQLi objectOctober 07, 2013

MySQLi – or improved MySQL – is for use with PHP 4.1 and above and provides improved security over MySQL.

Using the MySQLi object constructor to connect to a database:

$dbconn = new mysqli(‘localhost’, ‘user’, ‘pass’, dbname’);

Check if there was a connection error:

if ($dbconn->connect_errno)
{
  echo “Failed to connect: (” . $dbconn->connect_errno . “) ” . $dbconn->connect_error;
}

Construct and execute a query to a database table:

$sql = ‘SELECT id, title FROM books WHERE id = 1’;

$results = $dbconn->query($sql);

Count the number of results:

$num_rows = $results->num_rows;

Fetch each result from the results object in a while loop:

while($row = $result->fetch_assoc())
{
  echo $row[‘id’] . “, ” . $row[‘title’];
}

Free up Resources:

$result->free();
$dbconn->close();

Prepare a Statement:

$statement = $dbconn->prepare(“INSERT into products(id, product, price) VALUES(?,?,?)”);

Bind Parameters:

$id = 348;
$product_name = “Gold Widget”;
$price = 75.68;

$statement->bind_param(‘isd’, $id, $product_name, $price);

Execute the statement:

$statement->execute();

Check for errors whilst executing the statement:

if(!$statement->execute())
{
  echo “Execution of statement failed: (” . $statement->errno . “) ” . $statement->error;
}

Execute multiple statements:

for($id=348; $id<400; $id++) {   $statement->execute();
}

Close the connection:

$statement->close();

Bind the results:

$statement->bind_result($quantity);

Fetch each result:

while($statement->fetch())
{
  echo $quantity;
}

Free up resources:

$statement->free_result();

Category: Cheat Sheets
Tags:  

Leave a Reply

Your email address will not be published. Required fields are marked *