mySQL group by value depending on another value

[2260419]

I have some videos I upload to server for online viewing. Sometimes they are too long and I have to cut them into pieces. I am a beginner in this and I'm a little lost. 

the idea is this:

I have these table in MySQL:

and this is my php code I have so far:

$sql="SELECT * FROM table_name WHERE type = 'tutorials' and type2='word' and video_id='01' ORDER BY server ASC,format ASC,resolution ASC";

$result = mysql_query($sql);

<table>
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
        switch ($row['server'])
        {
        case "youtube":
          echo '<td><a href="';
          echo $row['short_link'].'"><image youtube/></a></td>';
          break;
        case "vimeo":
          echo '<td><a href="';
          echo $row['short_link'].'"><image vimeo/></a></td>';
          break;
        case "dailymotion":
          echo '<td><a href="';
          echo $row['short_link'].'"><image dailymotion/></a></td>';
          break;
        default:
           echo '<td><a href="';
               echo $row['short_link'].'"></a></td>';
        }
  echo "<td>" . $row['server'] . "</td>";
  echo "<td>" . $row['format'] . "</td>";
  echo "<td>" . $row['resolution'] . "</td>";
  echo "<td>" . $row['size'] . "</td>";
  echo "<td>" . $row['user'] . "</td>";
  echo "</tr>";
  }
echo "</table>"; 

but I need that if the server has parts, put all in a div and appears like this:

sometimes the video has more parts, I searched for "group by" but I don't know how to integrate it.

 

How I can do that or What do I need search in google to find examples?

[kicken]

You'll want to do this grouping from within PHP, not as part of the query using GROUP BY. Create an array to hold the results and store the results in a multi-dimensional structure with the first level being the main video information, and a second level for the parts listing. Eg:

$results = array();
while ($row = mysql_fetch_array($result)){
   if (!isset($results[$row['server']]){
       $row['videos'] = array($row['short_link']);
       $results[$row['server']] = $row;
   }
   else {
       $results[$row['server']]['videos'][] = $row['short_link'];
   }
}

var_dump($results);

Then you'd output the results using a foreach loop over the $results array. For each row in the results array, the key 'videos' would be another array of all the videos for that server.

[Barand]

You could use a query like this

SELECT user
, GROUP_CONCAT(short_link) as short_links
, server
, format
, SUM(size) as size
, resolution
FROM table_name 
WHERE type = 'tutorials' and type2='word' and video_id='01'
GROUP BY user, server

However, your sizes need to be stored as INT (eg value 100) and not as VARCHAR (eg value 100MB)

 

When processing, explode the "short_links" field.

[2260419]

You'll want to do this grouping from within PHP, not as part of the query using GROUP BY. Create an array to hold the results and store the results in a multi-dimensional structure with the first level being the main video information, and a second level for the parts listing. Eg:

$results = array();
while ($row = mysql_fetch_array($result)){
   if (!isset($results[$row['server']]){
       $row['videos'] = array($row['short_link']);
       $results[$row['server']] = $row;
   }
   else {
       $results[$row['server']]['videos'][] = $row['short_link'];
   }
}

var_dump($results);

Then you'd output the results using a foreach loop over the $results array. For each row in the results array, the key 'videos' would be another array of all the videos for that server.

 

 

Thanks Kicken, but the truth, I try to understand the code, but I ended losing me more. 

I try if watching the array could accommodate it, but could not.

 

 

You could use a query like this

SELECT user
, GROUP_CONCAT(short_link) as short_links
, server
, format
, SUM(size) as size
, resolution
FROM table_name 
WHERE type = 'tutorials' and type2='word' and video_id='01'
GROUP BY user, server

However, your sizes need to be stored as INT (eg value 100) and not as VARCHAR (eg value 100MB)

 

When processing, explode the "short_links" field.

 

it was easier with this option. I could do it the first time. 

 

 

thanks guys. you are amazing.