0

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 ecommerce site selling products with different attributes at different prices in different countries. For example, we could have a product like a t-shirt for example. 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 say 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 have been playing around with this for some time now and cannot figure out why this is happening. (but it is monday and I am gone a bit braindead trying to figure it out) Is there something I'm missing? Any help with this would be greatly appreciated.

Paddy Hallihan
  • 1,550
  • 3
  • 24
  • 56
  • what's with the for loop inside the foreach? – treyBake Mar 12 '18 at 14:51
  • I think this might help. http://php.net/manual/en/function.reset.php You need to reset the pointer on the array – Richard Housham Mar 12 '18 at 14:55
  • @ThisGuyHasTwoThumbs I get each attribute from a db which can have up to 15 columns in this case there are only 13 colours therefore I have the `if($value[$attributeColumn] != "")` statement, plus I need to add the $i to the names of the form elements to reference each field – Paddy Hallihan Mar 12 '18 at 14:56
  • @PaddyHallihan is it for a CMS system or something? – treyBake Mar 12 '18 at 15:00
  • @RichardHousham Thanks for your comment. I was not familiar with the reset() function. I tried `reset ($countryResult);` both inside the foreach loop and just after it but with no difference – Paddy Hallihan Mar 12 '18 at 15:02
  • @ThisGuyHasTwoThumbs it is for my first ecommerce site that I am trying to build from scratch. – Paddy Hallihan Mar 12 '18 at 15:03
  • @PaddyHallihan would it not be better to store the values as individual rows? e.g. foreach checkbox {insert} then just call by id? – treyBake Mar 12 '18 at 15:10
  • @ThisGuyHasTwoThumbs Sorry I am not sure what you mean – Paddy Hallihan Mar 12 '18 at 15:12
  • as in just have a prices table with a foreign key linking to a product id (as an example) or just store the fields as more distinct (e.g. price_before_vat, special_price etc.) - see Magento's eCommerce database structure for some ideas :) – treyBake Mar 12 '18 at 15:14
  • @ThisGuyHasTwoThumbs I got it figured out now to achieve what I was looking for. Thanks for your suggestions though – Paddy Hallihan Mar 12 '18 at 15:25
  • @PaddyHallihan no problem and good luck! :) – treyBake Mar 12 '18 at 15:28
  • Sorry it's been a while since I coded in php - maybe this post could help. https://stackoverflow.com/questions/6439230/how-to-go-through-mysql-result-twice Your answer will work but executes the query a few times so isn't the best. Adding the results to an array for processing might be a good idea too and then just reset the pointer – Richard Housham Mar 12 '18 at 15:32

1 Answers1

0

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>';
        }
    }
Paddy Hallihan
  • 1,550
  • 3
  • 24
  • 56