PDO/MySQLi Query to Class

[jay20aiii]

I have a function that performs a SELECT query on a MySQL database and populates the results in an array of Class. At the moment it is using PDO. Trouble is that PDO is not supported by the server the code will run on. Changing server is not an option, nor is installing PDO.

 

I have tried splitting the function to use the PDO method if installed or MySQLi if not. I am struggling to get the MySQLi part working though. Can anyone help me with this?

Here is the function I have so far which basically returns nothing from the MySQLi part:

		public function mysqlSelectToClass($query, $className, $args = NULL)
		{
			include (dirname(__FILE__) . "/../config.php");
			
			if (class_exists('PDO')) {
				$db		= new PDO('mysql:host=' . $db_host . ';dbname=' . $db_name . ';charset=utf8', $db_user, $db_pass);
				$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
				$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
				
				$dbQuery = $db->prepare($query);
				
				if (isset($args)) {
					foreach ($args as $arg) {
						$dbQuery->bindParam(array_values($arg)[0], array_values($arg)[0], PDO::PARAM_STR);
					}
				}
				
				$dbQuery->execute();
				return $dbQuery->fetchAll(PDO::FETCH_CLASS, $className);
			} else {
				$db		= mysqli_connect($db_host, $db_user, $db_pass, $db_name);
				$dbQuery	= $db->prepare($query);
				
				if (isset($args)) {
                                        // Type is a string of parameter types e.g. "is"
					$type	= array_values($args)[0];
                                        // Params is an array of parameters e.g. array(1, 'value')
					$params	= array_values($args)[1];
					
					call_user_func_array('mysqli_stmt_bind_param', array_merge(array($dbQuery, $type), $this->byrefValues($params)));
					$result = mysqli_stmt_execute($dbQuery);
					mysqli_close($db);
				} elseif ($dbResult = mysqli_query($db, $query)) {
					$result = mysqli_fetch_object($dbResult, $className);
					mysqli_close($db);
				}
				
				return $result;
			}
		}

the byrefValues function is simply swapping a value array to a reference array and seems to be working fine. I can paste that too if required.

 

Thanks

Jay

Edited September 24, 2014 by jay20aiii

[ginerjm]

I don't understand your usage of $args.  In the pdo section you do this:

bindParam(array_values($arg)[0], array_values($arg)[0], PDO::PARAM_STR);

My understanding of 'array_values' is that you get an array of the values contained in the input array.  So that means you are grabbing the first value of the input array and in this case you are using that value as both the parameter name and the parameter value in the query.  Is that what you mean to do?  I guess I need to see what your incoming array of '$args' looks like.  Perhaps you mean to do this:

foreach ($args as $k=>$v)
   bindparam($k,$v);

Edited September 24, 2014 by ginerjm

[mac_gyver]

you need to fix several things -

 

1) you should NEVER open a database connection, run one query, and close the database connection, and since this is a class/method, you are likely doing this in multiple methods in the class, resulting in duplicated code. you should create the database connection in your application code and pass it into the instance of your class.

 

2) so that the code will function the same, the call-time parameters must be the same for either type of database library functions. what you have shown isn't, since it appears that for the PDO, the $args is just an array of arrays of parameter numbers/values. since the first array element is the 'isiiiss...' type string, why don't you use that in the PDO bind statement to use the correct  PDO::PARAM_STR, PDO::PARAM_INT, ... type values?

 

3) you apparently have a typo in this line - $dbQuery->bindParam(array_values($arg)[0], array_values($arg)[0], PDO::PARAM_STR); that should probably be $dbQuery->bindParam(array_values($arg)[0], array_values($arg)[1], PDO::PARAM_STR);

 

4.a) the return value must also be the same for either type of database library functions. you are returning an array of objects for the PDO case. you are returning a Boolean value in the case of a prepared mysqli query (see the php.net documenation for mysqli_stmt_execute to see what it returns) and one row fetched as an object from the result set in the case of a non-prepared mysqli query.

 

4.b) for the case of a non-prepared mysqli query, you would need to loop over the result set, storing the object fetched for each row into an array that you finally return.

 

4.c) for the case of a prepared msyqli query, if the mysqli_stmt_get_result() function is present (this is both dependent on the php version and if the mysqlnd driver is being used), you can convert the result from the prepared query into a normal msyqli result set and use mysqli_fetch_object() in a loop to build the array of objects that you finally return. otherwise, you will need to dynamically bind each column selected in the query to an array element, fetch each row of data into that array, and store that data as an object into an array of objects that you finally return.

[jay20aiii]

Hi, I have had to work on other things in the mean time, but have managed to revisit this project again today.

1. Good advice thanks. This function will run on a website on a shared host. As a compromise I think each page load should use a single database connection so I have redesigned the class to do this.
2. I have done what you suggested.
3. I should have updated the typo, as I had been trying various things with the function prior to posting, but the array indexes were previously correct.
4. This was invaluable in helping me to understand what I needed to do to get each query back into an array of Class elements so thanks for taking the time to explain this.

I have pasted the class below that is now working for me. If there are any other suggestions that could help improve on this please do let me know.

<?php
	class connection
	{
	
		public $pdoConnection;
		public $mysqliConnection;
		
		public function __construct()
		{
			require_once (dirname(__FILE__) . "/../types/classes.php");
			
			$this->open();
		}
		
		public function open()
		{
			if (class_exists('PDO')) {
				if (!isset($this->pdoConnection)) {
					include (dirname(__FILE__) . "/../config.php");
					$this->pdoConnection = new PDO('mysql:host=' . $db_host . ';dbname=' . $db_name . ';charset=utf8', $db_user, $db_pass);
					$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
					$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
				}
			} else {
				if (!isset($this->mysqliConnection)) {
					include (dirname(__FILE__) . "/../config.php");
					$this->mysqliConnection = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
				}
			}
		}
		
		public function close()
		{
			if (class_exists('PDO')) {
				if (!empty($this->pdoConnection)) {
					$this->pdoConnection = NULL;
				}
			} else {
				if (!empty($this->mysqliConnection)) {
					mysqli_close($this->mysqliConnection);
				}
			}
		}
		
		function byrefValues(&$arr){
		    $refs = array();
		    foreach($arr as $key => $value)
		        $refs[$key] = &$arr[$key];
		    return $refs;
		}
		
		public function mysqlSelectToClass($query, $className, $args = NULL)
		{
			
			$this->open();
			
			if (class_exists('PDO')) {
				$dbQuery = $this->pdoConnection->prepare($query);
				
				if (isset($args)) {
					$type	= array_values($args)[0];
					$params	= array_values($args)[1];
					
					for ($i = 0; $i <= count($params); $i++) {
					     $param = $params[i];
					     $paramType;
					     
					     if ($i<=strlen($type)) {
					     	switch ($type[i]) {
					     		case 'i':
					     			$paramType = PDO::PARAM_INT;
					     			break;
					     		default:
					     			$paramType = PDO::PARAM_STR;
					     	}
					     } else {
					     	$paramType = PDO::PARAM_STR;
					     }
					     
					     $dbQuery->bindParam(array_values($param)[0], array_values($param)[1], $paramType);
					}
				}
				
				$dbQuery->execute();
				$result = $dbQuery->fetchAll(PDO::FETCH_CLASS, $className);
				
			} else {
			
				$dbQuery = $this->mysqliConnection->stmt_init();
				if ($dbQuery->prepare($query)) {
					if (isset($args)) {
						$type	= array_values($args)[0];
						$params	= array_values($args)[1];
						
						call_user_func_array('mysqli_stmt_bind_param', array_merge(array($dbQuery, $type), $this->byrefValues($params)));
						
						$dbQuery->execute();
						$dbResult = $dbQuery->get_result();
						
						$result = array();
						while ($item = $dbResult->fetch_object()) {
						    array_push($result, $item);
					        }
					} elseif ($dbResult = mysqli_query($this->mysqliConnection, $query)) {
						$result = array();
						while ($item = $dbResult->fetch_object()) {
							array_push($result, $item);
					        }
					}
				}
				
			}
			
			return $result;
		}
	}
?>

Cheers

Jay

Edited November 21, 2014 by jay20aiii

[maxxd]

Personally, I'd take this one step further and make it easier on yourself. Create child classes that extend or implement the class listing above and contain only the code specific to the connection type. For instance, create a class called PDOConnection.php that includes the open(), close(), and mysqlSelectToClass() methods using only PDO-specific functionality. Create another class called MysqliConnection.php that includes mysqli-specific implementations of the open(), close(), and mysqlSelectToClass() methods. Then, the connection class above can do something like this:

class Connection{
	private	$_dbConn;
	
	public function __construct(){
		if(!class_exists('PDO')){
			$DB = 'MysqliConnection';
		}else{
			$DB = 'PDOConnection';
		}
		$this->_dbConn = new $DB();
		$this->_dbConn->open();
	}
	
	public function close(){
		$this->_dbConn->close();
	}
	
	public function mysqlSelectToClass($query, $className, $args = null){
		return $this->_dbConn->mysqlSelectToClass($query, $className, $args);
	}
}

That way, if you need to add yet another database choice in the future you can do it without disturbing the current (working) code. Also, you avoid the repeated if-else clauses.

[jay20aiii]

Thanks Maxxd. I had the same idea earlier on but was unsure about the implementation. I appreciate the example as it is a massive help. I'm sure you will know how time consuming it can be to try and find a basic and complete snippet that reflects what you are trying to do. I shall upvote your post tomorrow when my daily limit of (1 ?!) has expired.

 

Jay

[maxxd]

It can take some time, but is well worth it. I don't know your level of experience, but if you're getting into PHP OOP, I highly recommend PHP Objects, Patterns, and Practice by Matt Zandstra. And, of course, you can always ask here if you run into any other issues!