Cannot get query between two dates to work

[shmideo]

Hi

 

Been trying to run a query to get all rows from a table between two dates, but nothing seems to work.

 

$query = "SELECT * FROM table WHERE date BETWEEN '%2014-11-17%' AND '%2014-11-18%'";

 

Strange thing if I try a search instead a query in phpmyadmin I don't see an operator called 'BETWEEN' on the remote host, but I do on my localhost.

Does this mean it will never work on the remote host? Nevertheless it doesn't work on either and I do have records for both dates in the table.

 

phpmyadmin:

Version 4.1.14 localhost (wamp)
Version 3.3.7deb7 on remote server

 

Thanks

[Barand]

Remove the "%"s

[Ch0cu3r]

Do note phpmyadmin is not the database. It is a PHP script for managing your MySQL databases through a web interface.

 

MySQL is the actual database.

[shmideo]

Thanks, that shows the result, but I'm trying to get 2 variables from the form now (from date - to date)

 

....WHERE calldate BETWEEN '$calldate' AND '$calldate2'; (this works after removing the %s) but only working for the first variable.

 

Trying to echo both variables so that they will display one after the other, then I get "Notice: Undefined index: calldatecalldate2 in"

 

I know I have the syntax wrong, but cannot find the answer on the web, hope you can help.

 

$calldatem = "calldate" . "calldate2";

echo $row[$calldatem];

 

Have also tried this but same error for the second variable.

echo $row['calldate'];

echo $row['calldate2'];

[CroNiX]

post your whole query.

[shmideo]

Here's the code:

 

If($calldate){

$query = "SELECT * FROM callable WHERE calldate BETWEEN '$calldate' AND '$calldate2' AND clid LIKE '%$clid%' AND channel LIKE '%$channel%' AND duration";

$result = mysqli_query($dbcon, $query) or die('Error getting data');

$num_rows = mysqli_num_rows($result);

 

echo "<br>";

echo "$num_rows results found";

echo "<br> For Date: '$calldate' - '$calldate2'";

echo "<br> Client ID: '$clid'";

echo "<p> </p>";

echo "<table width='100%' border='1px'>";

echo "<tr bgcolor='#EEEEEE'><th width='20%'>Date</th> <th width='60%'>Client ID</th> <th style='text-align:right' width='20%'>Call Duration</th></tr>";

 

$tot = 0;

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

$tot += $row['duration'];

 

$calldatem = "calldate" . "calldate2";

 

echo "<tr><td width='20%'>";

// echo $row['calldate'];

// echo $row['calldate2'];

echo $row[$calldatem];

echo "</td><td width='60%'>";

echo $row['clid'];

echo "</td><td width='20%' style='text-align:right'>";

echo $row['duration'];

echo "</td></tr>";

 

}

 

$tot = $tot / 60 / 60;

echo "</table>";

[Barand]

After this line

$query = "SELECT * FROM callable WHERE calldate BETWEEN '$calldate' AND '$calldate2' AND clid LIKE '%$clid%' AND channel LIKE '%$channel%' AND duration";

what does this

echo $query;

give?

[shmideo]

From echo $query I get:

SELECT * FROM  WHERE callable BETWEEN '2014-10-11' AND '2014-11-18' AND clid LIKE '%Client X%' AND channel LIKE '%SIP/123456%' AND duration
1821 results found
For Date: '2014-10-11' - '2014-11-18'
Client ID: 'Client X'

So the query works after removing the %s and dusplays rows except the date field has an error: "Notice: Undefined index: calldatecalldate2

So this context is wrong: $calldatem = "calldate" . "calldate2";

 

But if I try this instead it errors on the 2nd variable "Undefined index: calldate2"

 

echo $row['calldate'];
echo $row['calldate2'];
 

[shmideo]

Sorry ignore that, just realised using echo $row['calldate']; which cotains the rows partially works. Showing correctly except it's omitting records for the to end datye 2014-11-18

[Ch0cu3r]

What are you trying to do? Display $calldate2 by the side of $row['calldate']?

echo $row['calldate'] . ' - ' . $calldate2  . '<br />';

NOTE. $row will contain an associative array of values from your table. The keys will be the column names from your table.

[shmideo]

The code is working correctly with the exception of the end date. For some reason the query (BETWEEN) is not showing the end date.

The form has the fields 'calldate' and 'calldate2' (from and to). If I select 2014-11-17 to 2014-11-18, I only get data for the 17th. Selecting 2014-11-17 to 2014-11-19, I get data for 17th and 18th. Nothing seems to fix, even tried <= and >=

[Barand]

The dates in a BETWEEN clause are inclusive

mysql> SELECT * FROM test_date;
+----+------------+
| id | date       |
+----+------------+
|  1 | 2014-11-15 |
|  2 | 2014-11-16 |
|  3 | 2014-11-17 |
|  4 | 2014-11-18 |
|  5 | 2014-11-19 |
|  6 | 2014-11-20 |
+----+------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM test_date
    -> WHERE date BETWEEN '2014-11-16' AND '2014-11-19';
+----+------------+
| id | date       |
+----+------------+
|  2 | 2014-11-16 |
|  3 | 2014-11-17 |
|  4 | 2014-11-18 |
|  5 | 2014-11-19 |
+----+------------+
4 rows in set (0.00 sec)

It must be the other conditions in the WHERE clause. Try with just the date condition and see if that gets all the dates then add in the other conditions one by one