Exploding data from mysql using PHP

Question

I have a database table where I am storing all the values from an external xml file. Since my project requirement demands me to deal with this unnormalized data. I need to help to extract data in an appropriate way.

I have two web pages (one for categories) and one for products.My database table looks like this:

**product_id       Code        Name                   ProductRange         ProductSubRange         WebCategory**

   1               1002        Lid 30l                  Crystal;Uni         LIDs                    Household products

   2               10433       Casa Basket Silver       Casa                Casa Hipster BASKET      Kitchenware

   3               17443       Casa Basket Ice White    Casa;Laundry         LAUNDRY BASKET         Laundry products

   4               13443       Garden tub               Eden                Eden Garden Pots        Household products

   5               199990       Black Lid 201          Crystal             Crystal Lids            Household products

The product that belong to more than one productRange is indicated my semicolon(;). For example,above product_id 1 with name "Lid 301" belongs to two Product Ranges "Crystal" and "Uni". Same is for product_id 3. However product 2 belongs to single ProductRange.

MY QUESTIONs:

1) How can I construct a query so that it could return "ProductRange" based on my query_string values of "Webcategory"? For example: if I get "Household Products" as my WebCategory from query string, it could give me distinct like this:

      Household Products
                       |-Crystal
                       |-Uni 
                       |-Eden

      Laundry Products
                       |-Casa
                       |-Laundry 

            Kitchenware
                       |-Casa

2) Based on extracted ProductRanges, I want to display products separately in my webpages according to the product range and webcategory. For example, if I choose "Crystal" from above, it could give me Products with product_id "1" and "5" respectively like this:

  Household Products|
                    |-Crystal
                             |-Lid 301 (product_id=1)
                             |-Balck Lid 201 (product_id=5)

                    |-Uni
                             |-Lid 301 (product_id=1)

                    |-Eden
                             |-Garden Tub


         Kitchenware|
                    |-Casa
                          |-Casa Basket silver


    Laundry Products|
                    |-Casa
                            |-Casa Basket Ice White
                    |  
                    |-Laundry
                            |-Casa Basket Ice White

Can anyone guide me how can I retrieve records from the database and what I will need to do as I am new to programming? I would appreciate if anyone could help me in this regard.

I noticed a down vote on this, but I wasn't sure I agreed that it was necessary. I was able to understand the basic question-the person wanted an SQL query to parse a column with multiple values. Since this is an all too common reality for many programmers and since it's difficult to figure out if you haven't done it before, I think its good to have as many of these posts around as that increases the likely-hood that the next person who searches for it will find some post that has hopefully helpful advice. So, I cast my vote up to try to balance it out. — JM Hicks
– JM Hicks, Commented Dec 7, 2012 at 15:18
@JMHicks.I appreciate this.Hick the problem here is that if you don't agree with some programmers or someone can't help you in anyway, they simply down vote the question and i think this is some kind of discouragement to the questioner.Thanks. — Bilal Khalid
– Bilal Khalid, Commented Dec 7, 2012 at 16:26

JM Hicks · Accepted Answer · 2012-11-25 15:23:06Z

In order to get distinct product ranges based on a give WebCategory input = 'XYZ', you can use the following - don't be intimidated by the numberstable, it's just a helpful table that contains rows each with increasing integer values from 1 ... up to N where N is the maximum number of characters in your ProductRange column. These can be made by hand or using a special insert/select query like the one found here: http://www.experts-exchange.com/Database/MySQL/A_3573-A-MySQL-Tidbit-Quick-Numbers-Table-Generation.html

SELECT DISTINCT  
SUBSTRING(ProductRange FROM number FOR LOCATE(';', ProductRange, number) - number) AS ProductRange  
FROM (  
    SELECT ProductRange, CASE number WHEN 1 THEN 1 ELSE number + 1 END number  
    FROM (  
        SELECT mydatabasetable.ProductRange, numberstable.number  
        FROM mydatabasetable  
        INNER JOIN numberstable  
        ON numberstable.number >= 1  
        AND numberstable.number <= CHAR_LENGTH(mydatabasetable.ProductRange)  
        WHERE WebCategory = 'XYZ'  
    ) TT  
    WHERE number = 1 OR (number + 1) <= CHAR_LENGTH(ProductRange)  
) TT  
WHERE SUBSTRING(ProductRange FROM number FOR 1) = ';'  
OR numberstable.number = 1;

In order to retrieve a result set with all values WebCategory, ProductRange and Product for your website you can use the below slightly modified version derived from the above query. So that the results will appear more meaningful at first, I added an ORDER BY clause to keep all same-category, same-product-range products in sequence one after the other. This might or might not be desired as you might prefer to do that in your application/server-script code. In that case you can remove the ORDER BY clause without doing any harm.

SELECT WebCategory,  
SUBSTRING(  
    ProductRange  
    FROM number  
    FOR LOCATE(';', ProductRange, number) - number  
) AS ProductRange,  
Product  
FROM (  
    SELECT WebCategory, ProductRange, Product,  
    CASE number  
    WHEN 1 THEN 1  
    ELSE number + 1  
    END number  
    FROM (  
        SELECT WebCategory, ProductRange, Product, numberstable.number  
        FROM mydatabasetable  
        INNER JOIN numberstable  
        ON numberstable.number >= 1  
        AND numberstable.number <= CHAR_LENGTH(ProductRange)  
    ) TT  
    WHERE number = 1 OR (number + 1) <= CHAR_LENGTH(ProductRange)  
) TT  
WHERE SUBSTRING(ProductRange FROM number FOR 1) = ';'  
OR numberstable.number = 1  
ORDER BY WebCategory, ProductRange, Product

Jammin411 · Accepted Answer · 2012-11-25 13:45:02Z

You are probably going to want to do a GROUP BY clause in your query and maybe an JOIN if the detailed data is in a different table. If I understand you correctly it would look something like this.

SELECT T.WebCategory, T.ProductRange, T2.Product FROM table T 
INNER JOIN table2 T2 ON T2.ProductRange = T.ProductRange
WHERE T.WebCategory = 'Household products'
GROUP BY T.WebCategory, T.ProductRange, T2.Product

It is tough to test my query without having a database setup to test against, but something like the above should return what you are looking for. You will of course need to rename your columns based on the actual names in the second table. Overall though, this should get you started if I understood the question correctly.

Collectives™ on Stack Overflow

Exploding data from mysql using PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related