Create related items. SQL junkies help!

[gabrielcrowe]

In brief:

I have put, on pages in a store, a javscript that creates a unique ID for any user, browsing the site, then, stores the product refs of each view. Every time a user views a product, a row is added. if they have looked at it before, the ViewNum is incremented for that UID/ref.

Here is my table:

CREATE TABLE IF NOT EXISTS `actTrak` (
`ID` int NOT NULL AUTO_INCREMENT,
`UID` text,
`ProdID` text,
`ViewNum` int,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


So, what i'm looking for, is to use the data set, to work out what products are related, based on those products being viewed also by other people, and what they looked at.

I have looked at neural networks, and some rather crazy sql.

can anyone shed some light on this one? I'v struggled with it before and never got anywhere.

[gabrielcrowe]

Here is a very simple data set:

UID,ProdID
a,1
a,2
a,3
b,6
b,7
b,1

now, because user 'b' looked at product '1', this means that becasue user 'a' looked at this product, as well as products 1,2 and 3, this means that user b is shown these products.

hope this makes it easier to understand.

[pnagames]

Gabe

i maybe way off what you are after but

i think you need a simple query that returns the three most popular items viewed in relation to item "1"

however to do this you may need to change your table structure as you need to record not how many times user viewed one particular item but store the combinations

since you storing the product refs of the visit you should be able to create a table for the visit of all the combinations (i think this is the tricky part)

I.E. for visit 1, customer viewed product 1,2,3,4

which probably means 6 rows of combinations

then pick each combination and increment by one on your master table

[gabrielcrowe]

you're right i think.

i'll need to 'generate' a table, with these combinations. perhaps with a cron job or such.

but even it i changed the structure of the table, how would i record that data?

how can i generate a list for each product for my new table, based on this data?

[fergusw]

Gabe,

Interesting one this. There are quite a few ways of tackling it.
As the dataset grows, with a simple model you would run into the risk of many, many results returned.
You'd need to filter and define the query to return most popular I would have thought?

A couple of (or 3) nested SQL queries would most likely return what you are after.

It would be important to define the psuedo-query logic first though:

i.e.
Return dataset of users who bought product a
then-> return dataset of items these users have bought
then-> order by SUM of ViewNum
then-> show what you need i.e. limit to top 5 etc

Something like that? You can get quite funky with well written SQL. An interesting art form, writing quality SQL queries ;)

[fergusw]

Ah, what the hey - I'll have a stab at the SQL:

SELECT ProdID,SUM NumView as TotalViewNum FROM actTrak WHERE ProdID IN
(SELECT ProdID FROM actTrak WHERE USERID IN
(SELECT UID FROM actTrak WHERE ProdID = @ID)) ORDER BY TotalViewNum DESC

"should" give you a list of all products, by popularity of view of products associated with @ID. You could add a "WHERE ProdID <> @ID" at the end, befoe the order, to exclude the product that you began with.

Not tested, and would most likely fail - but you get the idea! :)

[gabrielcrowe]

nice idea:

$arrRelProdID = array();
$query = "SELECT ProdID,SUM NumView as TotalViewNum FROM actTrak WHERE ProdID IN
(SELECT ProdID FROM actTrak WHERE USERID IN
(SELECT UID FROM actTrak WHERE ProdID = {$prodID})) ORDER BY TotalViewNum DESC";
$result = mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result)>0) {
while ($row = mysql_fetch_array($result)) {
array_push($arrRelProdID, $row2['ProdID']);
}
}


produces:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as TotalViewNum FROM actTrak WHERE ProdID IN (SELECT ProdID FROM actTrak WHERE U' at line 1

Click here (http://www.skateasylum.co.uk/ActSQL/ProductTracker/trakview.php) to see your sql in action.

click the star next to a product to test 'association'. it will simulate being on a product page and pass the prodref into the sql query.

This will create a 4th column, and this column is the 'associated products' sql you just gave.

(note: you can pick up your own UID by browsing to any of the products on skate asylum, you will be assigned one, return to the page linked above and you're UID will be linked to the products you browse.

[fergusw]

Not tested, and would most likely fail - but you get the idea! :)
*Ahem*
:p

[acompton]

SELECT ProdID, SUM(ViewNum) as TotalViewNum FROM actTrak WHERE ProdID IN
(SELECT ProdID FROM actTrak WHERE UID IN
(SELECT UID FROM actTrak WHERE ProdID = @ID GROUP BY ProdID, UID) GROUP BY ProdID) GROUP BY ProdID ORDER BY TotalViewNum DESC

*tested version of Fergus's SQL*

[gabrielcrowe]

i did, lol, and i ahve found errors and fixed them, new error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause


looking it up now.

[gabrielcrowe]

SELECT ProdID, SUM(ViewNum) as TotalViewNum FROM actTrak WHERE ProdID IN
(SELECT ProdID FROM actTrak WHERE UID IN
(SELECT UID FROM actTrak WHERE ProdID = @ID GROUP BY ProdID, UID) GROUP BY ProdID) GROUP BY ProdID ORDER BY TotalViewNum DESC

*tested version of Fergus's SQL*

holy shit, i think i just crashed my testing server.

my tracking table has about 77k records. this nested beauty made the mysqld consume about 4,345,652,345 tb of ram.

I have no doubt in my mind that it gives the correct result, but i cannot view it. hehehe.

[fergusw]

There ya go - Nice one Alan.

[fergusw]

holy shit, i think i just crashed my testing server.

There ya go - Nice one Alan! :p

[fergusw]

Fortunately for you Gabe Actinic does not use PHP/mySQL and the site is still running? :p :p :p

[gabrielcrowe]

My Actinic does. heh.

[gabrielcrowe]

ack! i cannot get that query to run on my dataset, no matter how much time, cpu and ram i throw at it.

i think one's out of the reach of mere mortals. i'll need deep blue to crunch a single product's associateds.

*sigh*

:D

[fergusw]

Hmm - there will be a better, more efficient query that can be run. As I said, SQL can be an art form to build correctly.

[Jan]

This rather evil piece of SQL will do it. My table is called test and this gets a list out for user a

SELECT DISTINCT ProdID From Test WHERE UID <> "a" AND UID IN
(SELECT Test.UID FROM Test
WHERE (((Test.ProdID) In
(SELECT ProdID FROM Test
GROUP BY ProdID HAVING Count(ProdID) > 1))));

Includes the product being viewed as well ATM but you can easily screen that out either in the SQL or in the app.

Regards,