thanks all! as Danny Meidell had pointed out for me, the space between MAX and (ID) i.e. MAX (ID) vs. MAX(ID) had caused the problem. After removing the space the query works fine. TA Bill
-----Original Message----- From: Alan Munter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 12, 2002 12:01 PM To: whiskyworld.de Cc: MySql Lists Geral (E-mail); MyODBC Subject: Re: Tables in MySQL 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 --------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. --------------------------------------------------------------------- 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
