Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: search for a date close to a given one

    hello!

    i have a little problem (in fact quite big for me..)

    i have a database of notes and each note has a creation date( date_creation);

    when i am seeing a note i have to links, one to the following note and one to the note before this one;

    i will give you an exemple :

    in my database i have the following entries:

    id_note date_creation
    1 2003-08-01
    2 2003-08-02
    3 2003-08-06
    4 2003-08-03
    5 2003-08-10
    6 2003-08-08

    i am watching the note wrote in 2003-08-06;
    i want to have a link to 2003-08-03 and one to 2003-08-08

    how can i search for the 2 dates in the neighbourhood of the given date

    i have tried to order the database but i don(t know how to extract the dates

    thanks a lot

  2. #2
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    If I understand your problem...

    let's say you have a form with a query as datasource that select the data to display. So when you open the form the query asks for the date and then display only the record requested. Well... IF this is the case you can create two buttons: one going backwards in time and one going forwards... These buttons should run queries that search in the corresponding table a specific value: the value immediately before or after. To do so I would use the ID field. If the ID found is 3 then you can go to 2 or 4. This in the case you enter records chronologicaly. Otherwise you should use the date field.

    Hope this helps!
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  3. #3
    Join Date
    Aug 2003
    Posts
    6
    hi

    i have thought to the same idee but the informations are not entered chronologically; so i can not rely on the id field;

    i have found a solution, probably not the most clear but it seems to work:

    1.i order my database by date_creation values;
    2.i search if there are records that have the date_creation field value smaller than the given date
    3. if there is at least one record i repeatedly substract 1 day from the given date until i find a record with this creation date

    i don't know why i can't express myself very well, maybe i am to tired, i will give the code for a better understanding:




    $query_ord="ALTER TABLE `dos_notes` ORDER BY `date_creation`;";
    $resultat_ord=mysql_query($query_ord);

    $query_int="select * from dos_notes where (date_creation<'$date_notes')and (id_dos_deptravail='$dept_choisi')";
    $result_int=mysql_query($query_int);

    $number_int=mysql_num_rows($result_int);
    if($number_int!=0)
    {
    do
    {
    $date_notes=date("Y-m-d",dateadd($date_notes,-1,"d"));
    $query1="select * from dos_notes where (date_creation='$date_notes')and (id_dos_deptravail='$dept_choisi')";
    $result1=mysql_query($query1);
    $number1=mysql_num_rows($result1);
    $tableau1=mysql_fetch_array($result1);
    $max_bd="date_creation";
    }while($number1==0);
    }


    if someone else have a better solution i am looking forward to hear it, otherwise anyone can use this one

    ps. sorry for my english, i am a foreigner student that studies in France now and asks questions in English....

    brux

  4. #4
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    I can't help in this.

    Actually I thought the same solution... even if... maybe I would use vba with dateserial instruction (look it up in the help).

    Sorry...
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Sounds difficult, but it isn't. You can query you note table for the neighbours of a particular date, call it d:

    The previous date:
    SELECT max(Date_Creation) FROM Notes WHERE Date_Creation < d

    The next date:
    SELECT min(Date_Creation) FROM Notes WHERE Date_Creation > d


    If your d is the first or the last date, you just get NULL from your corresponding queries.

    If you have several notes on the same day, you will have to refine the approach.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Aug 2003
    Posts
    6
    thanks a lot DoktorBlue , i will try it...i haven't thought of that solution...

    anyway thanks a lot also to Marco Del Corno for answering so quickly

    i quess we can close this thread, can we?

  7. #7
    Join Date
    Aug 2003
    Posts
    6
    i have tried the solution and it works

    10x

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •