How to fetch values from specific columns using while loop in php/mysql?

Question

I am struggling again with my PHP structures and you guys gave me a lots of help. Thanks in advance. Now I got a new challenge to myself. I have the following table im my database:

  id |     htl_name    |      city_zone     |   price    |   given_date
------------------------------------------------------------------------
  1  | Hotel Beach Inn |   Cityzone1        |  10.00     |   2012-09-01
  2  | Hotel Beach Inn |   Cityzone1        |  10.00     |   2012-09-02
  3  | Hotel Beach Inn |   Cityzone1        |  10.00     |   2012-09-03
  4  | Hotel Beach Inn |   Cityzone1        |  11.00     |   2012-09-04
  5  | Hotel Beach Inn |   Cityzone1        |  11.00     |   2012-09-05
  6  | Hotel City Inn  |   Cityzone1        |  15.00     |   2012-09-01
  7  | Hotel City Inn  |   Cityzone1        |  15.00     |   2012-09-02 
  8  | Hotel City Inn  |   Cityzone1        |  16.00     |   2012-09-03
  9  | Hotel City Inn  |   Cityzone1        |  16.00     |   2012-09-04
 10  | Hotel City Inn  |   Cityzone1        |  16.00     |   2012-09-05
-------------------------------------------------------------------------

When the user enter an Check-In date, a Check-Out date and select a City-Zone, the script will use PHP/MySQL structure to filter the City-Zone required and the dates within the period entered. So far so good and working but let's pretend that the user enters:

Check-In: 2012-09-01

Check-Out: 2012-09-05

City-Zone: Cityzone1

The output should be:

RESULT 1

Hotel: Beach Inn

Nights: 4

Check-In: 2012-09-01

Check-Out: 2012-09-05

Total Rate: 42.00 --> this exclude the first night rate because the numbers of night just starts counting from the second night

RESULT 2

Hotel: City Inn

Nights: 4

Check-In: 2012-09-01

Check-Out: 2012-09-05

Total Rate: 67.00 --> this exclude the first night rate because the numbers of night just starts counting from the second night

I have done so far the following script:

//MySQL Select structure that apply filter on user's entered information


$filter1=mysql_query("SELECT * FROM.........  ORDER BY htl_name, city_zone, given_date LIMIT 1");
$filtern=mysql_num_rows($filter1);
...
if($filtern>=1){
  While($row=mysql_fetch_array($filter1){
     $first_hotel_found=$row['htl_name'];
  }

  // New filter but listing everything within the date period

  $new_filter=mysql_query("SELECT * FROM ........ORDER BY htl_name, city_zone, given_date");
  $final_price=0; 
  $output="";
  while($row=mysql_fetch_array($new_filter){
     $htl_name=$row['htl_name'];
     $price=$row['price'];

     $final_price=$final_price+$price;

     if($htl_name!==$first_hotel_found){
        output .='Hotel:'.$htl_name.'<br/>';
        ... NO WORRIES WITH NUMBER OF NIGHTS AND CHECK-IN / CHECK-OUT DATES
        output .='Total Rate:'.$final_price.'<br/>';
        //after output the result go back clean up the $final_price and start adding again for the new Hotel
        $final_price=0;
        //$first_hotel_found assumes the actual $row
        $first_hotel_found=$row['htl_name'];
      }

    } // End of the while

}else{
     echo "There are no rates available for the information entered!";
     }

}

.... HTML portion ....

<body>
  <?php echo $output; ?>
</body>
</html>

The problem is that the output doesn't list both hotels instead list the name of the last hotel and the total rate from the first hotel:

Hotel: City Inn

Nights: 4

Check-In: 2012-09-01

Check-Out: 2012-09-05

Total Rate: 42.00

I would appreciate any help that will lead me to finish my project.

Thank you very much.

Zefiryn · Accepted Answer · 2012-09-01 22:28:21Z

1

Here:

if($filtern>=1){
  While($row=mysql_fetch_array($filter1){
   $first_hotel_found=$row['htl_name'];
}

You are setting the first hotel under $first_hotel_found and when you later going to that loop:

 while($row=mysql_fetch_array($new_filter){

you have inside if($htl_name!==$first_hotel_found)

Since above you have set up $first_hotel_found this comparision with the first result is false and the first hotel does not showing in the results.

answered Sep 1, 2012 at 22:28

Zefiryn

2,2652 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Araujo Over a year ago

Hey Zefiry! It is working now! Thanks my friend. Best Regards.

Keval Domadia Over a year ago

@AlexAraujo Please mark the tick next to his answer so that others can also refer to it.

C. E. · Accepted Answer · 2012-09-01 22:38:10Z

1

This condition

if($htl_name!==$first_hotel_found){

is true exactly when $htl_name is Hotel City Inn for the first time (if the first hotel was Hotel Beach Inn). You print $htl_name and not $first_hotel_found, as you should.

You will never print information about the second hotel because the same condition will never be true again. If you add a third hotel, it will be true exactly when $htl_name is that hotel for the first time. To solve this you could copy the code that creates output to outside the loop as well, to execute it that last time:

output .='Hotel:'.$htl_name.'<br/>';
... NO WORRIES WITH NUMBER OF NIGHTS AND CHECK-IN / CHECK-OUT DATES
output .='Total Rate:'.$final_price.'<br/>';
//after output the result go back clean up the $final_price and start adding again for the new Hotel
$final_price=0;
//$first_hotel_found assumes the actual $row
$first_hotel_found=$row['htl_name'];

answered Sep 1, 2012 at 22:38

C. E.

10.7k11 gold badges57 silver badges81 bronze badges

1 Comment

Alex Araujo Over a year ago

That was a good shot Calle. Now I have the two output but the name of the hotels are duplicated.

Collectives™ on Stack Overflow

How to fetch values from specific columns using while loop in php/mysql?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related