Use AJAX to sort MySQL search results

Question

I am creating a flight search page. There's a form on the first page, when the submit button is clicked, the search results appear in the second page.

This is the first page: http://www.projectmeteor.dx.am/flights.php

Please do the following:

Select Origin: Guwahati
Select Destination: Kolkata
Click Search Fights

Something like this should appear on the second page.

Now I want to sort the results when I click on the headers(Operator, Departs,...).

I know I have to use AJAX. I've seen a few examples where they sort tables using AJAX but since my one's not implemented using tables, I'm not able to figure out how to get started.

Please ask for any code, in case you need it.

This is the first page(flights.php):

<div class="col-sm-12">

                <div class="search">

                    <div class="content">

                    <form action="flightSearch.php" method="POST">

                        <div class="radioContainer">

                            <div class="col-sm-6 text-left">

                                <input type="radio" name="flightType" value="One Way" id="oneWay">
                                <label for="oneWay"><span class="radioLeft">One Way</span></label>

                                <input type="radio" name="flightType" value="Return Trip" id="returnTrip" checked>
                                <label for="returnTrip"><span class="radioLeft">Return Trip</span></label>

                            </div>

                            <div class="col-sm-6 text-right">

                                <input type="radio" name="flightClass" value="Business Class" id="businessClass">
                                <label for="businessClass"><span class="radioRight">Business Class</span></label>

                                <input type="radio" name="flightClass" value="Economy Class" id="economyClass" checked>
                                <label for="economyClass"><span class="radioRight">Economy Class</span></label>

                            </div>

                        </div> <!-- radioContainer -->

                        <div class="col-sm-6">          
                        <div class="form-group">
                             <label for="origin">Origin:<p> </p></label>

                                <select id= "origin"  data-live-search="true" class="selectpicker form-control" data-size="5" title="Select Origin" name="origin">
                                    <option value="New Delhi" data-subtext="DEL" data-tokens="DEL New Delhi">New Delhi</option>
                                    <option value="Mumbai" data-subtext="BOM" data-tokens="BOM Mumbai">Mumbai</option>
                                    <option value="Kolkata" data-subtext="CCU" data-tokens="CCU Kolkata">Kolkata</option>
                                    <option value="Bangalore" data-subtext="BLR" data-tokens="BLR Bangalore">Bangalore</option>
                                    <option value="Chennai" data-subtext="MAA" data-tokens="MAA Chennai">Chennai</option>
                                    <option value="Pune" data-subtext="PNQ" data-tokens="PNQ Pune">Pune</option>
                                    <option value="Goa" data-subtext="GOI" data-tokens="GOI Goa">Goa</option>
                                    <option value="Guwahati" data-subtext="GAU" data-tokens="GAU Guwahati">Guwahati</option>
                                    <option value="Gandhinagar" data-subtext="ISK" data-tokens="ISK Gandhinagar">Gandhinagar</option>
                                    <option value="Jammu" data-subtext="IXJ" data-tokens="IXJ Jammu">Jammu</option>
                                    <option value="Bhopal" data-subtext="BHO" data-tokens="BHI Bhopal">Bhopal</option>
                                    <option value="Agartala" data-subtext="IXA" data-tokens="IXA Agartala">Agartala</option>
                                </select>
                        </div>
                    </div>

                        <div class="col-sm-6">          
                        <div class="form-group">
                             <label for="destination">Destination:<p> </p></label>

                                <select id= "destination"  data-live-search="true" class="selectpicker form-control" data-size="5" title="Select Destination" name="destination">
                                    <option value="New Delhi" data-subtext="DEL" data-tokens="DEL New Delhi">New Delhi</option>
                                    <option value="Mumbai" data-subtext="BOM" data-tokens="BOM Mumbai">Mumbai</option>
                                    <option value="Kolkata" data-subtext="CCU" data-tokens="CCU Kolkata">Kolkata</option>
                                    <option value="Bangalore" data-subtext="BLR" data-tokens="BLR Bangalore">Bangalore</option>
                                    <option value="Chennai" data-subtext="MAA" data-tokens="MAA Chennai">Chennai</option>
                                    <option value="Pune" data-subtext="PNQ" data-tokens="PNQ Pune">Pune</option>
                                    <option value="Goa" data-subtext="GOI" data-tokens="GOI Goa">Goa</option>
                                    <option value="Guwahati" data-subtext="GAU" data-tokens="GAU Guwahati">Guwahati</option>
                                    <option value="Gandhinagar" data-subtext="ISK" data-tokens="ISK Gandhinagar">Gandhinagar</option>
                                    <option value="Jammu" data-subtext="IXJ" data-tokens="IXJ Jammu">Jammu</option>
                                    <option value="Bhopal" data-subtext="BHO" data-tokens="BHI Bhopal">Bhopal</option>
                                    <option value="Agartala" data-subtext="IXA" data-tokens="IXA Agartala">Agartala</option>
                                </select>
                        </div>
                    </div>

                        <div class="col-sm-3">
                            <div class="form-group">
                                <label for="datetime1">Select Departure Date:<p> </p></label>
                                <div class="input-group date" id="datetimepicker1">
                                    <input id="datetime1" type="text" class="inputDate form-control" placeholder="Select Departure Date" name="depart"/>
                                    <span class="input-group-addon">
                                    <span class="fa fa-calendar"></span>
                                    </span>
                                </div>
                            </div>
                        </div>

                        <div class="col-sm-3">
                            <div class="form-group">
                                <label for="datetime2">Select Return Date:<p> </p></label>
                                <div class="input-group date" id="datetimepicker2">
                                        <input  id="datetime2" type="text" class="inputDate form-control" placeholder="Select Return Date" name="return"/>
                                        <span class="input-group-addon">
                                        <span class="fa fa-calendar"></span>
                                </span>
                        </div>
    </div>
