CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-array
Farjad Hasan
Farjad Hasan

Reputation: 3364

MySQL SELECT query returns multi dimensional array in case of no records

I am using simple select query in MySQL for fetching total number of managers exists in both tables. It works fine when there are managers present in DB and it return multidimensional array.

But when there is no manager exist. Even then it returns a multidimensional array with all values as null. I want it to return null or false or empty if there are not records exist.

CODE:

$query = "SELECT
                            mpp.ManagerWeeklyPointId,
                            mpp.ManagerId,
                            manager.ManagerName,
                            manager.ManagerLastName,
                                SUM(
                                    mpp.Point
                                ) AS Points
                        FROM
                            managerpredictorpoint as mpp
                        INNER JOIN manager ON mpp.ManagerId = manager.ManagerId
                        WHERE
                        manager.Verified = 1 AND
                        manager.Blocked = 0
                        ORDER BY Points DESC,manager.ManagerId ASC
                        LIMIT $offset, $limit";

$obj = $GLOBALS['DBConnect']->prepare($query);
        $executeResult = $obj->execute();
        $response2 = $obj->fetchAll(PDO::FETCH_ASSOC);
var_dump($response2);exit;

OUTPUT:

array (size=1)
  0 => 
    array (size=5)
      'ManagerWeeklyPointId' => null
      'ManagerId' => null
      'ManagerName' => null
      'ManagerLastName' => null
      'Points' => null

Upvotes: 0

Views: 162

Answers (3)

splash58
splash58

Reputation: 26143

I think this is because you use sum() function. Look at the example http://sqlfiddle.com/#!9/61816/2 : 

select id from t1 where c=2;
select id, sum(c) from t1 where c=2

first select returns nothing but the second returns row with nulls

write it so

select id, sum(c) s from t1 where c=2 having not isnull(s)

Upvotes: 0

Tony
Tony

Reputation: 1

You have not put the parameters (i.e $offset and $limit) into the execute method

Upvotes: 0

Tarun Upadhyay
Tarun Upadhyay

Reputation: 724

Add a NOT NULL condition in your query.

WHERE
                        manager.Verified = 1 
                      AND  
                      manager.ManagerName IS NOT NULL
                      AND
                        manager.Blocked = 0

This shall not give you the null records.

Upvotes: 0

Related Questions