Bound and Unbound Database Queries (what is the difference?)

[j.smith1981]

I am a bit stuck on understanding the differences between bound and unbound database queries.

 

Actually I am studying PDO and MySQLi in PHP and just fancied knowing what the difference between the 2 actually is, can not seem to find the actual solid differences between the 2.

 

Can someone help me or point me in the right direction to finding out the actual differences?

 

Thanks ever so much,

Jeremy.

[Adam]

Between PDO and MySQLi? There's no difference in the end really, it's just the API that's different. Personally out of the two I prefer PDO; more features, better API, better support for statements, etc. You're better off just experimenting with both of them and seeing which you're more suited to.

[Adam]

Ah sorry- I read the title again and you're on about the difference between bound and unbound queries (prepared statements). Prepared statements, besides being SOL-injection safe, are cached on the database server to make repeat queries faster. By cached I don't mean the results though; when you query a database it creates an execution plan, a plan of how it will perform the query, what resources it will need, etc. Normal queries have to recreate the same execution plan every time they're executed. Prepared statements cache this plan so that on the next query, it knows exactly how to do it.

[thehippy]

Are you talking about prepared statements (having bound parameters)?  Prepared statements are pre-parsed by the SQL server and so if you're say INSERTing a lot, cuts down on the overhead by the server of parsing each individual INSERT that would normally happen.  I was doing Java before PHP and it was common practice with jdbc, PHP didn't have any support for prepared statements until ADOdb implemented it then the database extensions slowly began implementing it, its very handy.

 

PHP Manual PDO Prepared Statements

 

... Adam beat me to it.. oh wells

[j.smith1981]

I do understand that now yes but what's the difference between an ubound and a bound query?

 

I mean I know it's quite a broad topic, on some sites on the web they go on about either objects being bound to data, but my argument would be that wouldn't it always be bound to data anyways?

 

Why would you go about using an unbound query though by comparison?

 

Apologies if I sounded a bit confusing there, this is what I am really wanting to know, how to better suit my queries for what circumstance you see.

 

Thank you though for your replies,

Jeremy.

[Adam]

The down-side to prepared statements is that they only exist while the connection is alive - so in a stateless language like PHP it's per-request unless you have persistent connections. Also prepared statements do generally require a little more typing, and a better understanding of database queries in general, which is why people don't use them 100% of the time (especially beginners).

[j.smith1981]

Arragghhh I can't believe I forgot that.

 

If I was working on a list of users and wanted someone to add one say, that text box I enter say their username would be bound to that/those tables right? (I think please correct me if I am wrong).

 

Or I was searching over one field, this is where I get confused you see if I was searching over multiple columns in a table or tables would that be unbound or bound? It's when their bound to one particular data source, so when you add users its going to be a bound query if I think I am searching then that would be an unbound.

 

I think but please could someone correct me if I am wrong?

 

Look forward to any replies if any,

Jeremy.

[j.smith1981]

Would this be a case of an unbound query the code below:

 

<?php
$connect = new mysqli('localhost', 'myuser', '', '');

if(mysqli_connect_errno()) {
  die(printf("Failed for the error code: %d and %s", mysqli_connect_errno(), mysqli_connect_error()));
}

echo "<p>Connection worked!</p>\n";

$result = $connect->query("SELECT * FROM zend_dwoo_books_author");

echo "<p>The books found are named:<br>\n";
while($row = $result->fetch_array()) {
  printf("<b>Book:</b> <em>%s</em> <b>was written by</b> <em>%s</em><br>\n", $row[1], $row[0]);
}
echo "</p>\n";

$result->close();

$connect->close();

 

Just wanted to ask if someone could advise me if I am correct or not I would very much appreciate that.

 

Thanks again in advance,

Jeremy.

[Adam]

"Bound queries" are called prepared statements. You were right though, the example you gave is not a prepared statement.

 

With MySQLi you need to use the prepare() method to create a statement object, that you can then bind variables to and execute. Generally the API uses methods like prepare() and execute() as opposed to query() which makes it easy to identify prepared statements.

 

That's not always the case though, and sometimes libraries will wrap normal queries within a prepared statement automatically.

[j.smith1981]

Yea suppose they would really.

 

It's weird how you say prepared statements (which of course you have every reason to call them that)

 

But I think in my head because I started off understanding this stuff in Access they call them unbound or bound queries.

 

