Question

I m new in php. Could you please help me to find the way to properly arrange following task:

Table "Products"
id - details
1 - 1-30,2-134:6:0;;2-7:55:0;;1-2,2-8:25:0 - where this string can be very long
2 -
3 - 1-360:17:0;;1-361:185:0

Every product 1, 2, 3, ... are stored in db in one row, although product is additionally recognized separately per size and color. That is why some products can have more sizes and colors then the others. Sometimes product is only in one size but in some colors. In such case this one size is not stored in db but only colors. Table is irregularly filled. The details column is not mandatory (can be empty). The details column consists in this way, example:
1-30,2-134:6:0;;2-7:55:0 where main separator is ;; , so this string will be splited to:
1-30,2-134:6:0
2-7:55:0

Considering frist splited row, there are next parts to be splited:
1-30 - first part
2-134 - second part
6 - third part
0 - forth part
The frist, thirt and forth parts appears always. The second part appears sometimes. The first part and second part can start from 1-XXX or 2-XXX. The third part means number of products which are on stock. The forth part is not important and can be ignored.

Table "Type"
id - value
1 - Product
2 - Color

Table "Arguments"
id - value
1 - sr20 h12
2 - sr21 h13
3 - blue
..
30 - sr25 h15
134 - red

Considering the above tables the early mentioned example would means:
1-30 would means 1=product, 30=sr25 h15
2-134 would means 2=color, 134=red

Could you please help me prepare php script which would properly display products in the way:
Product 1 - size: sr25 h15, color: red, on stock: 6
Product 1 - size: sr30 h16, color: blue, on stock: 13
Product 1 - size: sr35 h20, color: pink, on stock: 2
Product 2
Product 3 - color: white, on stock 4

############################

Thanks to Bill s suggestions I have splitted fileds by:
$products = explode(";;", $details);
foreach ...
$fields = explode(":", $products);
foreach ...
$attribs = explode(",", $fields);
foreach ...

However, I don t know how should I use:
($attrib_type, $attrib_value) = explode("-", $attribs[0]);

I have also prepared pre-loaded association table for arguments, but I don t know how can I use it.

This is my code:

$results = mysql_query("SELECT id, name, details FROM products") or die ( query error );

if(mysql_num_rows($results) > 0) {

echo "<table width= 780  cellpadding= 2  border= 1  rules= rows >";
    echo "<th width=50 align= left >ID</th>";
    echo "<th width=350 align= left >Name</th>";
    echo "<th width=380 align= left >Details</th>";

    while($r = mysql_fetch_array($results))
    {
        echo "<tr>";
            echo "<td width=50 align= left >".$r[0]."</td>";
            echo "<td width=350 align= left >".$r[1]."</td>";
            //echo "<td width=350 align= left >".$r[2]."</td>";

            $string = "$r[2]";
            $products = explode( ;; , $string);
            foreach ($products as $p)
            {
                $fields = explode( : , $p);
                foreach ($fields as $f)
                {

                    $attribs = explode( , , $f);
                    foreach ($attribs as $a)
                    {

                        $attrib_type_value = explode( - , $a);
                        foreach ($attrib_type_value as $t)
                        {

                            if ($t[0] == 1 or $t[0] == 2)
                            {
                                $query1 = mysql_query("SELECT products_type.id FROM products_type WHERE products_type.id = $t ")
                                or die( error query1:   . mysql_error());

                                $query2 = mysql_query("SELECT products_arguments.value FROM products_arguments WHERE products_arguments.id =  $t ")
                                or die( errur query2:   . mysql_error());

                                if(mysql_num_rows($query1) > 0)
                                {
                                    while($r2 = mysql_fetch_array($query1))
                                    {
                                         echo "<tr>
                                            <td width=350 align= left >".$r2[0]."</td>";
                                         echo "</tr>";
                                    }
                                }
                            }
                        }
                    }
                 }
            }
        echo "</tr>";
     }
echo "</table>";

}

Answer 1

update: I m going to offer another answer to your question, but I ll leave my original response included below.

You need to use a PHP function like explode() to separate the elements of your "details" string. For example, supposing you have fetched the row from the database and $details contains the string, you can separate it into individual products like this:

