PHP with MySQL SELECT

[RChilton]

I have a MySQL database.  The table name is prodcat - it has 3 fields sku, category and codes.  Some of the categorys have multiple items in it which is seperated by /

 

for example:  Ceramics/Sale Items or just Ceramics

 

in the codes it looks like this:  ceramics,saleitems or just ceramics

 

Basically i want to loop through each sku in the prodcat table and every sku that is in ceramics I want it to echo an image.  And if that sku is in multiple categories it needs to show the image no matter what category you are in.  So if it is a ceramic item that is also on sale I want the image to appear in both those categories

 

I've tried this multiple ways and can get a partial result but can not get it completely the way I want it.  Basically I can get it to give me the result in one category but it won't give me the result in both categories.

 

Thanks for the help.

[Barand]

I recommend you read up on data normalization before going any further

 

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

[Jacques1]

Your database design is broken. Stuffing multiple values in a single attribute is a classical mistake and a common source for problems.

 

In the relational model (which SQL is based on), attributes must be atomic. That is, there's only a single value, not a list, not a complex structure. As soon as you start abusing strings for lists, the data is no longer accessible to the database system. Yes, you may kinda sorta get it working with string operations, but those are all just hack. The fact is the database system cannot do its job.

 

Read up on normalization (in particular the first normal form) and fix the problem with an association table. The query itself will then be trivial.

Edited June 20, 2014 by Jacques1

[Psycho]

You may also have a problem with the field "codes" - the name suggests multiple. So, if you are putting multiple codes in that field, those should be broken out to a separate table as well. But, assuming codes is not storing multiple values and your previous description, the tables might look like this

 

pods

-------------------

sku (primary key)

codes

 

podcats

------------------

sku (foreign key)

category

 

Some data to illustrate how they are used:

 

pods

--------------

sku | codes

--------------

 A    123

 B    456

 C    789

 

 

 

podcats

--------------

sku | category-

---------------

 A    Ceramics

 B    Ceramics

 B    Sale items

 

So, the item with SKU A is only associate with the category Ceramics. But, the item with SKU B is associated with categories Ceramics and Sale items. You can now run a simple query to find all itesm associated with the category Ceramics, such as 

SELECT *
FROM pods
JOIN podcats on pods.sku = podcats.sku
WHERE podcats.category = 'Ceramics'

 

Although, a purist would state that there should be a third table to describe the unique categories and then only reference the category ID in the podcats table.

[RChilton]

So I did not originally develop this database table, but reading more it appears you guys are right on target with what is wrong and what I need to do to correct it.  Here is my main question.  Currently the field in question can have anywhere between 1-6 different attributes in it that are separated by a comma.  How do I get these attributes into their own row with the same sku?  I don't have a lot of mysql experience.

[Jacques1]

Psycho has just explained this in every detail. You need an additional table which assigns SKUs to attributes.

[Psycho]

So I did not originally develop this database table, but reading more it appears you guys are right on target with what is wrong and what I need to do to correct it.  Here is my main question.  Currently the field in question can have anywhere between 1-6 different attributes in it that are separated by a comma.  How do I get these attributes into their own row with the same sku?  I don't have a lot of mysql experience.

 

You will likely need to build a script to be executed one-time to update the records after you revise the tables. It may be able to be accomplished by running a single query, but I don't think so.

 

So, create a prepared statement to insert new records into the podcats table. Run a query to get all the records. Then, while looping through those results, get the SKU and the comma separated categories. Explode the categories on the commas and run the prepared statement for each category passing the SKU and the category value. After you've done this and verified the results you can delete the category field from the original table

[mogosselin]

So I did not originally develop this database table, but reading more it appears you guys are right on target with what is wrong and what I need to do to correct it.  Here is my main question.  Currently the field in question can have anywhere between 1-6 different attributes in it that are separated by a comma.  How do I get these attributes into their own row with the same sku?  I don't have a lot of mysql experience.

 

For this specific case, instead of rows like this:

 

SKU     |        attribute

432B             black,small,medium

837               orange,large,medium

11CA            white,small 

 

You will need 2 new tables.

 

One would hold the attributes. We could call it "attribute". 

Here's what it should contain:

 

Table: Attribute

attributeId     |       attribute

1                             orange

2                             small

3                             medium

4                             large

5                             black

6                             white

 

Now the table that makes the link between the skus and the attributes:

 

Table: prod_attribute (I prefer to add a _ between words, but keep it like what you have)

 

SKU    |  attributeId

432B      5

432B      2

432B      3

837        1

837        4

.....

 

Also, check if, instead of "SKU", you couldn't use an ID in the table. 

 

Now, like Psycho said, you'll need to do that manually if you don't have too much data OR code something to fill the 2 new tables (either in PHP or plain SQL). 

 

And, if you want further help, you could take your database, do a backup of only the structure and add some sample data. That way, we could restore it on our system and understand a little bit more your current structure.

[RChilton]

Had to leave this project for awhile but now I am in full swing.  All the above makes sense and I can restructure the existing information.  The only  part I can not get clear in my head is:  My original data is being exported from another database.  The plan is that the export will have each category path in a separate field something like:

 

SKU          Cat1          Cat2           Cat3           Cat4        Cat 5

78000       Candles     Ceramics    Accents

88000       Bowls        Glass          Accents

98000       Trays          Wood          Accents

 

Then from that export I need to put the info into my website database - from the above info with two tables structured like below:

 

Table - categories

 

id        name          parentid

1         Candles      7

2         Bowls         7

3         Trays           7

4         Ceramics    Null

5         Glass           Null

6         Wood           Null

7         Accents       Null

 

Table - prodcat

 

sku           id

78000       2

78000       4

78000       7

88000       2

88000       5

88000       7

98000       3

98000       6

98000       7

 

Assuming I have handled the above category and subcategories properly I can not figure out how to write a prepared statement so that each sku with each individual category gets from the export into the prodcat table.  Currently  the prepared statement is:

 

        //Insert prodcat data using prepared statemnt if product inserted
        if ($successFlag) {
          /* create a prepared statement */
          if ($stmt = mysqli_prepare($link, "INSERT INTO prodcat (sku, category, codes) VALUES (?, ?, ?)")) {
            /* bind parameters for markers */
            mysqli_stmt_bind_param($stmt, "sss", $data[0], $data[3], $codes);
            /* execute query */
            mysqli_stmt_execute($stmt);
            // if error
            if (mysqli_stmt_error($stmt)) {
                $error = "Product Category Insert Error (" . $data[0] . "): " . mysqli_stmt_error($stmt);
                errorLog($error); array_push($resultMessage, $error);
            // if Successful
            } else {
               $productsCategoryInsertedCounter++;
            }
            /* close statement */
            mysqli_stmt_close($stmt);
          }