Question 21818 in Mysql Question posted in
The official MySQL documentation can be found here.

issue in while loop inside foreach loop not working for my photopraphy project – Mysql

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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>";
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search