ansaurus

Question

Split Strings and arrange db to display products in PHP

Answer 1

+1 A:

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>

Bill Karwin 2009-12-27 05:20:34

Dear Bill, thank you for prompt reply. I forgotten to mention that I am not db developer and I can't change anything inside the tables. I can only base on the status as it is. I need to find a way to display products which has been placed in db in the way I have described. I can understand that it can be again to normalization and other standards. Have you an idea how this can be done ?

DanielDanielson 2009-12-27 06:09:08

Answer 2

A:

Hey,

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

yatin 2010-07-02 07:01:56

ansaurus

tags:

views:

answers:

Split Strings and arrange db to display products in PHP

related questions