A lot of newcommers to mysqli find it hard to get started. I have written this wrapper with object based response, that handles most of my queries. I hope it'll be usefull for others as well:
<?php
define('DB_HOST', 'localhost');
define('DB_USERNAME', '');
define('DB_PASSWORD', '');
define('DB_DEFAULT_DB', 'test');
function iQuery($sql, $arrParams, $arrBindNames=false) {
$result = new stdClass();
$mysqli = @new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DEFAULT_DB);
if (mysqli_connect_errno()) {
$result->error = 'Connection failed: '.mysqli_connect_error();
return $result;
}
if ($stmt = $mysqli->prepare($sql)) {
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();
if (!$meta) {
$result->affected_rows = $stmt->affected_rows;
$result->insert_id = $stmt->insert_id;
} else {
$stmt->store_result();
$params = array();
$row = array();
if ($arrBindNames) {
for ($i=0,$j=count($arrBindNames); $i<$j; $i++) {
$params[$i] = &$row[$arrBindNames[$i]];
}
} else {
while ($field = $meta->fetch_field()) {
$params[] = &$row[$field->name];
}
}
$meta->close();
$method = new ReflectionMethod('mysqli_stmt', 'bind_result');
$method->invokeArgs($stmt, $params);
$result->rows = array();
while ($stmt->fetch()) {
$obj = new stdClass();
foreach($row as $key => $val) {
$obj->{$key} = $val;
}
$result->rows[] = $obj;
}
$stmt->free_result();
}
$stmt->close();
}
$mysqli->close();
return $result;
}
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams);
print_r($result);
print $result->rows[1]->first_name . " " . $result->rows[1]->last_name;
?>
If $_POST['sex'] contains 'male' and $_POST['active'] contains 'yes' - and the field names are 'id', 'first_name', 'last_name', 'sex' and 'active', the printet result may look like this:
----------------------------------------------
stdClass Object
(
[rows] => Array
(
[0] => stdClass Object
(
[id] => 2
[first_name] => Peter
[last_name] => Johnson
[sex] => male
[active] => yes
)
[1] => stdClass Object
(
[id] => 5
[first_name] => Ole
[last_name] => Clausen
[sex] => male
[active] => yes
)
)
)
Ole Clausen
----------------------------------------------
You can also apply special field names to the response, if you use the parameter $arrBindNames:
$arrParams = array('ss', $_POST['sex'], $_POST['active']);
$arrNames = array('foo_id', 'bar_first', 'baz_last', 'foo_sex', 'bar_act');
$result = iQuery( 'SELECT * FROM `test_table` WHERE `sex`=? AND `active`=?', $arrParams, $arrNames);
- a row would then look like this:
[0] => stdClass Object
(
[foo_id] => 2
[bar_first] => Peter
[baz_last] => Johnson
[foo_sex] => male
[bar_act] => yes
)
The first argument 'ss' in $arrParams states, that the two following arguments are of type String. The options are 's' for String, 'i' for Integer, 'd' for Double and 'b' for Blob (sent in packages).
In queries, that do not return a result INSERT, UPDATE, etc. $result->affected_rows and $result->insert_id are available. Connection errors are available in $result->error. Additional error handling would be nice, but is not implemented for now. Play with the wrapper and use print_r on the result ... enjoy!
The name 'iQuery'? Well, it handles mysql*i* - and then I guess it's kind of a tribute to Mr. Jobs ... may he 'rest' in energetic, hungry foolishness =)
==
UPDATE: 08-NOV-2011 07:19
Due to changes in PHP 5.3 I encountered a problem with 'bind_param' in my iQuery function below. The values in the passed array *must* be references. The soloution is this function:
<?php
function getRefArray($a) {
if (strnatcmp(phpversion(),'5.3')>=0) {
$ret = array();
foreach($a as $key => $val) {
$ret[$key] = &$a[$key];
}
return $ret;
}
return $a;
}
?>
- and this change in iQuery:
if ($stmt = $mysqli->prepare($sql)) {
$arrParams = getRefArray($arrParams); // <-- Added due to changes since PHP 5.3
$method = new ReflectionMethod('mysqli_stmt', 'bind_param');
$method->invokeArgs($stmt, $arrParams);
$stmt->execute();
$meta = $stmt->result_metadata();