laurenssvo92 Posted May 29, 2020 Share Posted May 29, 2020 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("<", "<", $message); $message = str_replace(">", ">", $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("<", "<", $message); $message = str_replace(">", ">", $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", ""); }); Quote Link to comment Share on other sites More sharing options...
Barand Posted May 29, 2020 Share Posted May 29, 2020 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"; Quote Link to comment Share on other sites More sharing options...
requinix Posted May 29, 2020 Share Posted May 29, 2020 Why is the article number coming from the session? Shouldn't the query have a placeholder for the $pid? Quote Link to comment Share on other sites More sharing options...
laurenssvo92 Posted May 30, 2020 Author Share Posted May 30, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
requinix Posted May 30, 2020 Share Posted May 30, 2020 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. Quote Link to comment Share on other sites More sharing options...
laurenssvo92 Posted May 30, 2020 Author Share Posted May 30, 2020 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? Quote Link to comment Share on other sites More sharing options...
requinix Posted May 30, 2020 Share Posted May 30, 2020 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.