Problème sauvegarde contenu newsletter PHP


#1

Je travaille sur la création de newsletters avec la possibilité de rajouter des nouveaux modules et les modifier directement en ligne. Dans cet exemple, je souhaiterais modifier le contenu de ma newsletter (titres, textes, images, liens) en cliquant directement l’élément. Au clic une popup s’affiche avec des champs pour modifier l’url du lien, l’url de l’image, le titre ou bien le texte,…etc.

Le problème est que quand j’essaye de modifier un élément et le sauvegarder, je ne récupère rien en base de données. Et le contenu existant est éffacé. Merci par avance !

Pour comprendre ma logique je vous mets le code HTML, JS, PHP complet :

// Structure de la table `newsletter`
CREATE TABLE IF NOT EXISTS `newsletter` (
  `newsletter_id` int(11) NOT NULL AUTO_INCREMENT,
  `url_lien` varchar(255) NOT NULL,
  `titre_url_lien` varchar(255) NOT NULL,
  `titre` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `image` varchar(255) NOT NULL,
  PRIMARY KEY (`newsletter_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

// Contenu de la table `newsletter`
INSERT INTO `newsletter` (`newsletter_id`, `url_lien`, `titre_url_lien`, `titre`, `description`, `image`) VALUES
(1, 'http://link-j-en-profite', 'J''en profite', 'Item 1', 'Incenderat autem audaces usque ad insaniam homines ad haec, quae nefariis egere conatibus, Luscus quidam curator urbis subito visus: eosque ut heiulans baiolorum praecentor ad expediendum quod orsi sunt incitans vocibus crebris. qui haut longe postea ideo vivus exustus est.', 'image_01.jpg'),
(2, 'http://link-contact', 'Contact', 'Item 2', 'Incenderat autem audaces usque ad insaniam homines ad haec, quae nefariis egere conatibus, Luscus quidam curator urbis subito visus: eosque ut heiulans baiolorum praecentor ad expediendum quod orsi sunt incitans vocibus crebris. qui haut longe postea ideo vivus exustus est.', 'image_02.jpg'),
(3, 'http://link-a-propos', 'A propos', 'Item 3', 'Incenderat autem audaces usque ad insaniam homines ad haec, quae nefariis egere conatibus, Luscus quidam curator urbis subito visus: eosque ut heiulans baiolorum praecentor ad expediendum quod orsi sunt incitans vocibus crebris. qui haut longe postea ideo vivus exustus est.', 'image_03.jpg');

// Récupération contenu newsletter
// include connection file
require_once "db.php";

$sql1 = 'SELECT * FROM newsletter WHERE newsletter_id = 1';
$req1 = $db->prepare($sql1);
$req1->execute() or die(print_r($req1->errorInfo()));
$row1 = $req1->fetch();

$sql2 = 'SELECT * FROM newsletter WHERE newsletter_id = 2';
$req2 = $db->prepare($sql2);
$req2->execute() or die(print_r($req2->errorInfo()));
$row2 = $req2->fetch();

$sql3 = 'SELECT * FROM newsletter WHERE newsletter_id = 3';
$req3 = $db->prepare($sql3);
$req3->execute() or die(print_r($req3->errorInfo()));
$row3 = $req3->fetch();
<!-- corps de la newsletter -->
<div>
  <h4 class="row-edit" data-type="title"><?=$row1['titre'];?></h4>
  <p class="row-edit" data-type="image"><img src="<?=$row1['image_01'];?>"></p>
  <p class="row-edit" data-type="text"><?=$row1['description'];?></p>
  <div ><a href="<?=$row1['url_lien']; ?>" class="row-edit" data-type="link" data-id="<?=$row1['newsletter_id']; ?>"><?= $row1['titre_url_lien']; ?></a></div>
</div>
<div>
  <h4 class="row-edit" data-type="title"><?=$row2['titre'];?></h4>
  <p  class="row-edit" data-type="image"><img src="<?=$row2['image_02'];?>"></p>
  <p class="row-edit" data-type="text"><?=$row2['description'];?></p>
  <div ><a href="<?=$row2['url_lien']; ?>" class="row-edit" data-type="link" data-id="<?=$row2['newsletter_id']; ?>"><?= $row2['titre_url_lien']; ?></a></div>
</div>
<div>
  <h4 class="row-edit" data-type="title"><?=$row3['titre'];?></h4>
  <p  class="row-edit" data-type="image"><img src="<?=$row3['image_03'];?>"></p>
  <p  class="row-edit" data-type="text"><?=$row3['description'];?></p>
  <div><a href="<?=$row3['url_lien']; ?>" class="row-edit" data-type="link" data-id="<?=$row3['newsletter_id']; ?>"><?= $row3['titre_url_lien']; ?></a></div>
</div>
<div id="msg"></div>
<!-- Fin corps de la newsletter -->
<!-- Modale -->
<div class="edit" id="edit-image">
  <div class="edit-box" style="">
    <div class="edit-box-title">Edit Link</div>
    <div class="edit-box-content">
      <div class="edit-box-content-text">Title</div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input title"/></div>
      <div class="edit-box-content-text">URL:<span>(full address including   http://)</span></div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input url"/></div>
    </div>
    <div class="edit-box-buttons">
      <div class="edit-box-buttons-save">Save</div>
      <div class="edit-box-buttons-cancel">Cancel</div>
    </div>
  </div>
</div>
<div class="edit" id="edit-title">
  <div class="edit-box" style="">
    <div class="edit-box-title">Edit Link</div>
    <div class="edit-box-content">
      <div class="edit-box-content-text">Title</div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input title"/></div>
      <div class="edit-box-content-text">URL:<span>(full address including   http://)</span></div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input url"/></div>
    </div>
    <div class="edit-box-buttons">
      <div class="edit-box-buttons-save">Save</div>
      <div class="edit-box-buttons-cancel">Cancel</div>
    </div>
  </div>
</div>
<div class="edit" id="edit-text">
  <div class="edit-box" style="">
    <div class="edit-box-title">Edit Link</div>
    <div class="edit-box-content">
      <div class="edit-box-content-text">Title</div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input title"/></div>
      <div class="edit-box-content-text">URL:<span>(full address including   http://)</span></div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input url"/></div>
    </div>
    <div class="edit-box-buttons">
      <div class="edit-box-buttons-save">Save</div>
      <div class="edit-box-buttons-cancel">Cancel</div>
    </div>
  </div>
</div>
<div class="edit" id="edit-link">
  <div class="edit-box" style="">
    <div class="edit-box-title">Edit Link</div>
    <div class="edit-box-content">
      <div class="edit-box-content-text">Title</div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input title"/></div>
      <div class="edit-box-content-text">URL:<span>(full address including   http://)</span></div>
      <div class="edit-box-content-field"><input type="text" class="edit-box-content-field-input url"/></div>
    </div>
    <div class="edit-box-buttons">
      <div class="edit-box-buttons-save">Save</div>
      <div class="edit-box-buttons-cancel">Cancel</div>
    </div>
  </div>
</div>
<!-- Fin Modale -->
console.clear();
(function hover_edit(){
    var big_parent;  
  $(".row-edit").hover(
    function() {
        $(this).append('<span class="row-edit-hover"><i class="fa fa-pencil" style="line-height:30px;"></i></span>');
    }, 
    function() {
        $(this).children(".row-edit-hover").remove();
    }
  )
  $("a").click(function(e) {e.preventDefault()});
  $("body").on('click', ".row-edit-hover i", function(e) {
        e.preventDefault();
        big_parent = $(this).parent().parent();
       //edit title
       if(big_parent.attr("data-type")=='title'){
           $("#edit-title .title").val(big_parent.text());
           $("#edit-title").fadeIn(500);
           $("#edit-title .edit-box").slideDown(500);
       }    
       //edit text
       if(big_parent.attr("data-type")=='text'){
           $("#edit-text .text").val(big_parent.text());
           $("#edit-text").fadeIn(500);
           $("#edit-text .edit-box").slideDown(500);
       }
       //edit image
       if(big_parent.attr("data-type")=='image'){
          $("#edit-image .image").val(big_parent.children('img').attr("src"));
          $("#edit-image").fadeIn(500);
          $("#edit-image .edit-box").slideDown(500);
       }
       //edit link
       if(big_parent.attr("type")=='link'){
          $("#edit-link .title").val(big_parent.text());
          $("#edit-link .url").val(big_parent.attr("href"));
          $("#edit-link").fadeIn(500);
          $("#edit-link .edit-box").slideDown(500);
       }
       $(".edit-box-buttons-save").click(function() {
           var toSave = {};
           $(this).parents('.edit').fadeOut(500).children().slideUp(500);

           big_parent.text($("#edit-title .title").val());
           big_parent.text($("#edit-text .text").val());
           big_parent.attr("href",$("#edit-link .url").val());
           big_parent.attr("src",$("#edit-image .image").val());
           toSave = {
                'id'   : big_parent.data('id'),
                'url'  : big_parent.attr('href'),
                'img' : big_parent.attr('src'),
                'text' : big_parent.text()
           }
           console.info(toSave);
           $.post("page-update.php", toSave, function(response) {
                if(response.status) {
                    $("#msg").removeClass('danger');
                    $("#msg").addClass('success').html(response.msg);
                } else {
                    $("#msg").removeClass('success');
                    $("#msg").addClass('danger').html(response.msg);
                }
             }, 'json');
        });
        $(".edit-box-buttons-cancel").click(function() {
             $(this).parents('.edit').fadeOut(500).children().slideUp(500);
        });
    });
})();
// page-update.php
// include connection file
require_once "db.php";

function update_newsletter($url,$img,$text,$id){
  $sql = "UPDATE newsletter SET url_lien = :url, titre_url_lien = :text, titre = :text, description = :text,  image = :img WHERE newsletter_id = :id";
  $datas = array(":url"=>$url, ":img"=>$img, ":text"=>$text, ":id"=>$id);
  return queryExec($sql,$datas);
}
function queryExec($sql,$datas){
 global $db;
 try {
    $req = $db->prepare($sql);
    $res = $req->execute($datas);
    $result = array(
           'status'=> $res,
           'msg'=>'Saisie enregistrée avec success !',
           'debug'=>array("requete"=>$sql, "datas"=>$datas)
          );
  } catch (Exception $e) {
    //erreur dans la requete
    $result = array(
           'status'=>false,
           'msg'=>'Erreur ! '.$e->getMessage(),
           'debug'=>array("requete"=>$sql, "datas"=>$datas)
     );
  } 
   return $result;
}
//récupération "propre" des variable)
$id = !empty($_POST['id']) ? $_POST['id'] : NULL;
$url = isset($_POST['url']) ? $_POST['url'] : "";
$img = isset($_POST['img']) ? $_POST['img'] : "";
$text = isset($_POST['text']) ? $_POST['text'] : "";

if($id){
   $msg = update_newsletter($url,$img,$titre,$id); 
} else {
  //pas d'ID
  $msg = array( 'status'=>false,
       'msg'=>"Erreur ! ID de ligne vide !",
       'debug'=>$_POST
   );
}      
// send data as json format
echo json_encode($msg);