Why isn't this value defined by my query?

Question

I have a database query that displays a list of national birds. I picked up a script that adds a cool detail: The ability to add asterisks, stars, etc. to birds that represent more than one nation.

However, I must have changed something in my code or database table that broke it. I now get the error message Undefined index: Stars. Can anyone spot the problem?

I pasted the query into phpMyAdmin > SQL, and it seems to work OK, displaying rows representing nations with national birds...

SELECT GS.N, GS.IDArea SymArea, GS.IDSymbol, GS.URL, GS.Title,
GS.PageKind3, GS.Symbol, GS.Latin, GS.Desig, GS.DesigGen, GS.DesigGroup, GS.Date,
GG.N, GG.IDArea, GG.Name, GG.Type, GG.IDParent, GG.IDParent2, GG.Parent, GG.Parent2,    GG.IDReg, 
T.Stars 
FROM gs AS GS 
LEFT JOIN gw_geog AS GG ON GG.IDArea = GS.IDArea 

LEFT JOIN ( 
 SELECT Latin, COUNT(Latin) as Stars 
 FROM gs 
 GROUP BY Latin
) as T ON GS.Latin = T.Latin 

WHERE GS.DesigGen = 'bird' AND GG.Type = 'nat' AND GS.Symbol != '' 
OR GS.DesigGen = 'bird' AND GG.Type = 'dep' AND GS.Symbol != '' 
GROUP BY GS.IDArea ORDER BY GS.N

The most important value is probably GS.Latin, which simply lists various birds' Latin, or scientific names. It appears that the above query isn't "connecting" with the script in my loop, below. Does anyone have a hunch what the problem is?

while ($row = $stm->fetch())
{

  switch (TRUE)
 { 
  case ($row['Stars'] == 2): 
  $star_rating = ' (2)'; 
  break; 
  case ($row['Stars'] == 3): 
  $star_rating = ' (3)'; 
  break; 
  case ($row['Stars'] == 4): 
  $star_rating = ' (4)'; 
  break; 
  case ($row['Stars'] > 5): 
  $star_rating = ' (5 or more)'; 
  break; 
  default: 
  $star_rating = ''; 
  break; 
 }

}

This is PDO you're using? What is your fetch mode? Do you have associative keys? Inspect `$row` with `print_r($row);` to see what's in there. — Michael Berkowski, May 27 '14 at 02:12
@ Michael Berkowski... Yes, it's PDO. $stm = $pdo->prepare("SELECT..."); $stm->execute(array( )); I'll try print_r($row)... — , May 27 '14 at 02:16
Well, print_r($row) displays groups of data like this: Array ( [N] => [SymArea] => zwb [URL] => zambia/bird [IDSymbol] => zwb-Bird [Title] => Zambia’s National Bird [PageKind3] => 3 [Symbol] => fish eagle [Latin] => Haliaeetus-vocifer [Desig] => [DesigGen] => bird [DesigGroup] => Animals [Date] => [IDArea] => zwb [GName] => Zambia [Type] => nat [IDParent] => afr [Parent] => [IDReg] => afr_s [Rank] => [Name] => [Name2] => [NameCommon] => ) — , May 27 '14 at 02:21
However, I just discovered that my basic display is a little fishy, so maybe I need to go back to square one and create a new query from scratch. I don't understand what's going on. — , May 27 '14 at 02:22
When renaming a table (`gs AS GS`) consider renaming it to something else than it's name. In your query it would have made more sense to name the first occurrence gs1 and the second gs2 — Lennart - Slava Ukraini, May 27 '14 at 02:32

score 0 · Accepted Answer · answered May 27 '14 at 02:27

0

I think your left join make the Stars field to be null try to replace T.Stars in the query fields to

COALESCE(T.Stars,0) AS `Stars`

This will set a default 0 if the field is null

answered May 27 '14 at 02:27

Alon Eitan

11,997
8
49
58

score 0 · Answer 2 · answered May 27 '14 at 02:29

You forgot to put T.Latin on select part of your query, like

SELECT GS.N, GS.IDArea SymArea, GS.IDSymbol, GS.URL, GS.Title,
GS.PageKind3, GS.Symbol, GS.Latin, GS.Desig, GS.DesigGen, GS.DesigGroup, GS.Date,
GG.N, GG.IDArea, GG.Name, GG.Type, GG.IDParent, GG.IDParent2, GG.Parent, GG.Parent2,    GG.IDReg, 
T.Stars, T.Latin
FROM gs AS GS 
LEFT JOIN gw_geog AS GG ON GG.IDArea = GS.IDArea 

LEFT JOIN ( 
 SELECT Latin, COUNT(Latin) as Stars 
 FROM gs 
 GROUP BY Latin
) as T ON GS.Latin = T.Latin 

WHERE GS.DesigGen = 'bird' AND GG.Type = 'nat' AND GS.Symbol != '' 
OR GS.DesigGen = 'bird' AND GG.Type = 'dep' AND GS.Symbol != '' 
GROUP BY GS.IDArea ORDER BY GS.N

Why isn't this value defined by my query?

2 Answers2