Page 1 of 1

Trying ot return the results of a prepared statement

Posted: Sun Apr 24, 2016 9:56 am
by bonecone
I'm trying to return the results of a prepared statement from a function. I'm using a work-around by converting it into an array first and then returning the array, but I'd rather cut out the middle man if I could.
Problem is I'm running into errors when I try to do things this way.

Here's the current function:

Code: Select all

function get_client_data($name)
{
	global $config;
	$mysqli = new mysqli($config["host"], $config["username"], $config["password"], $config["dbname"]);

	if ($mysqli->connect_errno != 0)
	{
		printf("Connect failed: %s\n", mysqli_connect_error());
		return;
	}

	$stmt = $mysqli->stmt_init();

	$stmt->prepare("SELECT id, name, address, age FROM clients WHERE name=?");
	$stmt->bind_param("s", $name);
	$stmt->execute();

	return convert_to_array($stmt);
}
And here's the convert_to_array code:

Code: Select all

	function convert_to_array($stmt)
	{
		$parameters = array();
		$results = array();

		$meta = $stmt->result_metadata();
		 
		while($field = $meta->fetch_field())
			$parameters[] = &$row[$field->name];
		 
		call_user_func_array(array($stmt, "bind_result"), $parameters);
		 
		while($stmt->fetch())
		{
			$x = array();

			foreach( $row as $key => $val )
				$x[$key] = $val;

			$results[] = $x;
		}

		return $results;
	}
So I call the function and echo the results like this:

Code: Select all

	$client_data = get_client_data($name);
	echo $client_data[0]['name'];
	echo $client_data[0]['address'];
But I want to do it like this instead:

I directly return $stmt from the get_client_data function. Then:

Code: Select all

	$client_data = get_client_data($_GET['name']);
	$client_data->store_result();
	$client_data->bind_result($id, $name, $address, $age);
	$client_data->fetch();
	echo $id . "<br/>";
	echo $name . "<br/>";
	echo $address . "<br/>";
	echo $age;
And not only does it fail to work, it fails to connect to the server.

Re: Trying ot return the results of a prepared statement

Posted: Sun Apr 24, 2016 11:29 am
by Celauran
global is a code smell. Avoid using it if at all possible. Would it not make more sense to have a user class to which you could pass a database connection and call the method on directly?

Re: Trying ot return the results of a prepared statement

Posted: Sun Apr 24, 2016 11:33 am
by requinix
Your preferred method requires more code, code that you're going to write all over the place, and it looks like you're calling mysqli methods directly. Normally when people do these kinds of things they start with the second method and write code so they can use the first method instead. Are you doing this intentionally?

Anyway, if you want that method then all you have to do is have get_client_data return $stmt instead of convert_to_array($stmt).

Re: Trying ot return the results of a prepared statement

Posted: Sun Apr 24, 2016 12:28 pm
by bonecone
I abstract things behind classes or functions after I get them working. If the code isn't working when I use it directly then it's not going to work from inside of a class either.