how to set up the table structure to do date ranges searches?

[GridCube]

Hello, I'm very new to mysql and php, and whatnot, so I proposed myself to create this page:

http://gridcube.netii.net/test/XTCv2.html

As it was my idea I learned to do everything you see there by myself, the problem is that i don't really have a search "engine", so I was trying to make one now, and it turned out that I created a very bad table structure;

 

:~$ mysql --version
mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1

::::

mysql> explain NewTestsTable
    -> ;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | int(5)       | NO   | PRI | NULL    | auto_increment |
| YEAR      | varchar(4)   | NO   |     | NULL    |                |
| MONTH     | varchar(10)  | NO   |     | NULL    |                |
| DAY       | varchar(2)   | NO   |     | NULL    |                |
| CYCLE     | varchar(15)  | NO   |     | NULL    |                |
| NICK      | varchar(15)  | NO   |     | NULL    |                |
| TESTS     | varchar(30)  | NO   |     | NULL    |                |
| STATUS    | varchar(4)   | NO   |     | NULL    |                |
| Comm_Bugs | varchar(256) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

 

as you can see Year, Month, and Day are in separated rows, I did this because it was easier for me to do

   
$day = date("d");
$month = date("F");
$sql = "select DAY,MONTH,$cols from $table_name where DAY = '$day' && MONTH ='$month'"; 

(sorry for the php code, i though it was relevant)

 

to show daily results, which works perfectly.

 

But I wan't to be able to see tests by groups of "last 7 days" "last 30 days", also give a basic search engine to allow users to search for particular testcases, or even particular dates, those last two are easy, but the dates ranges are the problems

 

I think that i need to change my table structure to do so, I am correct? if yes, whats a good way of doing so? If i can work whit the current structure, how should i aproach the search?

 

Sorry if im being really dumb here, as said very new at this.

 

Thank you for any time you take, even to read this

 

If you need any other information please tell me so, i tried to give you all that was required on the ReadMeFirst post

[awjudd]

Since you are storing all of the date information (day, month, year) I would use a DATE column (http://dev.mysql.com/doc/refman/5.0/en/datetime.html) instead of breaking it out into 3 separate.  This will open you up to use the built in DATE functions in mysql (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html).

 

Hope this helps.

 

~juddster

[PFMaBiSmAd]

You would use a DATE (YYYY-MM-DD format) data type.

 

There are several reasons -

 

1) It is optimized to use the least amount of storage.

 

2) It results in the fastest queries involving dates and manipulation of dates.

 

3) It allows dates to be compared and sorted using greater-than/less-than comparisons.

 

4) It allows the few dozen mysql data/time functions to work on the value in a query (which will solve your interval problem.)

 

You can create a new date column and populate it from your existing data using a single UPDATE query (a query without a WHERE clause will update all the rows at one time) and the mysql STR_TO_DATE() function to produce a DATE (YYYY-MM-DD) value from your existing three column values.

[GridCube]

Since you are storing all of the date information (day, month, year) I would use a DATE column (http://dev.mysql.com/doc/refman/5.0/en/datetime.html) instead of breaking it out into 3 separate.  This will open you up to use the built in DATE functions in mysql (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html).

 

Hope this helps.

 

~juddster

You would use a DATE (YYYY-MM-DD format) data type.

 

There are several reasons -

 

1) It is optimized to use the least amount of storage.

 

2) It results in the fastest queries involving dates and manipulation of dates.

 

3) It allows dates to be compared and sorted using greater-than/less-than comparisons.

 

4) It allows the few dozen mysql data/time functions to work on the value in a query (which will solve your interval problem.)

 

You can create a new date column and populate it from your existing data using a single UPDATE query (a query without a WHERE clause will update all the rows at one time) and the mysql STR_TO_DATE() function to produce a DATE (YYYY-MM-DD) value from your existing three column values.

 

oh! i knew there was a better way, thank you so much, I will research on how to do that update! thanks!

[PFMaBiSmAd]

UPDATE your_table SET date_column = STR_TO_DATE(CONCAT(YEAR,MONTH,DAY),'%Y%M%d')

[fenway]

Then your only choice is timestamp vs date -- choosing the latter is really only advantageous with timezones.

[GridCube]

Ok, so i've changed my table to look like this:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | int(5)       | NO   | PRI | NULL    | auto_increment |
| ETAD      | date         | NO   |     | NULL    |                |
| CYCLE     | varchar(15)  | NO   |     | NULL    |                |
| NICK      | varchar(15)  | NO   |     | NULL    |                |
| TESTS     | varchar(60)  | NO   |     | NULL    |                |
| STATUS    | varchar(4)   | NO   |     | NULL    |                |
| Comm_Bugs | varchar(256) | YES  |     | NULL    |                |
| PASSWORD  | varchar(256) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

 

 

but if I fail to understand how can i retrieve the data from one single day.

 

I do not want to get the data from a range of time, that works, using for example:

 

<?php
$sql = "SELECT * FROM $table_name WHERE DATE_SUB(CURDATE(), INTERVAL 2 DAY) <= ETAD";
?>

 

But what i want is NOT to get the data from the past two days, but the data from that particular Alone.

 

I tried:

<?php
$sql = "SELECT * FROM $table_name WHERE  date(ETAD) = '2011-10-15'";
?>

 

and it works, but i cant just add a date like that everytime

 

i wanted to use this:

<?php


function datebefore ( $a )
{
    $hours = $a * 24;
    $added = -($hours * 3600)+time();
    $month = date("m", $added);
    $day = date("d", $added);
    $year = date("Y", $added);
    $result = "$year-$month-$day";
    return ($result);
}
$p1 = datebefore("3");


$sql = "SELECT * FROM $table_name WHERE  date(ETAD) = '$p1'";

?>

 

the datebefore function returns YYYY-MM-DD, but it just fails to search whit a NULL result.

 

 

i've read the whole mysql site in all the things regarding at dates and stuff but it just talks about ranges, even using a BETWEEN ... AND ... fails because i still need to use a way to tell the days to it, dinamically.

 

What i want to do is to get the results for today on one table, from the day before in another, the day before that on another one and so on for 7 days.

 

EDIT:

this probably deserves a new topic :|

[PFMaBiSmAd]

You would need to post your query that is failing (I'll guess it is missing single-quotes around the literal date being put into it via a php variable.)

 

For your 7 days of data, you would execute ONE query that gets all the data you are interested in, then just detect the date change as you are iterating over the data to close out one table and start the next.

[GridCube]

You would need to post your query that is failing (I'll guess it is missing single-quotes around the literal date being put into it via a php variable.)

 

For your 7 days of data, you would execute ONE query that gets all the data you are interested in, then just detect the date change as you are iterating over the data to close out one table and start the next.

 

the query is the one i posted last:

<?php
$sql = "SELECT * FROM $table_name WHERE  date(ETAD) = '$p1'";
?>

 

where $p1 value is (for example):  2011-10-16 

whit no quotations, no spaces, nothing, just 4 numbers, a line, two number, a line, two numbers,

 

i tried to take it and pass it trough

<?php
$p1s = "'$p1'"
?>

to get $p1s to be like '2011-10-16' whit the single qoutation, but this didnt work either

[Pikachu2000]

ETAD is already a date type field, correct? There should be no need to use MySQL's DATE() function on it for the comparison.

[PFMaBiSmAd]

What does echoing $sql show?

[GridCube]

What does echoing $sql show?

 

echoing $sql returns:

select ETAD,NICK, TESTS, STATUS, Comm_Bugs from Testings where date(ETAD)= ''

 

ETAD is already a date type field, correct? There should be no need to use MySQL's DATE() function on it for the comparison.

not using date() doesnt change anything :/

[PFMaBiSmAd]

If you look at what the query echoed as, you would see that the variable holding the date is empty or non-existent and you would need to troubleshoot why.

 

Do you have error_reporting set to E_ALL and display_errors set to ON so that all the php detected errors will be reported and displayed? You should be developing and debugging php code with these two settings to get php to help you.

[GridCube]

If you look at what the query echoed as, you would see that the variable holding the date is empty or non-existent and you would need to troubleshoot why.

 

Do you have error_reporting set to E_ALL and display_errors set to ON so that all the php detected errors will be reported and displayed? You should be developing and debugging php code with these two settings to get php to help you.

 

thank you, I did this:

<?php
    $result = "'$year-$month-$day'";
?>

so results from $previousday is 'YYYY-MM-DD' whit the single quotes, and I changed

<?php
$sql = "SELECT * FROM $table_name WHERE  date(ETAD) = $p1 ";
?>

so its not quoted, and now everything works