How to display number of rows in a sql table using php?

Question

I'm trying to get lines' number from the result of the sql query, so I started by connecting to the database, then checking if the connection was established and finally I tried to count the number of lines, here's the code :

 <?php   
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "db_test";
    
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    
    
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
        $res = $conn->query('SELECT COUNT (id_offre) AS nb FROM offres');
        
        $data = $res->fetch();
        
        $nb = $data['nb'];
        
        echo $nb;
?>

I get these errors :

Uncaught Error: Call to a member function fetch() on boolean

Call to a member function fetch() on boolean

I used echo to check the 'nb' value, what is the problem?

Community · Accepted Answer · 2023-11-17 20:38:18Z

2

Your query fails which makes $conn->query() to return false.

So, why does the query fail?

SQL doesn't allow for any spaces between function names and the parentheses, like you have at COUNT ().

So if you change:

SELECT COUNT (id_offre) AS nb FROM offres

to

SELECT COUNT(id_offre) AS nb FROM offres

...it should work.

Note: I would highly recommend you to have a read about mysqli::error() and add some error handling to your code.

edited Nov 17, 2023 at 20:38

CommunityBot

11 silver badge

answered Sep 8, 2018 at 11:46

M. Eriksson

13.7k4 gold badges31 silver badges42 bronze badges

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

4 Comments

B.Mayssa Over a year ago

I tried this $res = mysqli_query($conn,'SELECT COUNT(*) AS nb FROM offres'); and this $data = mysqli_num_rows($res); I no longer get the error but it gives me a false result, It gives 1 but i do have 4 lines in my table

M. Eriksson Over a year ago

@B.Mayssa - I can't answer that since I haven't seen your database and tables. If this solved your initial issue you posted about, feel free to mark it as accepted. If you have other issues (like the one you now mentioned), please make a new question where you include all that information (like example data etc).

M. Eriksson Over a year ago

@B.Mayssa - When you do a SELECT COUNT(what-ever) FROM ..., you will only get one single row back from the database. Instead of using num_rows(), you need to check the value nb that you got back from the database. It's just like you've done it in your question.

Funk Forty Niner Over a year ago

@B.Mayssa I agree with Magnus here. You haven't provided us with relevant information about the database in order to provide you with a solution. Magnus' answer IMHO does answer it. You asked to use COUNT() and it was provided to you. Now you say it doesn't return all results; your question is unclear.

7060965 · Accepted Answer · 2018-09-08 12:25:43Z

-1

<?php   
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "db_test";

    $conn = mysqli_connect($servername, $username, $password, $dbname);

    // Check connection
    if (! $conn) {
       die("Connection failed: " . mysqli_connect_error());
    }

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: ".$conn - > connect_error);
    }
    $res = $conn->query('SELECT * FROM offres');
    $data_count = mysqli_num_rows($res);
    echo($data_count);

?>

check this out!

edited Sep 8, 2018 at 12:25

answered Sep 8, 2018 at 12:12

7060965

693 bronze badges

1 Comment

Funk Forty Niner Over a year ago

Personally I feel that this answer even though accepted is 1) another form of "try this" (check this out!) and 2) it didn't answer the question as posted.

Collectives™ on Stack Overflow

How to display number of rows in a sql table using php?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related