Prepared statement / bind SQL - :1 or ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply

What placeholder do you prefer?

Question marks (?)
2
50%
:1, :2, :3
1
25%
:label1, :label2, :label3
1
25%
 
Total votes: 4

User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Prepared statement / bind SQL - :1 or ?

Post by Ambush Commander »

I came upon some bind SQL on the forum the other day, and I thought to myself, "Hmm, I use question-marks, but these people use :1, :2, :3s. Hmmm..."

Which do you prefer? Are there any definite advantages to using one over the other?

Jcart | I've added the requested option
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

PDO uses ? and :label1, :label2, :label3, so you should add the latter to your list.
(#10850)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

You can't edit poll options. I'd say :label1 is basically the same as :1.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I would avoid question marks, simply because their only relationship is the order of which they are presented. This can be a pain for debugging on going through someone elses code aswell, meanwhile a label eliminates this confusion.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Well they have a little different functionality. The ? is the one-shot style. With the labels you can bind variables to labels and then reuse the prepared statement with changed values.
(#10850)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Well, if you pass an array like this: array('value1', 'value2', 'value3') you're still constrained by order. ;-)

What you'd have to do, in that case, is use something like...

Code: Select all

$statement->setString(1, $string);
$statement->setFloat(2, $float);
//...
(from PoEAA, I suppose this is the Java style. But they still use ? marks)

Edit - So that's the difference! Does that mean this poll is null (if I remember correctly, MySQL doesn't have prepared statements)
User avatar
Nathaniel
Forum Contributor
Posts: 396
Joined: Wed Aug 31, 2005 5:58 pm
Location: Arkansas, USA

Post by Nathaniel »

May I suggest both?

I say this because I recently came on a situation where I had a variable number of statements to prepare...

Code: Select all

<?PHP
foreach ( $data as $key => $value )
{
   $query->prepare($value);
   $sql .= $key . ' = ":' . $query->countBinds() . '" '; //countBinds() counts the prepared value array
}
$query->execute($sql);
?>
I realize now that allowing question marks would be useful for such situations, although possibly confusing to someone else using your code...
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Hmm... Nathaniel, something like this? (part of a Domain Object supertype)

Code: Select all

function _insert() {
        global $wgDB;
        $this->id = $wgDB->KeyGenerator->nextKey($this->_table);
        $query  = " INSERT INTO `{$this->_table}` ( `";
            $query .= implode('`, `', $this->_fields);
        $query .= "` ) VALUES ( ";
        $places = array();
        $values = array();
        foreach ($this->_fields as $field) {
            $places[] = '?';
            $values[] = is_object($this->$field) ? $this->$field->toString() :
              $this->$field;
        }
        $query .= implode(', ', $places);
        $query .= " ); ";
        return $wgDB->execute($query, $values);
    }
User avatar
Nathaniel
Forum Contributor
Posts: 396
Joined: Wed Aug 31, 2005 5:58 pm
Location: Arkansas, USA

Post by Nathaniel »

Yes, something like that. When I can, I prefer using the numbered binds, though. IMO, easier to see what's going on.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I prefer named parameters instead of numbered ones... (ADO.net style)

Code: Select all

$command->CommandText = "SELECT blah FROM foo WHERE ((count > @count) AND (amount BETWEEN @minamount AND @maxamount))";
$command->Parameters->Add("@minamount", $minamount);
$command->Parameters->Add("@maxamount", 1000);
// let's forget to bind the count parameters
//$command->Parameters->add("@count", SOME_CONSTANT);
If i forget to bind one.. I get an error message that the parameters "@count" doesn't exist. Says (imho )more than parameter "1" does not exist.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

That's an interesting idea... maybe I'll do it (or maybe not... it requires so many more function calls)

We're all sure that @, ? and : have no meaning in SQL, right?
User avatar
Nathaniel
Forum Contributor
Posts: 396
Joined: Wed Aug 31, 2005 5:58 pm
Location: Arkansas, USA

Post by Nathaniel »

I would think that as long as they aren't escaped by mysql_real_escape_string or be likely to occur in a non-bind fashion*, it wouldn't matter.

* As in, you wouldn't want to use just numbers with nothing else, for example, because you might want to select "WHERE `flag` = '1'". But, duh.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

@ is a variable reference in MySQL.
Post Reply