5 Replies - 1807 Views - Last Post: 22 November 2012 - 11:20 PM Rate Topic: -----

#1 Tenderfoot  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 160
  • Joined: 21-March 12

PDO: How do I use query with a prepared statement?

Posted 22 November 2012 - 09:12 AM

How would I go about using a query in PDO with a prepared statement?

With a non-query, that is, something that I might execute in a MySQL, I'd do something like this:

	function exerciseUnDeprecate($ID)
	{
		$query = 'CALL exerciseUnDeprecate(:ID)';
	
		try
		{
			$stmt = $this->dbh->prepare($query);
		}
	
		catch (PDOException $e)
		{
			return false;
		}
	
		try
		{
			$stmt->execute(array(':ID' => $ID));
			return true;
		}
	
		catch (PDOException $e)
		{
			return false;
		}
	}



However, you don't exactly execute a query, so I'm at a loss here.

This is how I got it to work, but I don't think it's safe enough to be the final option, or maybe it is?

if (is_string($colValue))
		{
			$query = 'CALL GetExercise(\'' . $column . '\'' . ', \''  . $colValue . '\')';
		}
		
		else if (is_int($colValue))
		{
			$query = 'CALL GetExercise(\'' . $column . '\'' . ', ' . $colValue . ')';
		}
		
		$stmt = $this->dbh->query($query);
		
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
		{
			$ID = $row['ID'];
			$exerciseName = $row['exerciseName'];
			$description = $row['description'];
			$warning = $row['warning'];
			$videoPath = $row['videoPath'];
			printf("ID: %s Exercise name: %s Description: %s  Warning: %s Video Path: %s <br />", $ID, $exerciseName, $description, $warning, $videoPath);
		}
	}



This $query = 'CALL GetExercise(\'' . $column . '\'' . ', \'' . $colValue . '\')'; is the part I have a problem with. I've done a bit of sanitizing with these variables prior to this, but I'd just feel safer if I weren't doing it like that.

I'd prefer to do something like: $query = 'CALL GetExercise(:column, :value)';

And then below that, use something that works like the execute function. That is, I'd like to be able to put this in there: ...execute(array(':column' => $column, ':value' => $value);

However I can't find anything for a query that works like that.

Is This A Good Question/Topic? 0
  • +

Replies To: PDO: How do I use query with a prepared statement?

#2 Kruithne  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 99
  • View blog
  • Posts: 442
  • Joined: 28-July 09

Re: PDO: How do I use query with a prepared statement?

Posted 22 November 2012 - 09:19 AM

Hello,

I'm not 100% I follow, so forgive me if I am shooting away from the mark here.

You can create a prepared statement and execute it with the snippet below, comments should explain each step.

$query = $this->dbh->prepare('INSERT INTO someTable (someValue, someOtherValue) VALUES (:myValue, :myOtherValue)'); // Create the PDO query object.
$query->bindValue(':myValue', 'Chips'); // Bind this value
$query->bindValue(':myOtherValue', 'Hoovers'); // Bind that value
$query->execute(); // Execute the query!

Was This Post Helpful? 1
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: PDO: How do I use query with a prepared statement?

Posted 22 November 2012 - 09:31 AM

View PostTenderfoot, on 22 November 2012 - 05:12 PM, said:

However, you don't exactly execute a query, so I'm at a loss here.

why not? instead of doing
- escape()
- query() [you could also see that as prepare()+execute()]
- fetch()
you do
- prepare()
- bind()
- execute()
- fetch()

thatís how Prepared Statements work.
Was This Post Helpful? 1
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: PDO: How do I use query with a prepared statement?

Posted 22 November 2012 - 09:45 AM

View PostTenderfoot, on 22 November 2012 - 05:12 PM, said:

	function exerciseUnDeprecate($ID)
	{
		$query = 'CALL exerciseUnDeprecate(:ID)';
	
		try
		{
			$stmt = $this->dbh->prepare($query);
		}
	
		catch (PDOException $e)
		{
			return false;
		}
	
		try
		{
			$stmt->execute(array(':ID' => $ID));
			return true;
		}
	
		catch (PDOException $e)
		{
			return false;
		}
	}


two notes on the code itself
  • both try...catch blocks will only work, if (and only if) Exception handling is explicitly enabled. you do that via
    $pdo = new PDO(...);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    

    otherwise it will always return true, even if the execute() call failed (and returned false)
  • why using 2 try...catch blocks? itís the beauty of exceptions that you can catch one everywhere.
    	function exerciseUnDeprecate($ID)
    	{
    		$query = 'CALL exerciseUnDeprecate(:ID)';
    		$stmt = $this->dbh->prepare($query);
    		$stmt->execute(array(':ID' => $ID));
    	}
    // apply in code
    try
    {
    	$obj = new Whatever();
    	$obj->exerciseUnDeprecate("test")
    	// proceed
    }
    catch (PDOException $p)
    {
    	error_log( $p->getMessage() );
    }
    // proceed
    
    

Was This Post Helpful? 1
  • +
  • -

#5 Tenderfoot  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 160
  • Joined: 21-March 12

Re: PDO: How do I use query with a prepared statement?

Posted 22 November 2012 - 10:23 AM

View PostDormilich, on 22 November 2012 - 09:31 AM, said:

View PostTenderfoot, on 22 November 2012 - 05:12 PM, said:

However, you don't exactly execute a query, so I'm at a loss here.

why not? instead of doing
- escape()
- query() [you could also see that as prepare()+execute()]
- fetch()
you do
- prepare()
- bind()
- execute()
- fetch()

thatís how Prepared Statements work.


Thanks, that worked just fine. I could have sworn I read that the difference between a select query and something like, insert, or update, was that select couldn't be executed. Turns out it can.

My end result looks something like this:

		$query = 'CALL GetExercise(:column, :colValue)';
		$stmt = $this->dbh->prepare($query);
		$stmt->execute(array(':column' => $column, ':colValue' => $colValue));
		
				while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
					{
						$ID = $row['ID'];
						$exerciseName = $row['exerciseName'];
						$description = $row['description'];
						$warning = $row['warning'];
						$videoPath = $row['videoPath'];
						printf("ID: %s Exercise name: %s Description: %s  Warning: %s Video Path: %s <br />", $ID, $exerciseName, $description, $warning, $videoPath);
			 		}



Or well, this was my test to see if it worked. But I appreciate your tips on my code, I'll have a look at that ASAP. I believe I enabled ERRmode somewhere though, but thanks again.
Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: PDO: How do I use query with a prepared statement?

Posted 22 November 2012 - 11:20 PM

there is no need for lines #7 to #11, why not putting the array calls directly into printf()?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1