PHP - Textarea Code Adds Unwanted Extra Data To The End

Hello everyone!

I've been on a problem a few hours now and I've managed to finally find the solution however I feel it's very inefficient and am looking for a way to do this in a better way.

I have a textarea where each line is inserted into MySQL database table "lines" and I have the columns "line_number" and "row".

The textarea initial value on page load is simply a list of all lines ordered by line_number in ASC order.

If I modify/add/remove entries from the textarea it will remove data changed and add new data accordingly.

But I am making a lot of queries to do this and I'm not sure if there is a more efficient way than querying each line in the database to find the new value/modified value.

Here's my php script, hopefully this makes sense:

if (isset($_POST['lines'])) {
    
    //get lines posted from textarea
    $lines_posted = $_POST['lines'];
    
    //trim all white spaces and remove empty lines as they are not valid for mysql insert
    $lines = trim($lines_posted);
    $lines = explode("\n", str_replace("\r", "", trim($lines_posted)));
    $lines = array_map('trim', $lines_posted);
    $lines = array_filter($lines_posted);
    
    $line_count = count($lines);
    //output how many valid lines were posted
    echo "Posted Lines: $line_count<br>";
    
    //loop through all lines posted, query all rows in database and insert if lines row value does not exist
    for ($i = 0; $i < $line_count; $i++) {
        if (isset($lines[$i])) {
            $find_line_in_db = $db->query("SELECT row FROM lines WHERE row=?", $lines[$i]);
            if ($find_line_in_db->numRows() == 0) {
                $db->query("INSERT INTO lines (line_number, row) VALUES (?,?)", $i, $lines[$i]);
                echo 'Added the line: ', htmlspecialchars($lines[$i]), '<br>';
            }
        }
    }
    
	//query all rows in the database and check if posted lines array value is in the [row] column, delete rows changed/not found accordingly
    $rows             = "";
    $fetch_all_lines  = $db->query("SELECT row FROM lines");
    while ($all_lines = $fetch_all_lines->fetchRow()) {
        if (!in_array($all_lines['row'], $lines)) {
            $rows = $all_lines['row'];
            $db->query("DELETE FROM lines WHERE row=?", $all_lines['row']);
            echo 'Deleted the line: ', htmlspecialchars($lines[$i]), '<br>';
        }
    }
}

So to visualize what I'm doing imagine two arrays:

$lines = ["line1", "line2", "line3changed"] //textarea lines posted
$db_lines = ["line1", "line2", "line3"] 
//$db_lines = the current rows in lines db table
// line_number | row
// 0           | line1
// 1           | line2
// 2           | line3

//outputs 
//Added the line: line3changed
//Deleted the line: line3

So basically what I'm asking is, am I doing this the right way?

I would have over 10,000 lines that would be altered every hour in this table and am unsure if I'm doing it the correct way

Dear All Members

here is my table data..

(4 Columns/1row in mysql table)

id                 order_no               order_date                  miles
310                001                   02-15-2020                MI,108.53
                                                                                     Oh,194.57
                                                                                     PA,182.22
                                                                                     WA,238.57

How to split(miles) single column into (state, miles) two columns and output like following 5 columns /4rows in mysql using php code.

(5 Columns in mysql table)
------------------------------------------------------------------------------------

id                  order_no              order_date              state      miles

310                001                   02-15-2020               MI          108.53

310                001                   02-15-2020              Oh          194.57

310                001                   02-15-2020              PA          182.22

310                001                   02-15-2020             WA          238.57
 

------------------my php code -----------

<?php
//invoice.php  
include('../includes/config.php');

if(isset($_POST["add"]))
{
$order_no=$_GET['order_no'];
$order_date=$_GET['order_date'];
$miles=$_POST['miles'];

$miles = explode("\r\n", $_POST["miles"]);
$newdata = "'" . implode("','", $_POST["miles"]) . "'";
$sql ="INSERT INTO tble_states_miles(order_no,order_date, miles) VALUES(:order_no, :order_date, :newdata)";

$query = $dbh->prepare($sql);
$query-> bindParam(':order_no', $order_no, PDO::PARAM_STR);
$query-> bindParam(':order_date', $order_date, PDO::PARAM_STR);
$query-> bindParam(':miles', $newdata, PDO::PARAM_STR);

$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
echo "<script type='text/javascript'>alert('Added Sucessfull!');</script>";
echo "<script type='text/javascript'> document.location = 'ifta_miles_calculationlist_added.php'; </script>";
}
else 
{
$error="Something went wrong. Please try again";
}

if($query->execute()) {

$sql = "update tis_invoice set flag='1' where order_no=:order_no";
$query = $dbh->prepare($sql);
$query-> bindParam(':order_no', $order_no, PDO::PARAM_STR);

$query->execute();

}
}
?>

----------------- my form code ------------------

 <?php
                   $order_no=$_GET['order_no'];
        $sql = "SELECT * from tis_invoice where order_no = :order_no";
        $query = $dbh -> prepare($sql);
        $query->execute(
  array(
   ':order_no'       =>  $_GET["order_no"]
  )
 );
        $result = $query->fetchAll();
        foreach($result as $row)
?>
    
                    <form method="post">
                        <table align="center" style="width: 55%">
                        <tr><td>Invoice Number</td></tr>
                        <tr><td><input type="text" name="order_no" value="<?php echo $row["order_no"]; ?>" readonly=""></td></tr>
                        <tr><td>Invoice Date</td></tr>
                        <tr><td><input type="text" name="order_no" value="<?php echo $row["order_date"]; ?>" readonly=""></td></tr>
                        <tr><td>
                               <textarea name="miles" class="form-control" rows="10" placeholder="" required></textarea>
                           </td></tr>
                           <tr><td>
                         
                            <input type="submit" name="add" class="btn btn-primary" value="Add" />
                       </td></tr></table>
                    </form>

-- Can any one help how to correct my code..present nothing inserted on table

Thank You