[RESOLVED] I want to display the 50 most recent records

kayedj

Hi.. newbie question :queasy:

I am writing a script that displays a list of articles and I want it to only display the 50 most recent, decending from oldest to newest. I have the following (proably bloaded) code to achieve it, but it will only display the first 50, and never more. can anyone spot a logic error? here is the relevant part of the code.

The idea is that if the total number of messages is greater than 50 $cut will have the differance and the for loop will start at this number. $cut and $num_results are returning what i expect.

//.. some header code
  include("inc_db.php");
  $query = "select * from messages2";
  $result = $db->query($query);
  $num_results = $result->num_rows;

//.... some other code

if ($num_results < 50)
 {
   $cut = 0;
 }
   else
   {
     $cut = $num_results - 50;
   }
 for ($i = $cut;$i <$num_results; $i++)
 {
//display results here..
}
//...more code at end

abc123

I don't think I quite understand what the problem is.

You want it to display the first 50 from oldest to newest. But you say it does display the first 50 only.

Maybe it's late and I should give up but what is the unexpected result?

In sql you can use "ORDER BY" or "SORT" in your select query. This would order it how you want it and then you could display the ones you want starting from the top of the query result.

Apologies if I'm not quite "getting" what you mean here.

kayedj

sorry.. its probably me.. i want it to display the last 50 records.. but i dont want to run (unless i have to) 2 queries, the first of which will look up the total number of records in order to write a second query

abc123

So if there are more than 50 records then you need to split it up so that you only display 50 at a time.

Do you have an id field to use? If you have an id field that say starts from 0 to 400 then you could run a second query and select all the values where id = 350 to 400. Then display they results from the query and it should work.

You are simply entering a loop that runs 50 times. Can you post the code you are using to display the records inside the for loop?

I have a feeling that you are going through the loop 50 times (which you want) but it will start at the begining rather than the end. You're query will display from the begining and not the end.

kayedj

Yes thats what i want.. the ID from 350-400, but i wanted to avoid using the second query cos i have enough 😛

Theres a fair bit there, but, this is what is in the for loop

$row = $result->fetch_assoc();
     echo '<tr><td>';

 $memberID2 = $row['memberID'];

 $query2 = "select username, color from tblmembers where memberID = '".$memberID2."'";

 $result2 = $db->query($query2);
 $row2 = $result2->fetch_assoc();

 echo '<font color="#';
 echo $row2['color'];
 echo '">';
 echo $row2['username'];
 echo '</font>';

 $result2->free();

 echo '</td><td>';
 $currentdate = date(Y).'-'.date(m).'-'.date(d);
 if ($row['date']==$currentdate)
 {
        echo '<strong>Today </strong>';
 }
 else
 {
    echo stripslashes($row['date']);
        echo ' ';
 }
 echo 'at ';
 echo stripslashes($row['time']);
 echo '</td><td>';
 echo '<font color="#';
 echo $row2['color'];

 if ($memberID2 == 2)
 {
        echo '"><strong><center>';
        echo htmlspecialchars(stripslashes(strtoupper($row['message'])));
        echo '</center></strong>';
 }
 else
 {
        echo '">';
        include("inc_msgfilter.php");

 }
 echo "</font></td></tr>\n";

abc123

Does it just display the first record 50 times?

kayedj

no, it displays the first 50 records, but i want the last 50 records. i had it originally so that the newest was at the top by having 'order by messagID desc' in teh query, but my end user wanted it the other way up.

abc123

Would it help if you had another if statement there.

With this statement you would decide if it needs to be ordered ascending or descending. Then your for loop would run as normal.

I wouldn't have a query running inside a for loop. It is possible to get more than one value from a query and then interate through them. Extracting one value at a time does not seem like a good idea. Puts too much demand on the database.

I think you could also run a query outside the for loop to select ids where the value is > $somevalue.

kayedj

so let me get this straight...

you're suggesting running the

$query2 = "select username, color from tblmembers where memberID = '".$memberID2."'";

outside the for lop as

$query2 = "select username, color from tblmembers";

and then selecting the right memberID from inside the for loop (how!!)

and then ading an extra query to decide if the $query1 query needs to display all of the results or not?

abc123

