PHP – Loop through UNION ALL query and add custom text once after the last item per category

I have a union query which joins several categories. I want to include the link after the last item of the category.

The query goes like:

SELECT * FROM table WHERE category = 1 LIMIT 10
UNION ALL
SELECT * FROM table WHERE category = 2 LIMIT 6
UNION ALL
SELECT * FROM table WHERE category = 3 LIMIT 11

… the result is

Item 1 - Category 1
Item 2 - Category 1
Item 3 - Category 1
Item 4 - Category 2
Item 5 - Category 2
Item 6 - Category 3

The output should be like this:

Category 1
- Item 1
- Item 2
- Item 3
Go to category 1
Category 2
- Item 4
- Item 5
Go to category 2
Category 3
- Item 6
Go to category 3

Here is the loop thanks to user @Luuk, but now I would like to include a link after the last item from the category.

$c = "";
foreach ($query as $row)
{
  if ($c!=$row['category']) {
     echo $row['category'] . "<br>"; 
     $c = $row['category'];
  }
  echo 'Item name:' . $row['item'] . "<br>"; 
  
 echo 'Go to category - ' $row['category']; //This needs to be after the last item from the category and should be repeated only once per category change.
}

Answer

There are quite a few questions I have regarding this logic:

1. Are you getting all of this data from the same table, I assume so as you have used the same table name in the question.
2. Why are you limiting each category? do you only ever want to get that many records for that category? what if there was 7 records for category 2, why would you not want to show the last record?
3. What do you mean by go to the category? PHP is rendered on the server and therefore cannot perform any extra actions after it has ran. When you say go to the category do you mean open a new page displaying data about this category?
4. Is the category an ID to another table called “category” that has the information about that category? ( if not it really should be ).

My answer below is assuming the answers to the above questions are 1:Yes; 2:Don’t need a limit; 3:Yes, open a new page; 4:Yes. I don’t mean any offence but I am assuming from the question your new to Web Development so I will explain each step.

Query:

SELECT t1.item_id, t1.item_name, t2.category_id, t2.category_name FROM `item` t1 INNER JOIN `category` t2 ON t1.category = t2.category_id ORDER BY t2.category_id 

Here you’re getting all items in the item table and joining them onto the category table, then ordering it by the category_id to make sure they all come back in the order they will be displayed in. You should end up with a list that looks like this:

'item 1', 1, 'category 1';
'item 2', 1, 'category 1';
'item 3', 1, 'category 1';
'item 4', 2, 'category 2';
'item 5', 2, 'category 2';
'item 6', 3, 'category 3';
'item 7', 3, 'category 3';
so on...

the first variable being the item id, the second being the item name, the third being the category id and the forth being the category name

Code:

<?php   

$result = //code to run the query;
$previousItem['category_id'] = NULL;

foreach($result as $item)
{
  //checking to see if the category has changed
  if($previousItem['category_id'] != $item['category_id'])
  {
    //making sure that were not on the first category
    if($previousItem['category_id'] != NULL)
    {
      //ending the last categories list
      echo '</ul>';
      //echoing a link to another page which will contain the info about that category making sure to include the category_id within a URL parameter
      echo '<a href="/categories.php?category='.$item['category_id'].'"> Go to category - '.$item['category_name'].'</a><br>';

    }

    //echo the category name (I have added a header to make it stand out)
    echo '<h4>'.$item['category_name'].'</h4>';
    //start the list for this category;
    echo '<ul>';

  }

  //echo out the item
  echo '<li>'.$item['item_name'].'</li>'; 
  //set the new previous category for the next loop
  $previousItem = $item;
}

//finish everything off after the loop has ran
echo '</ul>';
echo '<a href="/categories.php?category='.$previousItem['category_id'].'"> Go to category - '.$previousItem['category_name'].'</a><br>';

?>

Then you would have another file, I have called it ‘category.php’ that has a URL parameter telling the category page which category it needs to load. On the category.php page the code to grab that ID would look like this:

<?php

//getting the category_id from the URL
$category_id = $_GET['category'];

//building your query based off the category_id requested
$query = sprintf("SELECT * FROM category WHERE category_id = %s", $category_id);

//you would then run this query and display whatever data you wanted to 

?>

as the user could change the URL parameter you would definitely need to wrap the $category_id in some sort of security function that strips it of any potential hackers code but for simplicity I have not included that.

Although I am not 100% this answers your question, I hope it provides some explanation as to how you would normally do something like this.