I mean if you are updating records or inserting data into a data base then that's when (as I understand it) you would use a bound (prepared statement), if you where searching over multiple rows and the form field wasn't to be filled in (this is how I differentiate them now) then that would be an unbound query when you would just use query();

 

Is there anycase on when you would input into a database an unbound, logically in my head that would not make sense but is there?

 

Thanks ever so much for going over this with me makes allot more sense.

[Adam]

I would forget what you have learnt in Access terminology. While it is a loosely based SQL-package, the support for standards isn't great.

 

A prepared (...) will finish this post off when I get home, my phone screwed it up!

[gizmola]

One of the confusing aspects of this is the idea of "bind" variables.  The process of "binding" a variable involves telling the libraries involved that a particular variable is bound to a placeholder in a query.  Once this "binding" occurs, as successive query operations occur, data is moved back and forth depending on the type of query, without actually having to pass the variables again as parameters or having them receive the result of a function call.

 

This can be confusing to people who are used to the more specific  $row = query_function().  In many rdbms's this is the only way to interface with stored procedures, so if you were using oracle in the old days, you absolutely had to use bind variables to be able to pass parameters to stored procedures or get the results.

[j.smith1981]

I think I will just use what I think is better, then maybe critique it and then work out if I should use bind/bound our unbound queries.

 

It's great though the help I am getting very much appreciated!

[Adam]

As I was going to say yesterday, I think Access has confused your terminology a bit. Bound/unbound means to me whether or not the statement has variables bound to it, which is optional but irrelevant, it would still be a prepared statement.

 

While ultimately achieving the same result, they're a different concept to standard queries. They're programmed differently and handled by the database server differently. You can't go from one to the other by not binding parameters or executing an INSERT instead of a SELECT, they're just not the same.

 

Best cheesy comparison I can think of right now is a push-bike and a motorbike... They look fairly similar and both work to the same goal, but function very differently.

 

then maybe critique it and then work out if I should use bind/bound our unbound queries.

 

Unless you're repeating the same query a lot, you probably won't notice much difference in terms of performance. The obvious benefit though is that you don't have to worry about SQL injection, and you'd also be covering yourself for future developments that may re-use the statement.

 

If you can I would say use them unless you encounter a specific situation where you think a regular query would be better.

[gizmola]

The other thing about Access, is that it has no server, so it's fundamentally a different approach.  Each client has the access engine in memory, and opens the data files.  On a fileserver,  there is a provision for locking, but that is also done in the client engine. 

 

Trying to relate most access concepts to mysql is comparing apples and oranges, as they are fundamentally different animals, and the terminology is probably not going to match up well in most cases, due to this profound difference.  Access like most other PC databases (Dbase, Paradox etc) were designed to be single user systems, running on one workstation with all the files local.  They each were extended with fairly simple file locking code so that multiple users could share databases, but there are very few scenarios these days where one would recommend using a PC database when relational database management systems are so prevalent and there are now several free/opensource options.  Even microsoft tends to supply a sqlserver in its business products that require sql database storage.

 

RDBMS's have a server which takes sql queries and handles all the details internally, returning results via its client library.  Clients never touch the data files directly.  The closest thing to Access in the opensource/php world would be sqllite.

 

 

[j.smith1981]

Oh I am well aware of that Access is a desktop based database application (not server), but it does illustrate on a very simple term what data binding is, like for a good example (sorry just trying to explain where I was going with this, bear with me lol, really appreciate your replies though!) is when you go into a form a data entry form it has usually all the records there for you when you go through forms designer or whatever it's called where all the fields in the database table it's assigned to insert rows on, will be filled up, having them prefilled with information is Access's way of simple data binding.

 

But they hold the same basic principle in some ways, you assign a piece of data to an attribute on the database, that's what I was getting it.

 

But Access does have in all fairness the ability to act like a server just the way it stores information ie as you so rightly puts data into a single file on the file system, I was working at Natwest bank a  few years ago EMIS their reporting tool for account merchants was done on an Access database going across their network and authenticated to the merchants, so other's of course would have no access to this, albeit extremely slow, Access can only handle a maximum of about 20 clients at any one time, compared to about what 32,000+ clients MySQL and say Oracle can do.

 

But it can enforce relational integrity unlike SQLite but it's really meant for as I know fair well for desktop single user based logins (if you allow for that, but Access is a pain in the backside restricting users access to the file database).