Large SQL query
Development and Programming
Hi Forum, I have some large SQL queries that are complex joins. see example
$query = "SELECT l1.id l1_id
,l1.na02_studio_production_number l1_na02_studio_production_nu
,l1.date_entered l1_date_entered
,l1.name l1_name
,l1.status l1_status
,l1_cstm.out_put_date_c l1_cstm_out_put_date_c
FROM accounts
INNER JOIN accounts_nao_production_c l1_1 ON accounts.id=l1_1.accounts_naonaccounts_ida AND l1_1.deleted=0
INNER JOIN na02_studio_production l1 ON l1.id=l1_1.accounts_naproduction_idb AND l1.deleted=0
LEFT JOIN na02_studio_production_cstm l1_cstm ON l1.id = l1_cstm.id_c
LEFT JOIN accounts_cstm accounts_cstm ON accounts.id = accounts_cstm.id_c
WHERE ((accounts_cstm.crn_c={crn} //this is a {exp:member:custom_profile_data} field
))
AND accounts.deleted=0
ORDER BY l1_na02_studio_production_nu
DESC LIMIT 5";
$result = mysql_query ($query);while ($row = mysql_fetch_array ($result, MYSQL_BOTH)) {is then used to loop through and populate a table
You will see above that {crn} is a {exp:member:custom_profile_data} field.
I have tried running as Plugin & Extension and directly in the template. When I use as a plug in it displays in the template even when I use an {if logged_in} conditional. If I use directly in the template, I am unable to pass a {exp:member:custom_profile_data} tag into it. You will see above that {crn} is a {exp:member:custom_profile_data} field.
Would anyone know the best way? I have been trying for hours!
Any slightest help would be amazing at this stage.
Thanks.
I have tried running as Plugin & Extension and directly in the template.
This makes no sense to me…so I am not sure if the problem is query itself or you need help getting the output to show. Either way, you can run a query through a query module or enable your template to process php.
Hi lebisol, I can understand it making no sense. Let me make it a bit clearer. The $query should only show data to logged in members based on one of their
{exp:member:custom_profile_data} fields which is named {crn}Attempting in Templates: If I run this using PHP in templates. I need to use a
{crn}data field in the SQL statement. However, I am not able to put the
{crn}in the SQL statement
i.e
$query = "SELECT data FROM table WHERE field = {crn}"If there was a way I could set <?php $variable = {crn};?> I would be OK.
Attempting in Plug In: When I tried the alternative of running these queries in a Plug In and then returning the $results to the template. I succeed as we can write the SQL and PHP as required. Lets say I call our plug in {exp:sugar_data:get_query} it displays in the template even if a user is not logged in and we try to restrict it with {if logged_in}{exp:sugar_data:get_query}.
Using EE query module to my knowledge only works with data in the EE database. Using External Entries paid Add-on does not appear to accommodate large complex queries.
So I am at a stand point with a deadline to meet.
Hope that helps and hope you are awake in you country and able to reply.
Thanks.
Hi lebisol,
All sorted!
I got it working with a plug in and
$this->EE->load->library('table');$query = $this->EE->db->select('m_field_id_3')and a bit more!
Thanks for your effort. This can be closed
Cool, good to hear. I am sure staff will get to moderate this as needed.