[PHP] and and weird results
I hope somebody can make sense of this. First, I don't understand what the difference is supposed to be between and - is there a difference for php and/or mysql? Second, I am trying to select all occurrences in a table.column where the first letter of the name is I, J, or K but not I or J or K; I want the all. Now, entering the following is no problem for any one letter: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' )); But the following does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')); Yet this gives off the wall results where the last names are nowhere near what they should be. Anyone have a rational explanation? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] and and weird results
PJ wrote: I hope somebody can make sense of this. First, I don't understand what the difference is supposed to be between and - is there a difference for php and/or mysql? Second, I am trying to select all occurrences in a table.column where the first letter of the name is I, J, or K but not I or J or K; I want the all. Now, entering the following is no problem for any one letter: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' )); But the following does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')); Yet this gives off the wall results where the last names are nowhere near what they should be. Anyone have a rational explanation? I wasn't aware that was an option in MySQL, I've always used AND. If there is a difference it's probably the same as in PHP and many other languages. Allow me to explain. In the case of the left-side expression is evaluated for true and if not true, the right side expression is not evaluated. In the case of both sides are evaluated regardless of the value of either side. Here's an example: $a = 1; $b = 2; $result = ($a == 2) $b++; // In this case, $a does not equal two and therefor the left-side is false, thus the right-side is never evaluated. $b remains 2. $result = ($a == 2) $b++; // In this case, $a still does not equal two and therefor the left-side is still false, however because we use the '' operator the right side is evaluated anyway and $b now equals 3. echo $result; // In both cases, $result is false. HTH, Kyle -- *Kyle Smith* UNIX/Linux Systems Administrator Inforonics, LLC
Re: [PHP] and and weird results
Kyle Smith wrote: PJ wrote: I hope somebody can make sense of this. First, I don't understand what the difference is supposed to be between and - is there a difference for php and/or mysql? Second, I am trying to select all occurrences in a table.column where the first letter of the name is I, J, or K but not I or J or K; I want the all. Now, entering the following is no problem for any one letter: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' )); But the following does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')); Yet this gives off the wall results where the last names are nowhere near what they should be. Anyone have a rational explanation? I wasn't aware that was an option in MySQL, I've always used AND. If there is a difference it's probably the same as in PHP and many other languages. Allow me to explain. In the case of the left-side expression is evaluated for true and if not true, the right side expression is not evaluated. In the case of both sides are evaluated regardless of the value of either side. Here's an example: $a = 1; $b = 2; $result = ($a == 2) $b++; // In this case, $a does not equal two and therefor the left-side is false, thus the right-side is never evaluated. $b remains 2. $result = ($a == 2) $b++; // In this case, $a still does not equal two and therefor the left-side is still false, however because we use the '' operator the right side is evaluated anyway and $b now equals 3. echo $result; // In both cases, $result is false. HTH, Kyle Oops, my first paragraph is backwards. I'm sure that was confusing! My example is the correct logic for and , and the paragraph before that is exactly wrong. -- *Kyle Smith* UNIX/Linux Systems Administrator Inforonics, LLC
Re: [PHP] and and weird results
Kyle Smith wrote: Kyle Smith wrote: PJ wrote: I hope somebody can make sense of this. First, I don't understand what the difference is supposed to be between and - is there a difference for php and/or mysql? Second, I am trying to select all occurrences in a table.column where the first letter of the name is I, J, or K but not I or J or K; I want the all. Now, entering the following is no problem for any one letter: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' )); But the following does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')); Yet this gives off the wall results where the last names are nowhere near what they should be. Anyone have a rational explanation? I wasn't aware that was an option in MySQL, I've always used AND. If there is a difference it's probably the same as in PHP and many other languages. Allow me to explain. In the case of the left-side expression is evaluated for true and if not true, the right side expression is not evaluated. In the case of both sides are evaluated regardless of the value of either side. Here's an example: $a = 1; $b = 2; $result = ($a == 2) $b++; // In this case, $a does not equal two and therefor the left-side is false, thus the right-side is never evaluated. $b remains 2. $result = ($a == 2) $b++; // In this case, $a still does not equal two and therefor the left-side is still false, however because we use the '' operator the right side is evaluated anyway and $b now equals 3. echo $result; // In both cases, $result is false. HTH, Kyle Oops, my first paragraph is backwards. I'm sure that was confusing! My example is the correct logic for and , and the paragraph before that is exactly wrong. Not really clear... I thought that $b+ would == 3 ; why two + ? But this does not explain the weir results I am getting; and BTW, if I use AND does not work either. What am I supposed to use to get the results I am looking for? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] and and weird results NOT solved but FIXED ....
PJ wrote: Kyle Smith wrote: Kyle Smith wrote: PJ wrote: I hope somebody can make sense of this. First, I don't understand what the difference is supposed to be between and - is there a difference for php and/or mysql? Second, I am trying to select all occurrences in a table.column where the first letter of the name is I, J, or K but not I or J or K; I want the all. Now, entering the following is no problem for any one letter: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' )); But the following does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')); Yet this gives off the wall results where the last names are nowhere near what they should be. Anyone have a rational explanation? I wasn't aware that was an option in MySQL, I've always used AND. If there is a difference it's probably the same as in PHP and many other languages. Allow me to explain. In the case of the left-side expression is evaluated for true and if not true, the right side expression is not evaluated. In the case of both sides are evaluated regardless of the value of either side. Here's an example: $a = 1; $b = 2; $result = ($a == 2) $b++; // In this case, $a does not equal two and therefor the left-side is false, thus the right-side is never evaluated. $b remains 2. $result = ($a == 2) $b++; // In this case, $a still does not equal two and therefor the left-side is still false, however because we use the '' operator the right side is evaluated anyway and $b now equals 3. echo $result; // In both cases, $result is false. HTH, Kyle Oops, my first paragraph is backwards. I'm sure that was confusing! My example is the correct logic for and , and the paragraph before that is exactly wrong. Not really clear... I thought that $b+ would == 3 ; why two + ? But this does not explain the weir results I am getting; and BTW, if I use AND does not work either. What am I supposed to use to get the results I am looking for? I did fix it - it seems that OR does the trick. But I sure would like to understand why does not work? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] and and weird results
PJ, On Wed, Apr 1, 2009 at 1:40 PM, PJ af.gour...@videotron.ca wrote: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')); Say $Auth = I, $Auth1 = J, $Auth2 = K. The above sub-query is stating select author ids where the first digit in last_name is I AND the first digit in last_name is J AND the first digit in last_name is K. For any given last_name (thus, author id), the first digit can only be one of those options, thus this sub-query will return ZERO results. To get a sub-query that will only return results that start with I, J, or K,you can instead use the following sub-query: SELECT author.id FROM author WHERE LEFT(last_name, 1) = '$Auth' OR LEFT(last_name, 1) = '$Auth1' OR LEFT(last_name, 1) = '$Auth2' The above will return all author IDs where the surname starts with I, J, or K. - Lex
Re: [PHP] and and weird results
Lex Braun wrote: PJ, On Wed, Apr 1, 2009 at 1:40 PM, PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca wrote: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id http://author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')); Say $Auth = I, $Auth1 = J, $Auth2 = K. The above sub-query is stating select author ids where the first digit in last_name is I AND the first digit in last_name is J AND the first digit in last_name is K. For any given last_name (thus, author id), the first digit can only be one of those options, thus this sub-query will return ZERO results. To get a sub-query that will only return results that start with I, J, or K, you can instead use the following sub-query: SELECT author.id http://author.id FROM author WHERE LEFT(last_name, 1) = '$Auth' OR LEFT(last_name, 1) = '$Auth1' OR LEFT(last_name, 1) = '$Auth2' The above will return all author IDs where the surname starts with I, J, or K. - Lex Thanks for that. I understand. Somehow, I had tried OR at one point but I got one of those orange boxes probably for some other error and in the confusion of my neurons I overlooked it. However, I am puzzled that using (not ) did give a lot of results, almost the whole db which is thankfully not very big for testing purposes. -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] and and weird results
Thanks for that. I understand. Somehow, I had tried OR at one point but I got one of those orange boxes probably for some other error and in the confusion of my neurons I overlooked it. However, I am puzzled that using (not ) did give a lot of results, almost the whole db which is thankfully not very big for testing purposes. means and. is a bitwise operation. These pages explain it much better than I can. http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html http://au.php.net/manual/en/language.operators.bitwise.php -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
