Jump to content

Cannot get query between two dates to work


shmideo

Recommended Posts

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

Link to comment
Share on other sites

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'];

Link to comment
Share on other sites

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>";

Link to comment
Share on other sites

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'];
 

Link to comment
Share on other sites

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 >=

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.