[SOLVED] Select between a range of dates.

[Scip]

I am trying to query a database for range of result which fall between two dates.

 

The table i am selecting from is set up so that there is a column called date under which the time a row is inserted into the database is recorded by the NOW() function. The date is not stored as a unix time stamp but in the mysql formate, this might be a bad decision in terms of database design on my behalf.

 

here is the code for the query, it only returns one row of results whilst there are two and the difference in terms of dates is oneday:

 

$query = "SELECT ";
    $query .= "item1, item2, item3, item4, item5, item6, item7, item8, item9, item10 ";
    $query .= "FROM loot ";
    $query .= "WHERE ";
    $query .= "Date < NOW() ";
    $query .= "AND Date > DATE_ADD(NOW(), INTERVAL -1 MONTH) ";
    
    $result = mysql_query($query,$connection);
    confirm_query($result);

 

here is  what the screen print of phpmyadmin looks like:

 

thanks in advance.

[rhodesa]

date is a reserved word in MySQL...you shouldn't use it as a column name. change it to something else if you can. otherwise, if you can't, use `backticks`:

 

$query = "SELECT ";

    $query .= "item1, item2, item3, item4, item5, item6, item7, item8, item9, item10 ";

    $query .= "FROM loot ";

    $query .= "WHERE ";

    $query .= "`Date` < NOW() ";

    $query .= "AND `Date` > DATE_ADD(NOW(), INTERVAL -1 MONTH) ";

 

    $result = mysql_query($query,$connection);

    confirm_query($result);

[Scip]

Hi thanks for replying, i managed to change date to picked on but i am still having the same problem. I think it has something to do with my date arithmetic. Would it have been better to use epoch format? I prefer the mysql format cause it means less time fiddling around with code converting one format to the next.

 

the code now looks like this:

$query = "SELECT ";
    $query .= "item1, item2, item3, item4, item5, item6, item7, item8, item9, item10 ";
    $query .= "FROM loot ";
    $query .= "WHERE ";
    $query .= "picked_on < NOW() ";
    $query .= "AND picked_on > DATE_ADD(NOW(), INTERVAL -1 MONTH)";
    
    $result = mysql_query($query,$connection);
    confirm_query($result);

 

 

[PFMaBiSmAd]

NOW() is a datatime. You need to use CURDATE()

[Scip]

Thanks for the reply but using CURDATE() doesn't seem to work either.

 

it now looks like this.

 

$query = "SELECT ";
    $query .= "item1, item2, item3, item4, item5, item6, item7, item8, item9, item10 ";
    $query .= "FROM loot ";
    $query .= "WHERE ";
    $query .= "picked_on < CURDATE() ";
    $query .= "AND picked_on > DATE_ADD(CURDATE(), INTERVAL -1 MONTH)";
    
    $result = mysql_query($query,$connection);
    confirm_query($result);
    $loot = mysql_fetch_array($result,MYSQL_ASSOC);

 

Now i am think if i can use a different query to get the results i want.

[Scip]

Ok i tried something typing the dates into the query and i still get the same problem. I dont know whats wrong and i am starting to get really frustrated. It is still returning only one row when there are two rows within the specified dates.

 

here is how it looks now.

      $query = "SELECT * ";
    /*$query .= "item1, item2, item3, item4, item5, item6, item7, item8, item9, item10 ";*/
    $query .= "FROM loot ";
    $query .= "WHERE ";
    $query .= "picked_on > '2009-06-01' ";
    $query .= "AND picked_on < '2009-07-30'";
    
    $result = mysql_query($query,$connection);
    confirm_query($result);
    $loot = mysql_fetch_array($result,MYSQL_ASSOC);
    
    print_r($loot);

 

I also tried the following and still the same problem.

  $query = "SELECT * ";
    /*$query .= "item1, item2, item3, item4, item5, item6, item7, item8, item9, item10 ";*/
    $query .= "FROM loot ";
    $query .= "WHERE ";
    $query .= "picked_on BETWEEN ";
    $query .= "'2009-06-01' AND '2009-07-30'";
    
    $result = mysql_query($query,$connection);
    confirm_query($result);
    $loot = mysql_fetch_array($result,MYSQL_ASSOC);
    
    print_r($loot);

 

A print_r of $loot for both of the above gives this:

Array ( [id] => 1 [picked_on] => 2009-06-28 [member_name] => tom [item1] => Ebonite [item2] => Aluminite [item3] => Kobalit [item4] => Dragoturkey Peak [item5] => Bear Bone [item6] => Black Dragoss Horn [item7] => Ice Kwak Beak [item8] => Dragoturkey [item9] => Barley [item10] => Barley Sugar ) 

 

Only the first row is returned as you can see, is there something wrong with my query?

 

thanks in advance.

[PFMaBiSmAd]

Up until reply #4 in this thread, you did not post anything showing how you are retrieving the data.

 

I suggest reading the php.net documentation for the mysql_fetch_array function you are using - http://us.php.net/mysql_fetch_array

 

[Scip]

Forgive but i tried using mysql_fetch array on it's own with the only parameter being $result, i also tried MYSQL_NUM AND MYSQL_ASSOC as you seen in the above post but still only the first row is returned. This is why i think there is a problem with the  query.

 

The reason why i used MYSQL_ASSOC  is because i am using array_count_values to count the numbers of times something is repeated in the array and using fetch_array without any parameter caused double counting since it return both an associative and number indicies.

 

I am very new to php(only 3 months) and programming is general, in fact mysql is the only programme apart from a little javascript i know.

 

I really want to find a solution to this, but i might not know whats wrong or i am not explaining it clear enough.

 

 

[PFMaBiSmAd]

The point of the link to the documentation was that mysql_fetch_array -

 

"Fetch a result row as an associative array, a numeric array, or both"

 

"Returns an array that corresponds to the fetched row and moves the internal data pointer ahead."

 

"Returns an array of strings that corresponds to the fetched row, or FALSE  if there are no more rows."

 

All of these state row (singular), not rows (plural) and the code examples at that link all use a while() loop to continue fetching a row until a FALSE value is returned.

[Scip]

Thanks so much for taking your time to answer, there was nothing wrong with the query as you said it was the way i was printing the results and i should have used a while loop to retrieve each row. I didn't know that lol.