mysql procedure to php code

[franbm]

i have this procedure

SELECT a.name AS attribute_name,

av.attribute_value_id, av.value AS attribute_value

FROM attribute_value av

INNER JOIN attribute a

ON av.attribute_id = a.attribute_id

WHERE av.attribute_value_id IN

(SELECT attribute_value_id

FROM product_attribute

WHERE product_id = inProductId)

ORDER BY a.name;

if i do for example catalog_get_product_attributes (1)

i have attributes names and atributtes values for each atributte name for product 1,

example mysql output:

attribute_name | attribute_value_id | attribute_value

+----------------+--------------------+--------------

| Color        | 6                  | White

| Color        | 7                  | Black

| Color        | 8                  | Red

| Color        | 9                  | Orange

| Color        | 10                | Yellow

| Color        | 11                | Green

| Color        | 12                | Blue

| Color        | 13                | Indigo

| Color        | 14                | Purple

| Size        | 1                  | S

| Size        | 2                  | M

| Size        | 3                  | L

| Size        | 4                  | XL

| Size        | 5                  | XXL

+----------------+--------------------+-----------------

the question:

which is the best way to convert that into php code like this(echo html from php

dinamically for each attribute name and atributtes values it has for product selected):

first attribute value Color(attribute_name) like select name,options values attribute_value_id field,

options names attribute_value field

attributes values

<select name="Color">

<option value selected>Select Color</option>

<option value="6">White</option>

<option value="7">Black</option>

<option value="8">Red</option>

<option value="9">Orange</option>

<option value="10">Yellow</option>

<option value="11">Green</option>

<option value="12">Blue</option>

<option value="13">Poor</option>

<option value="14">Purple</option>

</select>

 

first attribute value Size(attribute_name) like select name,options values attribute_value_id field,

options names attribute_value field

attributes values

<select name="Size">

<option value selected>Select Size</option>

<option value="1">S</option>

<option value="2">M</option>

<option value="3">L</option>

<option value="4">XL</option>

<option value="5">XXL</option>

</select>

thanks a lot

[Ken2k7]

With that, the only way would be to loop through them all keeping track of the attribute_name so you know if you should continue adding option tags or closing the select and opening a new select, making sure to change the variable keeping track of the attribute_name to the new value.

 

SAMPLE code -

<?php
// you DB connection info here
$sql = 'YOUR_SQL_HERE';
$results = mysql_query($sql);

// variable to keep track of the attribute name
// defaulted to null
$attribute_name = null;

// variable that holds the output html
// defaulted to an empty string
$output = '';

while ($row = mysql_fetch_assoc($result)) {
     // check if $attribute_name is not equal to $row['attribute_name']
     // If $output is not empty. If so, make the necessary closing </select>
     // Regardless, set $attribute_name = $row['attribute_name'] to keep track
     // of what attribute where are at.
     $row_attribute_name = $row['attribute_name'];
     if ($attribute_name !== $row_attribute_name) {
          $attribute_name = $row_attribute_name;
          if ($output !== '') $output .= '</select>';
          else $output .= '<select>';
     }

     // now that the hard part's done, just write the options
     // I'll let you handle that part.
}

// print out $output to the screen
echo $output;

 

You'll need a few modifications on the <select>. I just put up a sample.