[SOLVED] How do you limit results?

[Gem]

Hi

 

How do I limit the results returned by php from my database??

 

I.e. I have a table of articles, and I want to display the newest 5 articles on my homepage...

 

How do I do that?

 

This is the code I have so far... but this displays every record ...

 

<?php
$con = mysql_connect("CONNECTION DETAILS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
}mysql_select_db("bssql", $con);$result = mysql_query("SELECT * FROM articles ORDER BY ID DESC");while($row = mysql_fetch_array($result))
  {
   echo $row['Title']; 
   echo $row['SubTitle']; 
   echo "<br />";
  }mysql_close($con);
?>

 

Thanks in advance ..

 

Gem

 

PS: Can you tell me exactly where I need to add something if necessary ... I always mess things up otherwise 

[lonewolf217]

something along the lines of

 

select * from ( select *,ROW_NUMBER() OVER (ORDER BY id desc) as row from articles ) a where row <=5"

[Gem]

Would:

 

SELECT * FROM articles ORDER BY ID DESC LIMIT 5

 

Work??

[lonewolf217]

not everything supports LIMIT, i forget what the "limitation" is (pun intended) but it never worked for me on MYSQL

[samshel]

strange...LIMIT n1,n2 always worked for me..

 

may be u can try it and post what it does ?

[Gem]

  <?php

$con = mysql_connect("CONNECTION");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

}mysql_select_db("bssql", $con);$result = mysql_query("SELECT title, subtitle FROM articles ORDER BY ID DESC LIMIT 2");while($row = mysql_fetch_array($result))

  {

    echo $row['Title'];

    echo $row['SubTitle'];

       

  }mysql_close($con);

?>

 

Had to do 2 as theres only 3 records in the table lol ... doesnt do anything though ... all i get is a blank page ... I think I messed up the code ... the query works in SQLyog ...?

[samshel]

<?php
$con = mysql_connect("CONNECTION");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("bssql", $con);
$result = mysql_query("SELECT title, subtitle FROM articles ORDER BY ID DESC LIMIT 2") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
    echo $row['Title']; 
    echo $row['SubTitle'];
}
mysql_close($con);
?>

 

try this..

[Gem]

Hmmm ... still nothing?! ???

 

Did I do something to the Echo part??

 

no errors is weird...

[redarrow]

got to be a database set up problem the code provided is beautiful nice coding op.

[samshel]

try mysql_fetch_assoc() instead of mysql_fetch_array()

[redarrow]

 

all i can add like i said it already perfect.

<?php
$con = mysql_connect("CONNECTION");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("bssql", $con);
$result = mysql_query("SELECT `title`, `subtitle` FROM `articles` ORDER BY `ID` DESC LIMIT `2`") or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
    echo $row['Title']; 
    echo $row['SubTitle'];
}
mysql_close($con);
?>

[Gem]

red I copied your code and it didnt like the LIMIT '2' , came back with an error ...

 

so I did your code with LIMIT 2 .. and I've got a blank again ... this is interesting though, without the LIMIT 2 bit, the original code works fine ... with the LIMIT 2 I get no errors, but also no content ....

 

 

[Philip]

while($row = mysql_fetch_assoc($result))

{

    echo $row['Title'];

    echo $row['SubTitle'];

}

 

you're calling for Title, when it should be title - same with subtitle

[lonewolf217]

  Quote

red I copied your code and it didnt like the LIMIT '2' , came back with an error ...

 

so I did your code with LIMIT 2 .. and I've got a blank again ... this is interesting though, without the LIMIT 2 bit, the original code works fine ... with the LIMIT 2 I get no errors, but also no content ....

 

what error ?

[redarrow]

try this and cheek your spelling like uppercase letters are not the same as lowercase letters in php.

 

currently ID << capitalized is it in the database cheek.

<?php
$con = mysql_connect("CONNECTION");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("bssql", $con);
$result = mysql_query("SELECT `title`, `subtitle` FROM `articles` ORDER BY `ID` DESC LIMIT 2") or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
    echo $row['title']; 
    echo $row['subTitle'];
}
mysql_close($con);
?>

[Philip]

  Quote

  Quote

red I copied your code and it didnt like the LIMIT '2' , came back with an error ...

 

so I did your code with LIMIT 2 .. and I've got a blank again ... this is interesting though, without the LIMIT 2 bit, the original code works fine ... with the LIMIT 2 I get no errors, but also no content ....

 

what error ?

 

Caused by the ticks around the 2 in the limit clause. They shouldnt be there

[Gem]

Thanks guys ... we've cracked it ... it was just a caps/lower case problem ...

 

Thanks again

 

x