Complex Query dropdown multi

Hi, i'm trying to make a form like this below
http://www.screencast.com/t/soblyCjmVK1

DB Table - SEDE_INSTALLAZIONE
ID_SEDE
DESCRIZIONE

DB Table - ANAG_VIDEO
ID_VIDEO
DESCRIZIONE

DB Table - SEDE_INSTALLAZIONE_VIDEO -> Correlation between the two tables
ID_SEDE
ID_VIDEO

the form should be work in this mode
Select sede_installazione
Listbox "Video Associati" -> populing with all videos already associated
Listbox "Video Non Associati" -> populing with all videos available except that already associated

the buttons ADD and REMOVE have to move video from side to other side

and in the end will be button SUBMIT that save data in DB in SEDE_INSTALLAZIONE_VIDEO deleting all record for ID_SEDE and inserting new record from associated listbox
----
in this moment i made ( and works ) only first 2 points
(Select sede_installazione
Listbox "Video Associati" -> populing with all videos already associated )
but i have some problems to populing "video non associati" because i want put all video that i have in table ANAG_VIDEO ( and i can do it ), but i want don't show videos already present in "video associati"
i'm trying in event ajax2 to put the custom code after dbread

<?php
$options = array();
if ( !$form->data['AnagVideo'] || count($form->data['AnagVideo']) < 1 ) {
  // no result was found
  $options[] = 'Seleziona Sede/Nessun video da associato';
} else {
  foreach ( $form->data['AnagVideo'] as $d ) {
  	$trovato = 'no';
	  foreach ( $form->data['VideoSedeDescr'] as $v ) {
			if ( $d[ID_VIDEO'] == $v['ID_VIDEO'] ) {
				$trovato = 'si';
		  }
	  }
		if ( $trovato == 'no' ) {
			$options[$d['ID_VIDEO']] =  $d['DESCRIZIONE'];
	  }
  }
}
echo json_encode($options);
?>

but don't work, maybe 'VideoSedeDescr' is not available ?
OR
is possible make a where condition in dbread like this ?
WHERE ANAG_VIDEO.ID_VIDEO NOT IN ( SELECT ID_VIDEO FROM sede_installazione_video WHERE ID_SEDE = 1 )

Hi ilmagowalter,

I assume that you have two DB Read actions here - please can you add a Debugger and then copy and paste some examples of the data you are loading from each table.

Bob

when i add debugger on event, the system don't load data in listbox too

from logical point of view
anag_video is a list of videos
sede_installazione is the list of places to display video

in sede_installazione_video we have for each place what videos we can display

Hi ilmagowalter,

Please can you tell me what data is in the $form->data array from the DB Read actions, without that I'd be guessing at the code you need.

Bob

maybe is possible work on where condition of the second dbread with something like
WHERE ANAG_VIDEO.ID_VIDEO NOT IN ( SELECT ID_VIDEO FROM sede_installazione_video WHERE ID_SEDE = 'value of dropdown sede_installazione' )

but i have no idea to how write this condition for chronoform

Hi ilmagowalter,

The simplest way would be to put the MYSQL query into a Custom Code action like this

<?php
$db = \JFactory::getDBO();
$query = "
    SELECT `colname`, . . .
        FROM `#__some_table `
        WHERE `ANAG_VIDEO`.`ID_VIDEO` NOT IN  
          ( SELECT `ID_VIDEO` FROM `sede_installazione_video` WHERE `ID_SEDE` = 1 )
";
$db->setQuery($query);
$form->data['some_name'] = $db->loadAssocList();
?>

Bob

here the data requested

Array
(
    [option] => com_chronoforms5
    [chronoform] => Gestione_Video
    [SedeInstallazione] => Array
        (
            [0] => Array
                (
                    [ID_SEDE] => 1
                    [ID_CLIENTE] => 1
                    [DESCRIZIONE] => Piano primo, sala ristoro
                    [STATO] => Italia
                    [PROVINCIA] => Palermo
                    [COMUNE] => Palermo
                    [INDIRIZZO] => via le mani dal naso installato
                    [NUM_CIVICO] => 15
                    [ID_ECOBOX] => 1
                    [ID_DISPOSITIVO] => 1
                )

            [1] => Array
                (
                    [ID_SEDE] => 2
                    [ID_CLIENTE] => 3
                    [DESCRIZIONE] => piano quinto
                    [STATO] => Italia
                    [PROVINCIA] => Palermo
                    [COMUNE] => Palermo
                    [INDIRIZZO] => Piazza croci
                    [NUM_CIVICO] => 1
                    [ID_ECOBOX] => 2
                    [ID_DISPOSITIVO] => 2
                )

            [2] => Array
                (
                    [ID_SEDE] => 3
                    [ID_CLIENTE] => 5
                    [DESCRIZIONE] => Piano 2, Sala Ristoro
                    [STATO] => Italia
                    [PROVINCIA] => PA
                    [COMUNE] => Palermo
                    [INDIRIZZO] => Via Cuba
                    [NUM_CIVICO] => 59
                    [ID_ECOBOX] => 3
                    [ID_DISPOSITIVO] => 3
                )

        )

    [VideoSedeDescr2] => Array
        (
            [0] => Array
                (
                    [ID_VIDEO] => 1
                    [DESCRIZIONE] => Pubblicità numero 1
                )

            [1] => Array
                (
                    [ID_VIDEO] => 3
                    [DESCRIZIONE] => Pubblicità numero 3
                )

        )

    [AnagVideo2] => Array
        (
            [0] => Array
                (
                    [ID_VIDEO] => 1
                    [DESCRIZIONE] => Pubblicità numero 1
                )

            [1] => Array
                (
                    [ID_VIDEO] => 2
                    [DESCRIZIONE] => Pubblicità numero 2
                )

            [2] => Array
                (
                    [ID_VIDEO] => 3
                    [DESCRIZIONE] => Pubblicità numero 3
                )

            [3] => Array
                (
                    [ID_VIDEO] => 4
                    [DESCRIZIONE] => Pubblicità numero 4
                )

            [4] => Array
                (
                    [ID_VIDEO] => 5
                    [DESCRIZIONE] => Pubblicità numero 5
                )

        )

)

i tried with this code but don't works

<?php
$db = \JFactory::getDBO();
$query = "
    SELECT 'id_video','id_descrizione'
        FROM 'ecopm'.`anag_video`
        WHERE `ID_VIDEO` NOT IN  
          ( SELECT `ID_VIDEO` FROM 'ecopm'.`sede_installazione_video` WHERE `ID_SEDE` = " .$form->data['ID_SEDE'] "  )
";
$db->setQuery($query);
$form->data['ID_VIDEO_DISP'] = $db->loadAssocList();
?>

DB is not the same of joomla but is another in the same mysql server named 'ecopm'
$form->data['ID_VIDEO_DISP'] is the dropdown multi

Sorry for disturb
in ID_SEDE combobox i set event

in setup i created event named ajax3
and put custom code

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT ID_VIDEO, DESCRIZIONE FROM ANAG_VIDEO WHERE ID_VIDEO NOT IN 
( SELECT ID_VIDEO FROM SEDE_INSTALLAZIONE_VIDEO WHERE ID_SEDE = ' . form_id_sede;

mysql_select_db('ecopm');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "ID :{$row['ID_VIDEO']}  <br> ".
         "Title: {$row['DESCRIZIONE']} <br> ".
         "--------------------------------<br>";
}
mysql_close($conn);
?>

the questions are...
how i can selected value of ID_SEDE in string $sql ?
how i can put the result in dropdown multi ID_VIDEO_DISP ?
this way can work ?

Using a "DB Read" action to select data from the table should be easier, and you can use the "conditions" box for the WHERE data.

Regards,
Max

Using a "DB Read" action to select data from the table should be easier, and you can use the "conditions" box for the WHERE data.

Regards,
Max

Can you help me with sintax i have to put in conditions box ?

in sql the query is

SELECT id_video,id_descrizione
FROM ecopm.anag_video
WHERE ID_VIDEO NOT IN ( SELECT ID_VIDEO
													FROM ecopm.sede_installazione_video WHERE ID_SEDE = 'valueofmaindropdown' )

select ... from ... is in DB Read
i don't know like write where .... in condition box of db read

was more simple that i think...
in condition box i have to write

<?php
return array (":AnagVideo.ID_VIDEO not in ( select id_video from ecopm.sede_installazione_video WHERE ID_SEDE = " . $form->data['ID_SEDE'] . " )");
?>