What is the most efficient way I can get table_name from the following string. All I could think of was substr() but the length of table_name might not always bee the same
SELECT * FROM `table_name` WHERE `id` = '1'
-
Did you think of regex?Reeno– Reeno2017-07-31 21:11:18 +00:00Commented Jul 31, 2017 at 21:11
-
1Unless you only want to support very simple SQL strings, this looks more complex than the answers you're getting. There can certainly be much more between the FROM and the WHERE than one table name.Don't Panic– Don't Panic2017-07-31 21:19:34 +00:00Commented Jul 31, 2017 at 21:19
-
1an unusual need, perhaps there are better optionsuser8011997– user80119972017-07-31 21:20:30 +00:00Commented Jul 31, 2017 at 21:20
-
@Don'tPanic simple is my middle nameAlex– Alex2017-07-31 21:26:02 +00:00Commented Jul 31, 2017 at 21:26
Add a comment
|
4 Answers
I think this is what you want:
<?php
$query = "SELECT * FROM `table_name` WHERE `id` = '1'";
$pattern = "/SELECT \* FROM `(.*?)`/";
preg_match($pattern, $query, $matches);
print_r($matches);
?>
Then select the correct array element from the $matches array to get the table name. In this case that would be $matches[1]
Comments
Try this code, please.
<?php
$query = "SELECT * FROM `table_name` WHERE `id` = '1'";
$pattern = "/SELECT \* FROM `(.*?)`/";
preg_match($pattern, $query, $matches);
echo $matches[1];
?>
Comments
You can do it like below using preg_match()-
<?php
$string = "SELECT * FROM `table_name` WHERE `id` = '1'";
preg_match("/FROM (.*?) WHERE/",$string,$matches);
print_r($matches); // now you can do echo $matches[1];
Output:-https://eval.in/839906 OR https://eval.in/839907
answered Jul 31, 2017 at 21:17
Death-is-the-real-truth
72.3k10 gold badges59 silver badges105 bronze badges
Comments
If you want the most efficient, then do not use regex -- it is much slower than non-regex methods. My three methods that follow will all outperform the other answers on this page. The other patterns are not optimized for speed (they use capture groups and don't use negated character classes).
I have ordered my three following methods from fastest to slowest. This is a demo of all three methods.
This is the input for all methods:
$sql="SELECT * FROM `table_name` WHERE `id` = '1'";
All methods will output table_name without backticks.
Method #1 - explode() is fastest, but will only work if there are no backticks before the table name's leading backtick.
// explode(): *only works if no backticks before FROM clause*
echo explode('`',$sql,3)[1]; // limit elements to maximum of 3, we only want the 2nd
Method #2 - strpos() and substr() will be 2nd fastest, and provides 100% reliability because it is locating the backtick that follows FROM (assuming you don't have some zany column name that ends with FROM then a space & you wrapped it in backticks... I mean, you could break it if you tried hard enough.
// pure string functions:
$tick1=strpos($sql,'FROM `')+6;
$tick2=strpos($sql,'`',$tick1);
echo substr($sql,$tick1,$tick2-$tick1);
Method #3 - preg_match() is the slowest of my three methods, but will still be more efficient than all of the other answers. Pattern Demo (9 steps) FYI: Kamrans' = 40 steps, Alive's = 39 steps, Cagy's = 40.
Why is mine so much faster?
I am not using parentheses to capture the match, I restart the fullstring match by using
\K.I am also using a negated character class
[^]+` to match one or more non-backtick characters that follow the first backtick.
You cannot make preg_match() faster than this. As a bonus of using \K the output array is 50% smaller as well.
// Will work regardless of backticks in SELECT clause:
echo preg_match('/FROM `\K[^`]+/',$sql,$out)?$out[0]:'failed';
2 Comments
mickmackusa
@Alex Unfortunately you asked your question and awarded your green tick while I was away from my computer. Hopefully it is not too late for you to be impressed with three new, educational methods that will outperform the earlier submissions. If you want efficiency, I've got you covered.
Alex
Indeed, in my tests your methods all performed slightly faster than the rest. As my criteria was 'efficiency' you deserved the check. Although I do hate being an Indian-giver, I'll have to be less hasty next time when awarding the check.