</div>

                        <div class="col-sm-3">

                        <label for="adults">No. of adults:<p> </p></label>
                            <div class="form-group">
                                <select id= "adults" class="selectpicker form-control" data-size="5" title="Aged 18 and above" name="adults">
                                    <option value="1">1</option>
                                    <option value="2">2</option>
                                    <option value="3">3</option>
                                    <option value="4">4</option>
                                    <option value="5">5</option>
                                    <option value="6">6</option>
                                </select>
                            </div>
                        </div>

                        <div class="col-sm-3">

                        <label for="children">No. of children:<p> </p></label>
                            <div class="form-group">
                                <select id= "children" class="selectpicker form-control" data-size="5" title="Aged upto 17" name="children">
                                    <option value="0">0</option>
                                    <option value="1">1</option>
                                    <option value="2">2</option>
                                    <option value="3">3</option>
                                    <option value="4">4</option>
                                    <option value="5">5</option>
                                    <option value="6">6</option>
                                </select>
                            </div>
                        </div>

                        <div class="col-sm-12 text-center">

                            <input type="submit" class="button" name="searchFlights" value="Search Flights">

                        </div>

                    </form>

                    </div> <!-- content -->

                </div> <!-- search -->

            </div>

This is the second page(flight_search.php):

    <?php include("common/header.php"); ?>

    <?php

        $type=$_POST["flightType"];
        $class=$_POST["flightClass"];
        $origin=$_POST["origin"];
        $destination=$_POST["destination"];
        $depart=$_POST["depart"];
        if($type=="Return Trip") {  //let's store the return value only if the flightType is a Return Trip
            $return=$_POST["return"];
        }
        $adults=$_POST["adults"];
        $children=$_POST["children"];

        if($class=="Economy Class")
            $className="Economy";
        else
            $className="Business";

    ?>

    <div class="spacer">a</div>

    <div class="searchFlights">

        <div class="query">

            <?php echo $type; ?> <?php echo $className; ?> Flights from <?php echo $origin; ?> to <?php echo $destination; ?>       

        </div>


    <?php

        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "projectmeteor";

        // Creating a connection to projectmeteor MySQL database
        $conn = new mysqli($servername, $username, $password, $dbname);

        // Checking if we've successfully connected to the database
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $sql = "SELECT * FROM flights WHERE origin='$origin' AND destination='$destination'";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {

    ?>

        <div class="listItemMenuContainer">

            <div class="col-sm-2 text-center">

                <div class="headings">

                    Operator

                </div>

            </div>

            <div class="col-sm-2 text-center">

                <div class="headings">

                    Departs

                </div>

            </div>

            <div class="col-sm-2 text-center">

                <div class="headings">

                    Arrives

                </div>

            </div>

            <div class="col-sm-2 text-center">

                <div class="headings">

                    Fare

                </div>

            </div>

            <div class="col-sm-2 text-center">

                <div class="headings">

                    Ticket Type

                </div>

            </div>

            <div class="col-sm-2 text-center">

                <div class="headings">

                    Seats Available

                </div>

            </div>

        </div> <!-- listItemMenuContainer -->

        <div class="spacerLarge">.</div> <!-- just a dummy class for creating some space -->

    </div> <!-- searchFlights -->

    <?php
            while($row = $result->fetch_assoc()) {

    ?>

    <div class="searchFlights">

        <div class="listItem">

            <!-- FLIGHT INFO STARTS -->

            <div class="col-sm-2 text-center">

                <div class="col-sm-4 text-center">

                    <div class="operatorLogo text-center">

                        <img src="images/flights/operatorLogos/indigo.jpg">

                    </div>

                </div>

                <div class="col-sm-8 text-center">

                    <div class="values flightOperator">

                        IndiGo

                    </div>

                    <div class="flightNoSubscript">

                        <?php echo $row["flight_no"]; ?>

                    </div>

                </div>

            </div>

            <!-- FLIGHT INFO ENDS -->


            <!-- DEPARTS STARTS -->

            <div class="col-sm-2 text-center">

                <div class="values departs">

                    13:25

                </div>

                <div class="departsSubscript">

                    <?php echo $row["origin"]; ?>

                </div>

            </div>

            <!-- DEPARTS ENDS -->


            <!-- DESTINATION STARTS -->

            <div class="col-sm-2 text-center">

                <div class="values arrives">

                    15:49

                </div>

                <div class="arrivesSubscript">

                    <?php echo $row["destination"]; ?>

                </div>

            </div>

            <!-- DESTINATION ENDS -->


            <!-- FARE STARTS -->

            <div class="col-sm-2 text-center">

                <div class="values fare">

                    <?php echo $row["fare"]; ?>

                </div>

                <div class="fareSubscript">

                    incl. of GST

                </div>

            </div>

            <!-- FARE ENDS -->


            <!-- TYPE STARTS -->

            <div class="col-sm-2 text-center">

                <div class="values type">

                    Refundable

                </div>

                <div class="typeSubscript">

                    <?php echo $row["class"]; ?>

                </div>

            </div>

            <!-- TYPE ENDS -->


            <!-- SEATS AVAILABLE STARTS -->

            <div class="col-sm-2 text-center">

                <div class="values availabilityGreen">

                    <?php echo $row["seats_available"]; ?>

                </div>

                <div class="availabilitySubscript">

                    <input type="submit" class="availabilityBookingButton" value="Book Now">

                </div>

            </div>

            <!-- SEATS AVAILABLE ENDS -->

        </div> <!-- listItem 1 -->

    </div>

    <?php

            }

        } else {

    ?>

    <div class="searchFlights">

        <div class="noFlights">

            No flights found. Please consider modifying your search query.

        </div>

    </div>

    <?php

        }

    ?>

    <?php $conn->close(); //closing the connection to the database ?>

    <div class="spacerLarge">.</div> <!-- just a dummy class for creating some space -->

    <?php include("common/footer.php"); ?>

