By anthonybol on

This is a similar question as #278324 with some additional complexity.

I'm trying to populate my custom CCK content type field with data for users to select (multi select) when they create the new content. I have found this snippet that does what I want - but obviously not the data I want to be displayed.

$result = db_query_range('SELECT uid FROM {users} WHERE status != 0 ORDER BY uid DESC', 0, 8);
$items = array();
while ($row = db_fetch_object($result)) {
  $account = user_load(array('uid' => $row->uid));
  $items[] = check_plain($account->uid);
}
return $items;

Returned value : Array ( [0] => 4 [1] => 3 [2] => 2 [3] => 1 ) - These are UIDs as expected and it populates the multiselect :-)

What I want to do is something like this but I'm very new to PHP and having trouble with the correct syntax to build the proper array. 

//I need to determine the uid of the logged in user to build the query
global $user;
$uid = $user->uid;

$result = db_query_range('SELECT `content_field_my_classes`.`field_my_classes_value`
AS `classes`
FROM node, content_field_my_classes
WHERE ((`node`.`uid` = '. $uid .')
AND (`content_field_my_classes`.`nid` = `node`.`nid`)) ORDER BY
`classes` ASC');

//Here's where I get lost.  This is essentially the unmodified code from above.  Not sure where to go with it
$items = array();
while ($row = db_fetch_object($result)) {
  $account = user_load(array('uid' => $row->uid)); // I am pretty sure I don't need user_load()
  $items[] = check_plain($account->classes); //What does check_plain() do?  Is this a drupal function or PHP?
}
return $items;

If anyone can help me with the last few lines of the code I would be very appreciative.
Cheers!

⋅ Categories: Drupal 5.x

Comments

anthonybol’s picture

anthonybol commented

Hi ... just wondering if anyone has a suggestion.
Thanks!

j_ten_man’s picture

j_ten_man commented 
//I need to determine the uid of the logged in user to build the query
global $user;
$uid = $user->uid;

//Do you really want a db_query_range here? If you want to get all of the rows/fields where node.uid=$uid
//then you should use db_query and not pass 0, 10 in. Also, we use %d and then let the function handle
//replacing it with $uid to avoid SQL injections.
$result = db_query_range('SELECT `content_field_my_classes`.`field_my_classes_value`
AS `classes`
FROM node, content_field_my_classes
WHERE ((`node`.`uid` = %d)
AND (`content_field_my_classes`.`nid` = `node`.`nid`)) ORDER BY
`classes` ASC', $uid, 0, 10);

//Not positive what you want to do, but this should be close.
$items = array();
while ($row = db_fetch_object($result)) {
  //check_plain is a Drupal function.
  $items[] = check_plain($account->field_my_classes_value);
}
return $items;

A couple of things:
- From the original code that you took from someone, I don't see the point of calling user_load. It didn't do anything in the snippet you provided. Now, there may be some other reason they did it, but it is not obvious why they did. You don't need it, because it loads a user object,and you don't need that from what I understand.
- Try to avoid concatenating strings into your sql query. It is much safer to pass them in as I have done with $uid
- See the API for the check_plain function: http://api.drupal.org/api/function/check_plain/5

anthonybol’s picture

anthonybol commented

@j_ten_man
Thanks for the help! I am hoping to use this php code to create a multi-select box for my custom CCK field that I have created called, "my_classes." The data is in the table, "content_field_my_classes." (I'm sure you know this from looking at the code but just clarifying).

So I copied your suggestion into the Widget Settings ->> Default Value ->> PHP Code box for this CCK field. I know that it belongs in the Data Settings ->> PHP Code box to override the Allowed Values but putting it in Default Value returns some sort of feedback that I've been using to try to figure this out. If I just put it in the Data Settings ->> PHP Code box it doesn't return any errors and the multi-select box is just empty. So it's more of a way for me to debug the code - even if it's off base.

It appears that this returned an array with no values in it. When I run the SQL with a uid that I choose (node.uid=4) on my phpMyAdmin page, it returns these values which would be used to build the multi-select box:

Directed Green
Merit Red
Honor-Merit Yellow
Merit Blue

Right now, I am getting this message about what is returned in $items

The default value PHP code returned an incorrect value
Expected format :

array(
0 => array('value' => value for value),
// You'll usually want to stop here. Provide more values
// if you want your 'default value' to be multi-valued :
1 => array('value' => value for value),
2 => ...
);
Returned value : Array ( [0] => )

The sample code I used that returned the uid was successful in creating the multi-select box. It's possible that putting it in the Widget Settings ->> Default Value ->> PHP Code box requires one format and the Data Settings ->> PHP Code box requires a different format. I don't really know but since the original sample code snippet worked in the Data Settings ->> PHP Code box then I assume the format for the sample code was acceptable (it did create the multi-select box).

Does this provide any clues to the solution? Again, I appreciate your help very much.

j_ten_man’s picture

j_ten_man commented 
...
while ($row = db_fetch_object($result)) {
  //I was using $account->field_my_classes_value but needed $row->field_my_classes_value
  $items[] = check_plain($row->field_my_classes_value);
}
...

I think that should do it. Let me know if that works or not.

anthonybol’s picture

anthonybol commented

Making Progress ...
I used the new version of the while loop and it returned an empty array.

Returned value : Array ( [0] => )

I checked the HTML that it rendered and this was what was generated:

 <select name="field_class_name[keys][]" multiple="multiple"  class="form-select required" id="edit-field-class-name-keys" ></select>

It looks like the array is empty - or just not what the CCK wants. Is there a way to get find out exactly what is returned by $items? I don't have a great deal of PHP experience but what you've written seems to look like it should work. Any other thoughts?

anthonybol’s picture

anthonybol commented

To compare the output from the Data Settings ->> Allowed Values box to the "non-output" of the Data Settings ->> PHP Code box I did the following in hopes of figuring out how to get the PHP code to work.

I entered the following key|Value pairs into the Data Settings ->> Allowed Values box

1|Honors Red
2|Merit Blue
3|Directed Green
4|Merit-Directed Purple

and this is the rendered HTML output:

<select name="field_class_name[keys][]" multiple="multiple"  class="form-select required" id="edit-field-class-name-keys"  size="4"><option value="1">Honors Red</option><option value="2" selected="selected">Merit Blue</option><option value="3">Directed Green</option><option value="4">Merit-Directed Purple</option></select>

I don't know if this sheds any more light on how to create the php code or not but I'm hoping it will help. I'll keep reading.

j_ten_man’s picture

j_ten_man commented

First, run this sql in your database:

SELECT `content_field_my_classes`.`field_my_classes_value`
AS `classes`
FROM node, content_field_my_classes
WHERE ((`node`.`uid` = --PUT A VALID UID HERE--)
AND (`content_field_my_classes`.`nid` = `node`.`nid`)) ORDER BY
`classes` ASC

As long as that returns something then (not sure if this will work) try
this in your while loop and send me a copy of what you get:

while ($row = db_fetch_object($result)) {
  print_r($row);
  exit();
  //I was using $account->field_my_classes_value but needed
  $row->field_my_classes_value
  $items[] = check_plain($row->field_my_classes_value);
}

Just so you know this will exit your program and should jut print the results. If you remove print_r($row) and exit() calls then this will fix the problem, but this will be good for debugging this. If for some reason it doesn't exit, then that means that the query is not returning any results.

Let me know your results.

anthonybol’s picture

anthonybol commented

Ok ... running the SQL on my database returned this list of classes:

Directed Green
Honors Red
Merit Blue
Merit-Directed Yellow

However, when I ran the extra php code 


while ($row = db_fetch_object($result)) {
  print_r($row);
  exit();
  //I was using $account->field_my_classes_value but needed
  $row->field_my_classes_value
  $items[] = check_plain($row->field_my_classes_value);
}

it did not exit and nothing was put into the multi-select. That doesn't seem to make sense since the SQL on the database does return a result.

EDIT: I tried the PHP code again this time by substituting a real uid in the SQL and it still did not exit even though it's the exact SQL that I used to get the list of classes directly from the database.

anthonybol’s picture

anthonybol commented

It exited this time ... with this output:
stdClass Object ( [classes] => Directed Green )

This is the first returned value from the database. There are three more. Making progress!

j_ten_man’s picture

j_ten_man commented

Remove the print_r and exit statements from the while loop. You should get your desired results.

anthonybol’s picture

anthonybol commented

How do I "unbreak" the site? I clicked the BACK button, removed the code, and clicked "Save Field Settings" but it still exits.

j_ten_man’s picture

j_ten_man commented

Try clearing the cache and cache_content table. I think that should do it.

anthonybol’s picture

anthonybol commented

@j_ten_man
Hi j_ten_man ... I had to run to a meeting and then the rest of life took over. I emptied the cache_content table but I still get the exit on loading the site. I am not sure how to clear the cache if I can't get to the site. Any other ways to get rid of this?
Thanks!

j_ten_man’s picture

j_ten_man commented

Not sure. Run this query in your db and let me know what you get.

SELECT * FROM node_field_instance WHERE field_name='field_my_classes' AND type_name='TYPE_NAM_HERE';

Make sure you replace the type_name with the correct Content type that this is for (if you are unsure then you can remove this part of the query and just use the part up until the AND).

anthonybol’s picture

anthonybol commented

No worries. I had a very recent backup so I just restored it. Since this is a dev site there wasn't much to lose.

It's getting closer! In previous attempts, the rendered HTML form did not have anything in the select form so it looked like this:

 <select name="field_class_name[keys][]" multiple="multiple"  class="form-select required" id="edit-field-class-name-keys" ></select>

But now there is SOMETHING populating it. There are actually five "blank" options each with a value 0->4 which is exactly the number of classes that should be returned in $items.

<select name="field_class_name[keys][]" multiple="multiple"  class="form-select required" id="edit-field-class-name-keys"  size="5"><option value="0"></option><option value="1"></option><option value="2"></option><option value="3"></option><option value="4"></option></select>

So ... we *really you* have got it very close. Any ideas of why it's not giving the option even though it fills in the value?
Just as a reminder, here's the code I've used.

global $user;
$uid = $user->uid;

$result = db_query_range('SELECT `content_field_my_classes`.`field_my_classes_value`
AS `classes`
FROM node, content_field_my_classes
WHERE ((`node`.`uid` = %d)
AND (`content_field_my_classes`.`nid` = `node`.`nid`)) ORDER BY
`classes` ASC', $uid, 0, 10);

$items = array();
while ($row = db_fetch_object($result)) {
  $items[] = $row->field_my_classes_value;
}
return $items;
j_ten_man’s picture

j_ten_man commented

Change the following:

$items[] = $row->field_my_classes_value;

Should be

$items[] = $row->classes;

That's my oversight. Let me know if that works.

anthonybol’s picture

anthonybol commented

Ok, I have been tweaking the php with more of a trial and error attack and I was able to get some actual data to populate the select list. Here's what I did.

global $user;
$uid = $user->uid;

$result = db_query_range('SELECT `content_field_my_classes`.`field_my_classes_value`
AS `classes`
FROM node, content_field_my_classes
WHERE ((`node`.`uid` = %d)
AND (`content_field_my_classes`.`nid` = `node`.`nid`)) ORDER BY
`classes` ASC', $uid, 0, 10);

$items = array();
while ($row = db_fetch_object($result)) {
  $items[] = array($row->field_my_classes_value);
}
return $items;

Which renders the following HTML:

<select name="field_class_name[keys][]" multiple="multiple"  class="form-select required" id="edit-field-class-name-keys"  size="5"><optgroup label="0"><option value="0"></option></optgroup><optgroup label="1"><option value="0"></option></optgroup><optgroup label="2"><option value="0"></option></optgroup><optgroup label="3"><option value="0"></option></optgroup><optgroup label="4"><option value="0"></option></optgroup></select>

The odd thing about this is the <optgroup> tag. I'm not sure why that's there other than it's due to $items not returning the array in the way CCK likes it.

j_ten_man’s picture

j_ten_man commented

Did you see my above post? You need to change
$items[] = array($row->field_my_classes_value);
to
$items[] = $row->classes;

anthonybol’s picture

anthonybol commented

@j_ten_man thanks so much! You've really helped me out and I appreciate it. Now I can move forward. I'm hoping that doing it this way will allow me to create the proper Views to display the information to unauthenticated users. .... that may be another posting at a later time!