0

I have a string in PHP like:

"1234,2345,4567,5676,234,12,78957".....

I want to extract these numbers in varchar(30) format and use them on a command like

SELECT * FROM TABLE_NUM WHERE ID LIKE '%NUM';

where NUM will have above mentioned 7 strings.

And if possible i would also like to restrict '%' in '%NUM' to 1-5 characters only i.e. the prefix should not be greater than 5 characters. Example NUM = 1234 and ID has (31234,5678956781234) it should only provide first one as result and not the other one.

Accordingly I will get a merged result of all matching rows.

How can I achieve this ?

Thank You!

Gleon Apps
  • 61
  • 1
  • 9

1 Answers1

2

If that string is coming from a column somewhere in the database, you should fix the schema. It's almost always a bad idea to design a schema where you have to process sub-columnar data.

If it's a string from outside the database and you just want to run queries based on the individual parts, you're probably better off using facilities outside of your DBMS to construct the queries.

For example, using bash under Linux:

pax> list="1234,2345,4567,5676,234,12,78957"
pax> for i in $(echo $list | sed 's/,/ /g'); do
...>     echo mysql "\"SELECT * FROM TABLE_NUM WHERE ID LIKE '%$i'\""
...> done
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%1234'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%2345'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%4567'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%5676'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%234'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%12'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%78957'"

That script will echo the commands to do what you want (assuming mysql is the correct CLI interface to your DBMS) - simply remove the echo at the start to actually execute the commands.

For PHP (as per your question edit), you can use the explode function to split the string, something like:

$list = "1234,2345,4567,5676,234,12,78957";
$numbers = explode (",", $list);

then execute a query for each element of $numbers.

If what you're after is a single result set formed from all of those values, there are other ways to do it. One involves using the list to construct an "uber-query" string that will do all the work for you, then you execute it once.

Simply use an or clause to join the different "sub" queries into one (pseudo-code):

$query = "select * from table_num"
$joiner = " where"
for each $element in $list:
    $query = $query + $joiner + "id like '%" + $element + "'"
    $joiner = " or"
execute_sql $query

That ends up giving you the query string:

SELECT * FROM TABLE_NUM
    WHERE ID LIKE '%1234'
    OR    ID LIKE '%2345'
    :
    OR    ID LIKE '%78957'
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • i want a merged result of all these not singled out. – Gleon Apps Sep 02 '14 at 06:25
  • As the php query result will be further processed. I guess a proper SQL stored procedure will be more useful. – Gleon Apps Sep 02 '14 at 06:31
  • Okay, @Gleon, I've added a method for constructing a _single_ query which will extract all desired rows. My PHP knowledge is not up to the level where I'm confident providing bug-free code so I've just given pseudo-code. – paxdiablo Sep 02 '14 at 06:34
  • would this slow down as I will have over 100 IDs in one String. I doing something like this in SQL will be faster. and can you also check out my edit in question regarding restriction to '%'. – Gleon Apps Sep 02 '14 at 06:36
  • @Gleon, it's almost certainly going to be slow anyway since you use `%xxx` as the argument. Some DBMS' can alleviate this by using reversed indexes. Worry about functionality first, _then_ question performance. You can't get any less optimal than "wrong" :-) – paxdiablo Sep 02 '14 at 06:37
  • so what will be better php function OR SQL procedure? – Gleon Apps Sep 02 '14 at 06:40
  • *"You can't get any less optimal than "wrong""* ~ that's a viable office motivational poster right there – Phil Sep 02 '14 at 06:51