split and dived data from database PHP

Question

I am trying to convert this data I pull from the database "[29,0,246]". That is the data it pull from one of the coluums of a user. It is like how long they have been on for in minutes and the 3 data values measure for different parts. I was wondering if you were able to split them up into 3 different things and turn them into hour format. Mysqli and php

    if( isset( $_POST['submit-search'] ) ) {
        $search = mysqli_real_escape_string($conn, $_POST['search']);
        $sql = "SELECT * FROM players WHERE name LIKE '%$search%'";
        $result = mysqli_query($conn, $sql);
        $queryResults = mysqli_num_rows($result);

        if( $queryResults > 0 ) {
            while ($row = mysqli_fetch_assoc($result)) {
                echo "<div class='article'>
                <h1>".$row['name']."</h1>
                <h2>". "Last connected at " .$row['last_seen']."<h2>
                <h2>". "Cash:  " .$row['cash']."<h2>
                <h2>". "Bank Account:  " .$row['bankacc']."<h2>
                <h2>". "Time played: " .$row['playtime']."<h2>
                </div>";
            }
        } else {
            echo "There are no results matching your search";
        }
    }

Did you think of looking in the manual? [`explode()`](http://php.net/manual/en/function.explode.php) — RiggsFolly, Jul 17 '18 at 12:22
You should normalize the DB. Dont just fix it for this process, you will run into this problem again. — user3783243, Jul 17 '18 at 12:23
How much [research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) SO should be your last port of call not your first — RiggsFolly, Jul 17 '18 at 12:23
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Jul 17 '18 at 12:25
`[29,0,246]` looks to be valid JSON array @RiggsFolly `explode()` might not be the best function to handle this data `json_decode()` is the best function to handle this.. To bad the lower MySQL versions has a very inmature JSON handling MySQL 8.0 has a nice JSON_TABLE statement which in some way works as PHP's json_decode() function. — Raymond Nijland, Jul 17 '18 at 12:35
@RaymondNijland Absolutely right, I missed that. @KiKO answer looks good to me but does not seem to have received any upvotes. Wonder Why? However explode would also get the OP there, while JSON mighr confuse. But I agree using `json_decode()` looks like it would work to me. Assuming all the data in this column looks like this — RiggsFolly, Jul 17 '18 at 12:37

score 2 · Answer 1 · answered Jul 17 '18 at 12:24

2

Here is the string you wrote in whole hours:

echo round(array_sum(json_decode("[29,0,246]"))/60,2).' hours';

It looks like a json array, hence json_decode(), then we sum the resulting array, divide by 60 minutes and round.

The result is: 4.58 hours.

The assumption is that the string always, at least, contains [].

answered Jul 17 '18 at 12:24

KIKO Software

15,283
3
18
33

"The assumption is that the string always, at least, contains []." Technically the assumption here is that the data is a valid JSON array containing only numbers.. Still +1 for the answer.. – Raymond Nijland Jul 17 '18 at 12:48
@RaymondNijland Well, the question says that there will be numbers, just not how many. The requirement to have `[]` around it makes it a valid json array. – KIKO Software Jul 17 '18 at 12:50
Yes basically we are telling the same things about the question but only different versions – Raymond Nijland Jul 17 '18 at 12:54

split and dived data from database PHP

1 Answers1