Getting difficult data from db with MySQLi

PHP
1

Hi

I want to achieve something, maybe someone can help me out here. I am getting data from db like this:

SELECT *, favoritesfo.id as aid, favorites.favsiteid as siteis  from favoritesfo
LEFT JOIN favorites ON favorites.foid=favoritesfo.id  
 LEFT JOIN site ON favorites.favsiteid=site.id 
, user
 where favorites.id IS NOT NULL and favoritesfo.userid=user.id  
and 
(MATCH(favoritesfo.name) AGAINST('$q' IN BOOLEAN MODE )
 or MATCH(site.title) AGAINST('$q' IN BOOLEAN MODE )) 
and 
favoritesfo.priv='0' and (favoritesfo.type='0' or favoritesfo.type='1') 
         group by favoritesfo.id

This following is from the first JOIN, this is what item I want from the folder(favoritesfo)
to display:```
AND favorites.orde = (
SELECT MIN(m2.orde)
FROM favorites m2
WHERE m2.foid = favoritesfo.id
)


This(without the second add code) returns the "folder"(favoritesfo) even if inside item "site.title" is matched, but the problem is that if "site.title" makes the match(not the folder name), only this one item(one folder item  "table site") is retrievable from the "folder"(favoritesfo).  

I want that all items are retrievable from the folder not just the matched one inside the folder, but also others that are inside favoritesfo (maybe I want to display not the matched one but the first one in the folder, like the second code, I want the lowest "orde" count to be the first displayed not the matched one).

So if I retrive $siteis I won't get the matched one only but the first one in the row (ordered by "orde" row)

I hope it's not too confusing... 

Thank you for your time
2

What is the meaning of the field orde? What is it’s purpose? Because you need a group by here:

SELECT MIN(m2.orde) 
FROM favorites m2 
WHERE m2.foid = favoritesfo.id

The way you did you’re just getting the minimum orde inside the favorites folder. I don’t think that this is what you wanted.
Maybe you should post your tables structure.

1 Like
3

Thank you again, I fixed it already by adding extra attributed to JOIN site, just one thing ( or favorites.foid=favoritesfo.id ) was mission, so that the site JOIN will return all sites in folder not only the one.

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.