I'm learning to develop some simple functions retrieving data from database. I found db_query is the right thing to use, however I couldn't even make the following simple code work:

<?php
$result
= db_query('SELECT name FROM {users} WHERE uid = %d', 2);
print
$result->name;
?>

If I delete "->name" from the last line, the system says "recoverable fatal error: Object of class mysqli_result could not be converted to string in C:\wamp\www\poedemo\includes\common.inc(1655) : eval()'d code on line 3."

Maybe I should have some knowledge about object-oriented programming?
Thanks.

Comments

dougstum’s picture

db_query returns a database query result resource, which encodes a set of rows. you'll need to fetch each "row" in the query result using db_fetch_object or db_fetch_array.

<?php
$result
= db_query('SELECT name FROM {users} WHERE uid = %d', 2);
while (
$obj = db_fetch_object ($result) ) {
  print
$obj->name
}
?>

if you want to use array notation, then use db_fetch_array and print $result["name"].

And if you know your query returns a single value (that is, the value of one cell in the table) you can combine this way:

<?php
$name
=db_result(db_query('SELECT name FROM {users} WHERE uid = %d', 2));
print
$name;
?>

Doug Stumberger

wdfy’s picture

Thank you!

hedel’s picture

I'm following this examples and i have this error "Parse error: syntax error, unexpected '}' in ...\includes\common.inc(1699) : eval()'d code on line 5"... I'm new in this with PHP, can somebody help to understand where is my error? (I review the SQL sentence and is ok, i ahve that table, and field and row)

thanks

___________________
Hédel Nuñez Bolívar
http://www,hedmon.com/
********************

siliconmeadow’s picture

If you copied and pasted the code above (dougstum's comment), there's a missing semicolon, and that could be the source of your error. Here is the code in it's entirety with the fix:

<?php
$result
= db_query('SELECT name FROM {users} WHERE uid = %d', 2);
while (
$obj = db_fetch_object ($result) ) {
  print
$obj->name;
}
?>

HTH

spineless’s picture

I tried using the db_fetch_object in Drupal 7 and it is not working.

In my case all I want to achieve the following:

1. Identify select the last book that was created by the user who is logged in.
2. Select the last node that was created by the user who is logged in.

Right now I am just attempting to access the node and book table in the database. I attempted to use the code produced by siliconmeadow... noted below :

<?php
$result
= db_query('SELECT name FROM {users} WHERE uid = %d', 2);
while (
$obj = db_fetch_object ($result) ) {
  print
$obj->name;
}
?>

My version looks like this:

<?php
function select_last_node_created() {
 
$result = db_query('SELECT nid, title FROM {node}');
 while (
$obj = db_fetch_object ($results)){
   print
$obj->nid
   print
$obj->title;
   }
?>

I have a number of questions.
1. Is there a definition of the arguments for db_query? I do not understand the arguments "Select nid" or "title FROM {node}"..... I saw these terms in the database API.
http://drupal.org/node/310072
I do not know how to manipulate the arguments to meet my individual needs. The database API does not explain it well enough for me... I am rather new to PHP coding and the Drupal nomenclature.
2. When I attempt to run the above code I get an error message:

Fatal error: Call to undefined function db_fetch_object() in ....sites\all\modules\my_rules\add_child.rules.inc on line 57


3. How do I select the last book created by the current user from the {book} table of the database?
4. How do I update the {BID} of the last node created by the current user ?...Which table would I modify and how?.... I want to make the last node created by the current user a child of the last book created by the current user.

By the way.... I am creating this as a rules 2 enabled module.

Thanks,

Spineless

Spineless
iyerdevil’s picture

i am new to drupal and php
I copied the same code and pasted i.e

<?php
$result
= db_query('SELECT * FROM {users} WHERE uid = %d', 2);
while (
$obj = db_fetch_object ($result) ) {
  print
$obj->name;
}
?>

but i am getting a unique error
Recoverable fatal error: Argument 2 passed to db_query() must be an array, integer given,
please help.

Jaypan’s picture

If you look, the original post is for Drupal 6, and you are using Drupal 7. Which means you should be starting a new thread.

aosiname’s picture

<?php
$result
= db_query('SELECT name FROM {users} WHERE uid = %d', 2);
print
$result->name;
?>

print $result->name means it is getting the result from the SQL query called 'name' as you ran a "Select NAME from users" query

if you had said select * from users

you could retrieve other results from the query in the same way

ie if the users table has values called "name, email, pass, id"

you can retrieve them individually using

print $result->name;
print $result->email;
print $result->pass;

etc

$result-> rightly throws an error because you are trying to refer to 'nothingness'

ohnobinki’s picture

I think that you didn't look closely enough at the code sample...

One is never to directly access the value returned by db_query().

--
binki

Stoob’s picture

This is a very helpful and enlightening post for those new to drupal.

Thanks.

CiviCRM specialist

sklein121’s picture

I'm trying to get the zip code of users from the visitor info module and feed it into a view, using a PHP custom field.

This is the code I'm working on, but i can't seem to get more than 1 value...any suggestions?

<?php
$result
= db_query("SELECT zip FROM {visitorinfo} WHERE ip = '%s' ORDER BY RAND() " , "$data->votingapi_vote_node_percent_vote_vote_source"));
 
print
$result;

while (
$obj = db_fetch_object ($result)){


print
$obj->zip;

  }
?>

Thanks!!

RoloDMonkey’s picture

I'm surprised you got back any result.

First things first, print $result; will not get you anything, because the $result is an object that can't be converted to a string. You should have got any ugly error when you tried that.

Second, get rid of the double quotes on your second argument.

<?php
$result
= db_query("SELECT zip FROM {visitorinfo} WHERE ip = '%s' ORDER BY RAND() " , $data->votingapi_vote_node_percent_vote_vote_source));
while (
$obj = db_fetch_object ($result)) {
  print
$obj->zip;
}
?>

