car availability between 2 date, comma separated data

Question

I'm having trouble to list of car available by date. Here's ma data and code.

booking_tbl

car_tbl

I have a code that filter availability by date: From to

<input type="submit" name="button" id="button" value="Filter" />
  </label>
  <a href="q_search1_form.php">reset</a>
</form>
<script>
    $(function() {
        var dates = $( "#from, #to" ).datepicker({
            defaultDate: "+1w",
            changeMonth: true,
            numberOfMonths: 1,
            dateFormat: 'yy-mm-dd',
            onSelect: function( selectedDate ) {
                var option = this.id == "from" ? "minDate" : "maxDate",
                    instance = $( this ).data( "datepicker" ),
                    date = $.datepicker.parseDate(
                        instance.settings`enter code here`.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                        selectedDate, instance.settings );
                dates.not( this ).datepicker( "option", option, date );
            }
        });
    });
    </script>

And here is my query code:

<?php
include("config.php");
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) { die('Failed to connect to server: ' . mysql_error()); }
$db = mysql_select_db(DB_DATABASE);
if(!$db) { die("Unable to select database"); }
$start =$_POST['from'];
$end =$_POST['to'];

$query="SELECT car.id, car.description, car.name, car.status, booking.book_id FROM car LEFT JOIN booking ON booking.id_car=car.id ";
$query.="WHERE (start_date BETWEEN CAST('$start' AS DATE) AND CAST('$end' AS DATE) OR start_date IS NULL) ";
$query.="AND (end_date BETWEEN CAST('$start' AS DATE) AND CAST('$end' AS DATE) OR end_date IS NULL) ";
$query.="AND status = 'available'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

?>
<input type="checkbox" name="car[]" id="car" value="<?php echo $row['id'];?> "> <?php echo $row['name'];?><br>

<?php
}mysql_close();?>

I insert data with this code:

$id_car = @implode(',', $_POST['car']);

My problem is that I want to check what car or cars are available (for example 2016-10-02 in query appears also vw, but this car is booked). If I insert a new booking for 2016-10-03: Mercedes, after inserting it in my new query, it appears also Mercedes and I don't know why because it is already booked.

**Database Design Fault:** `booking_tbl.id_car` is a comma delimited list of ID'S in a single column. **BAD** Instead use a cross-reference_table — RiggsFolly, Oct 03 '16 at 10:25
Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Oct 03 '16 at 10:26
Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Oct 03 '16 at 10:26
`LEFT JOIN booking ON booking.id_car=car.id` cannot work when the `booking.id_car` is a comma delimited list — RiggsFolly, Oct 03 '16 at 10:28
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Oct 03 '16 at 10:38

car availability between 2 date, comma separated data

0 Answers0