get count of fields with a specific value ? :mysql-php

Question

I'm trying to get a count of number of payments(PAID) from the table.

Please Assume This is the table:

| book_no | name | mobile | date1 | date2 | date3 | date4 |.. daten |
|---------|------|--------|-------|-------|-------|-------|-------|
|    1    | Cell |        | PAID  | DUE   | DUE   |       |       |
|    2    | Cell |        | PAID  | PAID  | PAID  |       |       | 
|    3    | Cell |        | DUE   | DUE   | DUE   | DUE   |       |
|    4    | Cell |        | PAID  | PAID  | PAID  |       |       | 
|    5    | Cell |        | DUE   | DUE   | DUE   |       |       |

In the above table Count(Paid) = 7

The number of date columns is dynamic so I think it's wiser to search the entire table and get the PAID count.

this is the code I managed to write referring answers on Stack overflow but it I don't think it's the right one for this

        //what is the search?
        $search = "PAID";
        //get all the columns

    $columnsq ="SELECT
                COLUMN_NAME
                FROM
                information_schema.COLUMNS
                WHERE TABLE_NAME = " .$scheme_name. "
                AND TABLE_SCHEMA = 'gold' ";

            var_dump($columns);
        //put each like clause in an array
        $queryLikes = array();
        while ($column = $columns->fetch_assoc()) {
            $queryLikes[] = $column['COLUMN_NAME'] . " LIKE '%$search%'";
        }

        $query = "SELECT COUNT(*) FROM " .$scheme_name. "  WHERE " . implode(" OR ", $queryLikes);
        //echo $query; //should look like this:
        //SELECT * FROM users WHERE column1 LIKE '%something%' OR column2 LIKE '%something%' OR column3 LIKE '%something%' OR ...
        //so then
        $users=mysqli_query($conn,$query);
        while ($user = $users->fetch_assoc()) {
            //do stuff with $user
                echo $users;
        }

I get this error when I try to execute the above code

                Notice: Undefined variable: columns in E:\xampp\htdocs\schemeTable11.php on line 391
NULL 
Notice: Undefined variable: columns in E:\xampp\htdocs\schemeTable11.php on line 394

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on null

therefore I'm looking for alternative solutions. Please Help

@Marc Please see my edit to the post. I did a mistake in posting last time — Sapnesh Naik, Jul 29 '16 at 17:19
different dupe, then: http://stackoverflow.com/questions/5121027/fatal-error-call-to-a-member-function-fetch-assoc-on-a-non-object — Marc B, Jul 29 '16 at 17:45

score 1 · Accepted Answer · answered Jul 29 '16 at 16:39

1

You really need to learn how to read error messages:

$columnsq ="SELECT
       ^^----note those two letters

var_dump($columns);
              ^---UNDEFINED
    //put each like clause in an array
    $queryLikes = array();
    while ($column = $columns->fetch_assoc()) {
                          ^--UNDEFINED

You never executed your query, you never defined $columns,and this is EXACTLY what PHP is trying to tell you.

answered Jul 29 '16 at 16:39

Marc B

356,200
43
426
500

Please see my edit to the post. I did a mistake in posting last time – Sapnesh Naik Jul 29 '16 at 16:56

get count of fields with a specific value ? :mysql-php

1 Answers1