Display the amount of Paid items in my database using php

Question

I am using php and mysql to create a page that displays all of the jobs we have in the database. The data is shown is a table and when a row is clicked a modal window triggers with the information of the clicked job inside. At the top of the page I want a simple counter that shows amount of paid jobs, invoiced jobs etc etc. I am using the code below but having no luck...

<?php
$con = mysql_connect("localhost","databaseusername","password");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("databasename", $con);

$result = mysql_query("select count(1) FROM jobslist");
$row = mysql_fetch_array($result);

$total = $row[0];


mysql_close($con);
?>

This code as far as I am aware is counting the amount of INT columns set to 1 rather than 0. No matter what I try I can't seem to get it to count the amount of 'paid' items in the database or 'invoiced' etc etc.

Once the count function is complete currently I am echoing out the outcome as below:

<?php echo "" . $total;?>

I am sure I am overlooking something simple, but any help is appreciated.

EDIT: TABLE STRUCTURE INCLUDED

https://i.stack.imgur.com/hcMJV.png

no, it's not I don't believe - more like counting all rows return from the query — Professor Abronsius, Feb 08 '16 at 13:42
Shows your table structure. No way to help you without knowing how data is modeled. — digitai, Feb 08 '16 at 13:43
`"select count(1)"` - Shouldn't you be counting something from the table, and not just a single hard-coded value? — David, Feb 08 '16 at 13:43
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 08 '16 at 13:44

Tom Wright · Answer 1 · 2016-02-08T13:46:57.213

0

When I do this I usually name the COUNT result. Try this out:

$result = mysql_query("SELECT COUNT(*) AS total_rows FROM jobslist;");
$row = mysql_fetch_array($result);

$total = $row['total_rows'];

If you do not want to name the COUNT result, then give the following a go:

$result = mysql_query("SELECT COUNT(*) FROM jobslist;");
$row = mysql_fetch_array($result);

$total = $row['COUNT(*)'];

edited Feb 08 '16 at 13:46

answered Feb 08 '16 at 13:40

Tom Wright

2,841
4
22
30

This is by no way a correct answer. Without aliasing the column, mysql queries execute right. His problem should be related to other issue. – digitai Feb 08 '16 at 13:42
He is fetching the result as an array. I believe that without directly naming that `COUNT(*)`, it will actually be stored in the row as `'COUNT(*)'`. I will update my answer to state this... – Tom Wright Feb 08 '16 at 13:45
it should be `mysql_fetch_associate` then – Nagendra Nigade Feb 08 '16 at 13:48

score 0 · Answer 2 · edited May 23 '17 at 11:45

select count(1) FROM jobslist

This code as far as I am aware is counting the amount of INT columns set to 1 rather than 0.

No, this is just counting rows in your table and not filtering. If you want to count something with a specific filter you have to add that filter condition:

SELECT COUNT(*) AS `MyCount`
FROM `joblist`
WHERE `MyColumn` = 1; -- assuming MyColumn contains the INT you're looking for

You should stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy.

score 0 · Accepted Answer · answered Feb 08 '16 at 13:49

0

Assuming a column called paid you could restructure the query similar to the following. If you needed to sum the amounts involved that requires additional tweaking.

$result = mysql_query("select 
    ( select count(*) from `jobslist` where `paid`=1 ) as 'paid',
    ( select count(*) from `jobslist` where `paid`=0 ) as 'unpaid'
    from jobslist");

$rows   = mysql_num_rows( $result );

while( $rs=mysql_fetch_object( $result ) ){
    $paid=$rs->paid;
    $unpaid=$rs->unpaid;

    echo 'Total: '.$rows.'Paid: '. $paid.' Unpaid: '.$unpaid;
}

answered Feb 08 '16 at 13:49

Professor Abronsius

33,063
5
32
46

Why not one query with a `GROUP BY`? – Jay Blanchard Feb 08 '16 at 13:49
1

there's always more than one way to skin a cat! – Professor Abronsius Feb 08 '16 at 13:50

score 0 · Answer 4 · answered Feb 08 '16 at 13:52

First you should change deprecated mysql_... to mysqli_... (look here how to). But it's not the reason you fail.

Unlike you seem to suppose, COUNT(1) will not look for an INT column having value 1. Instead you must use COUNT(*) or COUNT(a_column_name) (same result), with adding a WHERE clause stating which condition is involved.

Here you seem wanting to count records where a given column (say the_column) has value 1. So you should:

SELECT COUNT(*)
FROM jobslist
WHERE the_column = 1

Last point: you don't need echo "" . in <?php echo "" . $total;?>.
Merely write <?php echo $total;?>.

Display the amount of Paid items in my database using php

4 Answers4