How to get the values in between the range by using where clause

Question

Database StructureI am trying raw query in an controller(Codeigniter) and i am trying to get the values from the range provided by the user on newDate and newDate2..My problem is it displays nothing..How do i write the query for this..I just want to display the qty,productname,bundle according to the voucherno(vno)...Here is my tried code

Controller Code:

$startdate         = $this->input->post('SDate');
$enddate           = $this->input->post('EDate');
$date              = str_replace('/', '-', $startdate);
$newDate           = date("Y-m-d", strtotime($date));
$date2             = str_replace('/', '-', $enddate);
$newDate2          = date("Y-m-d", strtotime($date2));
$data['startdate'] = $startdate;
$data['enddate']   = $enddate;
$query             = $this->db->query('SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate >= "$newDate"AND billdate <= "$newDate2" ORDER by `vno`')->result_array();
$data['query']     = $query;

View Code:

<?php foreach ($query as $row): ?>
                                            <tr><td></td><td></td><td></td><td></td>
                                        <td><?=$row['Prdtname'];?></td>
                                        <td><?=$row['Qty'];?></td>
                                        <td><?=$row['bundle'];?></td>

                                        <?php endforeach ?>

Hi! How is yout date stored in the db? Don't you need the H:i:s added to the date? — Eva, Nov 20 '18 at 07:02
@Yogendrasinh I am getting no values for this query tried in phpmyadmin — dhara, Nov 20 '18 at 07:04
Can you show the actual dates your using (both from the database and the ones in `$newDate`) as well as the datatype of the database values. — Nigel Ren, Nov 20 '18 at 07:08
I am getting the value from the user as dd-mm-yyyy and convert it into yyyy-mm-dd format..And i am using date as datatype and store it in yyyy-mm-dd format — dhara, Nov 20 '18 at 07:10
Can you see if https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql helps. — Nigel Ren, Nov 20 '18 at 07:14
@dhara I want to look in your printed query. as i have tested i am getting result as below query. SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate >= "2018-10-05"AND billdate <= "2018-10-11" ORDER by `vno` — Yogendrasinh, Nov 20 '18 at 07:16
@Yogendrasinh Yes i got the same result as yours..Tried in phpmyadmin — dhara, Nov 20 '18 at 07:23

Yogendrasinh · Accepted Answer · 2018-11-20T09:38:16.813

4

Change your query to below query and check.

You have to pass date in single or doube quote.

$query = $this->db->query('SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate >= "'.$newDate.'" AND billdate <= "'.$newDate2.'" ORDER by `vno`')->result_array();

Let me know if still having issue.

edited Nov 20 '18 at 09:38

answered Nov 20 '18 at 09:29

Yogendrasinh

895
1
8
23

score 0 · Answer 2 · answered Nov 20 '18 at 07:01

0

You can use Between

SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate BETWEEN "$newDate" AND "$newDate2" ORDER by `vno`

answered Nov 20 '18 at 07:01

Arup Garai

151
1
7

Thanks for the reply..But i get no value – dhara Nov 20 '18 at 07:03

score 0 · Answer 3 · answered Nov 20 '18 at 07:15

0

If you store the date only, I would try to use unix timestamp.

$snewsDate = strtotimr($startdate);
$newDate2 = strtotime($enddate);

SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE billdate BETWEEN 
UNIX_TIMESTAMP($newDate) AND UNIX_TIMESTAMP($newDate2) ORDER by `vno

answered Nov 20 '18 at 07:15

Eva

133
1
14

Thanks for the reply..But i didn't get any result – dhara Nov 20 '18 at 07:25

Twinkle · Answer 4 · 2018-11-20T09:41:54.247

0

Try this one:

$startdate         = $this->input->post('SDate');
$enddate           = $this->input->post('EDate');
$newDate           = date_format(date_create($startdate),"Y-m-d");
$newDate2          = date_format(date_create($enddate),"Y-m-d");
$data['startdate'] = $startdate;
$data['enddate']   = $enddate;
$query             = $this->db->query("SELECT vno,Prdtname,Qty,bundle FROM purchaseitem WHERE billdate BETWEEN '$newDate' AND '$newDate2' ORDER by vno")->result_array();
$data['query']     = $query;

edited Nov 20 '18 at 09:41

answered Nov 20 '18 at 08:07

Twinkle

191
2
12

Thanks for the reply..but getting no reuslt – dhara Nov 20 '18 at 08:09
date_format() expects parameter 1 to be DateTimeInterface, boolean given getting this error and printed nothing – dhara Nov 20 '18 at 08:12
Please share your database snapshot – Twinkle Nov 20 '18 at 08:15
added on my post – dhara Nov 20 '18 at 08:27
Where you are not getting the data, I think the problem is in the `view`. Share your view coding. – Twinkle Nov 20 '18 at 08:33
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/183931/discussion-between-dhara-and-twinkle). – dhara Nov 20 '18 at 08:35

How to get the values in between the range by using where clause

4 Answers4