MySQL - Help :( - Teh Forum

From:	steve	16 Sep 2006 18:55
To:	ALL	1 of 9

30653.1
I am now KING OF JOINS and SUB-QUERIES and things, but this is more scary. I have been collecting "appearence" details on bh101 along with profiles for a while now, with the intention of the best feature in the WORLD. "Find people who look like me" :$ So there's like. Hair colour: BLACK :@ Beard style: LONG :@ Eye colour: GREEN :@ Now. Just matching that to EXACT matches would be fine. But.. I'd like the query to come back, ordered by how many close matches there were. So for instance. The TOP result would be someone who matched all three, the last result would only match one. Is that even possible? :'S

0/0

Reply Quote

More

From:	Peter (BOUGHTONP)	16 Sep 2006 19:18
To:	steve	2 of 9

30653.2 In reply to 30653.1

I think it's possible.

Um. This is off the top of my head and may end up being nonsense, but who cares I'll give it a go anyway. :D

SQL code:

CREATE TEMPORARY TABLE stevealikes
AS
(SELECT id, 1 AS hair, 0 AS beard, 0 AS eyes FROM appearance WHERE hair_colour = 'black')
UNION
(SELECT id, 0 AS hair, 1 AS beard, 0 AS eyes FROM appearance WHERE beard_style = 'long')
UNION
(SELECT id, 0 AS hair, 0 AS beard, 1 AS eyes FROM appearance WHERE eye_colour = 'green');
 
SELECT id, hair, beard, eyes, SUM(hair,beard,eyes) AS total
FROM stevealikes
GROUP BY id, hair, beard, eyes
ORDER BY total DESC;

Something along those lines.... but it wont be exactly that, there's bound to be an error or two.

3.1415P265E589T932E846R64338

0/0

Reply Quote

More

From:	steve	16 Sep 2006 20:01
To:	Peter (BOUGHTONP)	3 of 9

30653.3 In reply to 30653.2

Oooh I can understand what you have done there, it is GOOD

I cannot however get it to run :( Syntex error! I have it to

sql code:

CREATE TEMPORARY TABLE stevealikes
                                AS
                                (SELECT uid, 1 AS hair, 0 AS beard, 0 AS eyes FROM people_main WHERE des_haircolour = 'black')
                                UNION
                                (SELECT uid, 0 AS hair, 1 AS beard, 0 AS eyes FROM people_main WHERE des_facialhair = 'long')
                                UNION
                                (SELECT uid, 0 AS hair, 0 AS beard, 1 AS eyes FROM people_main WHERE des_eyecolour = 'green');
 
                                 
                                SELECT uid, hair, beard, eyes, SUM(hair,beard,eyes) AS total
                                FROM stevealikes
                                GROUP BY uid, hair, beard, eyes
                                ORDER BY total DESC;

To work with stuff, but alas, it still errors out :'S The top create-bit seems to run through without errors. It doesn't like me adding the SELECT bottom part XD

0/0

Reply Quote

More

From:	Peter (BOUGHTONP)	16 Sep 2006 20:07
To:	steve	4 of 9

30653.4 In reply to 30653.3
Hmmm, dunno if PHP allows composite queries - have you tried running them individually, one after the other? Or even directly in a MySQL terminal to check the code is okay... `3.1415P265E589T932E846R64338`

0/0

Reply Quote

More

From:	steve	16 Sep 2006 20:19
To:	Peter (BOUGHTONP)	5 of 9

30653.5 In reply to 30653.4
I THINK I DID IT XD The sum(blah,blah,blah) as total wasn't valid - I bodged it to; (sum(blah)+sum(blah)+sum(blah)) as total And yay :D

0/0

Reply Quote

More

From:	steve	16 Sep 2006 20:36
To:	Peter (BOUGHTONP)	6 of 9

30653.6 In reply to 30653.4
omg DOUBLE HELP PETER :C Thankyou very much as I have made it work :D BUT! How do I make it so it only reports one of each ID? It's turning up some IDs several times in each :'S

0/0

Reply Quote

More

From:	Peter (BOUGHTONP)	16 Sep 2006 21:33
To:	steve	7 of 9

30653.7 In reply to 30653.6
Yay. :D Just change it to `SELECT DISTINCT ...` and it should return only one per person. `3.1415P265E589T932E846R64338`

0/0

Reply Quote

More

From:	steve	16 Sep 2006 21:36
To:	Peter (BOUGHTONP)	8 of 9

30653.8 In reply to 30653.7
Yay :D Hooray XD!

0/0

Reply Quote

More