Can we get old-style result rows from prepared statements?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Stokestack
Forum Newbie
Posts: 14
Joined: Thu Sep 16, 2010 2:37 am

Can we get old-style result rows from prepared statements?

Post 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!
Stokestack
Forum Newbie
Posts: 14
Joined: Thu Sep 16, 2010 2:37 am

Re: Can we get old-style result rows from prepared statement

Post 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
Stokestack
Forum Newbie
Posts: 14
Joined: Thu Sep 16, 2010 2:37 am

yeah

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