2

I am new to wordpress development & tried to learning plugin development.I have created a custom table from which i want to show the number of records found.I have tried the below code but it always shows 1 as a result irrespective of number of rows in a table.

//To show number of rows in table
    function DB_Tables_Rows()
    {
      global $wpdb;
      $table_name = $wpdb->prefix . 'mydata';
     $count_query = "select count(*) from $table_name";
         $num = $wpdb->get_var($count_query);

         $num = $wpdb->num_rows;

        echo  $wpdb->num_rows . 'Rows Found';


    }
  • get_var already returns count. Check dumping $num variable. – Nilambar Sharma Dec 8 '14 at 11:15
4

Why don't you directly echo the $num as it will contain the count of rows already...Here's the edited part..

//To show number of rows in table
  function DB_Tables_Rows()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'mydata';
    $count_query = "select count(*) from $table_name";
    $num = $wpdb->get_var($count_query);

    echo  $num . 'Rows Found';


}
| improve this answer | |
  • Old thread. There is a flaw in this. If it is an empty table it will return '1' as that single row will have the value 0 being the count. – Steve Apr 1 '17 at 6:14
  • @Steve This ain't true. It's taking the count value... not counting the number of rows returned. – Maxime Nov 26 '18 at 22:50
2

WAY #1

You can use the WordPress way using $wpdb class:

<?php
global $wpdb;
$table_name = $wpdb->prefix . 'mydata';
$my_query = $wpdb->get_results( "SELECT * FROM $table_name" );
echo $my_query->num_rows;

The codex says:

Since this function uses the $wpdb->query() function all the class variables are properly set. The results count for a 'SELECT' query will be stored in $wpdb->num_rows.

WAY #2

It can also be count by PHP way. Just use your generic query, and use the PHP count() function.

<?php
global $wpdb;
$table_name = $wpdb->prefix . 'mydata';
$my_query = $wpdb->get_results( "SELECT * FROM $table_name" );
$num_rows = count( $my_query ); //PHP count()
echo $num_rows;

Reference: http://php.net/manual/en/function.count.php

Though it's my favorite, but as @MarkKaplun says:

there is a huge performance difference between counting the size of an array in PHP, and letting mysql count the rows for you. You are unlikely to notice it on small data, but for big tables you will.

So I'd also love to stick with the $wpdb way. <3

| improve this answer | |
  • ,thanks for quick response butI have 12 rows in a table but first code is showing 0 as result & the second code which is MySQL way is showing warning mysql_num_rows() expects parameter 1 to be resource, array given in /home/u168837564/public_html/wp-content/plugins/DB_Tables/DB_Tables.php on line 136 – hitesh Dec 8 '14 at 7:23
  • 2
    there is a huge performance difference between counting the size of an array in PHP, and letting mysql count the rows for you. You are unlikely to notice it on small data, but for big tables you will. – Mark Kaplun Dec 8 '14 at 7:34
  • Thanks @MarkKaplun I's unaware about it. And now I posted my answer after some searching. My luck that it was wrong first time, because that triggered me to learn the whole. Thanks a lot for your comment. – Mayeenul Islam Dec 8 '14 at 8:05
  • @MayeenulIslam, np ;) just to write down the reason there is a difference, it is related to the cost of finding and transferring the info from the DB to the PHP code. When you query for only the number of rows there is much less communication overhead. (and of course the DB might have the rows number cached as part of its data structure and therefor it requires almost zero work to find it) – Mark Kaplun Dec 8 '14 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.