Click here to Skip to main content
15,074,626 members
Please Sign up or sign in to vote.
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
Updated 2-Mar-19 3:18am
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

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[^]
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900