SQL Count Feature?

[Guest]

I am starting to use google charts and want to echo the amount of rows on an sql table where the column Status 's value is completed, inprogress, booked. Have been at this for a while and cant find how to do it, have googled and looked on stack overflow and all are pointing to Count(status) inside a SQL query which i got working in phpmyadmin however cant get php to echo this in the head of the webpage where the google chart is created.

 

Google Charts: https://developers.google.com/chart/interactive/docs/

SQL COUNT(STATUS) EXAMPLE: http://prntscr.com/a2ui0a

[rEaGeNeReary]

What code are you using to connect to the database, and running the sql query?

Something like this

$connection = new PDO(DSN, username, password);
$sqlquery = $connection->prepare("select count(status) from Jobs where Status='Complete'");
if ( !$sqlquery->execute() )
{
	// error
}
else
{
	// success
	while ($result = $sqlquery->fetch(PDO::FETCH_ASSOC))
    {
        print_r($result);
    }
}

 

[Guest]

25 minutes ago, rEaGeNeReary said:

What code are you using to connect to the database, and running the sql query?

Something like this

$connection = new PDO(DSN, username, password);
$sqlquery = $connection->prepare("select count(status) from Jobs where Status='Complete'");
if ( !$sqlquery->execute() )
{
	// error
}
else
{
	// success
	while ($result = $sqlquery->fetch(PDO::FETCH_ASSOC))
    {
        print_r($result);
    }
}

 

Connection: (each file has a include at the top using this file)

 

<?php
define('DB_HOST','localhost');
define('DB_USER','user');
define('DB_PASS','pass');
define('DB_NAME','name');
$connection = mysql_connect(DB_HOST,DB_USER,DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());
?>

A normal query is like this:

$result = mysql_query("SELECT * FROM Jobs ORDER BY ID ASC");
if (!$result) {
die(mysql_error());
}
while ($Job = mysql_fetch_assoc($result)) {
}
mysql_free_result($result);

 

 

 

[rEaGeNeReary]

1 minute ago, D14RAP said:

-snip-

 

So you're not using the sql query you wanted?

select count(Status) from Jobs where Status='Complete'

 

 

Also, mysql() functions are already depreciated. Use mysqli or PDO instead and use prepared statements.

[Guest]

1 hour ago, rEaGeNeReary said:

So you're not using the sql query you wanted?

select count(Status) from Jobs where Status='Complete'

 

 

Also, mysql() functions are already depreciated. Use mysqli or PDO instead and use prepared statements.

the aforementioned query is for normal queries where i print to tables the raw data, wanting to display counts but un sure how to in php.

[rEaGeNeReary]

9 minutes ago, D14RAP said:

the aforementioned query is for normal queries where i print to tables the raw data, wanting to display counts but un sure how to in php.

Use the one I provided earlier. It's a working code. Use this as reference: https://secure.php.net/manual/en/pdo.connections.php

[Guest]

12 hours ago, rEaGeNeReary said:

Use the one I provided earlier. It's a working code. Use this as reference: https://secure.php.net/manual/en/pdo.connections.php

My connection code works, the issue is when trying to display the amount of rows that contain the same column value?

[rEaGeNeReary]

47 minutes ago, D14RAP said:

My connection code works, the issue is when trying to display the amount of rows that contain the same column value?

What sql query are you running to get the number of rows that contain the same column values anyway?

[Guest]

15 minutes ago, rEaGeNeReary said:

What sql query are you running to get the number of rows that contain the same column values anyway?

that's what I am asking for as can't find a working solution

[rEaGeNeReary]

Just now, D14RAP said:

that's what I am asking for as can't find a working solution

What exactly happened when you run "select count(status) from Jobs where Status='Complete'" query using PDO or whatever? Show the code on how you're even attempting to do it as I've already given you a working example, you just need to supply the DSN, username and password.

 

$connection = new PDO("mysql:host=localhost;dbname=testdb", "root", "12345678");
$sqlquery = $connection->prepare("select count(status) from Jobs where Status='Complete'"); // <-- the sql query you showed on your screenshot.
if ( !$sqlquery->execute() )
{
	// error
}
else
{
	// success
	while ($result = $sqlquery->fetch(PDO::FETCH_ASSOC))
    {
        print_r($result); // <-- this print whatever the result of the query
    }
}