Jump to content

[Résolu] Affichage variable modifiée par jointure

YakaLire
 Share

Recommended Posts

YakaLire Newbie

YakaLire

Posted
Posted (edited)

Bonjour à tous !

 

Voilà je suis parti d'un module déjà existant de témoignages mais il n'était pas assez complet et je l'ai donc "amélioré" pour qu'il colle au maximum à ce que je souhaite.

 

Je suis pas expert en code, mais je bloque sur l'affichage de certaines de mes infos qui nécessite une jointure.

 

Voici donc le bout de code comprenant les requêtes SQL sachant qu'en l'état je n'ai pas d'erreur qui s'affiche mais qu'au niveau de l'affichage, je n'ai pas le prénom et le nom qui s'affiche au lieu de l'id du client.

 

Merci d'avance du petit coup de main !

<?php
...
public function displayTestimonials()
{
$output = array(); // create an array named $output to store our testimonials. We will read the from the DB
$db = Db::getInstance(); // create and object to represent the database
$result = $db->ExecuteS('SELECT t.testimonial_id, t.testimonial_rate, t.testimonial_resume, t.testimonial_message, t.testimonial_date, t.testimonial_again, t.testimonial_status, t.testimonial_response, c.firstname, c.lastname FROM `'._DB_PREFIX_.'testimonials` t INNER JOIN `'._DB_PREFIX_.'customer` c ON t.testimonial_id_customer = c.id_customer;'); // Query to count the total number of testimonials
if ($result == true) {
$numrows = 0;
foreach ($result as $key => $row) {
$numrows++;
} 
} else {
$numrows = 1;
}


        $nextpage = "";
$prevpage = "";
// number of rows to show per page
$rowsperpage = Configuration::get('TESTIMONIAL_PERPAGE');


// find out total pages
$totalpages = ceil($numrows / $rowsperpage);
// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
  // cast var as int
  $currentpage = (int) $_GET['currentpage'];
} else {
  // default page num
  $currentpage = 1;
} // end if


// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;


  // get the info from the db 


$result = Db::getInstance()->ExecuteS('SELECT t.testimonial_id, t.testimonial_rate, t.testimonial_resume, t.testimonial_message, t.testimonial_date, t.testimonial_again, t.testimonial_status, t.testimonial_response, c.firstname, c.lastname FROM `'._DB_PREFIX_.'testimonials` t INNER JOIN `'._DB_PREFIX_.'customer` c ON t.testimonial_id_customer = c.id_customer WHERE testimonial_status = "Enabled" ORDER BY testimonial_id DESC LIMIT '.$offset.', '.$rowsperpage.';'); // Query to return the testimonials on that page
// while there are rows to be fetched...
foreach ($result as $key => $data) {
     $time = $result[$key]["testimonial_date"];
     $t = explode(" ", $time);
     $result[$key]["testimonial_date"] = date("d F Y", strtotime($t[0]));      
     } 
if ($result != false) {
foreach ($result as $key => $row) {
$results[] = $row;
$time = $result[$key]["testimonial_date"];
     $t = explode(" ", $time);
     $result[$key]["testimonial_date"] = date("d F Y", strtotime($t[0]));     
}
} else {
$results[] = "";
}


/****** pagination links ******/
// range of num links to show
$range = 3;


// if not on page 1, don't show back links
if ($currentpage > 1) {
  // show << link to go back to page 1
  
  // get previous page num
  $prevpage = $currentpage - 1;
  // show < link to go back to 1 page
} // end if 


// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
  // get next page
  $nextpage = $currentpage + 1;


} // end if
/****** end pagination links ******/
 $this->smarty->assign(array(
 'http_host' => $_SERVER['HTTP_HOST'],
 'this_path' => $this->_path,
         'base_dir'=> __PS_BASE_URI__,
                                  'testimonials' => $results,
                                  'currentpage' => $currentpage,
                                  'prevpage' => $prevpage,
                                  'nextpage' => $nextpage,
                                  'totalpages' => $totalpages
 ));


 return $this->display(__FILE__, 'displaytestimonials.tpl');
}


     public function displayrandomTestimonial()
     {
      $result = Db::getInstance()->ExecuteS('SELECT t.testimonial_id, t.testimonial_rate, t.testimonial_resume, t.testimonial_message, t.testimonial_date, t.testimonial_again, t.testimonial_status, t.testimonial_response, c.firstname, c.lastname FROM `'._DB_PREFIX_.'testimonials` t INNER JOIN `'._DB_PREFIX_.'customer` c ON t.testimonial_id_customer = c.id_customer
where testimonial_status = "Enabled" ORDER BY date_added DESC LIMIT '.Configuration::get('TESTIMONIAL_PERBLOCK'));
      foreach ($result as $key => $data) {
      $time = $result[$key]["testimonial_date"];
      $t = explode(" ", $time);
      $result[$key]["testimonial_date"] = date("d F Y", strtotime($t[0]));      
      }            
      return $result;
     }    
      
     


function hookLeftColumn()  //display a block link to the front office testimonials page
{
            $testimonials = $this->displayrandomTestimonial();
            if (!empty($testimonials)) {
        $this->smarty->assign(array(
'this_path' => $this->_path,
'testims' => $testimonials
));
       } else {
        $this->smarty->assign(array(
'this_path' => $this->_path,
        'testimonial_id_customer' => ''
));
       }
return $this->display(__FILE__, 'blocktestimonial.tpl');
}


function hookRightColumn()  //display a block link to the front office testimonials page Same as hookLeftColumn
{
return $this->hookLeftColumn();
}




public function hookHeader()
{
if (Configuration::get('PS_CATALOG_MODE'))
return;


$this->context->controller->addCSS(($this->_path).'css/testimonial.css', 'all');


}






        /* Module Config Page Functions */


/* This function administers the testimonials module
*
*
*/




public function getContent()
{
 if (isset($_POST['Enable']) OR isset($_POST['Disable']) OR isset($_POST['Delete']) OR isset($_POST['Update'])  OR isset($_POST['submitConfig']) OR isset($_POST['Backup']))
         {
             $this->_postProcess();
         }


          // echo var_dump($_POST);
$this->_html = $this->_html.'<h2>'.$this->displayName.' v.'.$this->version.'</h2>';
                $this->_html .= $this->_displayConfigForm();
                $this->_html .= $this->getadminTestimonials();


                return $this->_html;
        }


      private function _postProcess()
     {


          if (Tools::isSubmit('submitConfig'))
            {
                $reCaptcha = Tools::getValue('reCaptcha');
                if ($reCaptcha != 0 AND $reCaptcha != 1)
                        $output .= '<div class="alert error">'.$this->l('recaptcha : Invalid choice.').'</div>';
                else
                {
                        Configuration::updateValue('TESTIMONIAL_CAPTCHA', intval($reCaptcha));
                }


         
               $recaptchaPub = strval(Tools::getValue('recaptchaPub'));


                 if (!$recaptchaPub OR empty($recaptchaPub))
$this->_html .= '<div class="alert error">'.$this->l('Please enter your public key').'</div>';
else
                        {
                            Configuration::updateValue('TESTIMONIAL_CAPTCHA_PUB', strval($recaptchaPub));


                        }


               $recaptchaPriv = strval(Tools::getValue('recaptchaPriv'));
               
                 if (!$recaptchaPriv OR empty($recaptchaPriv))
$this->_html .= '<div class="alert error">'.$this->l('Please enter your private key').'</div>';
else
                        {
                            Configuration::updateValue('TESTIMONIAL_CAPTCHA_PRIV', strval($recaptchaPriv));


                        }


$perPage = strval(Tools::getValue('perPage'));


                 if (!$perPage OR empty($perPage))
$this->_html .= '<div class="alert error">'.$this->l('Please enter the amount of testimonials per page').'</div>';
else
                        {
                            Configuration::updateValue('TESTIMONIAL_PERPAGE', strval($perPage));
                        }


                        $perBlock = strval(Tools::getValue('perBlock'));


                 if (!$perBlock OR empty($perBlock))
$this->_html .= '<div class="alert error">'.$this->l('Please enter the amount of testimonials in the module').'</div>';
else
                        {
                            Configuration::updateValue('TESTIMONIAL_PERBLOCK', strval($perBlock));
                        }


              }


         if (isset($_POST['Backup']))
            {
                $result = Db::getInstance()->ExecuteS("SELECT * from `"._DB_PREFIX_."testimonials`");
                if ($result == true) {


                 $filename = dirname(__FILE__).'/backup.csv';
                    $fp = fopen($filename, 'w');


                 foreach ($result as $key => $res) {
                        fputcsv($fp,$res);                                                
                 }


                 $this->_html .= $this->displayConfirmation($this->l('The .CSV file has been successfully exported') );
                 fclose($fp);
                }


                else {
                    $this->_html .= $this->displayError($this->l('No Testimonials to Backup'));
                }
            }


          if (isset($_POST['Delete']))
         {
             foreach($_POST['moderate'] as $check => $val)
             {
                 $deleted=Db::getInstance()->Execute('
                 DELETE FROM `'._DB_PREFIX_.'testimonials`
                 WHERE testimonial_id =  "'.($val).'"
                 ');
             }
           }


         if (isset($_POST['Enable']))
                 {
                     foreach($_POST['moderate'] as  $check => $val)
                     {
                         $enabled=Db::getInstance()->Execute('
                         UPDATE `'._DB_PREFIX_.'testimonials`
                         SET `testimonial_status` = "Enabled"
                         WHERE `testimonial_id` = "'.($val).'"');
                     }
                 }


       if (isset($_POST['Disable']))
             {
  
foreach($_POST['moderate'] as  $check => $val)
                    {
              
$disabled=Db::getInstance()->Execute('
                         UPDATE `'._DB_PREFIX_.'testimonials`
                         SET `testimonial_status` = "Disabled"
                         WHERE `testimonial_id` = "'.($val).'"');
                     }
               }
  
if (isset($_POST['Update']))
             {
                    foreach($_POST['moderate'] as  $check => $val)
                    {
              $testimonial_message =  "testimonial_message_".$val;
                        //echo $testimonial_message;
$testimonial_message = $_POST[$testimonial_message];
                         
$update=Db::getInstance()->Execute('
                         UPDATE `'._DB_PREFIX_.'testimonials`
                         SET `testimonial_message` = "'.$testimonial_message.'"
                         WHERE `testimonial_id` = "'.($val).'"');
                    }
              }
  
  
return $this->_html;


     }


function backupFile(){  //check if backup file exists
if (file_exists(dirname(__FILE__).'/backup.csv')) {
return true;
}
return false;
}




        function _displayConfigForm(){
  global $cookie;
$this->smarty->assign('base_dir', __PS_BASE_URI__);
$this->smarty->assign('requestUri', $_SERVER['REQUEST_URI']);
$this->smarty->assign('recaptcha', Configuration::get('TESTIMONIAL_CAPTCHA'));
$this->smarty->assign('recaptchaPriv', Configuration::get('TESTIMONIAL_CAPTCHA_PRIV'));
$this->smarty->assign('recaptchaPub', Configuration::get('TESTIMONIAL_CAPTCHA_PUB'));
$this->smarty->assign('recaptchaPerpage', Configuration::get('TESTIMONIAL_PERPAGE'));
$this->smarty->assign('recaptchaPerBlock', Configuration::get('TESTIMONIAL_PERBLOCK'));
        $this->smarty->assign('backupfileExists', $this->backupFile());
return $this->display(__FILE__,'displayadmincfgForm.tpl');
        }




function getadminTestimonials()
{
$results = null;
$testimonials = Db::getInstance()->ExecuteS('SELECT * FROM `'._DB_PREFIX_.'testimonials` ORDER BY testimonial_date DESC');
// while there are rows to be fetched...
foreach ($testimonials as $key => $testimonial) {
$results[] = $testimonial;
}






$this->smarty->assign(array(
                  'testimonials' => $results,
     'requestUri', $_SERVER['REQUEST_URI'],
                  'http_host', $_SERVER['HTTP_HOST'],
     'base_dir', __PS_BASE_URI__,
     'this_path' => $this->_path
 ));


return $this->display(__FILE__,'displayadmintestimonialsForm.tpl');
        }


}
?>
Edited by YakaLire (see edit history)
Link to comment
Share on other sites
  • 2 weeks later...
YakaLire Newbie

YakaLire

Posted
  • Author
Posted

Pour info j'ai réussi à faire marcher ma jointure, pour ceux que cela peut intéresser, voici les codes fonctionnels :

 

Dans le fichier php :

				$result = Db::getInstance()->ExecuteS('SELECT t.testimonial_id,
				t.testimonial_rate, 
				t.testimonial_resume, 
				t.testimonial_message, 
				t.testimonial_date, 
				t.testimonial_again, 
				t.testimonial_status, 
				t.testimonial_response, 
				c.firstname AS cfirstname, 
				c.lastname As clastname
				FROM `'._DB_PREFIX_.'testimonials` t 
				INNER JOIN `'._DB_PREFIX_.'customer` c ON t.testimonial_id_customer = c.id_customer 
				WHERE testimonial_status = "Enabled" 
				ORDER BY testimonial_id DESC LIMIT '.$offset.', '.$rowsperpage.' ;');

Grâce à l'id customer de la table testimonials j'affiche les nom et prénom dans le fichier tpl avec {$nr.cfirstname} {$nr.clastname}

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...