How can I use `DESC by id WHERE userID` that has 2 or more users valued

Question

The problem here is the result is always descending by id in every group of userID.

$ids = $_SESSION['userid'];
$uosql = "SELECT userone FROM friends WHERE usertwo='$ids'";
$uoresult = mysqli_query($conn, $uosql);

while($uorow = mysqli_fetch_assoc($uoresult)){
   $userone = $uorow['userone'];

   $postsql = "SELECT * FROM userspost WHERE userID IN($userone) ORDER BY id DESC";         
   $postsqlres = mysqli_query($conn, $postsql);

   while($postrow = mysqli_fetch_assoc($postsqlres)){
      echo $postrow['post']."<br>";
   }
}

FIRST OUTPUT:

marvs post 3
marvs post 2
marvs post 1
juan  post 2
juan  post 1

This is the output I was expecting:

$ids = $_SESSION['userid'];
$uosql = "SELECT userone FROM friends WHERE usertwo='$ids'";
$uoresult = mysqli_query($conn, $uosql);

while($uorow = mysqli_fetch_assoc($uoresult)){
   $userone = $uorow['userone'];

   $postsql = "SELECT * FROM userspost WHERE userID IN(1,4) ORDER BY id DESC";          
   $postsqlres = mysqli_query($conn, $postsql);

}

while($postrow = mysqli_fetch_assoc($postsqlres)){
   echo $postrow['post']."<br>";
}

SECOND OUTPUT:

marvs post 3
juan  post 2
marvs post 2
marvs post 1
juan  post 1

I tried this kind of code because I also want to be like this kind of OUTPUT. Needs to be included my id session.

$ids = $_SESSION['userid'];
$uosql = "SELECT userone FROM friends WHERE usertwo='$ids'";
$uoresult = mysqli_query($conn, $uosql);

while($uorow = mysqli_fetch_assoc($uoresult)){
   $userone = $uorow['userone'];

   $postsql = "SELECT * FROM userspost ORDER BY id DESC";           
   $postsqlres = mysqli_query($conn, $postsql);

}

while($postrow = mysqli_fetch_assoc($postsqlres)){
   echo "id ".$postrow['id']."| userID ".$postrow['userID']."| post ".$postrow['post']."<br>";
}

THIRD OUTPUT: Please note: The numbers in the post is just a number that posted/uploaded in the userspost table together with words.

id 8| userID 1| post marvs post 3
id 7| userID 2| post abegs post 3
id 6| userID 4| post juan post 2
id 5| userID 1| post marvs post 2
id 4| userID 2| post abegs post 2
id 3| userID 1| post marvs post 1
id 2| userID 4| post juan post 1
id 1| userID 2| post abeg post 1

DATABASE STRUCTURE friends

id | userone | usertwo
23 |    4    |   1 
24 |    1    |   2 
25 |    4    |   2

DATABASE STRUCTURE userspost

id | userID |     post      | uploaddate | uploadtime | file
1  |    2   |  abeg post 1  |
2  |    4   |  juan post 1  |
3  |    1   |  marvs post 1 |
4  |    2   |  abegs post 2 |
5  |    1   |  marvs post 2 |
6  |    4   |  juan post 2  |
7  |    2   |  abegs post 3 |
8  |    1   |  marvs post 3 |

If I am not getting it wrong you want the result based on the last number of post columns i.e. abegs post 3 so 3 will be used for DESC purpose right? — Muhammad Asif, May 25 '21 at 05:36
I want the result to DESC all the post where userID ARE in. if it has 2 or more userID the problem will be in my first output. I dont want to include other userID. I just need to desc id's together with userID where the $userone has. — Marvic Clemente, May 25 '21 at 05:46
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, May 25 '21 at 08:48
All I need is to see the post who are only friends with account who is logged in and desc it by id :> — Marvic Clemente, May 25 '21 at 10:49

Shiz · Answer 1 · 2021-05-25T06:00:50.260

-2

Your code:

$ids = $_SESSION['userid'];
$uosql = "SELECT userone FROM friends WHERE usertwo='$ids'";
$uoresult = mysqli_query($conn, $uosql);

while($uorow = mysqli_fetch_assoc($uoresult)){
   $userone = $uorow['userone'];

   $postsql = "SELECT * FROM userspost WHERE userID IN($userone) ORDER BY id DESC";         
   $postsqlres = mysqli_query($conn, $postsql);

   while($postrow = mysqli_fetch_assoc($postsqlres)){
      echo $postrow['post']."<br>";
   }
}

Sample Solution:
$ids = $_SESSION['userid']; $uosql = "SELECT userone FROM friends WHERE usertwo='$ids' ORDER BY userone DESC"; $uoresult = mysqli_query($conn, $uosql); while($uorow = mysqli_fetch_assoc($uoresult)){ $userone = $uorow['userone']; $postsql = "SELECT * FROM userspost WHERE userID IN($userone)"; $postsqlres = mysqli_query($conn, $postsql); while($postrow = mysqli_fetch_assoc($postsqlres)){ echo $postrow['post']."<br>"; } }

Analyzing your code, it seems you wanted to make the userone IDs to descend.

Now that I saw your data structure, you might want to try this instead:

$ids = $_SESSION['userid'];
$uosql = "SELECT userone FROM friends WHERE usertwo='$ids'";
$uoresult = mysqli_query($conn, $uosql);

while($uorow = mysqli_fetch_assoc($uoresult)){
   $userone = $uorow['userone'];

   $postsql = "SELECT * FROM userspost WHERE userID IN($userone) ORDER BY id DESC, RIGHT(post,1) DESC";         
   $postsqlres = mysqli_query($conn, $postsql);

   while($postrow = mysqli_fetch_assoc($postsqlres)){
      echo $postrow['post']."<br>";
   }
}

Although I don't recommend this but I think this is the most probable answer right now I can come up with.

edited May 25 '21 at 06:00

answered May 25 '21 at 05:12

Shiz

695
10
27

The OUTPUT was juan post 1 juan post 2 marvs post 1 marvs post 2 marvs post 3 I want the output in my 2nd code. :> – Marvic Clemente May 25 '21 at 05:26
juan post 2 juan post 1 marvs post 3 marvs post 2 marvs post 1 Similar in FIRST OUTPUT. :> – Marvic Clemente May 25 '21 at 05:57
we're getting there :D that's how we debug things. if this new edit still won't work, I'll re simulate the problem in my own PC. – Shiz May 25 '21 at 06:02
1

marvs post 3 marvs post 2 marvs post 1 juan post 2 juan post 1 The problem here is that userID grouped instead of desc by id together with the userID WHERE $userone has in it. – Marvic Clemente May 25 '21 at 06:07
Hmmm,, this might be difficult, I'm looking for a way to do this without changing the upper codes and data structure. – Shiz May 25 '21 at 06:24
I've beeen figuring this out for days. Still cant solve the problem – Marvic Clemente May 25 '21 at 06:55
Please **never** recommend the usage of insecure SQL queries – Nico Haase May 25 '21 at 12:37

How can I use `DESC by id WHERE userID` that has 2 or more users valued

1 Answers1