Jump to content

chrisrulez001

Members
  • Content count

    34
  • Joined

  • Last visited

  • Days Won

    1

chrisrulez001 last won the day on June 28

chrisrulez001 had the most liked content!

Community Reputation

2 Neutral

About chrisrulez001

  • Rank
    Member

Profile Information

  • Gender
    Not Telling
  1. chrisrulez001

    Struggling to select posts & comments

    Thank you for your help 🙂
  2. chrisrulez001

    Struggling to select posts & comments

    Ok thanks for your reply, I'll re-jig the processing. 👍
  3. chrisrulez001

    Struggling to select posts & comments

    Hi, I've posted this here as I think it's more of a MySQL issue/question than PHP. What I'd like to happen is that if this is possible using one query, I'd like to select all the posts from the posts table, and all the relevant comments from the comments table, each post to many comments. Here's what I've tried so far, I've tried an inner join but that only selects the posts with comments only. The closest I've got is with a left join but it doesn't display correctly, it repeats the post (the one with comments) and then shows the rest of the posts. I'm doing a foreach in PHP, here is a print_r from PHP: Array ( [0] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test1 [comment_date_posted] => [posted_by] => ) [1] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test2 [comment_date_posted] => [posted_by] => ) [2] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test3 [comment_date_posted] => [posted_by] => ) [3] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test4 [comment_date_posted] => [posted_by] => ) [4] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test5 [comment_date_posted] => [posted_by] => ) [5] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test6 [comment_date_posted] => [posted_by] => ) [6] => Array ( [ID] => 13 [subject] => test [content] => test [post_date_posted] => 2018-07-08 21:20:24 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [7] => Array ( [ID] => 12 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:42:57 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [8] => Array ( [ID] => 11 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:30:34 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [9] => Array ( [ID] => 10 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:27:22 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) ) I'm not entirely sure how to proceed but I've attached my database schema below: MariaDB [blog]> show TABLES; +----------------+ | Tables_in_blog | +----------------+ | comments | | posts | +----------------+ MariaDB [blog]> DESCRIBE comments; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | post_id | int(11) | NO | MUL | NULL | | | comment | text | NO | | NULL | | | date_posted | varchar(25) | NO | | NULL | | | posted_by | varchar(150) | NO | | NULL | | | ip | varchar(100) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ MariaDB [blog]> DESCRIBE posts; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | subject | varchar(150) | NO | | NULL | | | content | text | NO | | NULL | | | date_posted | varchar(25) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ MariaDB [blog]> SELECT * FROM posts; +----+-------------------+---------------------+---------------------+ | ID | subject | content | date_posted | +----+-------------------+---------------------+---------------------+ | 1 | test | test | 2018-07-08 18:21:12 | | 2 | test | test | 2018-07-08 18:46:15 | | 3 | test | test | 2018-07-08 18:47:08 | | 4 | test | test | 2018-07-08 18:47:23 | | 5 | test | test | 2018-07-08 18:50:38 | | 6 | test | test | 2018-07-08 18:51:15 | | 7 | test | test | 2018-07-08 18:52:05 | | 8 | test | test | 2018-07-08 19:24:14 | | 9 | test | test | 2018-07-08 19:25:49 | | 10 | test | test | 2018-07-08 19:27:22 | | 11 | test | test | 2018-07-08 19:30:34 | | 12 | test | test | 2018-07-08 19:42:57 | | 13 | test | test | 2018-07-08 21:20:24 | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | +----+-------------------+---------------------+---------------------+ MariaDB [blog]> SELECT * FROM comments; +----+---------+---------+-------------+-----------+----+ | ID | post_id | comment | date_posted | posted_by | ip | +----+---------+---------+-------------+-----------+----+ | 1 | 14 | test1 | | | | | 2 | 14 | test2 | | | | | 3 | 14 | test3 | | | | | 4 | 14 | test4 | | | | | 5 | 14 | test5 | | | | | 6 | 14 | test6 | | | | +----+---------+---------+-------------+-----------+----+ MariaDB [blog]> SELECT posts.ID, posts.subject, posts.content, posts.date_posted, -> comments.post_id, comments.comment AS comments, comments.date_posted, comments.posted_by -> FROM posts -> INNER JOIN comments ON posts.ID = comments.post_id; +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | ID | subject | content | date_posted | post_id | comments | date_posted | posted_by | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test1 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test2 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test3 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test4 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test5 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test6 | | | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ MariaDB [blog]> SELECT posts.ID, posts.subject, posts.content, posts.date_posted, -> comments.post_id, comments.comment AS comments, comments.date_posted, comments.posted_by -> FROM posts -> LEFT JOIN comments ON posts.ID = comments.post_id; +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | ID | subject | content | date_posted | post_id | comments | date_posted | posted_by | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test1 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test2 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test3 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test4 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test5 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test6 | | | | 1 | test | test | 2018-07-08 18:21:12 | NULL | NULL | NULL | NULL | | 2 | test | test | 2018-07-08 18:46:15 | NULL | NULL | NULL | NULL | | 3 | test | test | 2018-07-08 18:47:08 | NULL | NULL | NULL | NULL | | 4 | test | test | 2018-07-08 18:47:23 | NULL | NULL | NULL | NULL | | 5 | test | test | 2018-07-08 18:50:38 | NULL | NULL | NULL | NULL | | 6 | test | test | 2018-07-08 18:51:15 | NULL | NULL | NULL | NULL | | 7 | test | test | 2018-07-08 18:52:05 | NULL | NULL | NULL | NULL | | 8 | test | test | 2018-07-08 19:24:14 | NULL | NULL | NULL | NULL | | 9 | test | test | 2018-07-08 19:25:49 | NULL | NULL | NULL | NULL | | 10 | test | test | 2018-07-08 19:27:22 | NULL | NULL | NULL | NULL | | 11 | test | test | 2018-07-08 19:30:34 | NULL | NULL | NULL | NULL | | 12 | test | test | 2018-07-08 19:42:57 | NULL | NULL | NULL | NULL | | 13 | test | test | 2018-07-08 21:20:24 | NULL | NULL | NULL | NULL | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+
  4. chrisrulez001

    if (empty()) not working for me

    No problem 👍
  5. chrisrulez001

    if (empty()) not working for me

    I've just noticed on the form it says methd="post". Instead of method="post" But the problem could be because the form is posting to the file called: backorder.php As the form is posting to this file, the error checking would be better suited in there. Although if you wanted to handle the form processing in one file, you could remove the action="backorder.php".
  6. chrisrulez001

    if (empty()) not working for me

  7. chrisrulez001

    jQuery progressbar issue

    Hi I'm reading from a MySQL database and then looping though the results with PHP. I'm having an issue of using a progress bar within the loop, it's showing the progress bar but it isn't reading the value of a hidden field with the value. The value of the progress bar should be 200 (value in hidden field) but it's just not showing I've uploaded an image of what's happened. Is there something obvious that I'm not seeing? PHP: <?php $List = $this->conn->query("SELECT users.username as Username, challenge.item as Item, SUM(challenge.cost) as Cost FROM users LEFT JOIN challenge ON users.ID = challenge.user_id GROUP BY users.username"); $List->execute(); $row = $List->fetchAll(PDO::FETCH_ASSOC); echo "<div id=\"Challenge\">"; foreach($row as $user) { echo "<div class=\"ChallengeHeader\">"; echo $user['Username']; echo "<span id=\"ChallengeAction\"><input data-index=\"".$user['Username']."\" type=\"submit\" class=\"ChallengeExpand\" id=\"Expand\" value=\"Expand\" /></span>"; echo "</div>"; echo "<div id=\"".$user['Username']."\">"; echo "<div class=\"ProgressBar\"></div>"; echo "<input class=\"Value\" type=\"hidden\" value=\"".$user['Cost']."\">"; echo "</div>"; } echo "</div>"; ?> jQuery: $(document).ready(function(){ $("#Challenge").each(function(){ var $div = $(this); var val = $div.find(".Value").val(); $div.find(".ProgressBar").progressbar({ max: 600, value : val }); if($div.find(".ProgressBar").progressbar("value") <= 500) { $div.find(".ProgressBar").css({ 'background': 'White' }); $div.find(".ProgressBar > div").css({ 'background': 'LightGreen' }); } else { $div.find(".ProgressBar").css({ 'background': 'White' }); $div.find(".ProgressBar > div").css({ 'background': 'Red' }); } }); })
  8. chrisrulez001

    XSS prevention

    Ok thanks your your help
  9. chrisrulez001

    XSS prevention

    Ok thank you for your informative post Jacques1 I'll have a look at Twig and implementing a Content Security Policy. With regards to htmlspecialchars(), I see from your other post you use ENT_QUOTES | ENT_SUBSITITUTE are these the best flags to use?
  10. chrisrulez001

    XSS prevention

    Hi there, It's been a few months since I've touched PHP. I've read that you only use htmlspecialchars() when outputting data (for example from a database). Is that the correct way of doing it? Put to prevent XSS from getting into the database from the form, could you not use preg_match() to whitelist what you can actually enter into the field? Thanks
  11. chrisrulez001

    Problem Creating MySQL Event using PHP

    Thank you very much for helping me out with this. I'll probably use the ->query() method to run this query. EDIT: As I need to pass values to the query, I would probably be best setting PDO prepared query to emulated as suggested EDIT 2: Just tried this with what was suggested above and it works. Thanks again
  12. chrisrulez001

    Problem Creating MySQL Event using PHP

    Thanks for your reply, I'm connecting to the database at the moment through the root account, although that probably makes sense why it isn't creating events. The PDO connection is set to throw any exceptions but I'm not catching any exceptions for this query through a try catch, I'll try that. PHP's error reporting is set to E_ALL. Edit: I tried creating a new user with global privileges and re-ran the query, unfortunately this hasn't worked. I also tried a try catch on the query, no exceptions are thrown, PHP doesn't report any errors either.
  13. Hi there, I'm trying to create a MySQL event using the built-in MySQL event scheduler. This is so that in an hour the users account can be automatically unlocked. The following is the query I'm trying to run: CREATE EVENT update_locked ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE `check_locked` SET `is_locked` = :locked WHERE `check_locked`.`username` = :username; Now if I take out the :username and replace it with a valid user from the database, example 'admin' and also replacing the :locked with 0, the query is run fine from PHPMyAdmin and the event is created. But when I run the query from PHP I get no errors and the query supposedly runs but when I check the events table in the MySQL database. Here is the code I'm trying to run in a function: protected function Lock_Account($username) { //Reset the login attempts to 0 $this->Reset_Login_Attempts($username); //Lock the users account //Use prepared query $Lock = $this->db->prepare("UPDATE check_locked SET is_locked=:locked WHERE username=:username"); //Bind values to prepared query //Execute the lock user prepared query $Lock->execute(array(":locked" => 1, ":username" => $username)); //Create event to unlock the users account after an hour //Use prepared query $Lock_Event = $this->db->prepare("CREATE EVENT update_locked ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE `check_locked` SET `is_locked` = :locked WHERE `check_locked`.`username` = :username;"); //Bind values to prepared query and execute the set the lock event prepared query $Lock_Event->execute(array(":locked" => 0, ":username" => $username)); } I've tried just running the query from PHP with the :locked replaced with 0 and :username replaced with 'admin', that didn't create the event. Thanks in advance.
  14. chrisrulez001

    Why wont this go to mysql form?

    Hi there "it won't send to mysql" isn't detailed enough to help at this stage. Is there any specific error messages?
  15. chrisrulez001

    Whats wrong with line 17?

    Your welcome. Please mark this as fixed.
×

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.