mysql case statement
in [General]
|
Prev: Problem with SOAP and authentication? SoapFault exception: [HTTP] Unauthorized
Next: Modifying Existing Text File From PHP Is Not Working
From: "Tanel Tammik" on 27 Jun 2010 04:08 Hello, how to select only if value is present? $query = $db->query("select menus.id, menus.name, case when panels.id is not null then '1' end as hiddenpanel from " . \DB_MENUS . " as menus left join " . \DB_HIDDENPANELS . " as panels on (menus.id = panels.menu_id) where menus.id='" . (int)$id . "' "); i would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding value in HIDDENPANELS table! Br Tanel
From: David McGlone on 27 Jun 2010 20:29 On Sunday 27 June 2010 04:08:24 Tanel Tammik wrote: > Hello, > > how to select only if value is present? > > $query = $db->query("select menus.id, menus.name, > case > when panels.id is not null then '1' > end as hiddenpanel > > from " . \DB_MENUS . " as menus > left join " . \DB_HIDDENPANELS . " as panels on (menus.id = > panels.menu_id) > where menus.id='" . (int)$id . "' > "); > > i would like to select hiddenpanel only if there is a corresponding value > in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding > value in HIDDENPANELS table! I would use an if statement since you only need to determine true or false. Something like: $query = $db->query("select menus.id, menus.name, from " . \DB_MENUS . " as menus left join " . \DB_HIDDENPANELS . " as panels on (menus.id = panels.menu_id) where menus.id='" . (int)$id . "' "); if (empty(DB_HIDDENPANELS)) { echo ""; } else { echo "hiddenpanel"; } But I would wait for others to chime in on this one, because I'm very far from an expert, there's also got to be a much better efficient way to write the if statement above, but it's what I would do in a case like this until I found a better way. -- Blessings, David M.
From: Brandon Rampersad on 27 Jun 2010 22:12 no On Sun, Jun 27, 2010 at 8:29 PM, David McGlone <david(a)dmcentral.net> wrote: > On Sunday 27 June 2010 04:08:24 Tanel Tammik wrote: > > Hello, > > > > how to select only if value is present? > > > > $query = $db->query("select menus.id, menus.name, > > case > > when panels.id is not null then '1' > > end as hiddenpanel > > > > from " . \DB_MENUS . " as menus > > left join " . \DB_HIDDENPANELS . " as panels on (menus.id = > > panels.menu_id) > > where menus.id='" . (int)$id . "' > > "); > > > > i would like to select hiddenpanel only if there is a corresponding value > > in DB_HIDDENPANELS. At the moment i get NULL if there is no > corresponding > > value in HIDDENPANELS table! > > I would use an if statement since you only need to determine true or false. > Something like: > > $query = $db->query("select menus.id, menus.name, > from " . \DB_MENUS . " as menus > left join " . \DB_HIDDENPANELS . " as panels on (menus.id = > panels.menu_id) > where menus.id='" . (int)$id . "' > "); > > if (empty(DB_HIDDENPANELS)) { > echo ""; > > } > > else { > echo "hiddenpanel"; > > } > > But I would wait for others to chime in on this one, because I'm very far > from > an expert, there's also got to be a much better efficient way to write the > if > statement above, but it's what I would do in a case like this until I found > a > better way. > > -- > Blessings, > David M. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- A Brandon_R Production
From: Andrew Ballard on 28 Jun 2010 09:49 On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik <keevitaja(a)gmail.com> wrote: > Hello, > > how to select only if value is present? > > Â Â $query = $db->query("select menus.id, menus.name, > Â Â Â case > Â Â Â Â when panels.id is not null then '1' > Â Â Â Â end as hiddenpanel > > Â Â from " . \DB_MENUS . " as menus > Â Â Â left join " . \DB_HIDDENPANELS . " as panels on (menus.id = > panels.menu_id) > Â Â where menus.id='" . (int)$id . "' > Â Â "); > > i would like to select hiddenpanel only if there is a corresponding value in > DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding value > in HIDDENPANELS table! > > Br > Tanel > That's what a LEFT JOIN does - it returns all rows from the LEFT table that match the criteria in the WHERE clause, and then returns any rows from the RIGHT table that happen do match. If you only want rows that exist in both tables, change the join from LEFT (OUTER) JOIN to INNER JOIN. Andrew
From: David McGlone on 28 Jun 2010 10:27
On Monday 28 June 2010 09:49:55 Andrew Ballard wrote: > On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik <keevitaja(a)gmail.com> wrote: > > Hello, > > > > how to select only if value is present? > > > > $query = $db->query("select menus.id, menus.name, > > case > > when panels.id is not null then '1' > > end as hiddenpanel > > > > from " . \DB_MENUS . " as menus > > left join " . \DB_HIDDENPANELS . " as panels on (menus.id = > > panels.menu_id) > > where menus.id='" . (int)$id . "' > > "); > > > > i would like to select hiddenpanel only if there is a corresponding value > > in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding > > value in HIDDENPANELS table! > > > > Br > > Tanel > > That's what a LEFT JOIN does - it returns all rows from the LEFT table > that match the criteria in the WHERE clause, and then returns any rows > from the RIGHT table that happen do match. If you only want rows that > exist in both tables, change the join from LEFT (OUTER) JOIN to INNER > JOIN. Tanel, we both learned something. I didn't fully understand join myself yet, but I think I do now. but let me ask this if the join wasn't there would an if statement like I mentioned have worked? Blessings, David M. |