How To Display The Hotel Names That Have The Maximum Count From Data Base By Calculating Sum In Php?

I am new to PHP coding. I want to display the names of the hotels from data base in ascending order according to the respective counts of the preferences selected by the user from

Solution 1:

You should make ordering by selected fields almost the same as $where is done. I would map option names to their respective column counts: 'pool' => 'pool_count'. Choosing from table selected by user is even more straightforward, just make array containing input values as indexes, table name as values and pick concatenate chosen index into query.

I have used simplified version of your code (only one city, less columns) but logic would work with your data as well just adjust it. There is one thing left, you should handle case where $_POST['list'] does not contain proper value.

Here's code:

$hotelOptions = array('swimming_pool', 'roof_top', 'sea_side');
$countOptions = array(
    'swimming_pool' => 'swimming_pool_count',
    'roof_top' => 'roof_top_count',
    'sea_side' => 'sea_side_count',
);
$cities = array(1 => 'Dubai');

if (isset($_POST['submit'])) {
    if (!empty($_POST['check_list']) && is_array($_POST['check_list'])) {
        // Counting number of checked checkboxes.$checked_count = count($_POST['check_list']);
        echo"You have selected following ".$checked_count." option(s): <br/>";
        // Loop to store and display values of individual checked checkbox.$where = '';
        $order = '';
        foreach($_POST['check_list'] as$selected) {
            echo"<p>".$selected ."</p>";
            if (array_search($selected, $hotelOptions) !== false) {
                $where .= " AND {$selected} = 1";
                $order .= " {$countOptions[$selected]} DESC,";
            }
        }
        $where = substr($where, 5, strlen($where));
        $order = substr($order, 0, strlen($order) - 1);

        if (isset($cities[$_POST['list']])) {
            $sql = "SELECT hotel_name FROM ".$cities[$_POST['list']]." WHERE ".$where." ORDER BY ".$order.";";

            $ret = $db->query($sql);

            while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
                echo"<p> <br /></p>\n";
                echo"\n". $row['hotel_name'] . "\n";
            }
            $db->close();
            echo"<br/><b>Note :</b> <span>Similarily, You Can Also Perform CRUD Operations using These Selected Values.</span>";
        }
    } else {
        echo"<b>Please Select Atleast One Option.</b>";
    }
}

Solution 2:

it should add pool_count with gym_count and spa_count and then display the names of the hotels in ascending order according to their respective total sum values

For this you can do it in multiple way,

Get the submitted checkboxes, now when you have them just loop through them and make the string if it is checked:

$query_string = '';
foreach($selected_checkboxesas$name=>$value)
{
     if($value==1)
          $query_string .= "`$name` + ";
}
$query_string = rtrim($query_string, " + ");

Query:

SELECT *, ($query_string) AS AMENITIES_COUNT 
FROM xyz 
ORDERBY AMENITIES_COUNT DESC

PHP:

Just sum the amenities in the usort function and sort it based on that.

dynamically send the city name in the query to open the table of that destination

Making the query dynamic is pretty easy, you just need to understand what will change and what will not.

So when user selects a city and hits the submit button your query variable will become this:

$selected_city = $_POST['city'];
$query = "SELECT hotel_name FROM `$selected_city` WHERE ...";

You may need to use that city in your filter queries too. (will edit the answer in case I have missed something)

===EDIT===

Some Optimization in your code:

$hotel_count_options = array ( ['pool'] => 'pool_count', ['gym'] => 'gym_count',  ['spa'] => 'spa_count', ['is_wifi'] => 'wifi_count', ['is_beach'] => 'beach_count', ['is_familyoriented'] => 'family_count', ['is_economical'] => 'econo_count' );
$where = '';
$query_select_string = '';
foreach($_POST['check_list'] as$selected) {
            echo"<p>".$selected ."</p>";
            if (array_search($selected, array_keys($hotel_count_options)) !== false) {
                $where .= " AND {$selected} = 1";
                $query_select_string .= "{$hotel_count_options['$selected']} + ";
            }
        }
$query_select_string = rtrim($query_string, " + ");

Now just change your select query string,

$sql = "SELECT hotel_name, $query_select_string as ame_count FROM Dubai WHERE ".$where." ORDER BY ame_count desc;";