Re: [PHP] mysql case statement
On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik keevit...@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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Monday 28 June 2010 09:49:55 Andrew Ballard wrote: On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik keevit...@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. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Sunday 27 June 2010 22:12:41 Brandon Rampersad wrote: no At least smack me and give us an explanation. :-) -- Blessings, David M. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Mon, Jun 28, 2010 at 10:27 AM, David McGlone da...@dmcentral.net wrote: 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. I think you are confusing a few things. You can't really rely on testing empty(DB_HIDDENPANELS) because the value of the constant DB_HIDDENPANELS is most likely a string that was set with an earlier call to define. The OP could have tested for the column value hiddenpanel using an if (...) test as you suggeted. However, given that the OP stated he would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS, the INNER JOIN will do that at the database query level, so an if (...) then test in PHP isn't really necessary. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Monday 28 June 2010 11:14:53 Andrew Ballard wrote: On Mon, Jun 28, 2010 at 10:27 AM, David McGlone da...@dmcentral.net wrote: 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. I think you are confusing a few things. You can't really rely on testing empty(DB_HIDDENPANELS) because the value of the constant DB_HIDDENPANELS is most likely a string that was set with an earlier call to define. The OP could have tested for the column value hiddenpanel using an if (...) test as you suggeted. However, given that the OP stated he would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS, the INNER JOIN will do that at the database query level, so an if (...) then test in PHP isn't really necessary. That is exactly what I was wondering. I haven't got to joins yet, and didn't understand them at all until Richard explained a few minutes ago. I knew my suggestion was a complete shot in the dark and I did feel stupid posting it, but I'm glad now, because I learned something invaluable. Thanks to this list, someday, which I hope is sooner than later, I'll be able to help somebody with complete confidence. -- Blessings, David M. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
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
Re: [PHP] mysql case statement
no
On Sun, Jun 27, 2010 at 8:29 PM, David McGlone da...@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
