posted July 13, 2000 11:47 AM            

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
---

code:

---

<!-- #!/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++; 
} 
?>

---

DONE.

posted July 13, 2000 01:21 PM            

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

posted July 13, 2000 02:15 PM            

quote:

---

Originally posted by JoelFord:
I think you just need a LIMIT clause. From the MySQL manual:

---

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.

posted July 13, 2000 02:42 PM            

$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]";
}

posted July 13, 2000 02:54 PM            

quote:

---

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:

---

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

posted July 14, 2000 04:26 AM            

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
}?>

posted July 14, 2000 06:30 AM            

quote:

---

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:

---

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,