How to search range of price?

Question

I need user inputs 1. city 2. min and max budget 3. date

city = city, minbudget <= childprice, maxbudget >= adultprice, date = day.

My problem, range of price and how to get the day from user input using datepicker? In database, I stored city, childprice, adultprice and day. I try for city and range of price like code below, the result display according to city only.

datepicker code :

<script src="js/jquery-ui.js"></script>
<script>
    $(function() {
        $( "#datepicker" ).datepicker();
    });
</script>

My code in php and html :

<form method="post" action="search.php">
  <label>City: </label>
  <select name="city">
  <option> Select </option>
  <option value="bandar hilir"> bandar hilir </option>
  <option value="bukit katil"> bukit katil</option>
  <option value="ayer keroh"> ayer keroh </option>
  <option value="alor gajah"> alor gajah </option>
  <option value="jasin"> jasin</option>
</select>

<h6>Budget per Person </h6>
<h6>Minimum Budget: RM <input type="text" name="minbudget" id="minbudget" placeholder="10.00"></h6>
<h6>Maximum Budget: RM <input type="text" name="maxbudget" id="maxbudget" placeholder="20.00"></h6>

<h6>Date:<input class="date" id="datepicker" type="text" placeholder="dd-mm-yyyy"  name="date"></h6>

    <br><br><input type="submit" name="search" class="btn-primary btn" value="Search">
</form>

My problem I write in the comment

<?php
$city="";
$PackageName="";
$address="";
$OperationHour="";
$minbudget="";
$maxbudget="";
$ChildPrice="";
$AdultPrice="";
$SeniorPrice="";
$PackageFetures="";
$PackageImage="";

if (isset($_POST["search"]))
{
include('includes/config.php');
$city = $_POST['city'];
$minbudget = $_POST['minbudget'];
$maxbudget = $_POST['maxbudget'];
$date=$_POST['date'];
$_POST['date'] = date('1', strtotime($_POST['date']));
$sql = "SELECT * FROM tbltourpackages WHERE city LIKE '%$city%' AND (ChildPrice >= $minbudget AND AdultPrice <= $maxbudget) OR OperationDay LIKE '%$date%'";
$query = $dbh -> prepare($sql);

$city="%$_POST[city]%";
$query->bindParam(':city', $city, PDO::PARAM_STR);
$query->bindParam(':minbudget', $_POST['minbudget'], PDO::PARAM_STR);
$query->bindParam(':maxbudget', $_POST['maxbudget'], PDO::PARAM_STR);
$query->bindParam(':date', $_POST['date'], PDO::PARAM_STR);

$exec=$query->execute();

if($exec)
{
    if ($query -> rowcount() >0)
    {
        foreach($query as $row)
        {
            $city=$row['city'];
            $PackageId=$row['PackageId'];
            $PackageName=$row['PackageName'];
            $address=$row['address'];
            $ChildPrice=$row['ChildPrice'];
            $OperationHour=$row['OperationHour'];
            $AdultPrice=$row['AdultPrice'];
            $SeniorPrice=$row['SeniorPrice'];
            $PackageFetures=$row['PackageFetures'];
            $PackageImage=$row['PackageImage'];
        ?>
        <div class="container">
            <div class="holiday">

        <div class="rom-btm">
            <div class="col-md-3 room-left wow fadeInLeft animated" data-wow-delay=".5s">
                <img src="admin/pacakgeimages/<?php echo $PackageImage; ?>" class="img-responsive" alt="">
            </div>
            <div class="col-md-6 room-midle wow fadeInUp animated" data-wow-delay=".5s">
                <h4><?php echo $PackageName;?></h4>
                <h6><b>Address : </b></h6><p><b><?php echo $address;?></b>
                <h6><b>Operation Hour:</b></h6> <p><b><?php echo $OperationHour;?></b></p>
                <h6><b>Features:</b></h6> <p><b><?php echo $PackageFetures;?></b></p>
            </div>
            <div class="col-md-3 room-right wow fadeInRight animated" data-wow-delay=".5s">
                <h6><b>Child Price:</b> RM <?php echo $ChildPrice;?></h6>
                <h6><b>Adult Price:</b> RM <?php echo $AdultPrice;?></h6>
                <h6><b>Senior Price:</b> RM <?php echo $SeniorPrice;?></h6>
                <a href="package-details.php?pkgid=<?php echo $PackageId;?>" class="view">Details</a>
            </div>
        </div>
        </div>
        </div>
        <?php
}
}
else
{
    echo 'no data';
}
}
else
{
    echo 'error';
}
    }
    ?>

function datepicker

