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.