3 Replies - 370 Views - Last Post: 14 September 2012 - 03:44 PM Rate Topic: -----

#1 skymonkier  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 14-July 12

Display three+ latest rows in MySQL?

Posted 14 September 2012 - 09:12 AM

I'm trying to display the three (or more) latest rows in MySQL, I'm currently using a small script that only displays one, but I'd like it to display three, or however many I want it to.

Le Script:
<?PHP
	$id = 1;
					
	try {
		include 'dbconnect.php'; //Connect to the database
		
		$displayact = $conn->prepare('SELECT * FROM news WHERE id = :id');
		$displayact->execute(array(':id' => $id));
		
		$result = $displayact->fetchAll();
		
		if ( count($result) ) { 
    		foreach($result as $row) {
      			echo "<p>" . $row['news'] . "</p>";
				echo '<p class="info">Posted on <span class="date">' . $row['date'] . '</span> by <span class="author">' . $row['name'] . '</span>.</p>';
    		}   
  		} else {
    		echo "No rows returned.";
 		 }
		
	} catch(PDOException $e) {
		echo "<p>Sorry, there was an error;   " . $e->getMessage() . ".</p>"; //Print out the exception
	}
?>


Is This A Good Question/Topic? 0
  • +

Replies To: Display three+ latest rows in MySQL?

#2 StefanOnRails  Icon User is offline

  • D.I.C Head

Reputation: 35
  • View blog
  • Posts: 106
  • Joined: 31-July 12

Re: Display three+ latest rows in MySQL?

Posted 14 September 2012 - 10:02 AM

First of all, I don't understand why do you manually set that $id to 1 and then you use it in the query :eh:.

View Postskymonkier, on 14 September 2012 - 06:12 PM, said:

I'm trying to display the three (or more) latest rows in MySQL,

Use date column to sort your rows.
$x = 3; 
$displayact = $conn->query("SELECT `news`,`date`,`name` FROM `news` ORDER BY `date` DESC LIMIT ". $x ."");

By the way, you only need to use prepared statements when you work with data that comes directly from the user.
Was This Post Helpful? 2
  • +
  • -

#3 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4354
  • View blog
  • Posts: 12,160
  • Joined: 18-April 07

Re: Display three+ latest rows in MySQL?

Posted 14 September 2012 - 10:02 AM

One way to do this is create a select statement where you sort by the id in DESCENDING order and get the top 3 using a LIMIT clause.

select * from table order by id desc limit 3 



This should do it. Here we are saying to select the top three rows of a list which is order by id in descending order. This would be the last three rows entered given that you properly made ID an auto_number that increments.

:)
Was This Post Helpful? 2
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Display three+ latest rows in MySQL?

Posted 14 September 2012 - 03:44 PM

View PostStefanOnRails, on 14 September 2012 - 05:02 PM, said:

By the way, you only need to use prepared statements when you work with data that comes directly from the user.

I wouldn't say that exactly. You should use prepared statements with any external data. Unless it's hard- coded, or taken from a trusted and reliably secure soure (which is very rare), it is a point of vulnerability and should not be trusted.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1