Query specific data from sqlite database with php

Question

Hi there i am using a sqlite database to manage reservations on a system (written in php and html) that I built. I have managed to connect to the database and run a simple count query that runs (see code below). However i am finding it difficult to execute a query that will allow me to select reservations between two dates i.e (2018-02-20 and 2018-02-25). I have also enclosed a screenshot of my database for visualization purposes.

Code for count query:

<?php
$db = new PDO('sqlite:daypilot.sqlite');

$query = 'SELECT count(*) FROM events;';

$result = $db->query($query);

$data = $result->fetch(PDO::FETCH_ASSOC);

echo "There are {$data['count(*)']} bookings made\n";
?>

Well, you didn't try: SELECT count(*) FROM events WHERE some_date BETWEEN '2018-02-20' AND '2018-02-25' — AbraCadaver
– AbraCadaver, Commented Feb 27, 2018 at 22:17
That is an example row of data and the fields are as follows...id, name, start, end , resource_id — Ryan
– Ryan, Commented Feb 27, 2018 at 22:18
Help would be greatly appreciated on this as its the last step in this particular project — Ryan
– Ryan, Commented Feb 27, 2018 at 22:20

Don't Panic · Accepted Answer · 2018-02-27 22:22:04Z

0

Since you'll be passing input to your query, you'll want to use a prepared statement rather than just query(). It looks like your events table has two dates, so the following query should catch all events that overlap a specified date range.

$start = '2018-02-20';
$end = '2018-02-25';

$sql = 'SELECT * FROM events WHERE `end` > ? AND `start` < ?';
$stmt = $db->prepare($sql);
$stmt->execute([$start, $end]);
$events = $stmt->fetchAll();

foreach ($events as $event) {
    var_dump($event);  // output with your desired formatting here
}

answered Feb 27, 2018 at 22:22

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

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

10 Comments

Ryan Over a year ago

Thank you this query has executed without any error. Would you recommend a table of some sort to display the results ?

Don't Panic Over a year ago

It really just depends on how you want it to look and what you want to do with it. An HTML table is usually appropriate to display rows from a database, but it's just as valid to have a stack of divs. A table can be advantageous if you're displaying quite a bit of data, because you can use something like datatables.net to easily make it sortable/filterable/etc.

Ryan Over a year ago

can you recommend a table format as this is what i require and im very confused but what to do in this area !!

Ryan Over a year ago

How might i implement the datatables.net the format looks very slick

Don't Panic Over a year ago

Here's a basic example of how to output a table: stackoverflow.com/questions/2465046/…

|

Collectives™ on Stack Overflow

Query specific data from sqlite database with php

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related