CODEWITHSUNDEEP
phpmysqlsql
ReduxedProfessor
ReduxedProfessor

Reputation: 38

Merge multiple database results into one

I am unsure as to what to call this question. I have been thinking a lot about it, and I do not think that what I've chosen covers it adequately, but it was the best I could manage. Forgive me if it is very misleading or incorrect.

I am creating a small CMS for personal use, but I have stumbled upon trouble.

The information is stored in a MySQL database, and the table has three columns: cid, page and cont.

The cid is the identifier of a certain piece of content. If a page has multiple pieces of content, such as a main area of text and a sub area of text, the cid on these rows would be different, but the page would be the same, seeing as the content is on the same page.

On the index page of the CMS I am then displaying all the different pages that exist in the database, as links. The user then clicks the link to the page on which he'd want to change the content.

Now, here is my issue:

When displaying the data from the database, rows that have the same page, but different cid is, logically, displayed as separate links.

That sounded confusing. I'll say it differently:

What I need is that if a result has the same page as another result, these two are merged together into one link, and not displayed as two separate links.

This is how the database looks:

+------------+----------+-----------------------------------------+                                                                           
| cid        | page     | cont                                    |
+-----------------------------------------------------------------+  
| page1-main | page1    | This is the first page on this website! |        
| page2-main | page2    | This is the second page!                |                                                                                                                                 
| page2-sub  | page2    | This is sub-content!                    |                                                                  
+------------+----------+-----------------------------------------+

As I said, what I want is to have any rows that have the same page be merged into one result in my while loop, because what happens now is that they are separated into two different results. This is my loop and query:

$query = mysqli_query("SELECT * FROM content");

while($row = mysqli_fetch_array($query))
  {
   ?>
     <a href="edit.php?p=<?php echo $row['page'];?>">Link!</a>
   <?php
  }

That will, of course, display two results, even though they have the same page. I am unsure as to whether it is some PHP I will have to do, or it is in the MySQL query. I am a good bit unexperienced in SQL, but fairly experienced in PHP.

I've been thinking about some ifs. Like, storing every new result that is displayed in a variable and then comparing them, and if one that has occurred before occurs again, then it is not displayed, or something. But I don't know.

It would be wonderful if anyone could help me out here.

Upvotes: 0

Views: 311

Answers (2)

spencer7593
spencer7593

Reputation: 108400

These queries will return an equivalent result:

SELECT page FROM content GROUP BY page;

SELECT DISTINCT page FROM content ORDER BY page;

If you don't need to return columns other than page, then replace the * in your query (which is shorthand for "all columns", and instead reference just the columns that you actually need returned.)

Upvotes: 0

user4035
user4035

Reputation: 23729

> what I want is to have any rows that have the same page
> be merged into one result

You can use GROUP BY to merge them:

$query = mysqli_query("SELECT * FROM content GROUP BY page");

Upvotes: 1

Related Questions