0

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 .

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;
}
  • Welcome to JSE. Unfortunately I'm finding it very hard to understand your question and the issue you face. Could you carefully go over it again and try to improve the use of punctuation and grammar? Please also only include relevant code snippets – Lodder Feb 11 at 14:12
  • I just wanna select users from database and their 2 kinds of users 1- users had answered the question of day and 2- user didn't answer , so the query is like that what I must add to make the query select also users didn't answer – Almokhtar bekkour Feb 11 at 14:19
  • $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'); – Almokhtar bekkour Feb 11 at 14:22
  • Please update your original post with any improvements as opposed to writing them as comments. – Lodder Feb 11 at 14:26
  • @Almokhtarbekkour I have the knowledge to help you, but your question is Unclear because you have not offered your database table schemas/data and I don't know what your exact expected output is. Providing a db-fiddle link for me to play with would be best. If you don't know how to do that.. Export the necessary tables from phpMyAdmin and output as text, then copy the CREATE and INSERT data for all relevant tables to db-fiddle, save, then post the generated hyperlink. Then explain the exact output that you desire. Only then will your question be clear, complete, upvote-worthy and answerable. – mickmackusa Feb 12 at 20:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.