Compare a given value with multiple values field

--0-1576415751-1158426464=:31391
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi My Best List, I have a field in a table contains "3,31,11,10" (let say tablefield1) and I have a given value which I need to check if it exists in that list.

I've tried:
SELECT * FROM table WHERE 3 IN ( tablefield1 );

Any ideas?


---------------------------------
Get your email and more, right on the new Yahoo.com
--0-1576415751-1158426464=:31391--

Re: Compare a given value with multiple values field

------=_Part_58826_31990360.1158433361766
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I'm not sure I understand your question, but my interpretation would be
solved like this

select * from table where variable in (select id from tablefield1)

I hope this helps you!!

Natalie

On 9/16/06, Hassan wrote:
>
> Hi My Best List, I have a field in a table contains "3,31,11,10" (let say
> tablefield1) and I have a given value which I need to check if it exists in
> that list.
>
> I've tried:
> SELECT * FROM table WHERE 3 IN ( tablefield1 );
>
> Any ideas?
>
>
> ---------------------------------
> Get your email and more, right on the new Yahoo.com
>

------=_Part_58826_31990360.1158433361766--

Re: Compare a given value with multiple values field

If you have the literal string "3,31,11,10" in a field in your table
then you can 'search' for numbers in the string like this:

SELECT * FROM table WHERE CONCAT(',', tablefield1, ',') LIKE '%,3,%'

I don't know if it's the best way of doing it but it works for me.

Natalie Leotta wrote:
> I'm not sure I understand your question, but my interpretation would be
> solved like this
>
> select * from table where variable in (select id from tablefield1)
>
> I hope this helps you!!
>
> Natalie
>
> On 9/16/06, Hassan wrote:
>>
>> Hi My Best List, I have a field in a table contains "3,31,11,10" (let say
>> tablefield1) and I have a given value which I need to check if it
>> exists in
>> that list.
>>
>> I've tried:
>> SELECT * FROM table WHERE 3 IN ( tablefield1 );
>>
>> Any ideas?
>>
>>
>> ---------------------------------
>> Get your email and more, right on the new Yahoo.com
>>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Compare a given value with multiple values field

--0-1405215031-1158438395=:53564
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I guess this is this closest, but apparently in this case if the "3" at the beginning doesn't exact, and even using LIKE '%3,%' (removing the first comma) would match an entry like 33, !

I need a better solution, does mysql have something similar to explode() in PHP?
if it does, then how to apply it in my situation?
of course it done using PHP but that's not feasible at all.

Tom Atkinson wrote:
If you have the literal string "3,31,11,10" in a field in your table
then you can 'search' for numbers in the string like this:

SELECT * FROM table WHERE CONCAT(',', tablefield1, ',') LIKE '%,3,%'

I don't know if it's the best way of doing it but it works for me.

Natalie Leotta wrote:
> I'm not sure I understand your question, but my interpretation would be
> solved like this
>
> select * from table where variable in (select id from tablefield1)
>
> I hope this helps you!!
>
> Natalie
>
> On 9/16/06, Hassan wrote:
>>
>> Hi My Best List, I have a field in a table contains "3,31,11,10" (let say
>> tablefield1) and I have a given value which I need to check if it
>> exists in
>> that list.
>>
>> I've tried:
>> SELECT * FROM table WHERE 3 IN ( tablefield1 );
>>
>> Any ideas?
>>
>>
>> ---------------------------------
>> Get your email and more, right on the new Yahoo.com
>>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




---------------------------------
Stay in the know. Pulse on the new Yahoo.com. Check it out.
--0-1405215031-1158438395=:53564--

Re: Compare a given value with multiple values field

Hassan wrote:
> I guess this is this closest, but apparently in this case if the "3" at the beginning doesn't exact, and even using LIKE '%3,%' (removing the first comma) would match an entry like 33, !
>
> I need a better solution, does mysql have something similar to explode() in PHP?
> if it does, then how to apply it in my situation?
> of course it done using PHP but that's not feasible at all.


why would you have multiple values held within one field? Why can't you have a
separate entry for each value?

see http://en.wikipedia.org/wiki/Database_normalization

Kae

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php