Edit MySQL query to get likes from user from diffrent table in same query and show if liked or not

Question

Hi Guys i got two tables the first on is called "posts" and looks like this

id    picture    title    description    poster    ip    posterid    .....
123   img-2.jpg  Title 1  Desc 1         Poster    xx    1

The second table is called "love" and looks like this

id    ip    userid    postid    created
1     xx    1         123       date

This is how my MySQL Query looks actually:

<?php

// Get records from the database
    $query = $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT 10");

    if($query->num_rows > 0){ 
        while($row = $query->fetch_assoc()){ 
            $postID = $row['id'];
    ?>

          <!-- POST ITEM START -->
          <div class="post-item">
            <div class="post-asset image">
              <img src="uploads/<?php echo $row['picture']; ?>">
            </div>
            <div class="post-header">
              <h3 class="post-title"><a href="#" data-loader="show"><?php echo $row['title']; ?></a></h3>
              <span class="post-category">

                <a class="favorite-button" href="#" data-post="<?php echo $row['id']; ?>" data-userid="<?php echo $_SESSION['user_id'];?>"><span class="favorite-button-icon fa fa-star-o"></span></a>

              </span>

              <span class="post-date font17"><i class="fa fa-clock-o"></i> <?php $timeago=get_timeago(strtotime($row['created'])); echo $timeago;?></span>
              <span class="post-comments font17"><i class="fa fa-comments-o"></i> 1,3k Reaktionen</span>
            </div>
            <div class="post-footer">
              <a href="#" class="post-author">
                <span class="author-img"><img src="img/avatar.png"></span>
                <span class="author-name">OnePost von<b><?php echo $row['poster']; ?></b></span>
              </a>
              <div class="post-extra">
                <!--<div class="add-favorite" id="heart-container"><span class="lovecount">112</span><input data-post="123" data-user="Jessica Jones" type="checkbox" id="toggle" onclick="myFunction(this)" checked><div id="twitter-heart" class=""></div></input></div>-->

                <div class="add-favorite" id="heart-container"><a href="#" title="Alle Loves anzeigen"><span class="lovecount"><?php echo $row['loves']; ?></span></a>

                <!--<input data-post="<?php echo $row['id']; ?>" data-userid="<?php echo $_SESSION['user_id'];?>" type="checkbox" id="toggle" class="toggle love" onclick="myFunction(this)">-->
                <input data-post="<?php echo $row['id']; ?>" data-userid="<?php echo $_SESSION['user_id'];?>" type="checkbox" id="toggle" class="toggle">

                  <div id="twitter-heart" class=""></div></input></div>
                <div class="post-share"><i class="fa fa-share-alt"></i>
                  <div class="social-links">
                    <a href="#" class="share-facebook social-links-a" data-network="Facebook" data-post="<?php echo $row['id'] ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-facebook"></i></a>
                    <a href="#" class="share-twitter social-links-a" data-network="Twitter" data-post="<?php echo $row['id']; ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-twitter"></i></a>
                    <a href="#" class="share-google-plus social-links-a" data-network="Pinterest" data-post="<?php echo $row['id']; ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-pinterest"></i></a>
                    <a href="#" class="share-whatsapp social-links-a" data-network="WhatsApp" data-post="<?php echo $row['id']; ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-whatsapp"></i></a>
                  </div>
                </div>
              </div>
            </div>
          </div>
          <!-- POST ITEM END -->

          <div class="form-mini-divider"></div>

          <?php } ?>






          <div class="form-divider"></div> -->
        <div class="show_more_main" id="show_more_main<?php echo $postID; ?>">

          <button id="<?php echo $postID; ?>" class="show_more button circle block green">Mehr OnePosts</button>
          <button class="postloading button circle block green" style="display: none;">Lade...</button>


        </div>

        <?php } ?>

What i want to get done is now to get to know if the actual user with its Session id which is also the user id hast liked the post or not and display it in if or if not.

