Having different results on same page

[Amarok]

Forgive my ignorance, I am fairly new to this whole thing. I have a database that I would like to use to display the contents in different lists on the same page. In other words, I would like a list of the latest 15 entries in the database to appear in list...then I would like the next 15 to appear on the same page but in a different list structure, etc. I have this so far, but it only displays the latest 15...I am stuck on how to get another list generated from the same query:

 

query="SELECT * FROM main ORDER BY date_post ASC LIMIT 0,2";

$result=mysql_query($query);

$num=mysql_numrows($result);



$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$title=mysql_result($result,$i,"title");
$embed=mysql_result($result,$i,"embed");
$date_vid=mysql_result($result,$i,"date_vid");
$date_post=mysql_result($result,$i,"date_post");
?>

<ul>
<?
echo "<li><a href='details.php?id=", $id, "'>" , $title, "</a></li>";
?>

</ul>

 

I was also wondering if there was a way to either change the text color, or even better, add a small graphic next to those results that have been posted to the database say within the past month...you know, something like a small graphic that would say "new" or something. Any help would be greatly appreciated!

[matthewhaworth]

I don't get exactly what you want, but if you want more than one entry to appear on that page then that script is definitely not going to work  .

 

With your loop (that should, really, in that context be a for loop) it erases the last piece of data as soon as it creates a new one.

 

You need to look up mysql_fetch_assoc, http://uk3.php.net/manual/en/function.mysql-fetch-assoc.php

 

Try this:

 

<ul>
<?
while($row = mysql_fetch_assoc($result)) {
echo "<li><a href='details.php?id=".$row['id']."'>".$row['title']."</a></li>";
}
?>
</ul>

 

Kinda kills two birds with one stone.. And as for the 'new post' thing.. surely, if they're the top 15 most recent, they'll all be new? But you could do something like this, it depends how your date is formatted in yoru database though

 

<ul>
<?
while($row = mysql_fetch_assoc($result)) {
echo "<li><a href='details.php?id=".$row['id']."'>".$row['title']."</a></li>";
if($row['date_post'] < now()) {
   echo "new post!";
}
}
?>

</ul>

 

Or something.. obviously the latter example is a little finicky..

[Amarok]

Thanks Matthew for correcting my script. Say I limit that to 5, it does give me a list of five results. Now my question is, how can I generate another list (for use in another <div>) of the next 5 entries in the database. I would ultimately like several <div>'s - the first with a list of the latest 5 entries, the next with the next five, the next with the 5 after that, etc. So that everytime a new entry is made the list in each div will be different.

[Jessica]

Look up the "select" syntax. gotapi.com is a good site for this stuff. Specifically, you're interested in : Limit and offset

[matthewhaworth]

Thanks Matthew for correcting my script. Say I limit that to 5, it does give me a list of five results. Now my question is, how can I generate another list (for use in another <div>) of the next 5 entries in the database. I would ultimately like several <div>'s - the first with a list of the latest 5 entries, the next with the next five, the next with the 5 after that, etc. So that everytime a new entry is made the list in each div will be different.

 

I think that's in the SQL, if you perform a different query everytime before you list it'll display each set that you queried.. if you get me.. though I'm not sure of the mysql syntax to query like 0 -> 4, then 5 -> 10

 

I believe, though not sure, that it should be:

 

$sql = "SELECT fields FROM table LIMIT 0, 5";

// list the information once queried and put in first div

$sql = "SELECT fields FROM table LIMIT 4, 5";

// list the information once queried and put in second div

$sql = "SELECT fields FROM table LIMIT 9, 5";

 

You get the idea? Not sure if that syntax works though

[Amarok]

I just about have this the way I want. One more thing though. I would like to pursue the "new post" function you mentioned, Matthew, but I'm not sure how it works. My "date_post" field has the following - TYPE: timestamp / ATTRIBUTES: ON UPDATE CURRENT_TIMESTAMP / DEFAULT: CURRENT_TIMESTAMP so a particular entry is getting a time stamp that ends up looking like this: 2007-08-23 13:30:17

 

I would like to have the "new post" notification show up only if the entry is less than 2 weeks old. Is this possible?

 

Right now my code is as follows:

 

$sql = "SELECT id as id, title
        FROM   main
LIMIT 0, 15
       ";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
    //echo $row["id"], ' ', $row["title"], '<br />';
    echo '<li><strong>','<a href="details.php?id=', $row[id], '">', $row[title], '</a></strong></li>';

}
echo '</ul>';