--

Read more at iRolo.net

jayjaydluffy’s picture

Hello,

I've been developing simple modules in Drupal 6 for some time now, but I have an which I really don't know why this happened and how to treat it.

<?php
 $update_role
= db_query("INSERT INTO {users_roles} (uid, rid) VALUES (%d, %d)", $uid, $ba_rid);
?>

I have that line of code in my module inside hook_user. The code shall run when a new user is created after signing up a form that gives him special role in the site. However, changes is not made in user_roles table. I have $uid, $ba_rid printed and those are correct variables with correct values. I also added and printed db_affected_roles and $update_role on another drupal_set_message and it says, 1 and 1, which means 1 row affected and query is successful. However, the changes wasn't made on the table.

Can anyone help me out? This could not be the right place to post this, and I apologize for that.

Thanks!

RoloDMonkey’s picture

Please do not reply to existing threads with a completely different question. If you want better responses, you should start a new thread.

--

Read more at iRolo.net

spineless’s picture

I tried using the db_fetch_object in Drupal 7 and it is not working.

In my case all I want to achieve the following:

1. Identify select the last book that was created by the user who is logged in.
2. Select the last node that was created by the user who is logged in.

Right now I am just attempting to access the node and book table in the database. I attempted to use the code produced by siliconmeadow... noted below :

<?php
$result
= db_query('SELECT name FROM {users} WHERE uid = %d', 2);
while (
$obj = db_fetch_object ($result) ) {
  print
$obj->name;
}
?>

My version looks like this:

<?php
function select_last_node_created() {
 
$result = db_query('SELECT nid, title FROM {node}');
 while (
$obj = db_fetch_object ($results)){
   print
$obj->nid
   print
$obj->title;
   }
?>

I have a number of questions.
1. Is there a definition of the arguments for db_query? I do not understand the arguments "Select nid" or "title FROM {node}"..... I saw these terms in the database API.
http://drupal.org/node/310072
I do not know how to manipulate the arguments to meet my individual needs. The database API does not explain it well enough for me... I am rather new to PHP coding and the Drupal nomenclature.
2. When I attempt to run the above code I get an error message:

Fatal error: Call to undefined function db_fetch_object() in ....sites\all\modules\my_rules\add_child.rules.inc on line 57


3. How do I select the last book created by the current user from the {book} table of the database?
4. How do I update the {BID} of the last node created by the current user ?...Which table would I modify and how?.... I want to make the last node created by the current user a child of the last book created by the current user.

By the way.... I am creating this as a rules 2 enabled module.

Thanks,

Spineless

Spineless
DanZ’s picture

Wow. Wherever that bad code came from, never go there again.

For starters, don't use database functions when you don't need to. Drupal already defines a very complete API for dealing with nodes. Use that.

Code that replicates what you mentioned would be:

<?php
 
// Load *all* nodes
 
$nodes = node_load_multiple(FALSE);
  foreach (
$nodes as $nid => $node) {
    print
$nid;
    print
$node->title;
  }
}
?>

If you want the very latest node created, don't use SQL to find it. Use an EntityFieldQuery instead. Here's one way. (There's probably a way to take the MAX creation date, but that's your research project.)

<?php
function select_last_node_created() {
 
$efq = new EntityFieldQuery();
 
// Get a list of nodes in descending  creation date order.
 
$result = $query
   
->entityCondition('entity_type', 'node')
    ->
propertyOrderBy('created', 'DESC')
    ->
execute();
  if (empty(
$result['node'])) {
    return
NULL;
  }
 
$nids = array_keys($result['node']);
 
// Get the ID of the first (newest) node.
 
$nid = reset($nids);
 
// Return fully populated node.
 
$node = node_load($nid);
  return
$node;
}
?>

I'm afraid that I don't know about the book module or database structure. You'll want to find out if books are entities and whether they have an API defined.

If you run into a situation where you simply have to use DB queries to get the job done, have a good long read of http://drupal.org/developing/api/database, first. That will answer your questions. Once you do, you'll see:

Don't use db_query(). use db_select(), instead.

Also, don't use db_fetch_object() (whatever that is). Use $result->fetch() or result->fetchAll() instead. See http://drupal.org/node/1251174 for a list of ways to get data back from your query.

--
www.ztwistbooks.com. Math books that are actually fun.

DanZ’s picture

Other notes.

db_select() is documented at api.drupal.org, and easy to find on Google.

Load books with book_get_books().

Records that are entities can be saved with entity_save(). If they aren't entities, you can use drupal_write_record() or db_update().

Finally, have a look at book_update_bid(), including the source code. It should prove illuminating.

--
www.ztwistbooks.com. Math books that are actually fun.

saladtech’s picture

Finally got it working, and it appears that db_fetch_object() has been removed from D7

while ($row = db_fetch_object($result)) {
      $user_settings[$row->uid] = $row->setting;
    }

Replace this part with

foreach ($result as $row) {
      $user_settings[$row->uid] = $row->setting;
    }

Source - https://drupal.org/node/1034288