By glitz on

looking for help in determining a custom sql query to enter into views.

im looking to query for a flag ratio based on a cck field (gender) within a certain time range.

The result im looking for is something like this: In the last 5 hours, x number of females flagged this. x number of males flagged this. the current ratio is x:x.

Any help is GREATLY appreciated.

thanks
Chris

⋅ Categories: Drupal 6.x

Comments

Drave Robber’s picture

Drave Robber commented

Not exactly Views, just a custom block with PHP input, but sort of works for me:

$wherearewe = menu_get_object();
$nid = $wherearewe->nid;
$flagfid = 1; // This is the ID of the flag.
$profilefid = 1; // This is the id of the profile field.
$timespan = 3600; // One hour.
$total = db_result(db_query('SELECT COUNT(fcid) FROM {flag_content} WHERE fid = %d AND content_id = %d AND timestamp > %d', $flagfid, $nid, time() - $timespan));
$male = db_result(db_query("SELECT COUNT(f.fcid) FROM {flag_content} f LEFT JOIN {profile_values} p USING (uid) WHERE f.fid = %d AND p.fid = %d AND f.content_id = %d AND f.timestamp > %d AND p.value ='Male'", $flagfid, $profilefid, $nid, time() - $timespan));
$female = db_result(db_query("SELECT COUNT(f.fcid) FROM {flag_content} f LEFT JOIN {profile_values} p USING (uid) WHERE f.fid = %d AND p.fid = %d AND f.content_id = %d AND f.timestamp > %d AND p.value ='Female'", $flagfid, $profilefid, $nid, time() - $timespan));
$malepct = round($male / $total * 100);
$femalepct = round($female / $total * 100);
$out = t('Flagged by %total persons (%malepct% male, %femalepct% female)', array('%total' => $total, '%malepct' => $malepct, '%femalepct' => $femalepct));
return $out;

The hardest part might be figuring out the IDs of the relevant flag and profile field.

glitz’s picture

glitz commented

This is awesome Thank you. I will give it a shot. Any idea where to start on how to find out the ID's I need to use?

Thanks

Drave Robber’s picture

Drave Robber commented

They're serial; the default 'bookmarks' flag coming with Flag module has ID = 1 – if you edited it to your needs, it's still 1, if you added one more, then it's 2. If you have added three profile fields, they have IDs 1, 2, 3. IDs are not reused when flags/fields are deleted, however, so if the site is old you might need to peek into database (PHPMyAdmin or otherwise).

On a side note, if the gender field is required and has been there since the site was started, then of course $femalepct = 100 – $malepct (or the other way around). :)

glitz’s picture

glitz commented

Ok, Thanks Drave!! This is a brand new install so I will attempt to try "2" as the ID. I will attempt to get this working tonight and post back with the result.

One more quick question, say I have 200 different nodes that I have this flag attached to, will I need to build out a block for each one? Or, can I use this code once and have it apply to the specific node being shown. I would assume this would be an argument I need to configure.

Chris

glitz’s picture

glitz commented

Also, not sure if this makes a differece or not either in the query, but the gender field I am trying to pull from is a field I created in the UProfile Content Type.

Label: Gender
Name: field_gender
Type: Text (select box)

Right now, It is not a required field.

Drave Robber’s picture

Drave Robber commented

I'm afraid this makes a lot of difference. I had core Profile module in mind.

Contributed modules would store their data in completely different tables however. Are you using Content Profile?

Drave Robber’s picture

Drave Robber commented

This is one block for all. This part:

$wherearewe = menu_get_object();
$nid = $wherearewe->nid;

retrieves the node ID of the node currently being displayed*. It is possible to have the rest execute only on nodes of certain type, so that there's no need to fiddle with block visibility settings, for example:

