How to display the number of rows using the SQL query COUNT

Question

When I run this query SELECT COUNT(ID) FROM blog_posts in PHPmyadmin it returns the number of rows in my database which is what I want but now I want this number to be displayed on my success.php page using the count method below.

Heres my code so far: Database.class.php

<?php 
include 'config/config.php'; // Inlude the config file, this is where the database login info is stored in an array

class database {
    private $dbc; // Define a variable for the database connection, it's visabiliatly is set to 'private' so only this class can access it

    function __construct($dbConnection){ 
    // Running the __construct() magic function, 
    // I am passing through a variable into the contruct method which when the object is created will hold the login details from the $dsn array in config.php

        $this->dbc = $dbConnection; // This is saying that the variable $dbc now has the same value as $dbConnection ($dsn array)
        $this->dbc = mysqli_connect($this->dbc['host'], $this->dbc['username'], $this->dbc['password'], $this->dbc['database']); 
        // ^ Running the mysqli_connect function to connect to the database.

        if(mysqli_connect_errno()){ // If there is a problem connecting it will throw and error
                die("Database connection failed" . mysqli_connect_error());
            } else {
                echo "allgood";
            }

    }

    function insert($insertSQL){ // Creating an insert function and passing the $insertSQL variable into it

        mysqli_query($this->dbc, $insertSQL); // Querying the database and running the query that is located in 'success.php'.

        if (mysqli_connect_errno($this->dbc)) { // Will throw an error if there is a problem
            die("Failed query: $insertSQL" . $this->dbc->error);
        }

    }

    function count($countSQL){ // This is the method used for counting
        mysqli_query($this->dbc, $countSQL);

        if (mysqli_connect_errno($this->dbc)) { // Will throw an error if there is a problem
            die("Failed query: $countSQL" . $this->dbc->error);
        }


    }



}



 ?>

Success.php

<?php
include 'classes/database.class.php';
include 'config/config.php';

echo '<h2>Success page</h2>';

$objdb = new database($dsn);

$insertSQL = "INSERT INTO blog_posts VALUES(NULL, 'Test', 'THis is a message')";

$objdb->insert($insertSQL);

$countSQL = "SELECT COUNT(ID) FROM blog_posts";

$objdb->count($countSQL); // Executes the query, now how do I display the result? I have tried 'echo'ing this but it doesn't seem to work



?>

Kevin · Accepted Answer · 2014-10-06 04:43:26Z

2

Actually its much better to add an alias in your query:

$countSQL = "SELECT COUNT(ID) as total FROM blog_posts";
$result = $objdb->count($countSQL);
echo $result['total'];

Then, on your methods:

function count($countSQL){ // This is the method used for counting
    $query = mysqli_query($this->dbc, $countSQL);

    if (mysqli_connect_errno($this->dbc)) { // Will throw an error if there is a problem
        die("Failed query: $countSQL" . $this->dbc->error);
    }

    $result = $query->fetch_assoc();
    return $result;
}

Additional Info:

It might be good also on your other methods to put a return value. So that you'll know that it worked properly.

Example:

function insert($insertSQL){ // Creating an insert function and passing the $insertSQL variable into it

    $query = mysqli_query($this->dbc, $insertSQL); // Querying the database and running the query that is located in 'success.php'.

    if (mysqli_connect_errno($this->dbc)) { // Will throw an error if there is a problem
        die("Failed query: $insertSQL" . $this->dbc->error);
    }

    return $this->dbc->affected_rows;

}

So that here:

$insertSQL = "INSERT INTO blog_posts VALUES(NULL, 'Test', 'THis is a message')";
$insert = $objdb->insert($insertSQL); // so that its easy to test if it indeed inserted
if($insert > 0) {
    // hooray! inserted!
}

edited Oct 6, 2014 at 4:43

answered Oct 6, 2014 at 4:18

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

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

4 Comments

ifusion Over a year ago

Beautiful! Thank you so much. One question though, as I am new to PHP and MYSQL etc I see you have added 'as total' to the $countSQL query. What is this doing exactly? Appreciate your help :)

Kevin Over a year ago

@ifusion thats what basically an alias would do, when you do not provide one, the result set will provide you with this COUNT(id), but you put an alias like COUNT(id) as total, you'd be able to use total. here is a good read about it: dev.mysql.com/doc/refman/4.1/en/identifiers.html

ifusion Over a year ago

I am getting a

Fatal error: Call to undefined method mysqli::affected_rows() in C:\xampp\htdocs\xampp\oop\3\classes\database.class.php on line 31

when I have added the return $this->dbc->affected_rows();

Kevin Over a year ago

@ifusion my goodness, i edited my answer, its supposed to be ->affected_rows the attribute, not a method ->affected_rows()

Collectives™ on Stack Overflow

How to display the number of rows using the SQL query COUNT

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related