Jump to content

SQL not doing anything in PHP


laurenssvo92

Recommended Posts

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", "");

});

 

Link to comment
Share on other sites

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";

 

Link to comment
Share on other sites

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 by laurenssvo92
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.