How to display own and/or friends posts using PHP?

Question

0.00/5 (No votes)

See more:

I want to display my own posts (as default) but also display friend posts if the Status = '1' otherwise, just display my own posts with the related images.

I only have this code which is showing all the posts with related images and content, because I don't know how to create that code that I am looking for.
The database structures

posts:

| commentid |  comment  | imagesid | author |
---------------------------------------------
|     1     |   fool    |   5557   | test   |
|     2     |  fool2    |   5585   | devel  |
---------------------------------------------

multiple_image:

| id |  image  | imagesid | author |
------------------------------------
| 1  |  name1  |    5557  | test   |
| 2  |  name2  |    5557  | test   |
| 3  |  name3  |    5585  | devel  |
------------------------------------

SQL

Connections:
 
| id |  Friend | FriendReferee | Status | 
-----------------------------------------
| 1  |  test   |     devel     |    1   |
-----------------------------------------

The current fetching structure

The actual code for fetching image(s):

PHP

$sql = "SELECT * FROM multiple_image";
  $result = $conn->query($sql);

  if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $imgs[$row['imagesid]][$row['id']]= "<img width='' src='../images/".$row['name']."' >"; // array of image id's, with arrays of images inside them.
  } 

 }

Fetching the related texts

$sql = "SELECT * FROM posts";
  $result = $conn->query($sql);

  if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
      $commentsToImages[$row['commentid']] = $row['imagesid'];
      $comments[$row['commentid']] = $row['comment']; 
  }     
 }

Displaying the results in sperated divs

PHP

foreach($commentsToImages as $commentID =>$imagesID) {
    ?>
<div class='main'>
  <div class='comments'>


<?php echo $comments[$commentID];?>
  </div>
  <div class='pics'>
    <?php
          foreach($imgs[$imagesID] as $img) { 
                  echo $img;
          }
    ?>
  </div>
</div>

<?php
}
?>

What I have tried:

I have tried to work with if else statement but it still not working as I want it.

Posted 2-Mar-19 2:22am

Galarist_00

Updated 2-Mar-19 3:18am

Add a Solution

Comments

Richard MacCutchan 2-Mar-19 9:12am

"not working as I want it."
You need to explain what that means. What does not happen that you expect, or what does happen that you do not expect? We cannot see your screen or read your mind.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2019-03-02T03:18:00

The main thing that will need to be done is to limit the results that are returned by MySql. This will be done by determining who your friends are by using the WHERE clause. The question mark that is in there is a Parameter which we will be filling in later to identify who you are.

SQL

SELECT FriendReference
FROM   Connections
WHERE  Friend = ?  -- Parameter
AND    Status = 1

This will then become a subquery attached onto the queries you want via the IN statement

SQL

SELECT *
FROM   Multiple_Image
WHERE  Author = ?    -- Parameter
OR     Author IN (
  SELECT FriendReference
  FROM   Connections
  WHERE  Friend = ?   -- Parameter
  AND    Status = 1
)

SELECT *
FROM   Posts
WHERE  Author = ?   -- Parameter
OR     Author IN (
  SELECT FriendReference
  FROM   Connections
  WHERE  Friend = ?   -- Parameter
  AND    Status = 1
)

In your PHP code, your calls to MySql will need to be changed to use prepared statements. This will allow us to safely add a variable into a statement.

PHP

$YourName = "devon"

$sql = $mysqli->prepare("SELECT * FROM posts WHERE Author = ? OR Author IN (SELECT FriendReferee FROM Connections WHERE Friend = ? AND Status = 1)");

$sql->bind_param("n", $YourName);
$sql->execute();

$sql->bind_result($result);

DISCLAIMER: I am not a PHP programmer, so there may be some syntax errors. You should refer to the PHP manual on Prepared Statements
PHP: mysqli::prepare - Manual[^]