Thanks

Seeing the javascript and html source code would be helpful :) Also the linked page says site can't be reached. — Anthony McGrath, Sep 10 '17 at 19:01
I can't access to your page, so see the code it will help me to show you a practical solution — rebduvid, Sep 10 '17 at 19:04

rebduvid · Accepted Answer · 2017-09-13T21:45:43.857

First all you need to know that is better to use one of the solutions that don't use ajax, becouse all the request that your are going to use in ajax are going to make new mysql query and server processing to make the html.

But if you want to use AJAX i have an easy idea how to do it.

1- First all you need to add the jquery.js library to your code if you haven't the source, you can download it from https://jquery.com/

2 - Change the code of the headings to

<div class="headings" id="order1" data-id="operator"> Operator</div>
<div class="headings" id="order2" data-id="departs"> Departs</div>

Also i think that you need to put all the results in a general div that should be before your while sentence and to close it after the } this div is the one that should contain all the results divs when you update the page with ajax you are going to replace this div content to the new content that the flightProcesor.php return.

<div id="results"> <!-- NEW TAG -->
    <?php
    while($row = $result->fetch_assoc()) 
    {
    ?>
    <div class="searchFlights">
        <div class="listItem">
             .....
        </div>
    </div><?php
    }?>
 </div> <!-- NEW TAG -->

