How to retrieve and display a COUNT query result in PHP?

Question

I'm a beginner who has problems with PHP :(

I have a PHP function which shows all the rows from the database table. Now I have to create paging to show only limited number of rows per one page.
I have a problem with retrieving a COUNT result from query. I want to create a condition where PHP & MySQL use LIMIT if number of rows is bigger than needed on one page.

The following code:

$count = "SELECT COUNT(*) FROM articles";
$countq = $db->query($count);
$countrs = mysql_fetch_array($countq);
echo $countrs;

should display a number of rows. However, it does not. What am I doing wrong? I want to see a result to make sure that everything else will work fine. But I can't get it working.
Error: mysql_fetch_array() expects parameter 1 to be resource, object given

$db contains database connection information (server, user...) and is working

http://stackoverflow.com/questions/2439829/how-to-count-all-rows-when-using-select-with-limit-in-mysql-query — Alive to die - Anant, Apr 17 '16 at 20:01
*$db contains database connection information (server, user...) and is working*... Probably not. Can you show that code? — fusion3k, Apr 17 '16 at 20:11
It is working ;) I already used the PDO from the answer and everything works OK. Thanks — encrypted21, Apr 17 '16 at 20:35

score 2 · Accepted Answer · answered Apr 17 '16 at 20:04

Use PDO for MySQL query.

$db = new PDO('mysql:host=#YOUR HOST#;dbname=#YOUR DB#;charset=utf8', '#YOUR LOGIN#', '#YOUR PASSWORD#');
$query = $db->query('SELECT COUNT(*) AS count FROM articles');
$countq = $query->fetch();
$query->closeCursor();
echo $countq['count'];

I hope this will help you

score 1 · Answer 2 · answered Apr 17 '16 at 20:12

1

You will have to set the limit in the query like

$count = "SELECT COUNT(*) FROM articles LIMIT 5,10";

where 5 is the starting point and 10 is the total number of results you want.

You mention: $db but not what $db is? i mean is it a database object class? this will work directly if you are using the a database class, and if that's the case the class will also have functions which will allow you to query data without using mysql_fetch_array (actually mysqli_fetch_array).

answered Apr 17 '16 at 20:12

chandan

123
13

Well, $db is a variable which contains new PDO( "mysql:host=" .dbserver. ";dbname=" .dbname,dbuser,dbpass, array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8" ) ); It's defined in an external file where database connection is created. This file is then included in current php file. – encrypted21 Apr 17 '16 at 20:39
in that case refer to this. http://php.net/manual/en/pdostatement.fetch.php PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0 – chandan Apr 17 '16 at 22:15
Oh, OK. Thank you! – encrypted21 Apr 18 '16 at 12:44

How to retrieve and display a COUNT query result in PHP?

2 Answers2