Sheduled task - php plugin - problem with databasejoin & cascadingdropdown

[blue-mouse]

Hi,
For my scheduled task i write php sript included in file holiday.php:

<?php
$db =& JFactory::getDBO();
 
 
$query1 = "UPDATE `ccjom_users` SET `urlop`= 'nie' WHERE `status_osoby` = 'pracownik'";
 
$db->setQuery($query1);
$db->query();
 
 
$query3 = "UPDATE ccjom_users LEFT JOIN  ccjom_cc_lista_zastepstw ON ccjom_cc_lista_zastepstw.id_osoba_zastepowana= ccjom_users.id
SET ccjom_users.urlop= 'dzisiaj' WHERE ccjom_cc_lista_zastepstw.dzisiaj='dzisiaj' ";
 
$db->setQuery($query3);
$db->query();
 
$query4 = "UPDATE ccjom_users LEFT JOIN  ccjom_cc_lista_zastepstw ON ccjom_cc_lista_zastepstw.id_osoba_zastepowana= ccjom_users.id
SET ccjom_users.idzas = ccjom_cc_lista_zastepstw.id_zastepujacy WHERE ccjom_cc_lista_zastepstw.dzisiaj='dzisiaj' ";
 
$db->setQuery($query4);
$db->query();
 
 
$query5 = "UPDATE ccjom_users LEFT JOIN  ccjom_cc_lista_zastepstw ON ccjom_cc_lista_zastepstw.id_osoba_zastepowana= ccjom_users.id
SET ccjom_users.usernamezas  = ccjom_cc_lista_zastepstw.zastepujacy WHERE ccjom_cc_lista_zastepstw.dzisiaj='dzisiaj' ";
 
$db->setQuery($query5);
$db->query();
 
 
$query6 = "UPDATE ccjom_users LEFT JOIN  ccjom_cc_lista_zastepstw ON ccjom_cc_lista_zastepstw.id_osoba_zastepowana= ccjom_users.id
SET ccjom_users.emailzas = ccjom_cc_lista_zastepstw.email_zastep WHERE ccjom_cc_lista_zastepstw.dzisiaj='dzisiaj' ";
 
$db->setQuery($query6);
$db->query();
 
 
?>

I have a problem to update correctly fields:
ccjom_users.usernamezas ---- should be name of user
&
ccjom_users.emailzas should be email of user
I get 47,48,75,47

This 47,48,75,47 is OK only for
ccjom_users.idzas ---- what is like user_id


I have a problem with query 5 and query 6 ,so:
ccjom_cc_lista_zastepstw.zastepujacy --- is databasejoin
ccjom_cc_lista_zastepstw.email_zastep --- is cascading dropdown
and this returns ccjom_cc_lista_zastepstw___id_zastepujacy --- that it also cascading dropdown but is like second_id (user_id) in table ccjom_cc_lista_zastepstw
---------------------------------------------------------------------------------------------
I include screenshot with table ccjom_users.


2. second way what i need could be realized like somethig:

for all records where "Na urlopie?" = 'dzisiaj' read "Id Zast?puj?cego" (example, =47), go to record with "Id"=47, read "email" (example,= e.gxxxx@candc.pl) , update column "email zast?puj?cego" with e.gxxxx@candc.pl.
----------------------------------------------------------------------------------------------

It is a bit too difficult for me to write right query to update correctly
ccjom_users.usernamezas ---- should be name of user
&
ccjom_users.emailzas should be email of user

Please any help,

 

[cheesegrits]

I'm sorry, but this level of question is outside the scope of Standard support, which doesn't include custom coding.

But in general, if you are getting a numeric ID where you are needing a username or email, that usually means you are getting the "raw" value of a join or a CDD, in other words the 'value' rather than the 'label'. So you need to left join the users table on that field, and use the name/email from the joined users table row.

-- hugh