填充select JQuery和PHP Zend ZF 2

Question

I'm using ZF2, and in a form, I have two selects: one for the specialite (id = select_spe) and promotion (id=select_promo).

I want to populate the second select (from the database) according to the selected value in the first one.

Here is the tables in the database (MySQL):

CREATE TABLE `DB_EMARGEMENT`.`SPECIALITE`(
  `id_specialite` INT UNSIGNED AUTO_INCREMENT,
  `nom_specialite` VARCHAR(30) NOT NULL,
  PRIMARY KEY(`id_specialite`)
) ENGINE = InnoDB;
CREATE INDEX `ix_id_specialite` ON SPECIALITE (`id_specialite`);
CREATE TABLE `DB_EMARGEMENT`.`PROMOTION`(
  `id_promotion` INT UNSIGNED AUTO_INCREMENT,
  `nom_promotion` VARCHAR(30) NOT NULL,
  `id_specialite`INT UNSIGNED NOT NULL,
  `annee_diplome` YEAR NOT NULL,
  PRIMARY KEY(`id_promotion`),
  CONSTRAINT `fk_id_specialite_id_specialite` FOREIGN KEY(`id_specialite`) REFERENCES SPECIALITE(`id_specialite`)
) ENGINE = InnoDB;

EmargementForm.php:

     $this->add(array(
         'name' => 'nom_specialite',
         'type' => 'Select',

         'options' => array(
             'label' => 'Spécialité',
             'label_attributes' => array(
                 'class'  => 'col-sm-4 control-label'
             ),
            // 'empty_option' => '-- Choisissez une spécialité --',
             'value_options' => $this->getSpecialiteOptions(),

         ),
         'attributes' => array(
                    'value' => '6', //set selected to '1'
                    'id'   => 'select_spe',
            )
     ));
     $this->add(array(
         'name' => 'nom_promotion',
         'type' => 'Select',
         'attributes' => array(
             'class' => 'form-control',
         ),
         'options' => array(
             'label' => 'Promotion',
             'label_attributes' => array(
                 'class'  => 'col-sm-4 control-label'
             ),
             'empty_option' => '-- Choisissez une promotion --',
         ),
         'attributes' => array(
                    'id'   => 'select_promo',
            )
     ));

Don't worry about the getSpecialiteOptions(),il fills the select with data form database (and this works well).

In my search.phtml,I have the javascript:

<script type="text/javascript">

    $(document).ready(function () {
    var $promo = $('#select_promo');
    var $departements = $('#departements');
        $("#select_spe").change(function () {
            var id_specialite = $(this).val();
            console.log(id_specialite);
            $.ajax({
                url: 'getPromotions.php',
                data: 'id_specialite='+ id_specialite, // on envoie $_GET['id_region']
                dataType: 'json', // on veut un retour JSON
                success: function(json) {
                    $.each(json, function(index, value) { // pour chaque noeud JSON
                        // on ajoute l option dans la liste
                        //$("#select_promo").append('<option value="'+ index +'">'+ value +'</option>');
                        $promo.append('<option value="'+ 3 +'">'+ A +'</option>');
                        console.log(index, value);
                    });
                }
            });
        });
    });

</script>


<p>
     <a href="<?php echo $this->url('emargement', array('action'=>'index'));?>">Retour</a>
 </p>
<?php
 echo $this->formHidden($form->get('num_carte'));
 ?>
 <div class="form-group <?php if($this->formElementErrors($form->get('nom_specialite'))) : ?>has-error<?php endif; ?>">
     <?php echo $this->formLabel($form->get('nom_specialite')) ?>
     <div class="col-sm-4 col">
         <?php echo $this->formSelect($form->get('nom_specialite')) ?>
         <?php if($this->formElementErrors($form->get('nom_specialite'))) : ?>
             <div class="alert alert-danger">
                 <?php echo $this->formElementErrors($form->get('nom_specialite'));?>
             </div>
         <?php endif; ?>
     </div>
 </div>
 <div class="form-group <?php if($this->formElementErrors($form->get('nom_promotion'))) : ?>has-error<?php endif; ?>">
     <?php echo $this->formLabel($form->get('nom_promotion')) ?>
     <div class="col-sm-4 col">
         <?php echo $this->formSelect($form->get('nom_promotion')) ?>
            <?php if($this->formElementErrors($form->get('nom_promotion'))) : ?>
             <div class="alert alert-danger">
                 <?php echo $this->formElementErrors($form->get('nom_promotion'));?>
             </div>
         <?php endif; ?>
     </div>
</div>
...

This script calls the getPromotions.php (and this script works well, I have tested it):

<?php

if(isset($_GET['go']) || isset($_GET['id_specialite'])) {


    if(isset($_GET['go'])) {

    } else if(isset($_GET['id_specialite'])) {
        $selectstatus = htmlentities(intval($_GET['id_specialite']));
}
}

$selectstatus = 6;

// Connexion à la base de données
try
{
    $bdd = new PDO('mysql:host=localhost;dbname=DB_EMARGEMENT', 'root', 'france47');
}
catch(Exception $e)
{
    die('Erreur : '.$e->getMessage());
}


$requete   = 'SELECT id_promotion, nom_promotion FROM PROMOTION WHERE id_specialite='. $selectstatus;


$selectData = array();

 $resultat = $bdd->query($requete) or die(print_r($bdd->errorInfo()));

    while($donnees = $resultat->fetch(PDO::FETCH_ASSOC)) {
        $selectData[$donnees['id_promotion']][] = utf8_encode($donnees['nom_promotion']);
    }

  //--------------------------------------------------------------------------
  // 3) echo result as json 
  //--------------------------------------------------------------------------
  echo json_encode($selectData);

return $selectData;

The problem is that I can get the selected value of the first select (specialite) (I see that with firebug), but the PHP script doesn't seem to be called.I say that because "console.log(index, value);" doesn't display anything. Do you have an idea how to solve the problem and get the second selected populated according to the selected value in the first one?

Thanks for your help.

Answer 1

I would do it differently if I were you.

1. I'd create a mapper that will handle the database interaction. Specifically, the mapper will be getting the info for your Select element.

2. I'd inject this mapper into the controller via dependency injection to be able to access the mapper from the controller's actions.

3. Have a dedicated controller's action that will be getting the needed info from DB for your Select element with the mapper.

4. It's not really clear from your code if you want get the info into your form's Select at the creation of form (when lands on the web page), or when the user is actually on the web page and did something, and depending on this something you want to retrieve different info from the DB. I'll assume it is the latter case. In your JS script, you send the data via Jquery Ajax $.post() function. Based on the response that you get from the controller's action that I described above, you fill the needed html's select element based on it's id.

Your approach is not bad, it's just when your system gets larger, it becomes very difficult to maintain it. The reason is that you use what's called transaction script, and this leads to boilerplates all over the place. So if your system gets larger, I'd strongly advise redesign.