how to use php method like "explode" in sql

Question

I am trying to get some data, and in the table, there is a field named "sysload". However, it is a var(string) type. The data in it is like "0.0, 0.2, 0.5",three numbers split by comma. However, in the sql, I only need the last number(in this example:0.5) to compare in "where". So how can I use it ? My code:

$termquery=mysql_query("SELECT a.terminal FROM terminal_server_log a
inner join
        (
            SELECT  terminal, MAX(timestamp) timestamp
            FROM    terminal_server_log
            group by terminal
        ) b on (b.terminal=a.terminal and a.timestamp=b.timestamp)
WHERE explode(',', sysload)[2]>$number");

The last line is the most important one, i want to compare with '$number', but it seems i cannot use 'explode'. Thanks

[Little Bobby](http://bobby-tables.com/) says **[your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)**. Learn about [Prepared Statements](http://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string)** is not safe! — GrumpyCrouton, Aug 01 '17 at 18:40
**Please**, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://news.php.net/php.internals/53799), and `mysql_*` functions have been officially removed in PHP 7. Instead you should learn about [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement) and use either `PDO` or `mysqli_*`. If you can't decide, [this article will help to choose your best option](http://php.net/manual/en/mysqlinfo.api.choosing.php). — GrumpyCrouton, Aug 01 '17 at 18:40
I have to write in one SQL, so i use inner join, i do not know there will be some mistakes if I have two sql — CPP, Aug 01 '17 at 18:43
if i do not have two queries, i cannot get "sysload" data in table. I have to get "sysload" first and then compare — CPP, Aug 01 '17 at 18:45
That is right, you cannot use the PHP function `explode` in your SQL code. — Andy Lester, Aug 01 '17 at 18:47
Look here, will help you https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — David Constantine, Aug 01 '17 at 18:56

score 0 · Answer 1 · answered Aug 01 '17 at 18:47

PHP functions will not work inside SQL queries.

You can remove this filter from your query and filter at the php side, or you can make a custom explode function using some mysql string functions.

In this link has an example: (I didn't check if it's working) Equivalent of explode() to work with strings in MySQL

score 0 · Accepted Answer · answered Apr 12 '18 at 11:36

0

You can probably use regular expressions to match the last element of your sysload column: https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-instr

Look also at CAST() function, as the extracted substring should be converted to a numeric value to allow comparison with a number.

answered Apr 12 '18 at 11:36

OuzoPower

230
2
11

1

I have not tested the feasability of my answer but would watch in this direction. – OuzoPower Apr 12 '18 at 11:38

how to use php method like "explode" in sql

2 Answers2