Don't do it this way. Do something like the following:
Assume two tables, "transaction" and "itemlist".
transaction
-----------
transaction_id
customer_id
purchase_date
....whatever other junk is relevant to a given transaction...
itemlist
--------
transaction_id
item_id
here is a way do it in php then just off the top of my head as an example.
$sql = "select transaction_id from itemlist where item_id = 12";
$result = mysql_query($sql,$link);
//loop over transaction_ids
while ($row = mysql_fetch_object($result) ) {
$transaction_id = $row -> transaction_id;
// get all the items from each of those transactions
$sql = "select item_id from itemlist where ".
"transaction_id = '$transaction_id' and ".
"item_id != '12' ";
$result2 = mysql_query($sql,$link);
// loop over these items and tabulate the frequency
while ($row2 = mysql_fetch_object($result2)) {
// add 1 to the array of possible suggestions in
// the index of the item_id you found
$suggestion[$row2 -> item_id] += 1;
}
// sort the suggestion list so that the ones with the
// highest totals are on the top and get the item_ids
// of the first few or something like that...
}
whiskyworld.de wrote:
> Hi,
>
> im currently developing a Webshop system. One of the new features of it
> should be a "Costumers that bought this product also bought...." feature -
> concerning this im currently unsure how to implement it - (LAMPS) - my
> current thought is following:
>
> Costumer A buys Products with NO: 12, 13 , 25 -> system says OK, looks for
> Tables 12,13,25 -> finds nothing creates table 12, inserts 13 and 25 and
> sets sold of each to 1, then creates table 13 and 25 and inserts like it did
> in table 12
>
> now cosumter B buys products 13,12,19 -> system says OK, looks for tables
> 13,12,19 and finds only 12 created, adds 19 into table 12 and updates sold
> from 13 in table 12 -> then does this with table 13 and finally creates
> table 19 (because new) and inserts like in Cosumter A's way...
>
> now the question: is MySQL aware of being with over 1500 tables ??? - is
> there a better way or more efficent way to do the same ?
>
> Hope sb. knows a trick :)
>
> Yours Sincerely
>
> Korbinian Bachl
> www.whiskyworld.de
--
Alan E. Munter NIST Center for Neutron Research
Physical Scientist 100 Bureau Dr., Stop 8562
[EMAIL PROTECTED] Gaithersburg, MD 20899-8562
http://www.ncnr.nist.gov/ (301)975-6244
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