3 - Read the div order* event and dispatch the ajax event

<script language="javascript">
$("div[id*=order]").on('click', function () 
{
    var column = $(this).attr('data-id');
    var oneWay = $('#oneWay').val();
    var returnTrip = $('#returnTrip').val();
    .....
    .....

    $.ajax({
        type:'POST',
        url:"/flightProcesor.php",
        data: {orderBy: column, oneWay: oneWay, returnTrip: returnTrip},

        success:function(data) {
            if(data) 
            {
                $('#results').html(data);
            } 
        }
    });
});
</script>

4 - Last step you need to make the flightProcesor.php that should to make the mysql query with the orderBy parameter that you are sending via POST to order and then you need to create a php $html var that contain all the code to return:

    <?php
if ($result->num_rows > 0) 
{
    $string_var = ""; //INIT STRING VAR.
    while($row = $result->fetch_assoc()) 
    {
        $string_var .= '<div class="searchFlights">
            <div class="listItem">
                <!-- FLIGHT INFO STARTS -->
                <div class="col-sm-2 text-center">
                    <div class="col-sm-4 text-center">
                        <div class="operatorLogo text-center"><img src="images/flights/operatorLogos/indigo.jpg"></div>
                    </div>
                    <div class="col-sm-8 text-center">
                        <div class="values flightOperator">IndiGo</div>
                        <div class="flightNoSubscript">'.$row["flight_no"].'</div>
                    </div>
                </div>
                <!-- FLIGHT INFO ENDS -->
                <!-- DEPARTS STARTS -->
                <div class="col-sm-2 text-center">
                    <div class="values departs">13:25</div>
                    <div class="departsSubscript">'.$row["origin"].'</div>
                </div>
                <!-- DEPARTS ENDS -->
                <!-- DESTINATION STARTS -->
                <div class="col-sm-2 text-center">
                    <div class="values arrives">15:49</div>
                    <div class="arrivesSubscript">'.$row["destination"].'</div>
                </div>
                <!-- DESTINATION ENDS -->
                <!-- FARE STARTS -->
                <div class="col-sm-2 text-center">
                    <div class="values fare">'.$row["fare"].'</div>
                    <div class="fareSubscript">incl. of GST</div>
                </div>
                <!-- FARE ENDS -->
                <!-- TYPE STARTS -->
                <div class="col-sm-2 text-center">
                    <div class="values type">Refundable</div>
                    <div class="typeSubscript">'.$row["class"].'</div>
                </div>
                <!-- TYPE ENDS -->
                <!-- SEATS AVAILABLE STARTS -->
                <div class="col-sm-2 text-center">
                    <div class="values availabilityGreen">'.$row["seats_available"].'</div>
                    <div class="availabilitySubscript"><input type="submit" class="availabilityBookingButton" value="Book Now"></div> <!-- MAYBE YOU NEED AN IDENTIFICATION VALUE FOR EACH ROW IN THIS SUBMIT -->
                </div>
                <!-- SEATS AVAILABLE ENDS -->
            </div> <!-- listItem 1 -->
        </div>';
    }
} 
else 
{
    $string_var = '<div class="searchFlights">
                        <div class="noFlights">No flights found. Please consider modifying your search query.</div>
                    </div>';
}

return $string_var;
?>

EDITED: The } to close the while need to be before the return. EDITED: ALL FLIGHT PROCESOR PHP CODE ADDED.

I hope that something like that help you.

The first 3 points are clear to me. I could not understand the last step. Could you please explain it to me again. BTW thanks for the help. — sequel, Sep 11 '17 at 18:30
Ok, I try to explain. After that you send via ajax the new parameters (you also need to send the form (your first page's parameters) and you need to receive those parameters in the flightProcessor.php so in this page you need to make the same query that you have in flight_search.php but instead close php code to print the html in the screen you need to save all your html in a $string_var, then when your while() ends you need to return this value (return $string_var;) and this value is going to the AJAX process. — rebduvid, Sep 12 '17 at 11:20
I think that I added all the code. Tell me if you need something else. Actually you have an example of the while that generate a big $string_var with all the item list of your results. — rebduvid, Sep 13 '17 at 21:46
Marked your answer as accepted. Thanks for helping me out :) — sequel, Sep 14 '17 at 17:26

Use AJAX to sort MySQL search results

1 Answers1