CODEWITHSUNDEEP
phphtmlmysql
ifusion
ifusion

Reputation: 2233

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

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



?>

Upvotes: 2

Views: 1226

Answers (1)

Kevin
Kevin

Reputation: 41885

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!
}

Upvotes: 2

Related Questions