3

I have table reservasi with structure data like this:

  • id_reservasi
  • package_name <- varchar
  • date_check_in <- varchar format (d-m-Y)
  • time_check_in <- varchar format (h:i:s)
  • price <- varchar

I want select data with filter between from date and time from previous data reservasi to Until the time specified.

For now, I use this sql query:

SELECT SUM(price) AS total
FROM reservasi 
WHERE date(str_to_date(date_check_in,'%d-%m-%Y')) BETWEEN date(str_to_date('$date_check_in_rekap','%d-%m-%Y')) AND date(str_to_date('$date_now','%d-%m-%Y') 
AND STR_TO_DATE(time_check_in, '%H:%i:%s') BETWEEN STR_TO_DATE('$time_check_in_rekap', '%H:%i:%s') AND STR_TO_DATE('$time_now', '%H:%i:%s')

But it don't show any data... How to use BETWEEN correctly?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Dammah SifLa
  • 89
  • 8

2 Answers2

2

edit, PDO version :

$cek_pembelian=$this->pdo->prepare("
    SELECT 
        SUM(netto_bersih) AS netto_beli, 
        SUM(jumlah_dibayar) AS rupiah_beli 
    FROM p_penerimaan_tbs 
    WHERE str_to_date(CONCAT(tanggal,' ',jam_keluar) ,'%d-%m-%Y %H:%i:%s') 
        BETWEEN str_to_date(:datetime_start ,'%d-%m-%Y %H:%i:%s') AND now()
        ");
$cek_pembelian->execute(array(
    ':datetime_start'=>$tanggal_rekap .' '. $jam_rekap
    ));

This is what you want I think ?

SQL Fiddle

MySQL 5.6 Schema Setup:

Query 1:

SELECT SUM(price) AS total
FROM t 
WHERE str_to_date(CONCAT(date_check_in,' ',time_check_in) ,'%d-%m-%Y %H:%i:%s')
BETWEEN '2017-01-24 18:20:33' AND now()

Results:

| total |
|-------|
|   300 |
Blag
  • 5,818
  • 2
  • 22
  • 45
  • Thanks for answer... i am try query like this... `$cek_pembelian=$this->pdo->prepare("SELECT SUM(netto_bersih) AS netto_beli, SUM(jumlah_dibayar) AS rupiah_beli FROM p_penerimaan_tbs WHERE str_to_date(CONCAT(tanggal,' ',jam_keluar) ,'%d-%m-%Y %H:%i:%s') BETWEEN '$tanggal_rekap $jam_rekap' AND now()");` Result sum total row... not filter – Dammah SifLa May 07 '17 at 21:52
  • @DammahSifLa take a look at the edit. your problem is how you wrote your date. the MySQL way is `YYYY-MM-DD HH:MM:SS` so anything than that should be manually parsed with `str_to_date()`. BTW, I secured your query by not putting the php `$var` inside the SQL, but use a `:flag` instead and providing in the `execute()` the array of `:flag => $value` that PDO should use – Blag May 07 '17 at 22:33
-1

Thanks for query from @Blag

Work Perfect...

Edited Mysql Query :

SELECT SUM(price) AS total FROM t WHERE str_to_date(CONCAT(date_check_in,' ',time_check_in) ,'%d-%m-%Y %H:%i:%s') BETWEEN str_to_date('$date_check_in_previous $time_check_in_previous') AND now()

Dammah SifLa
  • 89
  • 8