Is there alternative for FIND_IN_SET for SQL

PHP
,
1

Hello, i m now working on converting PHP script from mysqli to SQL (for Azure) and came across a big issue “FIND_IN_SET” i did read allot and as much as i understood there are no alternative for SQL :frowning:

FIND_IN_SET(obj.[id],'".implode(',',$selection)."')<>0

What would be the best solution for me ?

Thanks!

2

SQL has in “IN” clause. Example:
…where ID IN(‘125643’,‘489932834’,‘94059238’)
If you are talking about SQL, then this is an alternative.

1 Like
3

Might be better posted in the database section, as it’s nothing to do with the PHP language.

I’ve only briefly read about the function, but it seems most useful when storing multiple values in a single column, something I’ve tried to avoid doing.

1 Like
4

@phpRob IN('125643','489932834','94059238') seemed to not work but if i have only one value inside it do work IN('125643')

Yes i m talking about SQL in PHP!

5

I m doing this conversion first time so i thought that is PHP also.

In this case i do not store them in single column but i m getting data from arrays and array_push

6

I don’t believe you are going to find any single function equivalent for that. Storing multiple vales in a single column should be avoided in a relational database. Normalizing a poorly built database can be very expensive and time consuming affaire. Depending on how many places that function is used a band aid type approach might be creating a table or view that replicates the data in s normalized fashion and altering the queries where find in is set is used to use that table or view instead. This might come with a performance cost.

7

@phpRob @droopsnoot I think i found the issue but have issue to solve it . …

So it pass in like this IN('24715-001,O0022-001,27516-001') but should be like this IN('24715-001','O0022-001','27516-001') what do i do wrong?

Here is my code obj.[id] IN('".implode(',',$selection)."')

Edit:
I came to this solution and it seems to work, but i want to know is this a good way to do it?

function format_array($array){
    return implode("','", $array);
}				
$arrrr = format_array($selection);

And in query:

obj.[id] IN('".$arrrr."')

8

IN() is ment for that i just have wrongly used it!

9

Sounds like you may be happy and functional (working) now. If you are still using “IN” with success and are still using this code (“implode” like above), be sure to not forget to add/concatenated quotes to the outside values. Implode will only put string ‘,’ in-between values but not on the outside. So be careful that first and last value in array also properly included in the IN() clause string you build.

1 Like
10

I was under the impression the column in db has multiple values but now I see I was wrong. So IN should work fine. There is a difference though because find in set is an exact string match and IN is conditional based on matching a single value in a set. It would probably be best to see a screen capture of the data you’re working with in the table.

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.