SQL not doing anything in PHP

[laurenssvo92]

Hi,

I am working on adding comments to my blog.  When I enter the SQL information for selecting the comments from the DB it works on the PHP MYADMIN, but when I run in PHP,  It doesn't work and I receive no error messages.  I know this is along post, but I am really stuck and don't know what to do from here.

This part isn't working:

$articlenummer= $_SESSION['nummer'];

    $sql = "SELECT * FROM `comments` LEFT JOIN `artikelen` ON comments.nummer= artikelen.nummer where artikelen.nummer = $articlenummer ORDER BY artikelen.nummer";

 

Here is the HTML:

	
echo('    <!-- GIVE YOUR PAGE OR PRODUCT A POST ID -->
    <input type="hidden" id="post_id" value="999"/>

    <!-- CREATE A CONTAINER TO LOAD COMMENTS -->
    <div id="comments"></div>');

echo('    <!-- CREATE A CONTAINER TO LOAD COMMENTS -->
    <div id="comments"></div>
  <!-- CREATE A CONTAINER TO LOAD REPLY DOCKET -->
    <div id="reply-main"></div>');
	
	

   
}

PHP code

 

<?php
ob_start();
session_start();

class Comments {
	
  /* [HELPER DATABASE FUNCTIONS] */
  protected $pdo = null;
  protected $stmt = null;
  public $error = "";
  public $lastID = null;

  function __construct() {
  // __construct() : connect to the database
  // PARAM : DB_HOST, DB_CHARSET, DB_NAME, DB_USER, DB_PASSWORD
  // ATTEMPT CONNECT

    try {
      $str = "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET;
      if (defined('DB_NAME')) {
        $str .= ";dbname=" . DB_NAME;
      }
      $this->pdo = new PDO(
          $str, DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false
          ]
      );
      return true;
    }
    // ERROR - DO SOMETHING HERE
    // THROW ERROR MESSAGE OR SOMETHING
    catch (Exception $ex) {
      print_r($ex);
      die();
    }
  }

  function __destruct() {
  // __destruct() : close connection when done

    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  function start() {
  // start() : auto-commit off

    $this->pdo->beginTransaction();
  }

  function end($commit = 1) {
  // end() : commit or roll back?

    if ($commit) { $this->pdo->commit(); }
    else { $this->pdo->rollBack(); }
  }

  function exec($sql, $data = null) {
  // exec() : run insert, replace, update, delete query
  // PARAM $sql : SQL query
  //       $data : array of data

    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      $this->lastID = $this->pdo->lastInsertId();
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return true;
  }

  function fetch($sql, $cond = null, $key = null, $value = null) {
  // fetch() : perform select query
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $key : sort in this $key=>data order, optional
  //       $value : $key must be provided, sort in $key=>$value order

    $result = false;
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      if (isset($key)) {
        $result = array();
        if (isset($value)) {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row[$value];
          }
        } else {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row;
          }
        }
      } else {
        $result = $this->stmt->fetchAll();
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return $result;
  }
  
  /* [COMMENTS FUNCTIONS] */
  function get($pid = 0) {
    // get() : get all comments for the given post
    // PARAM $pid : post ID
$articlenummer= $_SESSION['nummer'];

    $sql = "SELECT * FROM `comments` LEFT JOIN `artikelen` ON comments.nummer= artikelen.nummer where artikelen.nummer = $articlenummer ORDER BY artikelen.nummer";
  
	  echo("this is".$sql);
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute([$pid]);
    $comments = [];
    while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
      if (is_numeric($row['reply_id'])) {
        $comments[$row['reply_id']]['reply'][$row['comment_id']] = $row;
      } else {
        $comments[$row['comment_id']] = $row;
      }
    }
    return count($comments) > 0 ? $comments : false;
  }

  function add($pid, $name, $message, $rid, $articlenummer) {
  // add() : add new comment
  // PARAM $pid : post ID
  //       $name : name
  //       $message : comment message
  //       $rid : reply id

    $fields = "`post_id`, `name`, `message`, `nummer`";
    $values = "?, ?, ?, ?";

    // Clean out HTML tags, prevent XSS
    $message = str_replace("<", "&lt;", $message);
    $message = str_replace(">", "&gt;", $message);

    $cond = [$pid, $name, $message, $articlenummer];
    if (is_numeric($rid)) {
      $fields .= ", `reply_id`";
      $values .= ", ?";
      $cond[] = $rid;
    }
    $sql = "INSERT INTO `comments` ($fields) VALUES ($values);";
	  echo($sql);
    return $this->exec($sql, $cond);
  }

  function edit($cid, $name, $message) {
  // edit() : update a comment
  // PARAM $cid : comment id
  //       $name : name
  //       $message : comment message

    $sql = "UPDATE `comments` SET `name`=?, `message`=?, nummer=? WHERE `comment_id`=?;";

    // Clean out HTML tags, prevent XSS
    $message = str_replace("<", "&lt;", $message);
    $message = str_replace(">", "&gt;", $message);

    return $this->exec($sql, [$name, $message, $cid]);
  }

  function delete($cid) {
  // delete() : delete a comment
  // PARAM $cid : comment ID

    $this->start();
    $pass = $this->exec("DELETE FROM `comments` WHERE `comment_id`=?;", [$cid]);
    if ($pass) {
      $pass = $this->exec("DELETE FROM `comments` WHERE `reply_id`=?;", [$cid]);
    }
    $this->end($pass);
    return $pass;
  }
}

