Combine multiple product attributes from MySQL result

Question

I have a mySQL table which has rows of product attributes and each one associated with a particular attribute category (id_attrib_cat).

User is supposed to define a price for each product attribute combination so I need a loop to create an attribute table with a price input at the end of each line.

Attribute category value is important to exclude attributes from same category from being combined.

How can I accomplish this?

EDIT

Example of Attribute categories : Values

Format: square, rounded

Size: S, M, L

Color: white, blue, black, yellow

Example of attribute combination table (loop below should do this):

Square + S + White = [price input]
Square + S + Blue = [price input]
Square + S + Black = [price input]

[...]

$q = mysql_query("SELECT id_attrib_cat, id_attrib, name FROM cms_products_attribs WHERE id_product=10 ORDER BY id_attrib ASC"); 

  while ($row = mysql_fetch_array($q, MYSQL_NUM)) {

      [** attribute combination + price input code **] 

  }

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 25 '16 at 14:26

score 1 · Answer 1 · edited May 23 '17 at 12:23

1

Concatenate in the query itself, using CONCAT

SELECT CONCAT(`id_attrib_cat`, ' ', `id_attrib`) AS `attributes`, `name` 
FROM `cms_products_attribs` 
WHERE `id_product`=10 
ORDER BY `id_attrib` ASC

What this means for you is that you will have a single output from the row:

while ($row = mysql_fetch_array($q, MYSQL_NUM)) {
  $attribs = $row['attributes'];
  echo $attribs . '<input name="price" type="text" />;
}

Mechanically you're likely to need a lot more than this, including full formation of the form and handling the form upon submit, but this should get you started.

When you can you should always let your database do the heavy lifting it was designed for.

Please stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy.

edited May 23 '17 at 12:23

Community

1
1

answered Jan 25 '16 at 14:31

Jay Blanchard

34,243
16
77
119

1

That's a better answer than mine Jay. Experience Matters :) – MDChaara Jan 25 '16 at 15:04
The great thing is that you're gaining experience each day @MDChaara :) – Jay Blanchard Jan 25 '16 at 15:06
Thanks but that's just listing the attributes associated with the product... I need to combine them for user be able to define price for each product combination - I've added for info on my post. Thanks. – Luis Jan 25 '16 at 15:21
@Luis: you mean like a nested loop? – MDChaara Jan 25 '16 at 15:26
1

@Luis you can output a form for each row. Then, when the user fills in the data for each form they can submit either one row at a time or a whole form of data. Did you want us to code that all up for you? – Jay Blanchard Jan 25 '16 at 15:30
I've added more details/examples on my post, see above... thanks. – Luis Jan 25 '16 at 15:47

score 0 · Answer 2 · answered Jan 25 '16 at 14:29

First, I would recommend using PDO. mysql_query was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0

Your query should be something like:

$q  =   $db->prepare("SELECT `id_attrib_cat`, `id_attrib`, `name` FROM cms_products_attribs WHERE `id_product`=:id_product ORDER BY `id_attrib` ASC");
$q->execute(array(':id_product'=>"10"));

I believe the query will return multiple rows. instead of while, use foreach:

foreach($q as $row){

$id_attrib_cat  =   $row['id_attrib_cat'];
$id_attrib      =   $row['id_attrib'];
$name           =   $row['name'];

//Price Input goes here
echo $id_attrib_cat.'<br>';
echo $id_attrib.'<br>';
echo $name.'<br>';
echo '<input type = "text" name="'.$id_attrib.'">';
}

Combine multiple product attributes from MySQL result

2 Answers2