1

I have done lot of things with Drupal 7. Now I have something to do that can't be done with view interface (in my opinion) and I need to ues php and SQL. Now I am testing in view php field.

I have many node types, i.e. students, past_study etc., with many custom fields.

the first problem I faced was the name of table, I though "node type name" i.e. students, past_study should be use in FROM but after lot of hours wasting I discover each filed has its own table so have to open so many table like this

FROM field_data_field_this, field_data_field_that

Now the main problem is with WHERE clause I have student_code in students node type and a reference field s_code_ref field in past_study. I am not sure how to compere student_code in student node type with s_code_ref.

WHERE student_code = s_code_ref

when s_code_ref is a reference entity field in past_study

What I am doing is to adding a field with complicated calculation from the field of two node types and have to add this in a view with various other field from two node types.

my SQL is something like this

<?php
$result = db_query ('
  SELECT SUM((pstd.field_studyDate_value2 - pstd.field_studyDate_value) + (std.field_joiningDate_value - now()))  AS duration 
  FROM field_data_field_past_study pstd, field_data_field_student std

  ');

std has single record for each student, and pstd may have 0 or more records for students. students name, code, etc are being displaying in the view with relationship of two node type.

my problem is that how to refer the student_code in record line displaying in view and then using that in where clause to calculate the sum for that particular student_code.

so I need something like

WHERE pstd.s_code_ref = the student_code of the current row.

besides I want to know if my approach to calculate this way is right or there is other better ways?

in short I have to calculate total past study duration from one node type (past_study) then add duration of current study from another node type (student) and bringing the total study duration in a field for each student in a view.

Any help guidance will be appreciated.

  • 1
    Try to use join to relate tables. – ARUN Mar 26 '14 at 11:24
  • Yah two table can be join in SQL but the big question is how to align it with the rows displaying by view. This is just one column (field) there are many fields being displayed by views. std_code, name, start date, current duration duration, etc then this calculation of total duration that is of 1 current and many past study records. I'll be thankful if someone can guide thoroughly this one calculation causing me headache other all things has been done month ago. – user21354 Mar 27 '14 at 5:13
  • besides which tables need to be join, as each field has its own table. This is new to me, we normally have all fields in a table and then join that table on given field. I Need more clear understanding. besides at some place I notice it table is use in brackets like this FROM {node} do we need this bracket? need better documents for developers , i have read these Static and dynamic queries drupal.org/node/310075 and drupal.org/node/310072 but yet confused. – user21354 Mar 27 '14 at 6:13
  • Is there any other place (stack overflow ??) where I can discuss about Drupal + PHP + SQL coding ? I think above problem can be solved just need little guidance. – user21354 Apr 2 '14 at 8:47
  • Finally I solved the problem – user21354 Jun 9 '14 at 6:28
0

Solved

Finally I solved the problem with following codes

$eid = $row->id; // a variable to store row id 
$sql = "SELECT SUM(field_past_std_date_value2 - field_past_std_date_value) as psd, field_std_pin_target_id as tid 
FROM field_data_field_pas_std_date d JOIN field_data_field_std_pin p ON d.entity_id = p.entity_id 
WHERE field_std_pin_target_id = $eid"; 

this bring me the total duration of past study of each student listed in my view. Although the problem is solved there may be better way to writer above squl. if anyone can give comments it will be highly appreciated. I'll continue improving my Drupal skills.

  • The query looks fairly good. You may want to look up db_query for how to execute it. Make sure you review how to safely include parameters in query since the style of just including a variable to be evaluated in the query string is a bad pattern that can lead to SQL injection security vulnerabilities. – Joe Murray Nov 11 '14 at 3:45

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.