#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Huntsville, AL, US
    Posts
    7
    Rep Power
    0
    Hi guys,

    I'm new here, thanks for the resource! I need help on something and I'm going completely nuts. I have a working news database that has no problems. Each record has a news_date field. What I want is to be able to pull out the last 14 dates in the database, and show each record, for each date, descending like this;

    13 July, 2000
    News item 1
    News item 2
    etc.

    12 July, 2000
    News item 1
    News item 2
    etc.

    11 July, 2000
    News item 1
    News item 2
    etc.

    ---

    the code I'm working with, doesn't apparently execute the second select and I need your help please? Earlier it was giving me the "Warning 0 is not a MySQL index" error, but it stopped and I don't really know what I did to change it, or how to get it working.

    Any help would be sincerely appreciated.

    Wayne Hunt
    Amiga.org

    start code
    ---
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    <!-- #!/usr/bin/php -->
    Building New Links menu for front page of Amiga.org ... <p>
    <?php

    mysql_connect("localhost", "username", "password");
    mysql_select_db("database") or die("Error opening database");

    $query = "select news_date " .
    "FROM news " .
    "GROUP by news_date " .
    "ORDER by news_date desc";

    $result = mysql_query($query);

    $i = 1;
    while ($row = mysql_fetch_row($result) and ($i < 15)) {
    $nd = $row[0];
    print $nd;

    $query2 = "select ID, news_date, news_headline, news_url " .
    "FROM news " .
    "WHERE news_date = $nd";


    print $query2 . "
    ";

    $result2 = mysql_query($query2);

    $g = 1;
    while ($row2 = mysql_fetch_row($result2)) {
    print $row2[2];
    $g++;
    }


    $i++;
    }

    ?>[/code]

    DONE.


  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    Seaside, CA
    Posts
    84
    Rep Power
    15
    I think you just need a LIMIT clause. From the MySQL manual:

    The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).
    mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15

    If one argument is given, it indicates the maximum number of rows to return.
    mysql> select * from table LIMIT 5; # Retrieve first 5 rows

    In other words, LIMIT n is equivalent to LIMIT 0,n

    ------------------
    From the day we're born, we're running out of time.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Huntsville, AL, US
    Posts
    7
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by JoelFord:
    I think you just need a LIMIT clause. From the MySQL manual:[/quote]Thanks, but I don't think you understand the problem (I'm confused myself).

    What I'm thinking is required is this sequence.

    1) connect to db (duh)
    2) SELECT 14 latest unique dates in desc order. (this works)
    3) for each date, SELECT all news items with that date and display them (this doesn't work)
    4) go back to #2 until you're out of the loop (denoted with $i)
    5) close up shop and call it done.
    ---

    I don't know, maybe it's a logic error, but I know it has to be do-able, because I see it on PHP sites all the time by means of "what's new" pages.

    Again, any help you can give would certainly be appreciated.

    Wayne Hunt
    wayne@amiga.org
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You don't want to do it that way. You'll run fifteen queries when one will suffice:

    $query="select * from news_table where to_days(date_field) between to_days(date_sub(now(),interval 14 day)) and to_days(now()) order by date_field desc";

    This will return all the records within 14 days ordered by the date they were inserted with the latest first. Then you can output them like this:

    $result=mysql_query($query);
    $cur_date="";
    while ($data=mysql_fetch_array($result))
    {
    if ($cur_date!=$data[date_field])
    {
    $cur_date=$data[date_field]);
    print "<p><h2>$cur_date</h2></p>"
    }
    print "<p><h3>$data[head_line]</h3></p>";
    print "<br>$data[story]";
    }

    Of course, you'll probably want to pretty up the output but just to give you an idea.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Huntsville, AL, US
    Posts
    7
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    You don't want to do it that way. You'll run fifteen queries when one will suffice:

    $query="select * from news_table where to_days(date_field) between to_days(date_sub(now(),interval 14 day)) and to_days(now()) order by date_field desc";

    This will return all the records within 14 days ordered by the date they were inserted with the latest first. Then you can output them like this:
    [/quote]Rod,

    Thanks for pointing me in the right direction. As you might understand from my code above, I'm only slightly above a newbie with PHP. I don't really understand how to integrate the select statement with what I've already got (above). For example, I don't understand "to_days(date_field)". Can you help out a bit? I know it's all elementary to you guys but I'm deeply struggling.

    Sincerely,
    Wayne Hunt
    wayne@amiga.org

  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    to_days() converts the date given into the number of days from year 0. It may not be required in this instance, however, not knowing the data type of the field you are storing the data in, it is the easiest way to make sure that all the elements of the BETWEEN statement are in the same format.

    I guess I wasn't paying attention to your code previously. Evidently you don't have the body of the story just the headline and a link to it??? If so, this code should work for you:

    <!-- #!/usr/bin/php --> Building New Links menu for front page of Amiga.org ... <p> <?php mysql_connect("localhost", "username", "password"); mysql_select_db("database") or die("Error opening database");
    $query="select news_date,news_headline,news_url from news where to_days(news_date) between to_days(date_sub(now(),interval 14 day)) and to_days(now()) order by news_date desc";
    $result=mysql_query($query);
    $cur_date="";
    while ($data=mysql_fetch_array($result))
    {
    if ($cur_date!=$data[news_date])
    {
    $cur_date=$data[news_date]);
    print "<p><h2>$cur_date</h2></p>"
    }?>
    <a href="<?php print $data[news_url] ?>"><?php print $data[news_headline] ?></a>
    <?php
    }?>


  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Huntsville, AL, US
    Posts
    7
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    I guess I wasn't paying attention to your code previously. Evidently you don't have the body of the story just the headline and a link to it??? If so, this code should work for you:[/quote] Rod,

    Aside from a curly bracket here, and a semicolon there, works like an absolute champ! I never could quite understand the functioning of arrays (which is why I'm a net admin, not a programmer) before but now I'm beginning to "get it". All I can say is thank you very, very much sir.

    Sincerely,

    Wayne Hunt
    wayne@amiga.org

Similar Threads

  1. simple form, simple trigger, whats wrong ??
    By zishto in forum Oracle Development
    Replies: 3
    Last Post: January 12th, 2004, 02:25 PM
  2. what is wrong with this code ????
    By fule in forum Database Management
    Replies: 1
    Last Post: November 20th, 2003, 02:24 AM
  3. *sigh*
    By vb.net in forum Dev Shed Lounge
    Replies: 45
    Last Post: November 9th, 2003, 08:52 PM
  4. offsetHeight returning the wrong height
    By Tobbe in forum HTML Programming
    Replies: 10
    Last Post: October 19th, 2003, 09:23 AM

IMN logo majestic logo threadwatch logo seochat tools logo