CODEWITHSUNDEEP
phpmysqldatabasegetposts
Codedstuff
Codedstuff

Reputation: 199

How to recall items from favourites table

I have a table of favourites with 'id' 'userid' and 'postid'. I'm trying to select all the entries in 'postid' when 'userid' = 1 for example.

I also have a table of posts with 'id' and others of content. I then want to select all the rows where a 'postid'='id' and then echo the content of those rows.

Essentially filtering the posts by which have been favourited by the user.

What I've got is

<?php
include 'connect.php';
$user = $_SESSION['id'];
$getfaves = mysql_query("SELECT postid FROM Favourites where userid='$user'") or die(mysql_query());
if ($rowfave = mysql_fetch_assoc($getfaves))
    {
        $faveposts = $rowfave['id'];

        $getposts = mysql_query("SELECT * FROM Posts where id='$faveposts' ORDER BY id DESC");
        while ($row = mysql_fetch_assoc($getposts))
        {
            $content = 'content';
            echo ($content);
        }
    }
?>

(the $content = 'content'; is just an example, it's not what I'm actually using in my code)

Obviously this is incorrect, probably because I want to select a list of ids and then search for everything in that list, but I've only coded to look for one item. However I don't know how to correct this.

Thanks in advance for any assistance.

Upvotes: 1

Views: 60

Answers (1)

Med Abida
Med Abida

Reputation: 1222

there is a more simple way to do that using just one sql query with the help of INNER JOIN. so your query will be

SELECT POST.*
FROM `Favourites`
INNER JOIN `Post`
ON Post.id = Favourites.postid WHERE (Favourites.userid='$user')
ORDER BY Post.id DESC;

here is doc about INNER JOIN and its utilities

Please let me know if you need more help,

the php part:

<?php
include 'connect.php';
$user = $_SESSION['id'];
$sql="SELECT POST.*
    FROM `Favourites`
    INNER JOIN `Post`
    ON Post.id = Favourites.postid WHERE (Favourites.userid='$user')
    ORDER BY Post.id DESC";
$conn = new mysqli($servername, $username, $password,$dataBase);
  $Result = $conn->query($sql);
  while($row = $Result->fetch_assoc()) {
    //do what you want with the $row, it contain ur resault
  }
?>

feel free to test it and report the problem if there is one

Upvotes: 1

Related Questions