Your query2 is only selecting one record at a time. It does this 50 times. That is 50 times you are having to access the database. Not a good idea.

MYSQL is a powerful tool and you can use the queries to select the values that you need to display. Then you will iterate from the top going through the result resource 50 times.

Outside the for loop:
Run a count query. This will return a number to tell you how many records there are. You could store this value as say $count.

Then if $count > 50 run a query to get the last 50 results. Else get the whole thing.

Then go into the for loop (no more queries needed) and iterate through the result resource and echo all the values in it.

If this isn't making any sense then there are two possibilites. Either I'm being stupid and not quite getting this or I am not explaining it correctly. Both are strong possiblities.

If you are still unsure then I'll try and make up an example. Probably one of the experts will jump in soon and have the magical answer. When this happens I will crawl into a corner and pretend I do not exist.

Hope this is helping. 🙂

kayedj

thanks for your help, but theres just one bit i dont understand (and its ben bugging me for a while, but i've always up to now been able to work arround it.)

If i have the query to get all the data needed inside the for loop, how do i relate to it inside the loop? i assume it uses some sort of array, but could you give an example please?
e.g. in the table 'messages2' theres a 'memberID' field and i then want to pass this ID into the members table query to get username and colour out, but how???

abc123

Firstly I would not be running a query inside a loop. If you are doing this then there is a problem that you need to address.

This is an example of an unary query. It will return one result and is like the one you have inside your 'for' loop.

$name = SELECT 'name' FROM member WHERE id = '3';

This would return only one value. Say if I was member with id 3 then $name would have value 'abc123'.

A lot of the time however you need more than one result returned. In your case you need 50. The following query will not hold a single result. Instead it will hold a muliple results and you can access these through a loop.

$result = SELECT 'name' FROM member;

$result now is what is called a resource. It has all the values of name from the member table.

How do you access these results? Simple! Iterate through them.

while ($row = mysql_fetch_assoc($result)) {
   echo $row['name'];
}

Obviosley instead of echo you can choose to store the value in a variable and do as you wish.

Use mysql to get the $result you need. There are really good ways to query to database to get the desired result set. Then simply iterate through them.

kayedj

I'm afraid I still dont understand how that would work in my situation, where the 'name' could be different for each itteration of the for loop.

There can be more than 50 messages, but only, say, 5 different members have posted a message. in the database, the memberID of the member who posted the message is stored in the same table as the message, but the extra details (name etc) are in another table which the second query was written for. how do i get it to extract the right name for the message?

abc123

I'm not doing very well here...

There are two databases? One has members and another has the message.

You need to get the last 50 messages (from table 1) and then relate them to the names of members from (table 2)?

Again this is sql. You need to perform a join query. Have a look in the mysql manual for join. That should be what your looking for (I hope).

kayedj

ok, thank-you v. much

abc123

Post back to say if it works.

Might be that I'm an idiot.

Just to correct what I said.... it isn't two seperate databases? You mean two seperate tables? Join works with tables but being in different databases is a different story.

kayedj

yeah, its 2 separate tables.

I'll probably not post back until tomorrow though, i'm getting tired and there's work tomorow :queasy:

abc123

Sleep is for the weak. 🙂

Best of luck with you queries. 🙂

kayedj

ok.. i lied.. i had a play with this query and code

 $testqry="SELECT * FROM messages2 INNER JOIN tblmembers ON messages2.memberID=tblmembers.memberID;";
 $result=$db->query($testqry);
 $num=$result->num_rows;
 for($w=0;$w<$num;$w++);
 {
   $row=$result->fetch_assoc();
   echo '<tr><td>'.$row['memberID'].' '.$row['message'].' '.$row['username'].' '.$num.'</td></tr>';
 }

but it will only display the first result, despite $num being equal to 20

Sorry, my mistake, I left a ; in the for command :quiet:

abc123

zzzzzz....

nope sleep is still for the weak so I'm not sleeping.

Do you get what a resource is? Why do you fetch_assoc() every time you go through the loop? Take it outside the loop. Is the result different?

What's the weather like in Cumbria? Scotland has rubbish weather. One day it will stop raining.

I really hope you get a guru soon. They will solve your problem in a moment and call me an idiot. Thank goodness I take critisim well eh!