explode a string like in SQL by preg_match

Question

I want to explode a string like in SQL

this is my code:

<?php

$subject = 'select onlineid from onlinelist where online_user_name=6565 order by htrdh limit 1';

preg_match('/^(select|SELECT) (?P<select>(.+)) (from|FROM) (?P<from>(.+)) ((where|WHERE) (?P<where>(.+))) ((order by|ORDER BY) (?P<order>(.+)))? ((limit|LIMIT) (?P<limit>(.+)))?$/', $subject, $matches);

echo @$matches["select"]."<br>".@$matches["from"]."<br>".@$matches["where"]."<br>".@$matches["order"]."<br>".@$matches["limit"];

outPut will be :

onlineid
onlinelist
online_user_name=6565
htrdh
1

workes well... But if I remove order by or limit it does not work.

it must if where, order by or limit exists in $subject put value

Instead of having `select|SELECT` and all those things you could just use `strtolower` or instead use the `i` modifier as the query could also look like `Select` and on that case your regex wont work. — Gal Sisso, Feb 07 '15 at 11:19

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Your ? did not include the spaces.

Incorrect: ... (ORDER BY ...)? (LIMIT ...)?

Correct: ...( ORDER BY ...)?( LIMIT ...)?

https://www.regex101.com/r/mE8qK5/1

I also recommend that you add ? to the .+ subpatterns so that it won't match greedily (lazy)

What do lazy and greedy mean in the context of regular expressions?

edited May 23 '17 at 11:50

Community

1
1

answered Feb 07 '15 at 10:47

microtony

192
7

score 1 · Answer 2 · answered Feb 14 '16 at 10:38

Regex is tricky and you should always be careful of using it unless you know every aspect of it. I would also suggest using /i for case insensitive

https://www.regex101.com/r/hM9aC7/1

/^select\s+(?<columns>.*?)\s+from\s+(.*?)?((where\s+(?<where>.*?))?(order by\s+(?<order>.*?))?(limit\s+(?<limit>(.*?)))?);/i

explode a string like in SQL by preg_match

2 Answers2