note 106402 added to mysqli-stmt.bind-param
| From: | OleClausen at osu1 dot php dot net | Date: | Sun, 06 Nov 2011 19:50:23 +0000 |
| Subject: | note 106402 added to mysqli-stmt.bind-param | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-184314@lists.php.net to get a copy of this message | ||
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 =)
----
Server IP: 87.54.44.10
Probable Submitter: 90.184.94.109
----
Manual Page -- http://www.php.net/manual/en/mysqli-stmt.bind-param.php
Edit -- https://master.php.net/note/edit/106402
Del: integrated -- https://master.php.net/note/delete/106402/integrated
Del: useless -- https://master.php.net/note/delete/106402/useless
Del: bad code -- https://master.php.net/note/delete/106402/bad+code
Del: spam -- https://master.php.net/note/delete/106402/spam
Del: non-english -- https://master.php.net/note/delete/106402/non-english
Del: in docs -- https://master.php.net/note/delete/106402/in+docs
Del: other reasons-- https://master.php.net/note/delete/106402
Reject -- https://master.php.net/note/reject/106402
Search -- https://master.php.net/manage/user-notes.php