Page 1 of 1
Can we get old-style result rows from prepared statements?
Posted: Sat Nov 13, 2010 6:15 pm
by Stokestack
Hi all.
I want to use prepared statements to issue queries, but process the result rows without binding hard-coded variables to them. My pre-mysqli code iterated through result rows and returned each column name and value as XML elements:
Code: Select all
foreach($row as $key => $val)
{
$this->writer->writeElement($key, $val);
}
The client could then access elements by name, and that's the only code I had to change if the table structure changed. If I have to bind variables to the result, it requires a massive amount of hard code to add every column as an XML element one by one, not to mention the maintenance hassle. Is there any other solution?
Thanks!
Re: Can we get old-style result rows from prepared statement
Posted: Sat Nov 13, 2010 6:39 pm
by Stokestack
It looks like this is a pretty major omission from mysqli, discussed here:
http://www.greebo.net/2010/01/02/conver ... statements
However, people have posted some workarounds in the user notes here:
http://php.net/manual/en/mysqli-stmt.fetch.php#82742
yeah
Posted: Sat Nov 13, 2010 8:01 pm
by Stokestack
OK, the workaround works fine. Here's an example. Right after you call execute() on your prepared statement, hand it to something like this (you don't need $rowName; it's just a string I'm using to label the XML structure I'm building):
Code: Select all
function resultSetToXML(&$stmt, $rowName)
{
$data = mysqli_stmt_result_metadata($stmt);
$fields = array();
$row = array();
$fields[0] = $stmt;
$count = 1;
while($field = mysqli_fetch_field($data))
{
$fields[$count] = &$row[$field->name];
$count++;
}
call_user_func_array(mysqli_stmt_bind_result, $fields);
while ($stmt->fetch())
{
// DO SOMETHING WITH THE ROW.
// I'm adding the column names and values to XML as follows.
AddResultRow($rowName, $row);
}
}
This is the function that the one above is calling to write XML; it shows how you can iterate through the row columns by name like you could before:
Code: Select all
function AddResultRow($rowName, $row)
{
$this->writer->startElement($rowName);
foreach($row as $key => $val)
{
$this->writer->writeElement($key, $val);
}
$this->writer->endElement();
}