?>

 

Javascript

var comments = {

  ajax: function (opt) {

  // ajax() : do AJAX request

  // PARAM opt : AJAX options



    // APPEND FORM DATA

    var data = new FormData();

    for (var key in opt.data) {

      data.append(key, opt.data[key]);

    }



    // INIT AJAX

    var xhr = new XMLHttpRequest();

    xhr.open('POST', "2c-ajax-comments.php", true);



    // WHEN THE PROCESS IS COMPLETE

    xhr.onload = function () {

      if (typeof (opt.load) == "function") {

        opt.load(this.response);

      }

    };



    // SEND

    xhr.send(data);

  },



  load: function () {

  // load() : load comments



    comments.ajax({

      data: {

        req: "show",

        post_id: document.getElementById("post_id").value

      },

      load: function (res) {

        document.getElementById("comments").innerHTML = res;

      }

    });

  },



  reply: function (cid, rid) {

  // reply() : load reply docket

  // PARAM cid : comment ID

  //       rid : reply ID



    comments.ajax({

      data: {

        req: "reply",

        reply_id: rid

      },

      load: function (res) {

        document.getElementById("reply-" + cid).innerHTML = res;

      }

    });

  },



  add: function (el) {

  // add() : add a new reply

  // PARAM el : reference to reply form



    // DATA

    var data = {

      req: "add",

      post_id: document.getElementById("post_id").value,

      name: el.querySelector('input[name="name"]').value,

      message: el.querySelector('textarea[name="message"]').value

    };

    var replyID = el.querySelector('input[name="reply_id"]').value;

    if (replyID != "") {

      data['reply_id'] = replyID;

    }



    // AJAX

    comments.ajax({

      data: data,

      load: function (res) {

        if (res == "OK") {

          // Clear comments

          el.querySelector('input[name="name"]').value = "";

          el.querySelector('textarea[name="message"]').value = "";

          

          // Refresh comments

          comments.load();

        } else {

          alert("ERROR");

        }

      }

    });

    return false;

  }

};



// INIT - LOAD COMMENTS + REPLY DOCKET

window.addEventListener("load", function () {

  comments.load();

  comments.reply("main", "");

});

 

[Barand]

It really helps if you tell what "doesn't work" means, otherwise it tells us nothing.

Although, glancing at that query, try this revised version

$sql = "SELECT * 
        FROM `comments` 
              LEFT JOIN 
             `artikelen` ON comments.nummer= artikelen.nummer 
                         AND artikelen.nummer = $articlenummer 
        ORDER BY artikelen.nummer";

 

[requinix]

Why is the article number coming from the session? Shouldn't the query have a placeholder for the $pid?

[laurenssvo92]

I have tried using the above SQL in PHP, but I'm not getting anything displayed on the page and I don't have a error message coming through.  However, when I tried the query in PHP myAdmin and it provides the correct result. I agree with you "it doesn't work', isn't very helpful, but when I don't know the error message. What else can I say?

I need to know the article number for each comment, so PHP doesn't just add all the comments in each blog post. 

The $articlenummer is coming from a session as the class Comments is in a separate file and wouldn't remember the article (blogpost) number without it.

  // PARAM $pid : post ID
  //       $name : name
  //       $message : comment message
  //       $rid : reply id

 

 

Edited May 30, 2020 by laurenssvo92

[requinix]

7 hours ago, laurenssvo92 said:

The $articlenummer is coming from a session as the class Comments is in a separate file and wouldn't remember the article (blogpost) number without it.

That doesn't make any sense.

Anyway, you skipped over the most important part of what I said: you're trying to pass $pid as a parameter but your query doesn't have a placeholder for it. That means you need to reconsider exactly what is happening around there.

[laurenssvo92]

Should it be remembering the number even if it goes through Javascript? All right, that's a good point. May I ask where does the name Poisonous Administrator come from?  

[requinix]

44 minutes ago, laurenssvo92 said:

Should it be remembering the number even if it goes through Javascript? All right, that's a good point.

Remember? There's nothing to remember. Javascript is passing the value to PHP as a whole, not to a specific file. More technically, the value is in $_POST (because you're using the POST method) and that can be accessed from any PHP code regardless of which file the code lives in.

 

Quote

May I ask where does the name Poisonous Administrator come from?  

It's just a reference to the character in my avatar.