issue in while loop inside foreach loop not working for my photopraphy project-CodePudding

I am facing a problem while calculating the quotation amount for my photography project.

Assume an Indian wedding has many events like Engagement, Haldi,Marriage, Reception etc., Pricing will be determined based on the number of events selected. A user can select his desired events and submit the quote

Sample screenshot

The values of the selected events are stored in enq_event column in mysql using implode function

$checkbox2_selected = implode( "," , $_POST['wed_enq_events']);

sample data stored in mysql

The results are displayed in a table using php

$query = "SELECT * FROM cm_enquiries WHERE enq_event_type = '2' ";
    $select_posts = mysqli_query($con,$query);  
     $counter = 0;
    while($row = mysqli_fetch_assoc($select_posts )) {
      $counter  =1;
        $get_enq_no           = $row['enq_no'];
        $get_enq_event        = $row['enq_event'];
        $get_enq_event_date   = $row['enq_event_date'];
                
        echo "<tr>";   
        echo "<td>$counter</td>";      
        echo "<td><a href='update_wedding_events.php?update=$get_enq_no'><btn class='btn btn-  primary'>view Events</btn></a></td>";
        if(empty($get_enq_event)){
          echo "<td><a href='update_wedding_events.php?update=$get_enq_no' class='btn btn-danger'>Update Events</a></td>";
        }
        else{
          echo "<td>";
          $get_enq_event;        
          $explode_events = explode(",", $get_enq_event);

          foreach($explode_events as $explode){           
            echo $explode . "<br>";
          }
          echo "</td>";
        }//end of else


        echo "<td>$get_enq_amount</td>"; 
        echo "</tr>";
    }
      ?>

Only relevant Php code is posted above.

The above code outputs in the below table

Till now, there is no issue. However When I try to insert a while loop inside foreach loop, I could not get the desired output

The rest of the rows could not be seen.

foreach($explode_events as $explode){           
            
            $event_name_query = "SELECT * FROM event_types WHERE event_code = '$explode'";
            $select_posts = mysqli_query($con,$event_name_query);

            while($row = mysqli_fetch_assoc($select_posts)) {
            echo  $event_code        = $row['event_code'];                     
              $event_amount        = $row['event_amount'];  
          }

experiencing issue in table

Been trying this for so many hours, could not get the desired output.. I want all the rows to appear.

CodePudding user response：

It's not a direct answer to your problem, but may I suggest you do something about your database design? Don't store the selected options as a comma seperated list, store them apart.

At the moment, I guess, you have a table Options like this:

option_id	option_name	price
1	proposal	10
5	kiss the bride	100
7	receive presents	20
9	throw out drunk uncle	1000
10	divorce	0

And a table Enquiries

enquiry_id	options_ids
19	5,7,9

If you want the options selected by a customer, you first have to retrieve the comma seperated list from Enquiries, explode it, select the related options from the Options table, loop them, calculate the price and finally show them.

If you store the Customer selected options like this

enquiry_id	option_id
19	5
19	7
19	9

you can with one query get the selected options, the price and their names:

SELECT 
   ENQ.option_id,
   OPT.option_name,
   OPT.price
FROM 
   Enquiries AS ENQ
LEFT JOIN 
   Options AS OPT
ON( ENQ.option_id = OPT.option_id )
WHERE ENQ.enquiry_id = 19

As a result, from one query you get the selected options by the customer, including their names. Only loop the result once for display and calculating the sum:

$result = array(
  0=>[
   'option_id' => 5,
   'option_name' => 'kiss the bride',
   'price' => 100
   ],
  1=>[
   'option_id' => 7,
   'option_name' => 'receive presents',
   'price' => 20
   ],
  2=>[
   'option_id' => 9,
   'option_name' => 'throw out drunk uncle',
   'price' => 1000
   ],
)

And similar if you want to present the customer with the possibility to edit the options after saving, you only have to select all the options in the Options table and join them with the options selected by the cusomer.

SELECT 
   OPT.option_id,
   OPT.option_name,
   OPT.price,
   ENQ.option_id AS is_selected
FROM 
   Options AS OPT
LEFT JOIN 
   Enquiries AS ENQ
ON( ENQ.option_id = OPT.option_id )
WHERE ENQ.enquiry_id = 19

Where the customer has selected the option, you get a is_selected number, otherwhise you get NULL

$result = array(
  0=>[
   'option_id' => 1,
   'option_name' => 'proposal',
   'price'       => 10,
   'is_selected' => NULL
   ],
  1=>[
   'option_id' => 5,
   'option_name' => 'kiss the bride',
   'price'       => 100,
   'is_selected' => 5
   ],
  2=>[
   'option_id' => 7,
   'option_name' => 'receive presents',
   'price'       => 20,
   'is_selected' => 7
   ],
  3=>[
   'option_id' => 9,
   'option_name' => 'throw out drunk uncle',
   'price'       => 1000,
   'is_selected' => 9
   ],
  4=>[
   'option_id' => 10,
   'option_name' => 'divorce',
   'price'       => 0,
   'is_selected' => NULL
   ]
)

It might seems more work for now, but in the end it makes life a lot easier.

CodePudding user response：

You don't need a while loop inside foreach:

$valueSearch = "";
$valueSearch2 = "";
foreach($explode_events as $explode){    
    $stmt = $conn->prepare("SELECT `event_code`,`event_amount` FROM `event_types` WHERE `event_code` = ?"); 
    $stmt->bind_param("s", $explode);
    $stmt->execute();
    $stmt -> store_result();
    $stmt -> bind_result($valueSearch,$valueSearch2);
    $stmt->fetch();
    $stmt->close();
    echo $explode. "-". $valueSearch. "-". $valueSearch2."<br>";
    }