$wherearewe = menu_get_object();
if ($wherearewe->type == 'project') { // Put "machine-readable name" of your content type in place of 'project'.
  $nid = $wherearewe->nid;
  $flagfid = 1; // This is the ID of the flag. 
  $profilefid = 1; // This is the id of the profile field.
  $timespan = 3600; // One hour.
  $total = db_result(db_query('SELECT COUNT(fcid) FROM {flag_content} WHERE fid = %d AND content_id = %d AND timestamp > %d', $flagfid, $nid, time() - $timespan));
  $male = db_result(db_query("SELECT COUNT(f.fcid) FROM {flag_content} f LEFT JOIN {profile_values} p USING (uid) WHERE f.fid = %d AND p.fid = %d AND f.content_id = %d AND f.timestamp > %d AND p.value ='Male'", $flagfid, $profilefid, $nid, time() - $timespan));
  $female = db_result(db_query("SELECT COUNT(f.fcid) FROM {flag_content} f LEFT JOIN {profile_values} p USING (uid) WHERE f.fid = %d AND p.fid = %d AND f.content_id = %d AND f.timestamp > %d AND p.value ='Female'", $flagfid, $profilefid, $nid, time() - $timespan));
  $malepct = round($male / $total * 100);
  $femalepct = round($female / $total * 100);
  $out = t('Flagged by %total persons (%malepct% male, %femalepct% female)', array('%total' => $total, '%malepct' => $malepct, '%femalepct' => $femalepct));
  return $out;
}

On nodes of other types, as well as on non-node pages, the code won't return anything, so the block won't be displayed.

* menu_get_object() is commonly used when you need a sidebar block to "know" about the node it is being displayed on. One of the most useful functions in Drupal. :)

glitz’s picture

glitz commented

ok thanks :) trying to get this work now....

Drave Robber’s picture

Drave Robber commented

Unfortunately, might not work in your setup – see above about core Profile vs. contributed modules.

glitz’s picture

glitz commented

Ahh, yes I didnt not see that reply from you. I apologize. Yes, I am using Content Profile.
Hmm. Would be great If I could get this working somehow. Do you think I Can use the core profile fields for gender selection (for this query), and then use Content Profile for the other fields?

Drave Robber’s picture

Drave Robber commented

It's my bad actually. I missed the reference to 'cck' in your initial post – had I read it a bit more carefully, I'd have realized we're not dealing with core Profile.

I'll take a look into adapting this for Content Profile.

By the way, what do you want to do with those who have flagged but have no gender indicated – list as 'unknown', include in total but don't list separately, exclude altogether?

glitz’s picture

glitz commented

Thanks for looking into this for me! I'm also investigating in the Content Profile forums to see if we can get some idea of the correct tables that we need.

Ideally, I'd like to have the gender field required upon the user setting up their profile.

Drave Robber’s picture

Drave Robber commented 
$wherearewe = menu_get_object();
if ($wherearewe->type == 'project') {
  $nid = $wherearewe->nid;
  $flagfid = 1; // This is the ID of the flag.
  $timespan = 172800; // Two days.
  $total = db_result(db_query('SELECT COUNT(fcid) FROM {flag_content} WHERE fid = %d AND content_id = %d AND timestamp > %d', $flagfid, $nid, time() - $timespan));
  $male = db_result(db_query("SELECT COUNT(f.fcid) FROM {flag_content} f LEFT JOIN {node} n USING (uid) INNER JOIN {content_type_profile} p USING (nid) WHERE f.fid = %d AND f.content_id = %d AND f.timestamp > %d AND p.field_gender_value ='Male'", $flagfid, $nid, time() - $timespan));
  $malepct = round($male / $total * 100);
  $femalepct = 100 - $malepct;
  $out = t('Flagged by %total persons (%malepct% male, %femalepct% female)', array('%total' => $total, '%malepct' => $malepct, '%femalepct' => $femalepct));
  return $out;
}

Adjust for your content type, flag ID and timespan.
This assumes the content type used for user profiles has "machine-readable name" equal to 'profile' (that is the default); if it's different, then the table name in the second query will be accordingly different.

glitz’s picture

glitz commented

Hi Drave,

I gave this a try and I'm not getting anything to appear after creating the block.
Im sure its something simple, but I have tried it several times with no luck.

The content type "machine-readable-name" I am using is: uprofile

I altered the script, as well as confirmed the flag id, and the fields were accurately stated.

Here is the final code I was placing into the block:

$wherearewe = menu_get_object();
if ($wherearewe->type == 'uprofile') {
  $nid = $wherearewe->nid;
  $flagfid = 2; // This is the ID of the flag.
  $timespan = 172800; // Two days.
  $total = db_result(db_query('SELECT COUNT(fcid) FROM {flag_content} WHERE fid = %d AND content_id = %d AND timestamp > %d', $flagfid, $nid, time() - $timespan));
  $male = db_result(db_query("SELECT COUNT(f.fcid) FROM {flag_content} f LEFT JOIN {node} n USING (uid) INNER JOIN {content_type_uprofile} p USING (nid) WHERE f.fid = %d AND f.content_id = %d AND f.timestamp > %d AND p.field_gender_value ='Male'", $flagfid, $nid, time() - $timespan));
  $malepct = round($male / $total * 100);
  $femalepct = 100 - $malepct;
  $out = t('Flagged by %total persons (%malepct% male, %femalepct% female)', array('%total' => $total, '%malepct' => $malepct, '%femalepct' => $femalepct));
  return $out;
}

I also tried several different block settings to no avail.

Do you see anything obviously wrong with what I used? Would help you to see if I provided you a temp admin account to access the site?
If so, I can email you the credentials.

Its a fresh install so not much to it besides trying to get this feature to work for now :)

Thanks
Chris

Drave Robber’s picture

Drave Robber commented

if ($wherearewe->type == 'uprofile') {
This is the content type it's supposed to be displayed on; are they flagging each other's profiles?

Also, is the input format for the block set to 'PHP code'?

glitz’s picture

glitz commented

OK, Defeinetly making some progress.

It is appearing now. And like you mentioned, I had to change the following:


if ($wherearewe->type == 'uprofile') {

to


if ($wherearewe->type == 'venues') {

Although now, When un-flagging, I get an error in the header that says:
warning: Division by zero in /home/dongland/public_html/includes/common.inc(1696) : eval()'d code on line 9.

Also, nodes that have not been flagged at all say:

Check In Ratio
Flagged by 0 persons (0% male, 100% female).

Other then that, Flagged nodes disply the proper info!

Chris

Drave Robber’s picture

Drave Robber commented

Well, yes, it obviously needs to handle the case where nobody has flagged it yet :)

$wherearewe = menu_get_object();
if ($wherearewe->type == 'venues') {
  $nid = $wherearewe->nid;
  $flagfid = 2; // This is the ID of the flag.
  $timespan = 172800; // Two days.
  $total = db_result(db_query('SELECT COUNT(fcid) FROM {flag_content} WHERE fid = %d AND content_id = %d AND timestamp > %d', $flagfid, $nid, time() - $timespan));
  if ($total > 0) {
    $male = db_result(db_query("SELECT COUNT(f.fcid) FROM {flag_content} f LEFT JOIN {node} n USING (uid) INNER JOIN {content_type_uprofile} p USING (nid) WHERE f.fid = %d AND f.content_id = %d AND f.timestamp > %d AND p.field_gender_value ='Male'", $flagfid, $nid, time() - $timespan));
    $malepct = round($male / $total * 100);
    $femalepct = 100 - $malepct;
    $out = t('Flagged by %total persons (%malepct% male, %femalepct% female)', array('%total' => $total, '%malepct' => $malepct, '%femalepct' => $femalepct));
  }
  else {
    $out = t('Nobody flagged this... yet.');
  }
  return $out;
}
glitz’s picture

glitz commented

Well,

I fixed the percentage for non-flagged nodes by replacing


$malepct = round($male / $total * 100);
$femalepct = 100 - $malepct;

with


$malepct = round($male / $total * 100);
$femalepct = round($female / $total * 100);

So, All SEEMS to be working fine. Except I am still getting an error in the header when un-flagging a node:

.warning: Division by zero in /home/dongland/public_html/includes/common.inc(1696) : eval()'d code on line 9.
warning: Division by zero in /home/dongland/public_html/includes/common.inc(1696) : eval()'d code on line 10.

I am seeing this error while logged in as admin so Im not sure if it will be visible to a standard registred user.
If its not visiable to them, I guess it wont matter.

I will post back after testing.

Thanks!

Chris

glitz’s picture

glitz commented

ah. just saw your last post....

trying that now :)

glitz’s picture

glitz commented

Your last code posted was PERFECT. works like a charm. thanks very much for your help in getting this figured out for me. The help is VERY MUCH appreciated!!

Chris

Drave Robber’s picture

Drave Robber commented

Congratulations! :)

Had I thought it over a bit more carefully from the start, we probably would have obtained the result sooner, but on the other hand developing it step by step is a chance to learn something new.

$timespan is seconds, so 5 hours would be 5*60*60 = 18000.