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();
	}