Database Records Query.

[NuTz]

Can anyone help me with the following mySQL code. Probably a simply solution but i cant find the answer anywhere.

I have a table called news full of articles each one has an associated news_id.

What i need to do is query the database and pull out the last article (coded that and it works).

Then i want to pull out the second to last article and finally the third to last article.

Can anyone give me a clue as to how to do this.

Thanks in advance

NuTz

[Norman_Graham]

Let's see your code for pulling out the last article (use PHP tags!!), then we can suggest an extension or an adaptation or a rewrite.

Norman

[bad76]

Hi,
you can use a sort field. If the last message is that have max news_id, the sort field can be news_id. If the last message is most recently you must use a datetime field and use it as sort field. Then when you make the select just add "ORDER BY news_id (or other filed...) DESC". First 3 record are last, second last and third last (by time or id depending sort field you use...)

see you

[peppeto]

Do you want to pull only last article or all query starting with last article
select from news order by news_id DESC //this is all query strating with last article
select from news LIMIT 0,1 order by news_id DESC //only one last article
regards peppe

[NuTz]

Ok heres the code in question:

<?php 

mysql_connect ("databse","user","password");
mysql_select_db("mydatabase");

$result = mysql_query ("SELECT * FROM news ORDER BY news_id DESC LIMIT 1");

if ($row = mysql_fetch_array($result)) {

do {
    $subject = $row["subject"];
    $text = $row["text"];

 echo $subject;
 echo $text;

   } while($row = mysql_fetch_array($result));

   } else {print "Sorry, no records were found!";}

?>

Any help much appreciated.

[NuTz]

Cheers Bad76 that does indeed get the last three records. What i need to do is split them out so i can refer to each one individually.

For example when the loop is running and extracting the records assign a variable name to the records it gets e.g.

loop 1
get record 1 and assign it to variable1
loop 2
get record 2 and assign it to variable2
loop 3
get record 3 and assign it to variable3

Any ideas?

Cheers

NuTz

[bad76]

Sure !

Try with array :

<?php 

mysql_connect ("databse","user","password");
mysql_select_db("mydatabase");

$result = mysql_query ("SELECT * FROM news ORDER BY news_id DESC LIMIT 0,3");
$i=0;
if ($row = mysql_fetch_array($result)) {

do {
    $subject[] = $row["subject"];
    $text[] = $row["text"];

 echo $subject[$i];
 echo $text[$i++];

   } while($row = mysql_fetch_array($result));

   } else {print "Sorry, no records were found!";}

?>

Exiting loop you have the needed array, sorted from last to oldest.

I hope this is what you want.

[ost87]

Hi

I used the above code to display the most recent record and it works nicely. I want to develop this and make the most recent record only appear if it has a certain value for a certain column.

I tried doing this:

<?php

mysql_connect ("qqqq","qqqqq","qqqqq");
mysql_select_db("qqqqq");

$result = mysql_query ("SELECT * FROM reviews WHERE visible="Y" ORDER BY reviewID DESC LIMIT 1");

if ($row = mysql_fetch_array($result)) {

do {
$rating = $row["rating"];
$title = $row["title"];
$review = $row["review"];
$name = $row["name"];

} while($row = mysql_fetch_array($result));

} else {print "Sorry, no records were found!";}

?>

As you can see I added "WHERE visible="Y" ", but I get error message: parse error, unexpected T_STRING

Any ideas?

[Norman_Graham]

Hi Ost87

A parse error means ... a parse error. I can see one error - you should have written:

$result = mysql_query("SELECT * FROM reviews WHERE visible='Y' ORDER BY reviewID DESC LIMIT 1");

You had double quotes round the parameter in the WHERE clause, which would certainly cause an error.

HTH

Norm