Search code examples
phpforms

Looping through database query results to generate a form

I am trying to set up a form on my page using php and am looping through database query results to generate it.

For a bit of understanding, this is for an e-commerce site selling products with different attributes at different prices in different countries. For example, we could have a t-shirt. This can have up to 15 different attributes (in this case colours). Different colours have different prices, and we need to adjust prices depending on what country we are selling to (depending on shipping costs etc). For this example there is only two countries we are selling to (although this could increase).

So what I'm looking for is something like:

<table>
<tr><td>Colour 1</td><td>Price country 1</td><td>Price country 2</td></tr>
<tr><td>Colour 2</td><td>Price country 1</td><td>Price country 2</td></tr>
<tr><td>Colour 3</td><td>Price country 1</td><td>Price country 2</td></tr>
</table>

My php is as follows:

$attributeResult = DB::run("SELECT * FROM attributes WHERE id='$new_product_attribute_id'");
    $countryResult = DB::run("SELECT * FROM countries");
    foreach ($attributeResult as $value) {
        for ($i = 1; $i <= 15; $i++) {
            $attributeColumn = "attribute".$i;
            $checkbox = "checkbox".$i;
            $priceValue = "priceValue".$i;
            $priceCurrency = "priceCurrency".$i;
            $priceAttribute = "priceAttribute".$i;
            if($value[''.$attributeColumn.''] != ""){
                $price_form .= '<tr><td><input type="checkbox" name='.$checkbox.' id='.$checkbox.' value='.$checkbox.'>'.$value[''.$attributeColumn.''].'</td>'; 
                foreach ($countryResult as $countryVal) {
                    $price_form .= '<td>';
                    $price_form .= '<input type="text" placeholder='.$countryVal['currency'].' name='.$priceValue.'_'.$countryVal['id'].' size="10">';
                    $price_form .= '<input type="hidden" value='.$countryVal['id'].' name='.$priceCurrency.'_'.$countryVal['id'].'>';
                    $price_form .= '<input type="hidden" value='.$i.' name='.$priceAttribute.'_'.$countryVal['id'].'>';
                    $price_form .= '</td>';
                }
                $price_form .= '</tr>';
            }
        }
    }

This is what is output on screen:

<tbody>
<tr>
<td><input type="checkbox" name="checkbox1" id="checkbox1" value="checkbox1">Black</td>
<td>
<input type="text" placeholder="€" name="priceValue1_1" size="10">
<input type="hidden" value="1" name="priceCurrency1_1">
<input type="hidden" value="1" name="priceAttribute1_1">
</td>
<td>
<input type="text" placeholder="£" name="priceValue1_2" size="10">
<input type="hidden" value="2" name="priceCurrency1_2">
<input type="hidden" value="1" name="priceAttribute1_2">
</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox2" id="checkbox2" value="checkbox2">White</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox3" id="checkbox3" value="checkbox3">Purple</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox4" id="checkbox4" value="checkbox4">Yellow</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox5" id="checkbox5" value="checkbox5">Red</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox6" id="checkbox6" value="checkbox6">Orange</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox7" id="checkbox7" value="checkbox7">Blue</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox8" id="checkbox8" value="checkbox8">Green</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox9" id="checkbox9" value="checkbox9">Pink</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox10" id="checkbox10" value="checkbox10">Grey</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox11" id="checkbox11" value="checkbox11">Brown</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox12" id="checkbox12" value="checkbox12">Spearmint</td>
</tr>
<tr>
<td><input type="checkbox" name="checkbox13" id="checkbox13" value="checkbox13">Lime Green</td>
</tr>
</tbody>

As you can see, it is entering the second for each loop correctly the first time around, but not again.

I cannot figure out why this is happening. Is there something I'm missing?

Solution

  • I changed my php to the following and I got the result I was looking for:

        $attributeResult = DB::run("SELECT * FROM attributes WHERE id='$new_product_attribute_id'");
    foreach ($attributeResult as $value) {
        for ($i = 1; $i <= 15; $i++) {
            $attributeColumn = "attribute".$i;
            $checkbox = "checkbox".$i;
            $priceValue = "priceValue".$i;
            $priceCurrency = "priceCurrency".$i;
            $priceAttribute = "priceAttribute".$i;
            if($value[$attributeColumn] != ""){
                $price_form .= "<tr><td><input type='checkbox' name='$checkbox' id='$checkbox' value='$checkbox'>$value[$attributeColumn]</td>";
                $countryResult = DB::run("SELECT * FROM countries");
                while ($row = $countryResult->fetch(PDO::FETCH_ASSOC)){
                    $country_id = $row["id"];
                    $country_currency = $row["currency"];
                    $price_form .= '<td>';
                    $price_form .= '<input type="text" placeholder='.$country_currency.' name='.$priceValue.'_'.$country_id.' size="10">';
                    $price_form .= '<input type="hidden" value='.$country_id.' name='.$priceCurrency.'_'.$country_id.'>';
                    $price_form .= '<input type="hidden" value='.$i.' name='.$priceAttribute.'_'.$country_id.'>';
                    $price_form .= '</td>';
                }
            }
            $price_form .= '</tr>';
        }
    }