function Datepicker() {
this.debug = false; // Change this to true to start debugging
this._curInst = null; // The current instance in use
this._keyEvent = false; // If the last event was a key event
this._disabledInputs = []; // List of date picker inputs that have been disabled
this._datepickerShowing = false; // True if the popup picker is showing , false if not
this._inDialog = false; // True if showing within a "dialog", false if not
this._mainDivId = 'ui-datepicker-div'; // The ID of the main datepicker division
this._inlineClass = 'ui-datepicker-inline'; // The name of the inline marker class
this._appendClass = 'ui-datepicker-append'; // The name of the append marker class
this._triggerClass = 'ui-datepicker-trigger'; // The name of the trigger marker class
this._dialogClass = 'ui-datepicker-dialog'; // The name of the dialog marker class
this._disableClass = 'ui-datepicker-disabled'; // The name of the disabled covering marker class
this._unselectableClass = 'ui-datepicker-unselectable'; // The name of the unselectable cell marker class
this._currentClass = 'ui-datepicker-current-day'; // The name of the current day marker class
this._dayOverClass = 'ui-datepicker-days-cell-over'; // The name of the day hover marker class
this.regional = []; // Available regional settings, indexed by language code
this.regional[''] = { // Default regional settings
    closeText: 'Done', // Display text for close link
    prevText: 'Prev', // Display text for previous month link
    nextText: 'Next', // Display text for next month link
    currentText: 'Today', // Display text for current month link
    monthNames: ['January','February','March','April','May','June',
        'July','August','September','October','November','December'], // Names of months for drop-down and formatting
    monthNamesShort: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'], // For formatting
    dayNames: ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'], // For formatting
    dayNamesShort: ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'], // For formatting
    dayNamesMin: ['Su','Mo','Tu','We','Th','Fr','Sa'], // Column headings for days starting at Sunday
    weekHeader: 'Wk', // Column header for week of the year
    dateFormat: 'yy-mm-dd', // See format options on parseDate
    firstDay: 0, // The first day of the week, Sun = 0, Mon = 1, ...
    isRTL: false, // True if right-to-left language, false if left-to-right
    showMonthAfterYear: false, // True if the year select precedes month, false for month then year
    yearSuffix: '' // Additional text to append to the year in the month headers
};

Do you have date field on your database? May you post your table design? — Roshan, Feb 01 '19 at 02:10
What Type of day is relevant? The day of the Week (e.g. Monday), the day of the month (1-31), the day of the year (1-365)? Also, when your prices are stored as decimal you dont need LIKE%%. Last but not least, if you want to find a result within the pricerange the `or` in your statement doesn't make sense. — KHansen, Feb 01 '19 at 02:10
KHansen for range of price you meant use AND instate of OR? in my database price in double(11,2), so should I write ChildPrice >= $minbudget like this? — mia, Feb 01 '19 at 03:00
watch out SQL Injection. Do not use POST values directly in your query — Raptor, Feb 01 '19 at 03:04
thanks Raptor. I had edit. I put my datepicker function. where i need put getDay() whether after date input in html or I can write in 1. datepicker code? — mia, Feb 01 '19 at 04:03
Take a look at my answer and your query. If you want help it would be nice to show at least a little effort. — KHansen, Feb 01 '19 at 10:58

KHansen · Answer 1 · 2019-02-01T06:11:41.407

0

To convert the datepicker date to a day you can use

$_POST['date'] = date('l', strtotime($_POST['date']));

If your Day is stored in lowercase you need

$_POST['date'] = strtolower(date('l', strtotime($_POST['date'])));

So your actual query could look like:

$sql = "SELECT * FROM tbltourpackages 
        WHERE city LIKE :city AND ChildPrice >= :minbudget AND AdultPrice <= :maxbudget AND yourDateColumn = :date";

$select = $db->prepare($sql);   

$city= "%$_POST[city]%";
$select->bindParam(':city', $city, PDO::PARAM_STR);
$select->bindParam(':minbudget', $_POST['minbudget'], PDO::PARAM_STR);
$select->bindParam(':maxbudget', $_POST['maxbudget'], PDO::PARAM_STR);
$select->bindParam(':date', $_POST['date'], PDO::PARAM_STR);

$select->execute();

But don't forget that you still need the value tags on your input fields.

Also you should never use $_POST values directly in your sql Statement. Read this.

edited Feb 01 '19 at 06:11

answered Feb 01 '19 at 04:32

KHansen

784
5
21

$_POST ['date'] put before $sql ? – mia Feb 01 '19 at 05:19
Yes you can put it between $maxbudget and $sql. Don't forget to rename `yourDateColumn` and setting the `value=""` tags. – KHansen Feb 01 '19 at 05:21
I get error "Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)" at line $city="%$_POST['city']%"; – mia Feb 01 '19 at 05:49
value tags for all name input? – mia Feb 01 '19 at 05:56
@mia I edited the line. Yes you need a `value=""` tag for all of your input fields. – KHansen Feb 01 '19 at 06:12
I can't get the result for date. My system give the result according city and range of price. In my database, day I put all in one column - OperationDay (Monday, Tuesday). it is the problem or problem with datepicker? – mia Feb 01 '19 at 07:00
You have multiple days comma separated stored in your OperationDay column? If yes use like%% on this columns as on city – KHansen Feb 01 '19 at 07:09
yes, I stored day with separated with comma. I change like you said but it still same. I edit my code above can you check the error? – mia Feb 01 '19 at 08:16

How to search range of price?

1 Answers1