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

Question

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

@Gunaseelan var_dump($executeResult) returns true for 0 records — Farjad Hasan
– Farjad Hasan, Commented Aug 7, 2015 at 8:16
Then follow this code. $executeResult = $obj->execute(); if($obj->rowCount()!=0){ $response2 = $obj->fetchAll(PDO::FETCH_ASSOC); var_dump($response2);exit;}else{ echo "No Manager Exists"} — Gunaseelan
– Gunaseelan, Commented Aug 7, 2015 at 8:20

splash58 · Accepted Answer · 2015-08-10 17:54:39Z

0

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)

edited Aug 10, 2015 at 17:54

answered Aug 7, 2015 at 8:48

splash58

26.2k3 gold badges25 silver badges35 bronze badges

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

1 Comment

Farjad Hasan Over a year ago

But how can I resolve it? Because I need sum of points of that particular manager

Tarun Upadhyay · Accepted Answer · 2015-08-07 08:07:40Z

0

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.

answered Aug 7, 2015 at 8:07

Tarun Upadhyay

7242 gold badges7 silver badges16 bronze badges

1 Comment

Farjad Hasan Over a year ago

it still gives the same result

Tony · Accepted Answer · 2015-08-07 10:33:09Z

0

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

edited Aug 7, 2015 at 10:33

answered Aug 7, 2015 at 8:43

Tony

11 bronze badge

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related