Need Help With GROUP BY

MattG

Hi,

I use the GROUP BY command in one of my queries, the trouble is although they share a common field sometimes the data differs in other fields for an individual record. Is it possible with the command to get the data for the selected item and not the first it comes across?

For instance if the data is this

ID, Cat ID, Name, Data
1, 1, Name 1, bla bl1
2, 1, Name 2, bla bl2

when grouping by cat id and group concat the name, irrespective of the id that is input to the database the query will deliver...

Cat ID: 1
Name: Name 1,Name 2
Data: bla bl1

What I want is for the query to output 'bla bl1' when the id on the php page is 1 and 'bla bl2' when the id on the php page is 2.

Is this possible? If the way I've explained it is confusing let me know and I'll try to make it clearer.

Roger_Ramjet

Way you are explaining it make no sense at all to me so have another go. Rea query and output data would be most helpfull.

Piranha

I think I know what you are after. Please tell me if I'm wrong. You want to group by a column and get the "correct" row outputted. That is unfortunatley not possible to do since GROUP BY is used to see for example how many there are in category 2. Can you tell us exactly what you want done and why, then it might be possible to help you find another way to solve the problem.

MattG

Thanks for the replies, okay...

What I have is a table of scenes which links actors to scenes and lists what happens to that person in the scene. By using group concat and group by in the scene I am able to get all actors in the database that are part of a certain scene so if there is one actor it only lists them, if there are three it lists them all.

What I wanted was for it to have the individual scene details for the person who is being viewed. To use an example...

The film is The Godfather, the scene has both Al Pacino and James Caan in it. Within the scene Al Pacino shoots somebody, within the same scene James Caan kicks somebody.

With two rows showing each actor within the scene if the person clicks on to Al Pacino's bio page and scene breakdown they would see...

Scene: X
Starring: Al Pacino, James Caan
Action: Shoots Person

And in James Caan it would be...

Scene: X
Starring: James Caan, Al Pacino
Action: Kicks Person

The best way I found of grouping the people together without going through every single row in the table was to group concat and group by but this only gives out the 'top' row in the group and for group concat will always list the first row first.

I'm still in the learning phase in regards to the many features of MySQL so maybe there is a better way. Hopefully this has made it a little clearer about what I'm trying to achieve, if not let me know.

The Query is...

SELECT movie.name as moviename, scenes.details, CONCAT(',', SUBSTRING(GROUP_CONCAT(scenes.personid),1,7),',') as pids, GROUP_CONCAT(people.name SEPARATOR ', ') as starring FROM movies LEFT JOIN scenes ON movies.id = scenes.movieid LEFT JOIN people ON scenes.personid = people.id WHERE scenes.movieid = '$GET[movieid]' GROUP BY scenes.sceneid HAVING pids LIKE '%,$GET[pid],%'

Piranha

May I suggest a totally different way of doing this? I will try to comment in the query to make it as easy as possible to understand. You might have to look at the names of the different tables and columns and might have to tweak it a bit to get it exactly as you want. From your queries I suspect that you output all scenes with the person in from a movie, if this is not correct you have to tweak the query a little.

if (is_numeric($_GET['movieid']) && is_numeric($_GET['pid'])) // If they are not numeric it might be an attempt to "hack" your site. Look at mysql_real_escape_string for handling this in strings. This should be done in EVERY query where you get information from the client.
{
    $movieid = $_GET['movieid']; // The ' sign since movieid is not a constant.
    $pid = $_GET['pid'];
}
else
{
    die("Don't you try with me your punk");
}

$sql = "
SELECT movie.name as mname, scene.name as sname, scene.id, people.name as pname, people.action // And whatever more you need to select
FROM scene // Scene is the main table in this query, that's why I start with that one. I don't think it makes any big difference though
LEFT JOIN movie ON movie.id = scene.movieid // Join movie table
LEFT JOIN people ON scenes.personid = people.id // Joins people table
WHERE scenes.movieid = $movieid
ORDER BY CASE pid // Sets the selected actor first
    WHEN $pid THEN 1
    ELSE 2
END";
$result = mysql_query($sql);
$sceneid = -1; // assuming that you will never have a scene with the id -1
while ($row = mysql_fetch_array($result))  // for each row returned
{
    if ($sceneid == $row['id']) // If you have the same sceneid then only add another name
    {
        $starring .= ", " . $row['pname'];
    }
    else // If you have a new scene you have to output everything, start a new scene and fill it with new information.
    {
        if ($sceneid != -1) // You don't want anything outputted the first time you get into the while-loop.
        {
            echo $scene . $starring . $action; // Output everything. You have to fix the formatting.
        }
        $scene = "Scene: " . $row['sname']; // New data to all the variables
        $starring = "Starring: " . $row['pname'];
        $action = "Action: " . $row['action'];
    }
}
echo $scene . $starring . $action; // Last row will not be outputted in the loop, have to output it here.

MattG

Thanks Piranha, I'll try that as soon as I can.