sql query help , spent 2 days trying to solve it

For Joomla! 3.x Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general

Moderator: ooffick

Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
Post Reply
moktar-br
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Tue Feb 05, 2019 3:07 pm

sql query help , spent 2 days trying to solve it

Post by moktar-br » Mon Feb 11, 2019 2:07 pm

I wanna select all users that they answered for question of the day also all users from db and export all that to csv file and if user answered the question of day in column of answer must show up the answer and the selected user that not answered question of the day the column of answer must be empty , I spend 2 days trying to solve it but nothing yet .

Code: Select all


	public function getVotesCsvData2($pollId)
	{

		$db = JFactory::getDbo();
		$query = $db->getQuery(true);
    	$query->select($this->getState('list.select', 'a.id, a.poll_id, a.voter_id, a.voted_on, a.ip_address, a.custom_answer'))
    	    ->from('#__jcp_votes AS a');
	    
	    $query->select('o1.title AS answer_title')
	       ->join('LEFT', $db->qn('#__jcp_options') . ' AS o1 ON o1.id = a.option_id');
	    
	    $query->select('o2.title AS column_title')
	       ->join('LEFT', $db->qn('#__jcp_options') . ' AS o2 ON o2.id = a.column_id');
	    	    

		$query->select('user.name AS name,
						user.username AS username,
						user.email AS email,
						user.password AS password,
						user.registerDate AS registerDate,
						user.lastvisitDate AS lastvisitDate,
						user.lastResetTime AS lastResetTime
						')->join('LEFT', $db->qn('#__users'). ' AS user ON user.id = a.voter_id');


		 $query->select('profile.firstname AS user_ftname, 
				    	profile.middlename AS user_middlename,
				    	profile.lastname AS user_lastname,
				    	profile.cb_freq_email AS cb_freq_email,
				    	profile.cb_num_port AS cb_num_port,
				    	profile.cb_num_fix AS cb_num_fix,
				    	profile.cb_rep_quest AS cb_rep_quest,
				    	profile.cb_naiss_date AS cb_naiss_date,
				    	profile.cb_sexe AS cb_sexe,
				    	profile.cb_birth_place AS cb_birth_place,
				    	profile.cb_arriv_suisse AS cb_arriv_suisse,
				    	profile.cb_sit_conj AS cb_sit_conj,
				    	profile.cb_duree_couple AS cb_duree_couple,
				    	profile.cb_nbr_enf AS cb_nbr_enf,
				    	profile.cb_nbr_pt_enf AS cb_nbr_pt_enf,
				    	profile.cb_naiss_pt_enf AS cb_naiss_pt_enf,
				    	profile.cb_vie_mere AS cb_vie_mere,
				    	profile.cb_vie_pere AS cb_vie_pere,
				    	profile.cb_proch_aid AS cb_proch_aid,
				    	profile.cb_typ_hab AS cb_typ_hab,
				    	profile.cb_prop_loc AS cb_prop_loc,
				    	profile.cb_prop_pro AS cb_prop_pro,
				    	profile.cb_prop_hebgr AS cb_prop_hebgr,
				    	profile.cb_nbr_persfoy AS cb_nbr_persfoy,
				    	profile.cb_satisf_hab AS cb_satisf_hab,
				    	profile.cb_mod_depla AS cb_mod_depla,
				    	profile.cb_freq_sorti AS cb_freq_sorti,
				    	profile.cb_loisirs AS cb_loisirs,
				    	profile.cb_niv_etud AS cb_niv_etud,
				    	profile.cb_retrait AS cb_retrait,
				    	profile.cb_an_retrait AS cb_an_retrait,
				    	profile.cb_domprof_retrait AS cb_domprof_retrait,
				    	profile.cb_domprof_autre_retrait AS cb_domprof_autre_retrait,
				    	profile.cb_domprof_actif AS cb_domprof_actif,
				    	profile.cb_domprof_autre_actif AS cb_domprof_autre_actif,
				    	profile.cb_adresse AS cb_adresse,
				    	profile.cb_codepost AS cb_codepost,
				    	profile.cb_commune AS cb_commune,
				    	profile.cb_canton AS cb_canton

						')->join('LEFT', $db->qn('#__comprofiler'). ' AS profile ON profile.user_id = a.voter_id');



	    $query->where('a.poll_id = '.$pollId);

		$db->setQuery($query);
		$results = $db->loadObjectList();
        $responses = $db->loadObjectList();
        	

        $headers = array();
        $headers[] = JText::_('COM_COMMUNITYPOLLS_COLUMN');
        $headers[] = JText::_('COM_COMMUNITYPOLLS_ANSWER');
        $headers[] = JText::_('COM_COMMUNITYPOLLS_FIELD_CUSTOM_ANSWER_LABEL');
        $headers[] = JText::_('Name');
        $headers[] = JText::_('User Name');
        $headers[] = JText::_('Email');
        $headers[] = JText::_('Password');
        $headers[] = JText::_('Register Date');
        $headers[] = JText::_('Last visit Date');
        $headers[] = JText::_('Last Updated');
        $headers[] = JText::_('First Name');
        $headers[] = JText::_('Middle Name');
        $headers[] = JText::_('Last Name');

        $headers[] = JText::_('A Quelle Fréquence Environ Vérifiez-vous Cette Adresse Email ?');
        $headers[] = JText::_('N° De Téléphone Portable (10 Chiffres)');
        $headers[] = JText::_('N° De Téléphone (natel De Préférence)');
        $headers[] = JText::_('De Façon Générale Sous Quelle(s) Forme(s) Préférez-vous Répondre à Un Questionnaire D’opinion ? (plusieurs Réponses Possibles)');
        $headers[] = JText::_('Année De Naissance');
        $headers[] = JText::_('Sexe');
        $headers[] = JText::_('Etes-vous Né(e) En Suisse ?');
        $headers[] = JText::_('En Quelle Année Environ êtes-vous Venu(e) Habiter En Suisse ?');
        $headers[] = JText::_('Situation Conjugale');
        $headers[] = JText::_('Combien D’années En Tout Avez-vous Vécu En Couple Dans Votre Vie ?');
        $headers[] = JText::_('Aujourd’hui, Que Vous Soyez En Couple Ou Pas, à Quel Point Vous Sentez-vous Seul(e)');
        $headers[] = JText::_('Nombre D’enfants');
        $headers[] = JText::_('Nombre De Petits Enfants');
        $headers[] = JText::_('En Quelle Année Est Né Votre Petit-enfant Le Plus âgé ?');
        $headers[] = JText::_('Votre Mère Est-elle Encore En Vie ?');
        $headers[] = JText::_('Votre Père Est-il Encore En Vie ?');
        $headers[] = JText::_('De Quel(s) Membre(s) De Votre Famille êtes-vous éventuellement Le Proche-aidant ?');
        $headers[] = JText::_('Concernant Votre Typologie D’habitation, êtes-vous');
        $headers[] = JText::_('Propositions (co)locataire');
        $headers[] = JText::_('Propositions (co)propriétaire');
        $headers[] = JText::_('Propositions Hébergement Gratuit');
        $headers[] = JText::_('Combien De Personnes Vivent Dans Votre Ménage Y Compris Vous-même ?');
        $headers[] = JText::_('A Quel Point Diriez-vous être Satisfait(e) De Votre Mode D’habitation');
        $headers[] = JText::_('Dans Votre Quotidien, Vous Vous Déplacez Le Plus Souvent (plusieurs Réponses Possibles)');
        $headers[] = JText::_('A Quelle Fréquence Sortez-vous De Votre Domicile ?');
        $headers[] = JText::_('Vos Principaux Domaines De Loisirs (plusieurs Réponses Possibles)');
        $headers[] = JText::_('Quel Est Le Plus Haut Niveau De Formation Que Vous Avez Terminé');
        $headers[] = JText::_('Êtes-vous à La Retraite ?');
        $headers[] = JText::_('Depuis Quelle Année ?');
        $headers[] = JText::_('Toute Dernière Question Mais C’est La Plus Longue : Quel était Votre Domaine Professionnel ?');
        $headers[] = JText::_('Domaine Professionnel Autre');
        $headers[] = JText::_('Toute Dernière Question Mais C’est La Plus Longue : Quel Est Votre Domaine Professionnel ?');
        $headers[] = JText::_('Adresse (n° Et Rue)');
        $headers[] = JText::_('Code Postal');
        $headers[] = JText::_('Commune');
        $headers[] = JText::_('Canton');
       
        

        
        $csvData = array();
        $csvData[] = $headers;
        
        foreach ($responses as $response)
        {
            $responseArray = array();
            $responseArray[] = $response->column_title;
            $responseArray[] = $response->answer_title;
            $responseArray[] = $response->custom_answer;
            $responseArray[] = $response->name;
            $responseArray[] = $response->username;
            $responseArray[] = $response->email;
            $responseArray[] = $response->password;
            $responseArray[] = $response->registerDate;
            $responseArray[] = $response->lastvisitDate;
            $responseArray[] = $response->lastResetTime;
            $responseArray[] = $response->user_ftname;
            $responseArray[] = $response->user_middlename;
            $responseArray[] = $response->user_lastname;
            $responseArray[] = $response->cb_freq_email;
            $responseArray[] = $response->cb_num_port;
            $responseArray[] = $response->cb_num_fix;
            $responseArray[] = $response->cb_rep_quest;
            $responseArray[] = $response->cb_sexe;
            $responseArray[] = $response->cb_birth_place;
            $responseArray[] = $response->cb_arriv_suisse;
            $responseArray[] = $response->cb_sit_conj;
            $responseArray[] = $response->cb_duree_couple;
            $responseArray[] = $response->cb_nbr_enf;
            $responseArray[] = $response->cb_nbr_pt_enf;
            $responseArray[] = $response->cb_naiss_pt_enf;
            $responseArray[] = $response->cb_vie_mere;
            $responseArray[] = $response->cb_vie_pere;
            $responseArray[] = $response->cb_proch_aid;
            $responseArray[] = $response->cb_typ_hab;
            $responseArray[] = $response->cb_prop_loc;
            $responseArray[] = $response->cb_prop_pro;
            $responseArray[] = $response->cb_prop_hebgr;
            $responseArray[] = $response->cb_nbr_persfoy;
            $responseArray[] = $response->cb_satisf_hab;
            $responseArray[] = $response->cb_mod_depla;
            $responseArray[] = $response->cb_freq_sorti;
            $responseArray[] = $response->cb_loisirs;
            $responseArray[] = $response->cb_niv_etud;
            $responseArray[] = $response->cb_retrait;
            $responseArray[] = $response->cb_an_retrait;
            $responseArray[] = $response->cb_domprof_retrait;
            $responseArray[] = $response->cb_domprof_autre_retrait;
            $responseArray[] = $response->cb_domprof_actif;
            $responseArray[] = $response->cb_domprof_autre_actif;
            $responseArray[] = $response->cb_adresse;
            $responseArray[] = $response->cb_codepost;
            $responseArray[] = $response->cb_commune;
            $responseArray[] = $response->cb_canton;

            
            $csvData[] = $responseArray;
        }
        
        return $csvData;
	}


Last edited by toivo on Mon Feb 11, 2019 3:05 pm, edited 1 time in total.
Reason: mod note: moved from General Questions/New to Joomla! 3.x

User avatar
pe7er
Joomla! Master
Joomla! Master
Posts: 22117
Joined: Thu Aug 18, 2005 8:55 pm
Location: Nijmegen, The Netherlands
Contact:

Re: sql query help , spent 2 days trying to solve it

Post by pe7er » Mon Feb 11, 2019 5:02 pm

I'd like to advise you to cut the problem in pieces.
And start with creating the SQL that you need to retrieve the right data.

Furthermore you can debug the $query object (output your SQL to the screen) with:

Code: Select all

echo $query->dump();
Kind Regards,
Peter Martin, Global Moderator
https://db8.nl - Joomla specialist, Nijmegen, Nederland
Co-developer of d2 Content https://data2site.com/joomla-extensions/d2-content


Post Reply

Return to “Joomla! 3.x Coding”