MySQLi can be confusing for certain things. Here's an essential checklist of examples on how to do things such as selecting single and multiple rows, inserting, deleting and updating tables. If you're new to MySQLi this should be a good start for you.

�

Selecting & printing a single row

$db = new mysqli(host, username, password, database); // connect to your database
$id = 25;
public function grabData() { // lets use a function for these examples
$query = '
SELECT title, body
FROM Table
WHERE id = ?
LIMIT 1';
$result = $db->prepare($query); // prepare your query
$result->bind_param('i', $id);�// bind $id to WHERE (i = integer, s = string, b = blob, and d = double)
$result->execute(); // now we execute the query
$result->bind_result($row['title'], $row['body']); // bind results to $row
while($result->fetch()) $r[] = $row; // store $row for printable data
$result->close(); // lets not keep this open
return $row;�
}�

Now to retrieve this information, it's a simple array call:

�

$r = grabData(); // will return the array $row
echo 'Title: '.$r['title'].'
Body: '.$r['body'];

�

Selecting & printing multiple rows

�

$db = new mysqli(host, username, password, database); // connect
$category = 5;
public function grabData() {
$query = '
SELECT title, body
FROM Table
WHERE category = ?
ORDER BY date DESC
LIMIT 10';�// get the latest articles in category 5
$result = $db->prepare($query);
$row = rePrepared($result); // it's messy to select multiple rows and you'll see why
return $row;
}
// because it's messy, we have to have another function (a static function is beautiful for this)�
public function rePrepared($a) {
$results = $binder = $row = array();
$a->execute();�
$meta = $a->result_metadata(); // grab the information
while($col = $meta->fetch_field()) $binder[] =& $row[$col->name];
call_user_func_array(array($a, 'bind_result'), $binder);
$meta->close();
while($a->fetch()) $results[] = array_map('nothing', $row);
$a->close();
return $results;�
} public function nothing($a) { return $a; } // we don't want to do anything�

See, messy. Putting rePrepared in a database class full of static functions that get used a lot is a great thing.

Updating/deleting/inserting into a table

This is the easiest part.

�

$db = new mysqli(host, username, password, database);
$title = 'This is a dummy title'; $body = 'This is dummy data';
$query = '
INSERT INTO Table (
title, body
) VALUES (?, ?)';
$result = $db->prepare($query);
$result->bind_param('ss', $title, $body);
$result->execute();
if($result->affected_rows==1) {
// if you wanted to grab the ID it has just now, you can do this:
$last_id = $result->insert_id;
Header('Location: /article/'.$last_id);
} else die($result->error); 
$result->close();�

Updating and deleting are similar:

$query = '
UPDATE Table
SET title = ?, body = ?
WHERE category = ?
LIMIT 1';
// or, for a DELETE query:
$query = '
DELETE FROM Table
WHERE (
title = ? 
AND body = ? 
AND category = ?
)';
$result = $db->prepare($query);
$result->bind_param('ssi', $title, $body, $category);
$result->execute();
if($result->affected_rows==1) Header('Location: /article/'.$result->inserted_id); 
else die($result->error);
$result->close();�

