Clearing binded params in pdo

[fastsol]

I have a DB wrapper class that I built, it's not fancy and likely not "real" good I would suspect as classes are not my strong suit.  I ran into a situation today that I didn't know could happen cause I probably don't fully understand how pdo works or maybe it's my db class creating this issue.

 

I called a function and tried to run a prepare() using bindParam()s, then later in the same function I tried to run a prepare with just using the execute(array('blah')) type format.  For some reason (this is my issue I'm asking about) even though I used different php vars to store the retrieved db info, the 2nd query failed cause it was still holding the binded values from the first query.  Once I switched the first query to the same execute(array('blah')) format both queries worked just fine.

 

Now granted I can certainly leave the queries written like they are now and all will be fine, but I am curious as to how to fix such a situation.  I don't know if the issue lies in my db class or maybe pdo just doesn't work this way.  Here is the code for the db class (be kind, it works how I want other than this snag).

class DB
{
	private static $_instance = NULL;
	private $_pdo,
			$_query,
			$_results,
			$_count = 0,
			$_error = FALSE,
			$_sql,
			$_bind = FALSE,
			$_error_reason,
			$_params,
			$_found_rows,
			$_success,
			$_query_type;
			
	private function __construct()
	{
		try {
				$db_options = array(
		            PDO::ATTR_EMULATE_PREPARES => false,                     // important! use actual prepared statements (default: emulate prepared statements)
		            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,           // throw exceptions on errors (default: stay silent)
		            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays)
	        	);
	        	
				$this->_pdo = new PDO('mysql:host='.Config::get('site_host').';dbname='.Config::get('database_name'), Config::get('mysql_username'), Config::get('mysql_password'), $db_options);
			} catch (PDOException $e) {
			    header('Location: maintenance.php');
			    exit();
			}

	}
	
	public static function getInstance()
	{
		if(!isset(self::$_instance))
		{ self::$_instance = new DB(); }
		
		return self::$_instance;
	}
	
	public function bind($param, $value, $type = null)
	{
        switch ($type)
        {
            case 'INT':
                $bind = PDO::PARAM_INT;
                break;
            case 'BOOL':
                $bind = PDO::PARAM_BOOL;
                break;
            case 'NULL':
                $bind = PDO::PARAM_NULL;
                break;
            default:
                $bind = PDO::PARAM_STR;
        }
	    
	    $this->_query->bindValue($param, $value, $bind);
	    $this->_bind = TRUE;
	}

		
	public function query($sql)
	{
		$this->_bind = FALSE;
		$this->_sql = $sql;
		$this->_query_type = 'query';
		$this->run();		
		return $this;	
	}
	
	public function prepare($sql)
	{
		$this->_sql = $sql;
		$this->_query_type = 'execute';
		$this->_query = $this->_pdo->prepare($sql);		//print_r($this->_query);
		return $this;
	}
	
	public function execute($arg = array())
	{
		$this->_params = $arg;
		$this->run();
		return $this;
	}
	
	private function run()
	{
		try
		{
			$this->_count = '';
			if($this->_query_type == 'query')
			{ $this->_query = $this->_pdo->query($this->_sql); }
			elseif($this->_bind !== FALSE)
			{ $this->_query->execute(); }
			else
			{ $this->_query->execute($this->_params); }
						
			$this->_count = $this->_query->rowCount();
			
			if(strpos($this->_sql, 'SQL_CALC_FOUND_ROWS') !== FALSE)
			{ $this->_found_rows = $this->_pdo->query('SELECT FOUND_ROWS();')->fetch(PDO::FETCH_COLUMN); }
			else{ $this->_found_rows = ''; }
			
			$this->_query_type = '';
			$this->_success = TRUE;
			$this->_error = FALSE;
			$this->_error_reason = '';

			return TRUE;
		}
		catch(PDOException $e)
		{
			$this->_error = TRUE;
			$this->_error_reason = $e;
			$this->_success = FALSE;
			return FALSE;
		}
	}
	
			
	public function foundRows()
	{ return $this->_found_rows; }
	
	public function fetch()
	{ return $this->_query->fetch(); }
	
	public function fetchAll()
	{ return $this->_query->fetchAll(); }
	
	public function fetchColumn()
	{ return $this->_query->fetchColumn(); }
	
	public function fetchAllColumns()
	{ return $this->_query->fetchAll(PDO::FETCH_COLUMN); }
	
	// $item allows you to select a specific item from the array returned.
	// $index allows you to select a specific array key from the array returned.
		
	public function error()
	{ return $this->_error; }
	
	public function success()
	{ return $this->_success; }
	
	public function rowCount()
	{ return $this->_count; }
	
	public function printSql()
	{ return $this->_sql; }
	
	public function errorReason()
	{ return $this->_error_reason; }
	
	public function debugDumpParams()
	{ return $this->_query->debugDumpParams(); }
	
	public function lastInsertId()
	{ return $this->_pdo->lastInsertId(); }

}

Here is the first query that oiginally had the bind().  I left the bind() code in there for future reference but is currently commented out.  When I did try the bind() I did not have anything in the execute(), just to be clear.

$check_quotes = DB::getInstance()->prepare("SELECT `".QUOTE_REQUESTS."`.*,
									  `".QUOTE_RESPONSES."`.`id` AS `quote_id`,
									  `".QUOTE_RESPONSES."`.`code`,
									  `".QUOTE_RESPONSES."`.`quote_active`
								FROM `".QUOTE_REQUESTS."`
								LEFT JOIN `".QUOTE_RESPONSES."` ON `".QUOTE_RESPONSES."`.`request_id` = `".QUOTE_REQUESTS."`.`id`
								WHERE `".QUOTE_REQUESTS."`.`email` = :e AND `".QUOTE_REQUESTS."`.`vehicle` = :v 
										OR `".QUOTE_REQUESTS."`.`name` = :n AND `".QUOTE_REQUESTS."`.`vehicle` = :v2");
		
		/*$check_quotes->bind(':e', $emaile);
		$check_quotes->bind(':v', $full_car);
		$check_quotes->bind(':n', $full_name);
		$check_quotes->bind(':v2', $full_car);*/

		$check_quotes->execute(array($emaile, $full_car, $full_name, $full_car));
		$quotes = $check_quotes->fetchAll();

Here is the 2nd query further down the function.

$set = $db->prepare("INSERT INTO `".QUOTE_REQUESTS."`
								SET `request_active` = 1,
									`name` = ?,
									`vehicle` = ?,
									`options` = ?,
									`units` = ?,
									`phone` = ?,
									`email` = ?,
									`city` = ?,
									`key_type`= ?,
									`key_id` = $key_id,
									`trans`= ?,
									`date_sent`= $now,
									`cookie_code` = ?,
									`send_sms` = $sms,
									`comments` = ?,
									`email_list` = $email_list,
									`ad_campaign` = ?,
									`engine` = ?,
									`gift_name` = ?,
									`gift_phone` = ?,
									`gift_email` = ?,
									`gift_date` = ?");
									
		$set->execute(array($full_name, $full_car, $the_options, $units, $phone, $emaile, $city, $key_type, $trans, $cookie_code, $comments, $ad_campaign, $engine, $gift_name, $gift_phone, $gift_email, $gift_date_formatted));

[requinix]

You don't reset _bind after the first query, thus the ->execute() branch in run() gets executed again.

 

I suggest resetting (to false) in prepare().

[fastsol]

Ahh yes, I ended up putting it at the bottom of run() with all the other var resets cause I do a _bind check in the run to determine what execute method to use.  Either way it worked.  It's been roughly 5 months since I have done much coding so I forgot how I really built that wrapper.

 

Thanks for the help!