Geek Answers Handbook

Select the last 5 rows by date

I have not touched PHP and am trying to select the last 5 entries in my database and print them on the screen.

I see that the mysql command is no longer recommended and use PDO-> mysql instead.

My query looks something like this:

SELECT id,title,date,author FROM table ORDER BY date DESC LIMIT 5;

I assume that I will have to put the values ​​in an array and create a loop and output the results.

 <?php $db = new PDO('mysql:dbhost='.$dbhost.';dbname='.$dbname, $user, $pass); while () { print($title[$i], $date[$i], $author[$i]); $i++ } $db = null; ?>

I am stuck in filling in the blanks with the above code.

Update: line $ db = new PDO .... reports an error:

 PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] Can't connect to local MySQL server through socket... in /var/...

Confirmed that PDO is installed and enabled. My other web applications on the server can connect to one remote mysql server.

+6
source share
2 answers
 <?php $host = 'localhost'; $db = 'db-name'; $user = 'db-user'; $pw = 'db-password'; $conn = new PDO('mysql:host='.$host.';dbname='.$db.';charset=utf8', $user, $pw); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); ?> <?php $sql = "SELECT id,title,date,author FROM table ORDER BY date DESC LIMIT 5"; $query = $conn->prepare($sql); $query->execute(); $row = $query->fetch(PDO::FETCH_ASSOC); $totalRows = $query->rowCount(); ?> <?php do { // print your results here ex: next line echo 'Title: '.$row['title'].' Date: '.$row['date'].' Author: '.$row['author'].'<br>'; } while ($row = $query->fetch(PDO::FETCH_ASSOC)); ?>

Happy coding!

Remember to shut down and free resources

 <?php $query->closeCursor(); ?>

EDIT

I recommend not repeating the error messages as soon as you confirm your code functions as expected; however, if you just want to use plain text, you can do it ...

You can add this to your connection block ...

 if ($conn->connect_error) { die("Database Connection Failed"); exit; }

You can also change your query block ...

 try { $sql = "SELECT id,title,date,author FROM table ORDER BY date DESC LIMIT 5"; $query = $conn->prepare($sql); $query->execute(); $row = $query->fetch(PDO::FETCH_ASSOC); $totalRows = $query->rowCount(); } catch (PDOException $e) { die("Could not get the data you requested"); exit; }

Again, it is recommended that errors not be repeated. Use the error check function for debugging only .

+4
source 
 <?php $db = PDO('mysql:dbhost=$dbhost;dbname=$dbname', $user, $pass); $sth = $db->prepare("SELECT id,title,date,author FROM table ORDER BY date LIMIT 5"); $sth->execute(); $result = $sth->fetch(PDO::FETCH_ASSOC); print_r($result); ?>

in the loop:

 while($row = $sth->fetch()) { echo $row['column']; }

From the documentation: http://php.net/manual/en/pdostatement.fetch.php

-1
source

More articles:


All Articles