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

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;  ?>

Answer

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.

Advertisement

Answer