Using prepared statement in PHP

So this is the result of using prepared statements in PHP on not so comfort way. Each time when I had to perform some query, I had something like this in my code

<?php
	$stmt = $con->prepare("SELECT * FROM table WHERE id = ?");
	$stmt->bind_param("i", $id);
	$stmt->execute();
	$results = $stmt->get_result();
?>

First problem occurred when I wanted to use this code on server where MySQL native driver for PHP was not enabled. When driver is not enabled, using get_result() function is not possible. It was free server, something I was doing just for fun, so it was not possible either to contact support or to install it by myself. Then I had to change the way I get results. And I had to do that on every single line in a code where I had query. A lot of work.

Second problem was a lot of repeating code which I don’t like. And I wanted to catch errors (which I didn’t do from the beginning). Again, I had to change my code on every place where I had query executing.

So I decided to create function, which will do executing queries instead of me.  I Googled a little bit, and found this.

That allowed me to create function like this:

<?php
	function prepareAndExecuteQuery($con, $query, $bindString, $bindArgs) {
		if (!is_array($bindArgs)) 
			exit ("prepareAndExecuteQuery - bindArgs not array");

		$n = strlen($bindString); 
		if ($n != count($bindArgs))
			exit("prepareAndExecuteQuery - bindString params count not equal to the bindArgs count");

		$a_params = array();
		$a_params[] = & $bindString;
		for($i = 0; $i < $n; $i++) 
			$a_params[] = & $bindArgs[$i];

		$stmt = $con->prepare($query);

		call_user_func_array(array($stmt, 'bind_param'), $a_params);

		if (!$stmt)
			exit("prepareAndExecuteQuery - Binding parameters failed");

		if(!$stmt->execute())
			exit("prepareAndExecuteQuery - Execute failed - " . $stmt->error);

		$result = $stmt->get_result();
		$stmt->close();
		if (!$result) 
			exit("prepareAndExecuteQuery - No results - " . $stmt->error);

		return $result;
	}
?>

Now when I have to execute some query I do it this way:

<?php
	$sql = "SELECT * FROM table WHERE id = ?";
	$result = prepareAndExecuteQuery($con, $sql,"i"', array($id));
?>

Less code and much more flexibility. If one day I decide to change something in execution of queries, I’ll have to do it just at one place.

I hope it will be helpful to someone else too 😉

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.