quecoder Posted February 25, 2008 Share Posted February 25, 2008 Hello ! I have a big list of merchants ( 1300+ ) , each one has , website & address ...etc and his combination of products he is selling .. I got a unique list of products ( 3000+) that all merchants share .. some share ( provide ) 3 products , and up to 100.. I have the list of merchants stored properly in a MySQL database , but the problem is , the list of products is just a list of products ordered alphabetically .. **I want my visitors , besides browsing my list of merchants and read their data and products , to search , as well , for merchants by browsing these products keywords and when someone clicks on one , it shows him a list of merchants who provide this product ... Thanks alot Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 what do you mean "list of products ordered alphabetically"? Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 what do you mean "list of products ordered alphabetically"? a list ( in a text file ) of all the products starts from A to Z with no duplicates .. a merchant X can have product P1 , P2 and a merchant Y can have P1 , P90 , P100 from this list. the merchant list is already loaded with products .. Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 I want to let them search and browse merchants by products not only by merchants... Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 So this text file is not in the database? Or you want to produce this file? Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 no , it's not in the database . I want to , for example , insert each product in a record but links to each merchant sell it ..so , when clicking in a product it shows me all merchants who sell it ... Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 no , it's not in the database . I want to , for example , insert each product in a record but links to each merchant sell it ..so , when clicking in a product it shows me all merchants who sell it ... Forget about what you want to do with "clicking" -- that's not a DB question. You first need to import these data... do you have a separate file for each merchant? How is the link identified in the raw text file. Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 here is an image Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 I said , to click , just to simplify it .. anyway , I have all the merchants in records in a database already , each one in a separate record with it's own products field Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 anyway , I have all the merchants in records in a database already , each one in a separate record with it's own products field The problem is you have a list of products... you need another table, merchant_products, one for each product with a merchantID link. Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 yes I know that !!! :-\ I'm asking about how to make that while having 3000 products , and when inserting each in a single record , how to search for all merchants who sell this specific item and put them in another field in the products table .... any thing to automate this process ? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 To automate the searching or to automate inserting all of these records? Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 can I make this as a PHP function that query for each product a list of merchant that have this product in their product field and increase the product table for every product one record with the product and the result of the query .. but how to search the database ,, !! this is the main question for this idea .. I don't know how to search it from within PHP Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 To automate the searching or to automate inserting all of these records? BOTH Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 Well, the inserting is more critical at this stage. Are they all separated by commas, all the time? Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 yes products are sperated by commas in the text file and also in the product field in merchants records .. ALL time Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 please fenway , tell me one answer instead of asking questions that spanned to 2 pages.. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 Unless you wanted to write a stored procedure, it's probably easier to simply pull each merchant record, grab the products field, split by comma, and then insert a new record into the merchant_product table with the appropriate merchant_uid. Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 this will not work , I want to do this with the products , grabbing one product at time from the text file , searching for all possible merchants that shares this product , get their IDs , and insert these IDs as well as this product into a new record in Merchant_products .... Not vice versa , because two or more merchants may share two or more products .. and I want to browse by products Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 but how to store 100+ IDs in the same record of the product , and how to , after that , query for these IDs when browsing by this product Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 aha , it may be the same , but urs is longer Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 your'e not going to store multiple IDs in the same product. as indicated previously, you'll need a table of products, and then a merchant_product table linking the two. then it will be easy to search by product and find all merchants with that product. However, you just have text descriptions for your products right now -- you need to normalize this into a proper database table. You can either (a) create the product table first, then when you parse the product field like I said before, create the merchant_product record by using the merchantID which you know and the productID which you have to look up, or (b) make the merchant_product table with just product text, and then create the products table for that. Quote Link to comment Share on other sites More sharing options...
quecoder Posted February 25, 2008 Author Share Posted February 25, 2008 ah !! but when grabbing product fields, splitting and inserting each product in a record ..i may come across another merchant with that specific product again , so how to deal with this ?? check if it exists first or else, make a new one ? or what did you mean ? and what is the structure of that merchant_product table ? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2008 Share Posted February 25, 2008 ah !! but when grabbing product fields, splitting and inserting each product in a record ..i may come across another merchant with that specific product again , so how to deal with this ?? check if it exists first or else, make a new one ? That depends which way you do it... either way, you will find the same productID at some point, and everything will be fine. If you use the text version, make a new one, and then when you make the product table, you use the same ID. If you make the products table first, there is no issue at all. and what is the structure of that merchant_product table ? It should have ( uid, merchant_uid, product_uid ). Quote Link to comment Share on other sites More sharing options...
aschk Posted February 26, 2008 Share Posted February 26, 2008 Ouch, just looking at the data setup makes me cry :'( You seriously need to consider normalisation that data. Currently by the looks of it you're storing ALL the products for the merchant in a field called products in the merchant table. I can only imagine how this is going to hit performance, and as it is currently obviously it's causing you issues with doing a reverse lookup (i.e. all merchants that have product X). Normalise your data before you even think about doing anything else... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.