$products = explode(";;", $details);

Then you can separate the product fields like this:

$fields = explode(":", $products[0]);

Then you can separate the attributes like color, size, etc. like this:

$attribs = explode(",", $fields[0]);

Then you can get the key for color/size like this:

($attrib_type, $attrib_value) = explode("-", $attribs[0]);

Now you can use $attrib_type for "color" vs. "size". You can use $attrib_value to look up in the Arguments table. I would recommend pre-loading the arguments into an associative array by primary key, so you can just reference them without having to run extra SQL queries.

This really is much, much more work than you should have to do for this task.

You re sorely in need of database normalization. By using the design you show, you aren t taking advantage of the power your RDBMS could give you with respect to enforcing consistent, structured data.

First you need to create a table to record each type of products.

CREATE TABLE ProductTypes (
  product_id SERIAL PRIMARY KEY,
  description TEXT
);

INSERT INTO ProductTypes (product_id, description) VALUES
  (1234,  Pants ), 
  (3456,  Shirts );

Then you need a table for each SKU. This table records common product information such as quantity in stock, and price.

CREATE TABLE ProductSkus (
  product_sku CHAR(15) PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(9,2) NOT NULL,
  UNIQUE KEY (product_id, product_sku),
  FOREIGN KEY (product_id) REFERENCES ProductTypes(product_id)
);

INSERT INTO ProductSkus (product_sku, product_id, quantity, price) VALUES
  ( B001CKD28O , 1234, 33, 36.99), -- pants
  ( B001CKD270 , 1234, 17, 34.99), -- pants
  ( B002DLD410 , 3456,  8, 17.50); -- shirt

For each subtype of product, you also need a table to record attributes that are specific to the respective type. You might also need some lookup tables listing the permitted values for some of these attributes.

CREATE TABLE PantsSizes (
  size VARCHAR(20) PRIMARY KEY
);
INSERT INTO PantsSizes VALUES ( sr25 h15 ), ( sr20 h12 ), ( sr21 h13 );

CREATE TABLE PantsColors (
  color VARCHAR(20) PRIMARY KEY
);
INSERT INTO PantsColors VALUES ( red ), ( blue );

CREATE TABLE Pants (
  product_sku CHAR(15) PRIMARY KEY,
  product_id INT NOT NULL CHECK (product_id = 1234) -- pants
  size VARCHAR(20) NOT NULL,
  color VARCHAR(20) NOT NULL,
  FOREIGN KEY (product_id, product_sku) REFERENCES ProductSkus(product_id, product_sku),
  FOREIGN KEY (size) REFERENCES PantsSizes(size),
  FOREIGN KEY (color) REFERENCES PantsColors(color)
);

INSERT INTO Pants (product_id, product_sku, size, color) VALUES
  (1234,  B001CKD28O ,  sr25 h15 ,  blue );

Notice how the product_id in this table is limited by a CHECK constraint, and the foreign key to ProductSkus is a compound key. So it can only reference a SKU with the Pants product type. This way you don t accidentally create a row in Pants that references a Shirts SKU.

Now to display pants information in PHP, you can do something simple like this:

<?php

$pdo = new PDO(...connection...);
$stmt = $pdo->query("SELECT * FROM ProductSkus s
                     JOIN ProductTypes t USING (product_id)
                     JOIN Pants p USING (product_id, product_sku)");
?>
<table>
<tr>
 <th>Product</th>
 <th>Size</th>
 <th>Color</th>
 <th>Quantity</th>
 <th>Price</th>
</tr>
<?php while ($row = $stmt->fetch()) { ?>
<tr>
 <td><?php echo $row[ description ]; ?></td>
 <td><?php echo $row[ size ]; ?></td>
 <td><?php echo $row[ color ]; ?></td>
 <td><?php echo $row[ quantity ]; ?></td>
 <td><?php echo $row[ price ]; ?></td>
</tr>
<?php } ?>
</table>

Answer 2

It s a great tutorial for split description with php but what happen if string having html tags in it.

Here is a tutorial to work with that condition

http://phpschools.freehostia.com/other-stuff/split-html-description-in-php

友情链接