I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on. Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%'; I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute(); The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it. -- Earnie -- https://sites.google.com/site/earnieboyd
Two things: 1. Don't use db_select just because you're converting a module. You are adding overhead that doesn't need to be there. Event the authors of DBTNG agree with this statement. Stick to db_query unless you need to generate SQL dynamically. 2. Change the variable prior to binding it. Don't do token replacement at all. The idea is that the second parameter of the condition method should be that variable that contains the array, not a token replacement thing so if you're trying to make a wild card like, just concat like this: db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condition('mystring', '%' . $somrvariable . '%', LIKE) ->execute(); Dave -----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Thursday, May 24, 2012 1:28 PM To: support@drupal.org Subject: [support] db_select()->condition() I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on. Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%'; I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute(); The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it. -- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ]
On Thu, May 24, 2012 at 4:56 PM, Metzler, David wrote:
Two things:
1. Don't use db_select just because you're converting a module. You are adding overhead that doesn't need to be there. Event the authors of DBTNG agree with this statement. Stick to db_query unless you need to generate SQL dynamically.
I understand that db_select builds a string that is eventually passed to MySQL but what overhead is added? Can you point to threads of discussion?
2. Change the variable prior to binding it. Don't do token replacement at all. The idea is that the second parameter of the condition method should be that variable that contains the array, not a token replacement thing so if you're trying to make a wild card like, just concat like this:
db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condition('mystring', '%' . $somrvariable . '%', LIKE) ->execute();
Ok, thanks. It just doesn't seem Drupalish, I would have expected an optional 4th argument to provide token substitution. Shouldn't it be check_plain($somevariable) then? Probably depends on how $somevariable gets a value. -- Earnie -- https://sites.google.com/site/earnieboyd
As far as the overhead of dynamic queries, see this issue starting at comment #8: http://drupal.org/node/835068#comment-3125136 You don't have to do a check_plain on your field. PDO knows what type of field is being populated and automatically prepares them as needed. This all happens up in the driver and invoked by PDO::Prepare(). In DBTING, the prepare is called when you do the ->execute(), so you don't have to worry about calling it. Jamie Holly http://www.intoxination.net http://www.hollyit.net On 5/25/2012 8:04 AM, Earnie Boyd wrote:
On Thu, May 24, 2012 at 4:56 PM, Metzler, David wrote:
Two things:
1. Don't use db_select just because you're converting a module. You are adding overhead that doesn't need to be there. Event the authors of DBTNG agree with this statement. Stick to db_query unless you need to generate SQL dynamically.
I understand that db_select builds a string that is eventually passed to MySQL but what overhead is added? Can you point to threads of discussion?
2. Change the variable prior to binding it. Don't do token replacement at all. The idea is that the second parameter of the condition method should be that variable that contains the array, not a token replacement thing so if you're trying to make a wild card like, just concat like this:
db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condition('mystring', '%' . $somrvariable . '%', LIKE) ->execute();
Ok, thanks. It just doesn't seem Drupalish, I would have expected an optional 4th argument to provide token substitution. Shouldn't it be check_plain($somevariable) then? Probably depends on how $somevariable gets a value.
The question about check plain vs. non-check plain is really about the data and how it is used. Check plain is generally not used in the condition db-comparison layer because that's not the point where you're trying to prevent cross site scripting vulnerabilities. Rather that happens at presentation. In general drupal's strategy has been to sanitize data prior to presentation, not prior to storage. So until you're passing stuff to the presentation layer, you wouldn't normally run stuff through check_plain. Check_plain does not provide SQL Injection protection, and since the data is stored in the raw, you don't want to run checkplain on something before the comparison of that value in the condition. The SQL injection protection is all handled internally within DBTNG and db_query now, and check_plain never performed this protection anyway. The idea behind DBTNG is to get away from token substitution and into an object-relational mapping (ORM) style of coding. So this really is the new drupal way. http://drupal.org/node/1067802#comment-4169252 is one of the places where you'll find information posted by crell on this topic. Here's another interesting stack exchange article that lists some reasons to use db_select: http://drupal.stackexchange.com/questions/1200/given-that-db-select-is-much-... Hope that helps, Dave -----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Friday, May 25, 2012 5:05 AM To: support@drupal.org Subject: Re: [support] db_select()->condition() On Thu, May 24, 2012 at 4:56 PM, Metzler, David wrote:
Two things:
1. Don't use db_select just because you're converting a module. You are adding overhead that doesn't need to be there. Event the authors of DBTNG agree with this statement. Stick to db_query unless you need to generate SQL dynamically.
I understand that db_select builds a string that is eventually passed to MySQL but what overhead is added? Can you point to threads of discussion?
2. Change the variable prior to binding it. Don't do token replacement at all. The idea is that the second parameter of the condition method should be that variable that contains the array, not a token replacement thing so if you're trying to make a wild card like, just concat like this:
db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condition('mystring', '%' . $somrvariable . '%', LIKE) ->execute();
Ok, thanks. It just doesn't seem Drupalish, I would have expected an optional 4th argument to provide token substitution. Shouldn't it be check_plain($somevariable) then? Probably depends on how $somevariable gets a value. -- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ]
For others who are later looking at this thread, one consideration is that db_select()->execute() returns an array of rows of objects matching the select. You could quickly consume all memory pulling all nodes so don't do something stupid like $nodes = db_select('nodes', 'n')->fields('n')->execute(); To process one row at a time from the DB you must use db_query() which has an appropriate fetch() method to retrieve the data from the DB one row at a time. Thanks for the responses David and Jamie, sounds like a heated debate but I now understand when db_select should be used and when not to. -- Earnie -- https://sites.google.com/site/earnieboyd
No, no, no. SelectQuery::execute() does not return an array. Execute (at least for select queries) returns the result of DatabaseConnection::query which ends up returning the DatabaseStatement. To get an array of all results you'd have to call fetchAllAssoc() on the the resulting DatabaseStatement. If you'd actually read the docs and the code you'd see that SelectQuery::execute and db_query() actually return the *exact same thing*. The real answer is that since both SelectQuery::execute and db_query end up calling and returning the same thing, is that the method for getting the query to execute with SelectQuery is much more convoluted, while helpful, each call to a method of SelectQuery, such as condition(), fields(), orderBy(), join(), etc takes additional time to execute, and then must be compiled into a string by calling __toString() on the query, the args have to be gathered by calling getArguments(), and so on. Please look at the API docs and code before posting misinformation to the list. -Mike __________________ Michael Prasuhn http://mikeyp.net On May 25, 2012, at 1:16 PM, Earnie Boyd wrote:
For others who are later looking at this thread, one consideration is that db_select()->execute() returns an array of rows of objects matching the select. You could quickly consume all memory pulling all nodes so don't do something stupid like
$nodes = db_select('nodes', 'n')->fields('n')->execute();
To process one row at a time from the DB you must use db_query() which has an appropriate fetch() method to retrieve the data from the DB one row at a time.
Thanks for the responses David and Jamie, sounds like a heated debate but I now understand when db_select should be used and when not to.
-- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ]
On Fri, May 25, 2012 at 5:07 PM, Michael Prasuhn <mike@mikeyp.net> wrote:
If you'd actually read the docs and the code you'd see that SelectQuery::execute and db_query() actually return the *exact same thing*.
Uh, no it does not return the same thing or I would be able to do db_select()->execute()->fetch(). You must use foreach(db_select()->execute() as $row) to get the data and the data type of the first parameter of foreach is an array. -- Earnie -- https://sites.google.com/site/earnieboyd
On May 25, 2012, at 2:16 PM, Earnie Boyd wrote:
Uh, no it does not return the same thing or I would be able to do db_select()->execute()->fetch().
You must use foreach(db_select()->execute() as $row) to get the data and the data type of the first parameter of foreach is an array.
Nope, db_query() and SelectQuery::execute() *DO* return the same thing, and it's NOT an array. Just because you can use foreach does not make it an array. Both db_query() and SelectQuery::execute() return the exact same thing: an object of class DatabaseStatmentBase (may be different depending on the database being used) that implements DatabaseStatementInterface. These classes both implement the iterator interface which allows them to be, well, iterated upon as if they were arrays. If you read the code at http://api.drupal.org/api/drupal/includes%21database%21database.inc/function... and http://api.drupal.org/api/drupal/includes%21database%21select.inc/function/S... you can clearly see that they are both calling the same function as their return value. Before you say that the code you suggested doesn't work please give it a try and actually run this: <?php $result = db_query("SELECT * FROM {node}"); print get_class($result) . "\n"; $result_2 = db_select('node', 'n')->fields('n')->execute(); print get_class($result_2); Or this call to fetch() from the result of db_query() which you claim doesn't work: <?php $result = db_query("SELECT * FROM {node}"); var_dump($result->fetch()); $result_2 = db_select('node', 'n')->fields('n')->execute(); var_dump($result_2->fetch()); You'll notice that in the second example the results are identical. You can read more about the Iterator interface at: http://www.php.net/manual/en/class.iterator.php. -Mike __________________ Michael Prasuhn http://mikeyp.net
Please accept my apology. I did try db_select()->execute()->fetch() and was returned an "Undefined method" error but now I try it and it worked. Thanks for the explanations and push Michael. Also the php.net/foreach syntax diagram suggest array_expression as required which is why I was also thinking it must be an array. But the sentence before the diagram states "array or object". The do give a link to php.net/manual/en/language.oop5.iterations.php which gives an example of object iteration. Earnie On Fri, May 25, 2012 at 11:47 PM, Michael Prasuhn <mike@mikeyp.net> wrote:
On May 25, 2012, at 2:16 PM, Earnie Boyd wrote:
Uh, no it does not return the same thing or I would be able to do db_select()->execute()->fetch().
You must use foreach(db_select()->execute() as $row) to get the data and the data type of the first parameter of foreach is an array.
Nope, db_query() and SelectQuery::execute() *DO* return the same thing, and it's NOT an array. Just because you can use foreach does not make it an array.
Both db_query() and SelectQuery::execute() return the exact same thing: an object of class DatabaseStatmentBase (may be different depending on the database being used) that implements DatabaseStatementInterface. These classes both implement the iterator interface which allows them to be, well, iterated upon as if they were arrays.
If you read the code at http://api.drupal.org/api/drupal/includes%21database%21database.inc/function... and http://api.drupal.org/api/drupal/includes%21database%21select.inc/function/S... you can clearly see that they are both calling the same function as their return value.
Before you say that the code you suggested doesn't work please give it a try and actually run this:
<?php $result = db_query("SELECT * FROM {node}"); print get_class($result) . "\n";
$result_2 = db_select('node', 'n')->fields('n')->execute(); print get_class($result_2);
Or this call to fetch() from the result of db_query() which you claim doesn't work:
<?php $result = db_query("SELECT * FROM {node}"); var_dump($result->fetch());
$result_2 = db_select('node', 'n')->fields('n')->execute(); var_dump($result_2->fetch());
You'll notice that in the second example the results are identical.
You can read more about the Iterator interface at: http://www.php.net/manual/en/class.iterator.php.
-Mike
__________________ Michael Prasuhn http://mikeyp.net
-- [ Drupal support list | http://lists.drupal.org/ ]
-- Earnie -- https://sites.google.com/site/earnieboyd
PHP's documentation is sometimes a little quirky when it comes to arrays, because there are arrays, then there are iteratable things (some objects plus arrays), []-accessible things (some other objects plus arrays), etc. It's kinda screwy. :-) For the record, yes, both db_query() and db_select()->execute() give you back an identical statement object. db_select() is more flexible, but adds a dozen or two function calls to the process before you get to the actual query execution. Don't do that unless you need to. Also, the previous code had a security hole. db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condition('mystring', '%' . $somrvariable . '%', LIKE) ->execute(); You actually want db_like($somevariable), which handles DB-specific escaping in LIKE strings. Forgetting to do so is almost the only possible SQL injection attack vector left in Drupal unless you bypass DBTNG entirely. :-) --Larry Garfield, primary author, DBTNG On 05/26/2012 09:47 AM, Earnie Boyd wrote:
Please accept my apology. I did try db_select()->execute()->fetch() and was returned an "Undefined method" error but now I try it and it worked. Thanks for the explanations and push Michael.
Also the php.net/foreach syntax diagram suggest array_expression as required which is why I was also thinking it must be an array. But the sentence before the diagram states "array or object". The do give a link to php.net/manual/en/language.oop5.iterations.php which gives an example of object iteration.
Earnie
On Fri, May 25, 2012 at 11:47 PM, Michael Prasuhn<mike@mikeyp.net> wrote:
On May 25, 2012, at 2:16 PM, Earnie Boyd wrote:
Uh, no it does not return the same thing or I would be able to do db_select()->execute()->fetch().
You must use foreach(db_select()->execute() as $row) to get the data and the data type of the first parameter of foreach is an array. Nope, db_query() and SelectQuery::execute() *DO* return the same thing, and it's NOT an array. Just because you can use foreach does not make it an array.
Both db_query() and SelectQuery::execute() return the exact same thing: an object of class DatabaseStatmentBase (may be different depending on the database being used) that implements DatabaseStatementInterface. These classes both implement the iterator interface which allows them to be, well, iterated upon as if they were arrays.
If you read the code at http://api.drupal.org/api/drupal/includes%21database%21database.inc/function... and http://api.drupal.org/api/drupal/includes%21database%21select.inc/function/S... you can clearly see that they are both calling the same function as their return value.
Before you say that the code you suggested doesn't work please give it a try and actually run this:
<?php $result = db_query("SELECT * FROM {node}"); print get_class($result) . "\n";
$result_2 = db_select('node', 'n')->fields('n')->execute(); print get_class($result_2);
Or this call to fetch() from the result of db_query() which you claim doesn't work:
<?php $result = db_query("SELECT * FROM {node}"); var_dump($result->fetch());
$result_2 = db_select('node', 'n')->fields('n')->execute(); var_dump($result_2->fetch());
You'll notice that in the second example the results are identical.
You can read more about the Iterator interface at: http://www.php.net/manual/en/class.iterator.php.
-Mike
__________________ Michael Prasuhn http://mikeyp.net
-- [ Drupal support list | http://lists.drupal.org/ ]
On Sat, May 26, 2012 at 1:41 PM, Larry Garfield wrote:
Also, the previous code had a security hole.
That's why I was asking in the original post.
db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condition('mystring', '%' . $somrvariable . '%', LIKE) ->execute();
You actually want db_like($somevariable), which handles DB-specific escaping in LIKE strings. Forgetting to do so is almost the only possible SQL injection attack vector left in Drupal unless you bypass DBTNG entirely. :-)
Ah, thanks for that, certainly feels better.
--Larry Garfield, primary author, DBTNG
I'm impressed by it. And whether or not using db_select adds overhead, it does simplify even the simplest query string. My use of it though is in a dynamic situation where I need the likes of extenders for a themed paged list of items. -- Earnie -- https://sites.google.com/site/earnieboyd
It may generate a database compatibility error, but it is not a SQL injection attack vector. If you cannot trust the data passed to the condition in the second parameter, then the function should never be used. In truth data passed to condition is appropriately escaped, and should not represent a SQL injection vector. If I'm wrong about that, then we need to file a security issue with DBTNG and get the condition method fixed. Either you can trust the condition method to properly escape that variable or you can't. My bet is that it won't be different using LIKE vs '=' and that using condition is safe if not optimal for The like clause. That doesn't mean it would function as well, but it is ancillary to my point about how the condition method works. You'd still need to do the string concatenation of the wild card variables for the like method prior to passing it. On an off note using the Like method currently can result compatibility problems with postgres, as it converts it to use ILIKE which will fail against certain data types. I recently had to temporarily hack the database.inc to get a core upgrade script to complete properly with postgres... sigh. -----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Larry Garfield Sent: Saturday, May 26, 2012 10:41 AM To: support@drupal.org Subject: Re: [support] db_select()->condition() PHP's documentation is sometimes a little quirky when it comes to arrays, because there are arrays, then there are iteratable things (some objects plus arrays), []-accessible things (some other objects plus arrays), etc. It's kinda screwy. :-) For the record, yes, both db_query() and db_select()->execute() give you back an identical statement object. db_select() is more flexible, but adds a dozen or two function calls to the process before you get to the actual query execution. Don't do that unless you need to. Also, the previous code had a security hole. db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condition('mystring', '%' . $somrvariable . '%', LIKE) ->execute(); You actually want db_like($somevariable), which handles DB-specific escaping in LIKE strings. Forgetting to do so is almost the only possible SQL injection attack vector left in Drupal unless you bypass DBTNG entirely. :-) --Larry Garfield, primary author, DBTNG On 05/26/2012 09:47 AM, Earnie Boyd wrote:
Please accept my apology. I did try db_select()->execute()->fetch() and was returned an "Undefined method" error but now I try it and it worked. Thanks for the explanations and push Michael.
Also the php.net/foreach syntax diagram suggest array_expression as required which is why I was also thinking it must be an array. But the sentence before the diagram states "array or object". The do give a link to php.net/manual/en/language.oop5.iterations.php which gives an example of object iteration.
Earnie
On Fri, May 25, 2012 at 11:47 PM, Michael Prasuhn<mike@mikeyp.net> wrote:
On May 25, 2012, at 2:16 PM, Earnie Boyd wrote:
Uh, no it does not return the same thing or I would be able to do db_select()->execute()->fetch().
You must use foreach(db_select()->execute() as $row) to get the data and the data type of the first parameter of foreach is an array. Nope, db_query() and SelectQuery::execute() *DO* return the same thing, and it's NOT an array. Just because you can use foreach does not make it an array.
Both db_query() and SelectQuery::execute() return the exact same thing: an object of class DatabaseStatmentBase (may be different depending on the database being used) that implements DatabaseStatementInterface. These classes both implement the iterator interface which allows them to be, well, iterated upon as if they were arrays.
If you read the code at http://api.drupal.org/api/drupal/includes%21database%21database.inc/func tion/db_query/7 and http://api.drupal.org/api/drupal/includes%21database%21select.inc/functi on/SelectQuery%3A%3Aexecute/7 you can clearly see that they are both calling the same function as their return value.
Before you say that the code you suggested doesn't work please give it a try and actually run this:
<?php $result = db_query("SELECT * FROM {node}"); print get_class($result) . "\n";
$result_2 = db_select('node', 'n')->fields('n')->execute(); print get_class($result_2);
Or this call to fetch() from the result of db_query() which you claim doesn't work:
<?php $result = db_query("SELECT * FROM {node}"); var_dump($result->fetch());
$result_2 = db_select('node', 'n')->fields('n')->execute(); var_dump($result_2->fetch());
You'll notice that in the second example the results are identical.
You can read more about the Iterator interface at: http://www.php.net/manual/en/class.iterator.php.
-Mike
__________________ Michael Prasuhn http://mikeyp.net
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Earnie Boyd wrote:
I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on.
Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%';
I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute();
The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it.
I have just read an email to the developer list where you, I felt, were quite rude towards someone who, in your estimation, asked a question on the wrong list. Now I see a question of yours on what I suspect is the wrong list. According to the mailman page for the support list, "If you need help with getting Drupal up and running this is the list for you." It seems to me you are hacking on code, not trying to get Drupal up and running. It happens I agree with you on the distinction between the two lists, I asked a little while ago and this seemed to be the majority view. However, I would not reply to someone's question (or others' answers) just to castigate them for their stupidity. Now to your question: 1. The code you posted contains at least one typo. It has no prospect of working as you showed it. 2. Did you read this? http://api.drupal.org/api/drupal/includes!database!database.inc/function/db_... -- Cheers John Summerfield.
Please refer to the list descriptions: http://drupal.org/mailing-lists/ Support A list for support questions. Development A list for Drupal core developers. Earnie was asking a question for personal development and not core development, so currently he did ask in the right place. There has been past confusion on this and there is actually an issue on D.O. to try and get it resolved: http://drupal.org/node/1163962 Jamie Holly http://www.intoxination.net http://www.hollyit.net On 7/11/2012 6:30 AM, John Summerfield wrote:
Earnie Boyd wrote:
I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on.
Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%';
I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute();
The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it.
I have just read an email to the developer list where you, I felt, were quite rude towards someone who, in your estimation, asked a question on the wrong list.
Now I see a question of yours on what I suspect is the wrong list.
According to the mailman page for the support list, "If you need help with getting Drupal up and running this is the list for you."
It seems to me you are hacking on code, not trying to get Drupal up and running.
It happens I agree with you on the distinction between the two lists, I asked a little while ago and this seemed to be the majority view.
However, I would not reply to someone's question (or others' answers) just to castigate them for their stupidity.
Now to your question: 1. The code you posted contains at least one typo. It has no prospect of working as you showed it. 2. Did you read this? http://api.drupal.org/api/drupal/includes!database!database.inc/function/db_...
I seem to be missing some of the messages on this thread, so I apologize if this question has already been answered more thoroughly. You appear to be confusing db_query syntax with db_select. The query builder does not take tokens. This might help grok db_select. db_select('mytable', 'mt') ->fields('mt', array('mycolumn')) ->condition('mycolumn', '%' . $mystring . '%', 'LIKE') ->execute(); Like is particularly confusing because it basically takes a regular expression inside the quotes. Hope this helps... Dave On 7/11/2012 6:30 AM, John Summerfield wrote:
Earnie Boyd wrote:
I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on.
Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%';
I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute();
The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it.
-- [ Drupal support list | http://lists.drupal.org/ ]
This is an old thread (May 24th) and answered to satisfaction some time ago. David, you responded then also. On Wed, Jul 11, 2012 at 12:05 PM, Metzler, David <metzlerd@evergreen.edu> wrote:
I seem to be missing some of the messages on this thread, so I apologize if this question has already been answered more thoroughly.
You appear to be confusing db_query syntax with db_select. The query builder does not take tokens. This might help grok db_select.
db_select('mytable', 'mt') ->fields('mt', array('mycolumn')) ->condition('mycolumn', '%' . $mystring . '%', 'LIKE') ->execute();
Like is particularly confusing because it basically takes a regular expression inside the quotes.
Hope this helps... Dave
On 7/11/2012 6:30 AM, John Summerfield wrote:
Earnie Boyd wrote:
I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on.
Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%';
I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute();
The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it.
-- [ Drupal support list | http://lists.drupal.org/ ] -- [ Drupal support list | http://lists.drupal.org/ ]
-- Earnie -- https://sites.google.com/site/earnieboyd
Well they say that your memory is the first thing to go.... I can't remember what the second is.... Dave -----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Wednesday, July 11, 2012 11:36 AM To: support@drupal.org Subject: Re: [support] db_select()->condition() This is an old thread (May 24th) and answered to satisfaction some time ago. David, you responded then also. On Wed, Jul 11, 2012 at 12:05 PM, Metzler, David <metzlerd@evergreen.edu> wrote:
I seem to be missing some of the messages on this thread, so I apologize if this question has already been answered more thoroughly.
You appear to be confusing db_query syntax with db_select. The query builder does not take tokens. This might help grok db_select.
db_select('mytable', 'mt') ->fields('mt', array('mycolumn')) ->condition('mycolumn', '%' . $mystring . '%', 'LIKE') ->execute();
Like is particularly confusing because it basically takes a regular expression inside the quotes.
Hope this helps... Dave
On 7/11/2012 6:30 AM, John Summerfield wrote:
Earnie Boyd wrote:
I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on.
Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%';
I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute();
The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it.
-- [ Drupal support list | http://lists.drupal.org/ ] -- [ Drupal support list | http://lists.drupal.org/ ]
-- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ]
Junk Sent from my iPhone On Jul 11, 2012, at 2:53 PM, "Metzler, David" <metzlerd@evergreen.edu> wrote:
Well they say that your memory is the first thing to go.... I can't remember what the second is....
Dave
-----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Wednesday, July 11, 2012 11:36 AM To: support@drupal.org Subject: Re: [support] db_select()->condition()
This is an old thread (May 24th) and answered to satisfaction some time ago. David, you responded then also.
On Wed, Jul 11, 2012 at 12:05 PM, Metzler, David <metzlerd@evergreen.edu> wrote:
I seem to be missing some of the messages on this thread, so I apologize if this question has already been answered more thoroughly.
You appear to be confusing db_query syntax with db_select. The query builder does not take tokens. This might help grok db_select.
db_select('mytable', 'mt') ->fields('mt', array('mycolumn')) ->condition('mycolumn', '%' . $mystring . '%', 'LIKE') ->execute();
Like is particularly confusing because it basically takes a regular expression inside the quotes.
Hope this helps... Dave
On 7/11/2012 6:30 AM, John Summerfield wrote:
Earnie Boyd wrote:
I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on.
Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%';
I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute();
The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it.
-- [ Drupal support list | http://lists.drupal.org/ ] -- [ Drupal support list | http://lists.drupal.org/ ]
-- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ] -- [ Drupal support list | http://lists.drupal.org/ ]
I've been noticing you replying with a lot of "junk" replies. If you no longer wish to receive emails from this list, then I suggest heading to the mailman page and unsubscribing: http://lists.drupal.org/mailman/listinfo/support Jamie Holly http://www.intoxination.net http://www.hollyit.net On 7/11/2012 2:55 PM, Michael Tinney wrote:
Junk
Sent from my iPhone
On Jul 11, 2012, at 2:53 PM, "Metzler, David" <metzlerd@evergreen.edu> wrote:
Well they say that your memory is the first thing to go.... I can't remember what the second is....
Dave
-----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Wednesday, July 11, 2012 11:36 AM To: support@drupal.org Subject: Re: [support] db_select()->condition()
This is an old thread (May 24th) and answered to satisfaction some time ago. David, you responded then also.
On Wed, Jul 11, 2012 at 12:05 PM, Metzler, David <metzlerd@evergreen.edu> wrote:
I seem to be missing some of the messages on this thread, so I apologize if this question has already been answered more thoroughly.
You appear to be confusing db_query syntax with db_select. The query builder does not take tokens. This might help grok db_select.
db_select('mytable', 'mt') ->fields('mt', array('mycolumn')) ->condition('mycolumn', '%' . $mystring . '%', 'LIKE') ->execute();
Like is particularly confusing because it basically takes a regular expression inside the quotes.
Hope this helps... Dave
On 7/11/2012 6:30 AM, John Summerfield wrote:
Earnie Boyd wrote:
I have an existing module I'm trying to convert to DBTNG and have a condition I need a suggestion on.
Original statement is something like SELECT mt.myvar FROM {mytable} mt WHERE mystring LIKE '%%%s%%';
I want to change it to db_select('mytable', 'mt') ->fields('mt', array('myvar')) ->condtion('mystring', '%:mystring%', LIKE) ->execute();
The problem is condition() only takes three parameters, what would you suggest to use to replace :mystring? I'm using t() but that seems like a misuse of it.
-- [ Drupal support list | http://lists.drupal.org/ ] -- [ Drupal support list | http://lists.drupal.org/ ]
-- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ] -- [ Drupal support list | http://lists.drupal.org/ ]
participants (7)
-
Earnie Boyd -
Jamie Holly -
John Summerfield -
Larry Garfield -
Metzler, David -
Michael Prasuhn -
Michael Tinney