How to made "named"-vars?

[garex]

How to made "named"-vars?

To write in query something like "SET bla = :mynamed", passing ['mynamed' => 123]?

Add answer in *.md as a section.

[Synchro]

I'd like to see this too, for two reasons:

• where a parameter is used multiple times in a query it's better not to have to repeat it
• when there are a lot of parameters it's easy to lose track of which parameter corresponds to which placeholder

[colshrapnel]

@Synchro I doubt this feature is that important.
The only case known to me when there are a lot of parameters is already covered by ?u placeholder.
Don't you have a link to such a query by chance?

[Synchro]

I doubt this feature is that important.

Really? I want to switch to entirely parameterised queries, and named parameters is first on my list of wants; I don't want to even start an implementation without them.

?u is only any use for updates. Take an ugly query like:

SELECT * FROM table WHERE f1=1 and f2=2 and f3=3 and f4=4 and (f5=5 or f6=6) or f4 !=1 f3 and f5!=3

Turning that into a dynamic query, we substitute each fixed value for a var and use safemysql format:

"SELECT * FROM table WHERE f1=?n and f2=?n and f3=?n and f4=?n and (f5=?n or f6=?n) or f4 !=?n f3 and f5!=?n", $a, $b, $c, $fd, $e, $f...

As you can see, it's already a total nightmare and completely unreadable. The chances of altering it without making a mistake are quite small, and that's a relatively small query.

For re-use, it would be nice not to have to say:

"SELECT * FROM mytable WHERE a = ?n or b = ?n", $myvar, $myvar

but to be able to say something like (using numbered rather than named parameters):

"SELECT * FROM mytable WHERE a = ?n:0 or b = ?n:0", array($myvar)

and the same thing with named params:

"SELECT * FROM mytable WHERE a = ?n:myvar or b = ?n:myvar", array('myvar' => $myvar)

In things like CRM systems which tend to be field-heavy, it's not uncommon for queries to have hundreds of terms, often repeating values. Trying to alter them using only positional parameters is extremely error-prone.

Objective C does much the same thing, when using named arguments to functions, and even in their simple examples readability is vastly improved.

I don't care at all if it makes the syntax longer, it makes it far more maintainable, and it's not as if parameterised queries are a performance measure anyway.

[garex]

I doubt this feature is that important.

Associative arrays was invented, because we are people and not machines. The art of readable code is those, where all is named right. If we left something like $var1, $var2, $var3 and etc. instead of human-readables vars like $name, $sex, $birthday, then we will sin.

Those, who ignores readabilty should know, that a good old pan is already waiting him in the hell :)

[colshrapnel]

Look, @Synchro
What I really were after is a real life example. Something right out of the code. It helps me to realize the problem.
Your current example looks quite redundant to me. Let's make it a bit readable and see:

WHERE age = 1 and name='Bob' and sex=3 and cat=4 and so on

What you are asking for would be

WHERE age = :age and name=:name and sex=:sex and cat=:cat

Note all the duplicates. An age = ?i statement already has it's name from the field name.

(you know, all these duplicates from long and windy inserts using PDO (when one have to name their field six to ten times, with all this assigning and binding) is made me to create this library. I hate duplicates.)

So, the only problem remains is order. But this query looks like a conditional search. Yet such searches I always had constructed out of a series of conditions using parse(), not in a single piece:

$w = array();
$where = '';
if ($one) $w[] = $db->parse("one = ?s",$one); 
if ($two) $w[] = $db->parse("two IN (?a)",$two);
if ($tre) $w[] = $db->parse("tre <= ?i",$tre);
if (count($w)) $where = "WHERE ".implode(' AND ',$w);
$data = $db->getArr("SELECT * FROM table ?p LIMIT ?i,?i",$where, $start,$per_page);

As you can see, the final query takes only 3 placeholders and order is not a problem.

That's why I asked for a real life example - as I never met a case where named placeholders really were of any significant help myself, I suppose it's more a matter of taste rather than a real necessity.

Though I am thinking of the implementation anyway, as this taste is quite common.
Still unsure of the format, what it have to be. Do you have any suggestions? @garex do you have one in mind?

[garex]

@colshrapnel see http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html for examples and arguments. It's a 1st google result for "named prepared statement". Also there we also have a syntax, as I described in issue.

[colshrapnel]

The very idea of this lib is type-hinted placeholders. So, if you prefer typeless :mynamed, you can use conventional PDO.

[garex]

Yes, I see that we have interesting conflict here. But difficulties are given to us to overcome them.

[colshrapnel]

Oh, this is so true. Go on, start with overcoming then.

[Synchro]

I quite agree that this library is about type-hinted placeholders, but we're not asking to go typeless, but to add naming or numbering to type-hinted params. Even if you do want to go typeless, PDO is not a good way to go since it only get parameterised queries via prepared statements which are an unnecessary and inconvenient (though oddly popular) overhead.

I take your point about using parse, but generally I deal with single large queries, building them out of pieces like you suggest only rarely.

I don't get why there should be any objection to this - it results in more readable, DRYer, safer code, and isn't that the point of the library? It's not as if parameterisation is a performance measure. There's a nice simple implementation of named parameters for sprintf in the PHP docs; we shouldn't need it to be much more complex than that.

[mihai-vlc]

Hello, i'm planning to use this class in one of my projects and i'm trying to contribute
i saw this issue so i tried to implement it in a forked version of this project(great one in my view),
you can find the forked version in my repos( https://github.com/ionutvmi/safemysql ) it still requires some serious testing and i could use your feedback on it...