Count number of rows of specific mysql columns that are not empty

Question

I have a mysql table with an x amount of columns. I would like to check if the rows of a specific columns are empty or not ..

Currently I have:

$query10 ("SELECT count(*) FROM content WHERE linkname IS NOT NULL")     or die(mysql_error());
$result10 = mysql_query($query10);
$row10 = mysql_fetch_assoc($query10);
echo $row10['0'];

But I get an error:

Fatal error: Function name must be a string in ... on line 227

I never used count, before I used num_rows .. but can't seem to get it to work with that either ..

So just to make clear, my table looks like

|  column1  |   column2   |   column3  |
--------------------------------------------
|     a     |      b       |              |
---------------------------------------------
|     a2    |      b2      |      c2      |
----------------------------------------------
|     a3    |      b3      |              |

I want to count al rows of column3 that are not empty, so in this case the result of count should be 1.

counting gives following error:

mysql_fetch_assoc() expects parameter 1 to be resource, string given

Thanks so much

There is an error $query10 ("SELECT count(*) FROM content WHERE linkname IS NOT NULL") you are storing query in variable i guess, so do like this $query10 = "SELECT count(*) FROM content WHERE linkname IS NOT NULL"; removed parenthesis — Murtaza Bhurgri
– Murtaza Bhurgri, Commented Jan 14, 2017 at 10:41
Please use MySQLi instead of MySQL. MySQL is deprecated and removed in PHP 7.0. — Frank M
– Frank M, Commented Jan 14, 2017 at 10:50
No, that is not how it is working :) First you have to connect with MySQLi. After that you can setup your query and the fetch_assoc function is here. — Frank M
– Frank M, Commented Jan 14, 2017 at 10:55

Kudratullah · Accepted Answer · 2017-01-14 11:01:44Z

2

you need to use <> '' instate of IS NOT NULL

SELECT count(*) FROM content WHERE linkname <> ''

SQL Fiddle Demo

And as Murtaza Bhurgri says $query10 ("SELECT count(*) FROM content WHERE linkname IS NOT NULL") or die(mysql_error()); this will definitely give error. it should be something like

$query10 = "SELECT count(*) FROM content WHERE linkname <> ''";
$result10 = mysql_query( $query10 );
if ( !$result10 ) {
    die( 'Invalid query: ' . mysql_error() );
}
$row10 = mysql_fetch_assoc($result10);
echo $row10[0];

answered Jan 14, 2017 at 11:01

Kudratullah

1351 silver badge9 bronze badges

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

1 Comment

Sam Over a year ago

I added the 'as total' otherwise mysql_fetch_assoc doesn't seem to work

Sam · Accepted Answer · 2017-01-14 11:30:58Z

0

I came up with this, which works:

$query10 = "SELECT count(*) as total FROM content WHERE linkname <> '' AND content.album_id = $album_id  ";
$result10 = mysql_query( $query10 );
if ( !$result10 ) {
    die( 'Invalid query: ' . mysql_error() );
}
$row10 = mysql_fetch_assoc($result10);
echo "zo veel:".$row10[total];

I added the 'content.album_id = $album_id', since I want this only for more specific columns

and sorry for being in deprecated mode ..

answered Jan 14, 2017 at 11:30

Sam

579 bronze badges

Comments

Wolverine · Accepted Answer · 2017-01-14 11:32:46Z

mysql_connect('localhost', 'root', 'password');
mysql_select_db('database');

$query10 = "SELECT COUNT(linkname) AS linkname_rows_count FROM tbl WHERE linkname != '' AND linkname IS NOT NULL";
$result10 = mysql_query($query10);

if(isset($result10)) {
    $row10 = mysql_fetch_assoc($result10);
    echo $row10['linkname_rows_count'];
}

The rows in the specific column you mentioned may contain an empty string or a null value. The query you used only checks against the null value. Moreover, whenever you use COUNT function with the column passed in its parameter, assign an alias to that to make the result set easy to be handled.

Also, your code uses deprecated mysql extension. You need to migrate to mysqli or PDO to secure your code against attacks like SQL Injection. So, I've also given you alternative code written using mysqli functions.

$con = mysqli_connect('localhost', 'root', 'password', 'database');
$sql = "SELECT COUNT(linkname) AS linkname_rows_count FROM tbl WHERE linkname != '' AND linkname IS NOT NULL";
$query = mysqli_query($con, $sql);

if(isset($query)) {
    $result = mysqli_fetch_assoc($query);
    echo $result['linkname_rows_count'];
}

Hope it helps!

Collectives™ on Stack Overflow

Count number of rows of specific mysql columns that are not empty

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related