Where did you stuck? getting the data from the DB? Or displaying it in HTML? Don't say both, because that would be two questions. — Paul Spiegel, Jul 20 '19 at 18:55
i get stuck to find out the right query displaying it in html should be possible and i could figure it out @PaulSpiegel — Phil Hedtmann, Jul 20 '19 at 18:57
Any Help? Or Ideas how to get the data in one quer and display it? — Phil Hedtmann, Jul 20 '19 at 19:03

score 0 · Accepted Answer · answered Jul 20 '19 at 19:08

0

Use a LEFT JOIN to the love table and check if the post is actually loved/liked with a IS NOT NULL condition:

<?php

// Get records from the database
    $stmt = $db->prepare("
        SELECT p.*, (l.postid IS NOT NULL) as is_liked
        FROM posts p
        LEFT JOIN love l
          ON  l.postid = p.id
          AND l.userid = ?
        ORDER BY p.id DESC
        LIMIT 10
    ");
    $stmt->bind_param('i', $currentUserId);
    $stmt->execute();
    $result = $stmt->get_result();

    if($result->num_rows > 0){ 
        while($row = $result->fetch_assoc()){ 
            $postID = $row['id'];
    ?>

    [...]

You can access the values with $row['is_liked'] which will contain either 1 or 0.

You need to replace $currentUserId with the variable which holds the ID of current use. That could be $_SESSION['userid'].

answered Jul 20 '19 at 19:08

Paul Spiegel

30,925
5
44
53

Tried it out but shows no query and a blank page do i do anything wrong?`// Include the database configuration file include 'inc/dbConfig.php'; $stmt = $db->prepare("SELECT p.*, (l.postid IS NOT NULL) as is_liked FROM posts p LEFT JOIN love l ON l.postid = p.id AND l.userid = ? ORDER BY p.id DESC LIMIT 10"); $stmt->bind_param('i', $currentUserId); $stmt->execute(); $result = $stmt->get_result(); if($result->num_rows > 0){ while($row = $result->fetch_assoc()){ $postID = $row['id'];` – Phil Hedtmann Jul 20 '19 at 19:20
Do you have the variable `$currentUserId`? Please read my last sentence in the answer. – Paul Spiegel Jul 20 '19 at 19:21
hey buddy, yes replaced it actualy looking this way but shows nothing '// Include the database configuration file include 'inc/dbConfig.php'; $stmt = $db->prepare("SELECT p.*, (l.postid IS NOT NULL) as is_liked FROM posts p LEFT JOIN love l ON l.postid = p.id AND l.userid = ? ORDER BY p.id DESC LIMIT 10"); $stmt->bind_param('i', $_SESSION['user_id']); $stmt->execute(); $result = $stmt->get_result(); if($result->num_rows > 0){ while($row = $result->fetch_assoc()){ $postID = $row['id'];' – Phil Hedtmann Jul 20 '19 at 19:28
"White page" is not helping. Turn error reporting on. See: [how-to-get-mysqli-error-information-in-different-environments](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments-mysqli-fetch-as) – Paul Spiegel Jul 20 '19 at 19:32
he displays me this error `Fatal error: Call to undefined method mysqli_stmt::get_result() in /usr/www/users/onepos/onepost/neu/postline.php on line 172` – Phil Hedtmann Jul 20 '19 at 20:07
Knowing the problem? Any Idea? – Phil Hedtmann Jul 20 '19 at 20:13
What is your PHP version? It must be really old. [mysqli_stmt::get_result](https://www.php.net/manual/en/mysqli-stmt.get-result.php) is available since PHP 5.3 which was released 10 years ago. – Paul Spiegel Jul 20 '19 at 20:14
got 5.3 any other ideas? – Phil Hedtmann Jul 20 '19 at 20:58
will update and see again looking forward that it runs and will accept your answer if works but i think so looks everything good right now – Phil Hedtmann Jul 20 '19 at 21:00
awesome my friend you saved my day is working and the failure or compliction was php version. – Phil Hedtmann Jul 20 '19 at 21:30

Edit MySQL query to get likes from user from diffrent table in same query and show if liked or not

1 Answers1