Combining more than 1 mysql rows into one (from query)

[vadimyer]

Hello there.

 

In brief, I have a MySQL query that selects the data from one table, joins two more tables that contain additional data like tags, etc. And this query uses LIKE to get only necessary data. So, when I foreach the query result in php, it prints 1 result twice, if it contains more than 1 tag. And each tag is placed under each result, alone. Hope I described it clearly.

 

So, the question is, how can I manipulate the database query or the foreach loop so I can print only 1 result containing 2 or more tags in it? Because it seems the query works like it multiplies results, if there are more than 1 rows in joined table that are equal to the id column of the main table.

 

Thanks!

[TeNDoLLA]

Try SELECT DISTINCT FROM bla bla bla..

[vadimyer]

Thanks! but I think that's not what I'm looking for.

 

I have a SEARCH query (that contains LIKE condition and JOIN to join tags table) that returns this array:

 

Array ( [0] => stdClass Object ( [title] => First post [tag] => cats ) [1] => stdClass Object ( [title] => First post [tag] => dogs ) )

 

And when I foreach() it in PHP, it returns 2 same results, but first result contains the tag from [0] and the second - from [1].

 

How can I print only one result containing both two tags? Cause it is the same “first post” but because of tags the query returns me two array objects.

Also notice that tags should be html-formatted and contain links, so mysql GROUP_CONCAT is not the solution.

 

Thanks in advance!

[TeNDoLLA]

Nevermind.

[TeNDoLLA]

I think this kinda of structure would work, cant really test it without knowing your db structure. Tested on mine and it works I think.

SELECT t1.title, GROUP_CONCAT(DISTINCT t2.tag) AS tags FROM table1 t1
JOIN table2 t2 ON t2.title = t1.title
GROUP BY t1.title

 

Gives result like

Array( [title] => "First post" [tags] => "dog, cat, someothertag")

[vadimyer]

I guess I need to make 2 queries, first search query and the second query gets tags for the search result... Because tags have URIs and other properties.

 

My DB structure is like:

 

posts

=========

id  title

1    First Post

2    Second Post

 

tags

=========

id  post_id    tag_title    uri

1    1            cats          /cats/

2    1            dogs          /dogs/

 

 

And the query is:

 

SELECT * FROM posts JOIN tags ON posts.id = tags.post_id WHERE posts.title LIKE '%$match%'

 

This query returns two results, but with one tag in each, as I've already said.

How to combine?

[TeNDoLLA]

Yeah, well if you need to get properties for the tags also then you can do two queries, or you can just do it with one query using JOIN (gettint the multiple titles) and then parse the array/object in code for further needs. It will be pretty simple manage the arrays/objects in PHP's side.

[vadimyer]

Can you please explain how to construct a query on my example? I'm a perfect newbie in this...

[TeNDoLLA]

SELECT t1.title, t2.tag_title, t2.uri
FROM posts t1
JOIN tags t2 ON t1.id = t2.post_id

 

..and then just format the array/object or what ever format the results you get.

[Roman Fedorov]

If you want to get titles of the objects containing both tags i can suggest you two ways:

1) you make a query that makes the INTERSECTION between the selection of titles of one keyword and the selection of titles of second keyword

2) use the query you are using, but group the query by title, and then put a condition count(*)=2 (the number of keywords)

[Murdock]

I previously encountered a similar scenario; this may be of some use.

 

$last_title = '';

foreach ($items as $item) {
if($item['title'] != $last_title) {
	echo $item['title'];
	echo $item['tag'];
   } else {
	echo $item['tag'];
}
$last_title = $item['title'];
}