mysql order by last value in table separated by comma

Question

so I'll try to be short, I'm trying to ORDER BY ID from table whose values are separated by commas. Here's an Image:

I want them to be ordered like this when displayed: 34, 40, 33, 0.

here's my code:

            /// movie
            $myuserid = $_SESSION['user_id'];
            $mymovies = "SELECT p_movies FROM user_details WHERE user_id='$myuserid' ";
            $mymoviesresult = mysqli_query($_db,$mymovies);
            $mymovie = mysqli_fetch_array($mymoviesresult); 
            /// movie

            $mypurchases = $mymovie['p_movies'];
            $sql = "SELECT * FROM movies WHERE find_in_set(id, '$mypurchases') > 0";
            $res_data = mysqli_query($_db,$sql);
                if($res_data = mysqli_query($_db, $sql)){
                    if(mysqli_num_rows($res_data) > 0){
                        while($row = mysqli_fetch_array($res_data)){ include 'movies/appearance.php'; }}}

I tried to add ORDER BY DESC and ASC, it doesn't work. Is it possible to order results in the manner stated above? Don't know how to explain it better, sorry for my English.

You might want to read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Nick, Jan 07 '20 at 09:18
*I want them to be ordered like this when displayed: 34, 40, 33, 0.* What determines exactly this sorting order? — Akina, Jan 07 '20 at 09:25
@Akina Yes, i want it to be reversed order of p_movies as Barmar said and those numbers could've been words, for example - a,c,e,b,o and i would want them to be displayed - o,b,e,c,a in mysql — Reinis Vārtukapteinis, Jan 07 '20 at 10:13

Barmar · Accepted Answer · 2020-01-07T10:20:57.073

1

Since FIND_IN_SET() returns the position in the list, you can use that for your ordering.

There's also no need to use two queries, you can join the tables. And you should use a prepared statement to prevent SQL injection.

$stmt = $_db->prepare("
    SELECT m.*
    FROM movies AS m
    JOIN user_details AS d ON FIND_IN_SET(m.id, d.p_movies)
    WHERE d.user_id = ?
    ORDER BY FIND_IN_SET(m.id, d.p_movies) DESC") or die($_db->error);
$stmt->bind_param("s", $_SESSION['user_id']);
$stmt->execute();
$result = $stmt->get_result();
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        include 'movies/appearance.php';
    }
}

edited Jan 07 '20 at 10:20

answered Jan 07 '20 at 09:27

Barmar

741,623
53
500
612

thank you, but I'm a beginner in php language, started it as hobby. Haven't really used table joining. I'm getting **Fatal error : Uncaught Error: Call to a member function bind_param() on bool in** And is d.user_id = ? supposed to be with a question mark? – Reinis Vārtukapteinis Jan 07 '20 at 10:15
I updated the answer to add error checking. What does the full error message say? – Barmar Jan 07 '20 at 10:16
Of course it's supposed to be a question mark. Have you read any of the documentation on prepared statements? The question mark is replaced with the value of the variable in `bind_param`. – Barmar Jan 07 '20 at 10:17
The error **You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE d.user_id = ?' at line 5** Oh, I haven't really read, thank you. I guess I should.... – Reinis Vārtukapteinis Jan 07 '20 at 10:19
Never mind, I was stupid, I had `WHERE` in the wrong place. – Barmar Jan 07 '20 at 10:21

mysql order by last value in table separated by comma

1 Answers1