1

I am trying to SELECT all the rows in my MySQL where a field matches a variable using the db_query() function. The variable is not a user input, it is retrieved from another database table based on the user currently logged on, so basically the variable is safe, but it does contain hyphens. Obviously without the hyphens the queries won't work properly, but for some reason if I include the variable the database query stops working.

When I say it stops working, I can use the functions in the object that is returned but whenever I try to get individual fields I get an error. In case it affects any suggestions, the query has two more 'comparisons' but without the third, containing hyphens, the query could return more than one row. I only want either one or zero rows to be returned.

Anybody any ideas?

UPDATE: The code I have looks something like this:

global $user;

        $ukey = "A-B-C";

        $result = db_query("SELECT NAME FROM ukeys WHERE uid = :uid AND EMAIL = :email AND GENERATEDKEY = :key", array(
            ':uid' => $user->uid,
            ':email' => $user->mail,
            ':key' => $ukey 
        ));

        if($result->rowCount() == 1){
            $name = $result->NAME;

And the error comes on the bottom line where I try to get the NAME from the results.

  • 3
    do you have an example of the SQL or a print_r() of the result. What you are trying to describe makes little sense. – tenken Jan 4 '13 at 21:44
  • 1
    This question needs the SQL query and subsequent processing code added. – David Thomas Jan 4 '13 at 22:01
  • notwithstanding the first two very good and on point comments, but to hazard a guess, if you are doing something along the lines $query=db_query('SELECT * from {foo} WHERE bar = %s', $bar); and $bar is the variable in question, it will most probably fail if it has hyphens in it because you aren't quoting it in your sql, eg, you should be doing $query=db_query("SELECT * from {foo} WHERE bar = '%s'", $bar); (or whatever the d7 equivalent is, but the point being make sure you are quoting the variable you are comparing against.) – Jimajamma Jan 4 '13 at 22:42
  • 3
    May you show the code? Without seeing that, is impossible to tell you what is wrong with the code. – kiamlaluno Jan 5 '13 at 0:12
  • Ok, I will post the MySQL query and some code as soon as I can today, but first I will also test out whether it is as simple as the comment by @Jimajamma :) – Andy Jan 5 '13 at 8:12
1

Make sure you use proper placeholders in your db queries, as per the Drupal API.

E.g in D7:

$query = db_query("SELECT * from {foo} WHERE bar = :bar", array(':bar' => $bar));

or

$query = db_select('foo', 'f')
  ->fields('f')
  ->condition('bar', $bar, '=')
  ->execute();

More info:

UPDATE: Now the the original SQL has been posted, the problem appears to be

  1. Lack of use of fetchObject, fetchAssoc etc.. on the database result

You should be able to use

if($result->rowCount() == 1){
  $name = $result->fetchObject()->NAME;
}

As an aside, better to limit the query if you only want one record returned, e.g

$name = db_select('ukeys', 'u')
  ->fields('u', array('NAME'))
  ->condition('uid', $user->uid)
  ->condition('EMAIL', $user->mail)
  ->condition('GENERATEDKEY', $ukey)
  ->range(0,1)
  ->execute()->fetchfield();

That will return the $name value only, or FALSE if no record exists.

  • Thank you for your answer but as you will now see I have indeed formatted my query correctly. I just didn't include the curly braces because I don't need the prefix to be added – Andy Jan 5 '13 at 15:21
  • @Andy updated answer posted, your problem appeared to be no ->fetchObject etc.. on the database result resource – David Thomas Jan 5 '13 at 21:44
  • Yep, that appears to be have fixed it thank you. Although how come in my other queries where hyphens aren't including I did not receive the same error without fetchObject()? Also, regarding limiting the query, this isn't what I want to achieve. There should be one, and only one, row that matches the conditions and so if there isn't I need to know because that means an error. :) – Andy Jan 6 '13 at 11:55
  • You won't need fetchObject if you loop over the result resource with foreach. e.g foreach($result as $row){ print $row->uid; } is fine. To check the total count, perhaps better to use a count query. – David Thomas Jan 7 '13 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.