Search between two date range Error

[ehpratah]

Hi need some help suggestion 

I am trying to search data form two date range but it seems not working properly when i search for


$from_date = "2013-04-01";
$to_date = "2013-04-04";

$query = mysql_query("SELECT * FROM date WHERE Date between '" . $from_date . "'  AND '" . $to_date . "' ORDER by id DESC");

it will only return

Guest 1           2013-04-01
Guest 2           2013-04-02
Guest 3           2013-04-03
Guest 4           2013-04-01

it is omitting

Guest 5           2013-04-04

and i also try uisng this query

$query = mysql_query("SELECT * FROM date WHERE Date >= '" . $from_date . "' AND Date <= '" . $to_date . "' ORDER by id DESC");

it will show just the same result

Here's my table

id  Name                  Date

1   Guest 1           2013-04-01
2   Guest 2           2013-04-02
3   Guest 3           2013-04-03
4   Guest 4           2013-04-01
5   Guest 5           2013-04-04

[ehpratah]

Hi nevermind guys found away already

[pixelsoul]

You should post it here, so if anyone else ever has the problem and searches the forum they will be able to see how you solved it :)

[ehpratah]

Sure this is the complete and working script

<?php


if ($_REQUEST["from"]<>'' and $_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE Date >= '".mysql_real_escape_string($_REQUEST["from"])."' AND Date <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string;
} else if ($_REQUEST["from"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE Date >= '".mysql_real_escape_string($_REQUEST["from"])."'".$search_string;
} else if ($_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE Date <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_stringy;
} else {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE Id>0".$search_string;
}

$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
    while ($rows = mysql_fetch_assoc($sql_result)) {
?>

[ehpratah]

Hi i thought the script is already ok but it seems that when searching on a larger data its not working properly just like my original post..any help will be appreciated

thanks

[pritaeas]

Just a thought, although date is allowed as a name, try putting backticks around your table and column names, just to be sure.

[ehpratah]

Hi do you mean something like this

<?php
if ($_REQUEST["from"]<>'' and $_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE `SDate` >= '".mysql_real_escape_string($_REQUEST["from"])."' AND `SDate` <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_city;
} else if ($_REQUEST["from"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE `SDate` >= '".mysql_real_escape_string($_REQUEST["from"])."'".$search_string.$search_city;
} else if ($_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE `SDate` <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_city;
} else {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE `Id`>0".$search_string.$search_city;
}

$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
    while ($rows = mysql_fetch_assoc($sql_result)) {
?>

still not getting it...

[pritaeas]

I can't reproduce the problem, the following works. I have tried this query:

SELECT * FROM `table1` WHERE `date` BETWEEN '2013-04-01' AND '2013-04-04' ORDER BY `id` DESC

on this table:

/*
Navicat MySQL Data Transfer
Source Server Version : 50524
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `table1`
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES ('1', 'Guest 1', '2013-04-01');
INSERT INTO `table1` VALUES ('2', 'Guest 2', '2013-04-02');
INSERT INTO `table1` VALUES ('3', 'Guest 3', '2013-04-03');
INSERT INTO `table1` VALUES ('4', 'Guest 4', '2013-04-01');
INSERT INTO `table1` VALUES ('5', 'Guest 5', '2013-04-04');

[ehpratah]

Hi thanks pritaeas i think the problem is with my table structure on date im just using varcar not date...theory not yet tested yet but i think i'll get it now..thanks for the help by the way..

[ehpratah]

Hi i've tested the query again with the same table structure as what you've posted but it still not including Guest 5 it only show Guest 1 to 4..On your test does it include Guest 5? because from the query it must display all the data from the table cause it is looking for the date with the range of 2013-04-01 to 2013-04-04

the query is

$from_date = "2013-04-01";
$to_date = "2013-04-04";

$query = mysql_query("SELECT * FROM `table1` WHERE `date` between '" . $from_date . "'  AND '" . $to_date . "' ORDER by id DESC");

[pritaeas]

Yes, my test showed all five records.

[ehpratah]

weird im using the table that you've provided and the data on it but im getting just 4 records but when i add another record which is Guest 6 and date 2013-04-04.. it will only show Guest 1 to 5 and Guest 6 will be omitted..or the last record will not be dispalyed but when i add a counter for the record it will show that i have 6 record on my table

[pritaeas]

Something wrong with your PHP loop then? Have you tried executing the query in phpMyAdmin?

[ehpratah]

ive tried it on phpmyadmin and its working fine it show all 6 records..

heres my complete code if you can look what i am doing wrong

<?php

$db = mysql_connect ('localhost', 'root', '');

mysql_select_db ('date');


$query = mysql_query("SELECT * FROM `table1` WHERE `date` BETWEEN '2013-04-01' AND '2013-04-04' ORDER BY `id` DESC");

while($rows= mysql_fetch_row($query)) {
$string = '';

echo "<table border='1'><tr><th '>Guest Name</th><th>date</th></tr>";

if (mysql_num_rows($query)){
while($rows = mysql_fetch_assoc($query)){
$string .= "<tr><td>".$rows['name']."</td><td>".$rows['date']."</td></tr>";

}
$total=mysql_num_rows($query);
echo "<B><h3><br>Total Records : $total";
}else{
$string = "No matches found!";
}

echo $string;
}
?>

[pritaeas]

You have two while's screwing things up. First while reads the first record, the second all the others (the actual output). You don't need the first.

[ehpratah]

Geezz "sheepish" thanks pritaeas didn't notice that im so focus on the query itself that i didn't look on the whole script..now its working fine just need to integrate it to the actual page..