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

$db contains database connection information (server, user...) and is working... Probably not. Can you show that code? — fusion3k
– fusion3k, Commented Apr 17, 2016 at 20:11
It is working ;) I already used the PDO from the answer and everything works OK. Thanks — encrypted21
– encrypted21, Commented Apr 17, 2016 at 20:35

Ikishie · Accepted Answer · 2016-04-17 20:04:42Z

2

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

answered Apr 17, 2016 at 20:04

Ikishie

463 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chandan · Accepted Answer · 2016-04-17 20:12:29Z

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, 2016 at 20:12

chandan

1231 silver badge13 bronze badges

3 Comments

encrypted21 Over a year ago

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.

chandan Over a year ago

in that case refer to this. 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

encrypted21 Over a year ago

Oh, OK. Thank you!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related