How to sort array data differently

PHP
1

Hi guys, me yet again :blush:

I have this code to display my list of movies and it works, but I’m not sure what I can do to display them sorted into sections by genre. I only want a genre to show up if I have a movie from that genre in my list. even any tips as to what specifically I need to change to make this work so that I can look it up would be greatly appreciated.


<?php
require_once $_SERVER['DOCUMENT_ROOT'] . '/includes/connect.php';
require_once $_SERVER['DOCUMENT_ROOT'] . '/includes/functions.php';
if (isset($_GET['user'])) {
$username = $_GET['user'];
$username = safe($link, $username);
}
$result = mysqli_query($link, "SELECT id FROM users WHERE username = '$username'");
$row = mysqli_fetch_assoc($result);
if (!$row)
		{
			$error = 'Error.';
			include 'error.php';
			exit();
		}
$userid = $row['id'];
$result = mysqli_query($link, "SELECT movie_name FROM movies INNER JOIN usermovies ON movie_id = usermovies.movieid WHERE usermovies.userid = '$userid'");
if (!$result)
		{
			$error = 'Error.';
			include 'error.php';
			exit();
		}
while ($row = mysqli_fetch_array($result))
		{
			$usermovies[] = array('movie_name' => $row['movie_name']);
		}

$result = mysqli_query($link, 'SELECT genre_name FROM genre');
$row = mysqli_fetch_assoc($result);
	if (!$result)
		{
			$error = 'Error.';
			include 'error.php';
			exit();
		}
	while ($row = mysqli_fetch_array($result))
		{
			$genres[] = array('genre_name' => $row['genre_name']);
		}
?>

<?php foreach ($genres as $genre): ?>
<?php htmloutput(genre['genre_name']); ?>
<?php endforeach; ?>

<?php foreach ($usermovies as $usermovie): ?>
<?php htmloutput($usermovie['movie_name']); ?>
<?php endforeach; ?>
2

What is your table structure? Generally speaking it’s more efficient to sort the data at query time rather than after the fact, but we need to know the table structure to give any insight into that.

If you do need to sort after querying, [fphp]array_multisort[/fphp] will probably be the tool you end up using.

3

these are my tables.

movies (
movie_id
movie_name
genre_id
movie_region
release_date )

genres (
id
genre_name )

users (
id
username
email
password )

usermovies (
userid
movieid )

4

Something like this

SELECT m.movie_name, g.genre_name
FROM movies m
INNER JOIN usermovies u ON m.movie_id = u.movieid
LEFT JOIN genre g ON g.id = m.genre_id
WHERE usermovies.userid = ‘$userid’

5

Assuming every movie has a genre,


SELECT movies.movie_name
     , genre.genre_name
  FROM users
INNER
  JOIN usermovies
    ON usermovies.userid = users.id
INNER
  JOIN movies
    ON movies.movie_id = usermovies.movieid
INNER
  JOIN genre
    ON genre.id = movies.genre_id
 WHERE users.username = '$username'
ORDER
    BY genre.genre_name
     , movies.movie_name



$groups = array();
while ($row = mysqli_fetch_assoc($result)) {
    $groups[ $row['genre_name'] ][] = $row;
}


// study the structure of the multidimensional array we created
// by doing print_r($groups); and view html source




foreach ($groups as $genre_name => $movies) {
    echo "$genre_name\
";
    foreach ($movies as $movie) {
        echo "$movie[movie_name]\
";
    }
}
6

works beautifully, thank you! studying how it works right now.

how can I add a linebreak after each genre+movie section? at the moment it displays like:

GENRE
movie
movie
GENRE
movie
movie

I want to be able to style / code each section (genre+that genres movies) individually. I plan on figuring out how to display stats for each genre next to the genre name next.

7

Try adding some output to various points in those nested loops. Study the effects.