Welcome Guest, Not a member yet? Register   Sign In
Query Binding
#1

[eluser]zyzzzz[/eluser]
To do a query binding, you have to use this syntax:
Code:
$sql = 'SELECT ? FROM ? LIMIT ?';
$query = $this->db->query($sql, array('username', 'users', 10));

However, I would like to use some syntax like this:
Code:
$sql = 'SELECT ? FROM ? LIMIT ?';
$this->db->bind('username', 'users', 10);
$query = $this->db->query($sql);

Is it possible to extend the active record class to acheieve this?
#2

[eluser]MEM[/eluser]
Same question here.

Regards,
Márcio
#3

[eluser]alboyd[/eluser]
Just out of curiosity can you tell me why you would want to do this? I'm scratching my head...
#4

[eluser]MEM[/eluser]
Well the point is to use the benefits of Active Records and the benefits of Data Binding all together. It seems a nice feature to me.

More then looking at the code exemplified, the main point, I believe, is asking if there is any combination between Active Record way of querying and data bindings and, if so, how can we accomplish that ?




Regards,
Márcio
#5

[eluser]bretticus[/eluser]
[quote author="James Brauman" date="1250015057"]To do a query binding, you have to use this syntax:
Code:
$sql = 'SELECT ? FROM ? LIMIT ?';
$query = $this->db->query($sql, array('username', 'users', 10));

However, I would like to use some syntax like this:
Code:
$sql = 'SELECT ? FROM ? LIMIT ?';
$this->db->bind('username', 'users', 10);
$query = $this->db->query($sql);

Is it possible to extend the active record class to acheieve this?[/quote]

How is the first example not binding?

I think alboyd's question is the same as mine. What significant benefit do you get from the syntax in the second form? Yes, there's some separation from the query call and the actual binding, but your pseudo code in the $sql variable is useless without binding, so why is it important to have that separation?

The nice thing about codeigniter is you can rewrite it how you like. It's just PHP code. I say rewrite because I believe the data class is one of the core libs that you can't extend in the usual way. It would most likely take some hacking of the CI core to create the alternate syntax. A seasoned PHP developer familiar with OOP experience ought to be able to look under the hood and extend the database library. But I doubt anyone has done this before so you are probably on your own.
#6

[eluser]Unknown[/eluser]
I like something like this like prepare statements




$sql = 'SELECT ? FROM ? LIMIT ?';
$this->db->bind('username1', 'user1', 10);
$this->db->bind('username2', 'user2', 10);
$this->db->bind('username3', 'user3', 10);
$this->db->bind('username4', 'user4', 10);
$query = $this->db->query($sql);
#7

[eluser]axelitus[/eluser]
I would love to see this implemented!

[quote author="alboyd" date="1251261182"]Just out of curiosity can you tell me why you would want to do this? I'm scratching my head...[/quote]
The answer is in this post: http://crynobone.com/codeigniter-for-ora...erecord/4/
Quote:As a programmer, when writing a long SQL with multiple bind parameter you will have some headache to manually calculate the position of each parameter.

So it's better to use named query bindings where you would have something similar as this:
Code:
$sql = 'SELECT @column FROM @table LIMIT @limit';
$this->db->bind('@column', 'username');
$this->db->bind('@table', 'users');
$this->db->bind('@limit', 10);
$query = $this->db->query($sql);

This way it would be so much easier to manage the code as it is much more clearer.

Taking flavin example:
[quote author="flavin" date="1267938719"]
Code:
$sql = 'SELECT ? FROM ? LIMIT ?';
$this->db->bind('username1', 'user1', 10);
$this->db->bind('username2', 'user2', 10);
$this->db->bind('username3', 'user3', 10);
$this->db->bind('username4', 'user4', 10);
$query = $this->db->query($sql);
[/quote]

I would not know what this would be used for as your are generating 4 different queries binding all parameters 4 times using different values... it would be much easier using a for loop at PHP level:
Code:
$query = array();
$sql = 'SELECT ? FROM ? LIMIT ?';
for($i = 1; $i<5; $i++) {
  query[$i] = $this->db->bind("username{$i}", "user{$i}", 10);
}

So you would end up with all four query results in the array...

Hope this helps to get some attention to named parameter binding in queries using Code Igniter! It would be so helpful...




Theme © iAndrew 2016 - Forum software by © MyBB