How to split and sum comma separated multiple line values in php

Question

I have one issue. How can i split and sum up multiple comma separated line values in php. Following my mysql table screenshot

here my date wise miles filter page

I want to output miles colums like following when users enter from and to date.

IA      59.62
MD       7.88    
IL     359.22

Total Miles : 426.72

How can i achieve this in php? Please help me.

my php code:

<?php
// Range.php
if(isset($_POST["From"], $_POST["to"]))
{
    $conn = mysqli_connect("localhost", "root", "", "truckinvo");

    $result = '';
    $query = "SELECT * FROM tble_states_miles WHERE order_date BETWEEN '".$_POST["From"]."' AND '".$_POST["to"]."'";
    $sql = mysqli_query($conn, $query);
    ?>
    <table class="table table-bordered">
    <tr>
Total Miles from the date between <?php echo $_POST["From"]; ?> to <?php echo $_POST["to"]; ?>  
<th width="40%">Miles</th>
    </tr>
    <?php
    if(mysqli_num_rows($sql) > 0)
    {
        while($row = mysqli_fetch_array($sql))
        {
            ?>
            <tr>


            <td>
            <?php 

            echo nl2br($row["miles"]); ?></td>
            </tr>
            <?php
        }
    }
    else
    {
    ?>

        <tr>
        <td colspan="5">No Data Found</td>
        </tr>
        <?php
    }
    ?>
    </table>
    <?php   echo $result; }
?>

I tried to use following code to split

<?php
    $f1 = preg_replace('/,\\s*/', "', '", $row["miles"]);
$f2 = preg_replace('/\\n/', "'), \n('", $f1);
?>
    <?php echo $f2;  ?>

Simply add the column names in the insert query, and put the values in each row of of the values (`$final`) — Sloan Thrasher, Jan 28 '20 at 08:18
Note that it's not just the MySQL vs PDO that makes your code vulnerable to SQL Injection, but not processing each row and processing them all at once. — Sloan Thrasher, Jan 28 '20 at 08:21
OOF dude :/ Security issues aside your doing your team a great disfavor leaving the code like this. Please read into seperation of concerns and restructure your code. This is just a spaghetti of css, html and php firmware. — cptnk, Feb 14 '20 at 11:11
@cptnk what wrong in my question..i am new in php. Kindly please how can i achieve — Ramchendan karthick, Feb 14 '20 at 11:16

Mike Doe · Accepted Answer · 2020-02-14T12:30:58.733

2

Break per line, break each row to parts, display the name and sum up the values. Try:

Before the loop:

<?php $totalMiles = 0 ?>

Instead of:

<td>
<?php 
  echo nl2br($row["miles"]); ?>
</td>

In the loop:

<td>
<?php
    // iterate per state
    foreach (explode("\n", $row['miles']) as $stateRow) {
        // prepare the parts, we don't want any whitespace
        $stateParts = array_filter(array_map('trim', explode(',', $stateRow)));

        // the state's name (the first part)
        $stateName = $stateParts[0];

        // sum everything in the array
        // this works, because the name will get cast to "0"
        // try it: echo (int) 'IL';
        $stateMiles = array_sum($stateParts);

        // total mileage sum
        $totalMiles += $stateMiles;

        // display the result, any way you want
        printf('%s miles %.2f', $stateName, $stateMiles);
    }
?>
</td>

After the loop:

Total miles <?= $totalMiles ?>

And please, use prepared statements, your application is unsecure.

edited Feb 14 '20 at 12:30

answered Feb 14 '20 at 11:17

Mike Doe

16,349
11
65
88

thank you for your quick reply..yes sure i ll learn the pdo php prepared statement code and ill change my application securely. Stack overflow build me good platform – Ramchendan karthick Feb 14 '20 at 11:26
People usually leave the code "as is" as soon as it works properly. Never do that, your applications should be secure from the start, get that paranoic attitude – it's a good way of writing software for any programmer. Good luck. – Mike Doe Feb 14 '20 at 11:28
one more thing as per your code advise how i can fetch repeated states one time and sum their miles values (for eg: IA,59.62 IL,170.99 MD,7.88 IL,188.23 Result i want: IA 59.62 IL 359.22 (two times) MD 7.88 can you please help me – Ramchendan karthick Feb 15 '20 at 09:22
Your database schema is invalid. You should keep trips in a separate table. Instead of a single totalMiles variable you should create an array and keep separate counter for each state. This isn’t hard. – Mike Doe Feb 15 '20 at 09:45

How to split and sum comma separated multiple line values in php

1 Answers1