users who favourited this also favourited this

[namtax]

hi i am trying to set up a system similar to amazons, whereby when you search a book, it tells you Customers who bought this item also bought these items( a list of other books)
What i am trying to do is have a system which tells you "users who have favourited this musician have also favourited these artists"...
At the moment I have a query which takes an artists primary id from the url of thier page, and the users primary id who favourited the artist, and puts this into a table, so then you can call the users fav artists, using

SELECT *
FROM artists_fav
WHERE id_usr_fav=#SESSION.auth.idnumber#

But im unsure of how to write a query that would take all the other artists that a user has, who has favourited "massive attack" for example, and present this in a list whereby the artist who has been favourited the most by users who have also favourited massive attack is at the top, then the next popular is second etc

here my site

http://www.musicexplained.co.uk/index_artist.cfm?id_art=38


and if you would like to test the favouriting system, you can use username blanko, and password dranko

any help here would be MUCH appreciated

[Dinghus]

If you are looking for purchases you have to just check the purchase records to see what customers also bought along with the selected item.

Your purchases table would have to have userID and itemID. Search for all userIDs that purchased the selected item and then pull all other itemIDs that these userIDs purchased.

[namtax]

yeah, i have tried to do what you are referring to, but its more complicated than that, I was looking for a more indepth way of how to structure my query etc etc....especially as the list needs to show the other artists that users have favourited in order of popularity

cheers

[Dan_Bracuk]

If I favourite artist abc, what data exists on your db?

[namtax]

if you favourite artist abc, the primary key of artist abc gets entered into my "favourite artists table" along with your session id....

so youll then have the fav table with
id_usr_favtable="your session number"
id_artist_favtable="artist "abc's" primary key(taken from the url)"

cheers

[Dan_Bracuk]

select artist_name, count(artist_name) favourite_count

from artist a join fav on a.artist_id = f1.id_artist_favtable
join
(select id_usr_favtable
from fav
where id_usr_favtable <> "your session number"
and f1.id_artist_favtable = "the one from the url"
) f2 on f1.id_usr_favtable = f2.id_usr_favtable

group by artist_name
order by favourite_count
having favourite_count > some_number

[namtax]

cheers for this, just on way out.....will let you know if this works for me when i try tommorrow, just wanted to say thanks as you have been very helpful in the past on this forum

[namtax]

hi, have changed my query to

<cfquery name="sim_art_2" datasource="#APPLICATION.Datasource#">
SELECT bandname_art, count(bandname_art)as favourite_count
FROM artist_art
JOIN artists_fav
ON artist_art.id_art = f1.artists_fav.id_artkey_fav
JOIN
(
SELECT id_usr_fav from artists_fav
WHERE id_usr_fav <>"#SESSION.auth.idnumber#"
AND f1.id_artkey_fav="#url.id_art#"
)
f2 on fl.id_usr_fav = f2.id_usr_fav
</cfquery>

but am unfortunately now getting this error message

http://www.musicexplained.co.uk/index_artist_1.cfm?id_art=27&CFID=83776&CFTOKEN=57625191

[Dan_Bracuk]

It's the double quotes. If the field is numeric, and id fields should be, you use no quotes. It the field is text, you use single text.

[namtax]

have changed query to this, but still same error message
<cfquery name="sim_art_2" datasource="#APPLICATION.Datasource#">
SELECT bandname_art, count(bandname_art)as favourite_count
FROM artist_art
JOIN artists_fav
ON artist_art.id_art = f1.artists_fav.id_artkey_fav
JOIN
(
SELECT id_usr_fav from artists_fav
WHERE id_usr_fav <> #SESSION.auth.idnumber#
AND f1.id_artkey_fav= #url.id_art#
)
f2 on fl.id_usr_fav = f2.id_usr_fav
</cfquery>

cheers

[Dan_Bracuk]

I don't work with Access, so I'm guessing. Perhaps you have to use inner join instead of join. Also, Access might not support subqueries in the from clause.

To trouble shoot, see if this works:
SELECT bandname_art, count(bandname_art)as favourite_count
FROM artist_art
JOIN artists_fav
ON artist_art.id_art = f1.artists_fav.id_artkey_fav

That will test the join vs inner join possibility.

If that works, it's probably the subquery. In that case, you can try joining to artists_fav twice. You give them different alias names. Or, you can move the subquery to the where clause.

[namtax]

yes, join doesnt work, but inner join does

ill have a play around with the queries and let you know what happens

cheers

[namtax]

hi, following your advice i have pretty much got this up and running, the only thing is, that when i try and run the count (bandname_art) as favourite in the query i get an error message

so

<!---similar artists--->
<cfquery name="sim_art" datasource="#APPLICATION.DataSource#">
SELECT bandname_art
FROM artist_art
LEFT OUTER JOIN artists_fav
ON artist_art.id_art = artists_fav.id_artkey_fav
WHERE id_usr_fav IN
(select id_usr_fav
from artists_fav
where id_usr_fav <> #session.auth.idnumber#
AND id_artkey_fav = #url.id_art#)
AND id_art <>#url.id_art#
</cfquery>

is fine

but

<!---similar artists--->
<cfquery name="sim_art" datasource="#APPLICATION.DataSource#">
SELECT bandname_art, count (bandname_art) as favourite_count
FROM artist_art
LEFT OUTER JOIN artists_fav
ON artist_art.id_art = artists_fav.id_artkey_fav
WHERE id_usr_fav IN
(select id_usr_fav
from artists_fav
where id_usr_fav <> #session.auth.idnumber#
AND id_artkey_fav = #url.id_art#)
AND id_art <>#url.id_art#
</cfquery>
returns the error message of "You tried to execute a query that does not include the specified expression 'bandname_art' as part of an aggregate function."

i was wondering there was another way to order by favourite count...maybe by using another query in conjuction etc etc

cheers

[Dan_Bracuk]

You forgot the group by clause

[namtax]

ok, ill put it in
cheers