PHP - Getting Rid Of Null Values

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

Hi,

My company has 240+ locations and as such some users (general managers) cover multiple sites.  When I run a query to pull user information, when the user has multiple sites to his or her name, its adds the second / third sites to the next columns, rather than wrapping it inside the same table cell.  It also works the opposite way, if a piece of data is missing in the database and is blank, its pull the following columns in.  Both cases mess up the table and formatting.  I'm extremely new to any kind of programming and maybe this isn't the forum for this question but figured I'd give it a chance since I'm stuck.

The HTML/PHP code is below:

<table id="datatables-column-search-select-inputs" class="table table-striped" style="width:100%">

                                        <thead>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                                <th>Actions</th>

                                            </tr>

                                        </thead>

                                        <tbody>

                                            <?php

                                            //QUERY TO SELECT ALL USERS FROM DATABASE

                                            $query = "SELECT * FROM users";

                                            $select_users = mysqli_query($connection,$query);

                                            // SET VARIABLE TO ARRAY FROM QUERY

                                            while($row = mysqli_fetch_assoc($select_users)) {

                                                $user_id = $row['user_id'];

                                                $user_firstname = $row['user_firstname'];

                                                $user_lastname = $row['user_lastname'];

                                                $username = $row['username'];

                                                $user_phone = $row['user_phone'];

                                                $user_image = $row['user_image'];

                                                $user_title_id = $row['user_title_id'];

                                                $user_role_id = $row['user_role_id'];

                                                // POPULATES DATA INTO THE TABLE

                                                echo "<tr>";

                                                echo "<td>{$user_id}</td>";

                                                echo "<td>{$user_firstname}</td>";

                                                echo "<td>{$user_lastname}</td>";

                                                echo "<td>{$username}</td>";

                                                echo "<td>{$user_phone}</td>";

                                                //PULL SITE STATUS BASED ON SITE STATUS ID

                                                $query = "SELECT * FROM sites WHERE site_manager_id = {$user_id} ";

                                                $select_site = mysqli_query($connection, $query);

                                                while($row = mysqli_fetch_assoc($select_site)) {

                                                    $site_name = $row['site_name'];

                                                    echo "<td>{$site_name}</td>";

                                                }

                                                echo "<td>{$user_title_id}</td>";

                                                echo "<td>{$user_role_id}</td>";

                                                echo "<td class='table-action'>

                                                    <a href='#'><i class='align-middle' data-feather='edit-2'></i></a>

                                                    <a href='#'><i class='align-middle' data-feather='trash'></i></a>

                                                    </td>";

                                                //echo "<td><a href='users.php?source=edit_user&p_id={$user_id}'>Edit</a></td>";

                                                echo "</tr>";

                                            }   

                                            ?>

                                            <tr>

                                                <td>ID</td>

                                                <td>FirstName</td>

                                                <td>LastName</td>

                                                <td>Username</td>

                                                <td>Phone #</td>

                                                <td>Location</td>

                                                <td>Title</td>

                                                <td>Role</td>

                                                <td class="table-action">

                                                <a href="#"><i class="align-middle" data-feather="edit-2"></i></a>

                                                <a href="#"><i class="align-middle" data-feather="trash"></i></a>

                                                </td>

                                            </tr>

                                        </tbody>

                                        <tfoot>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                            </tr>

                                        </tfoot>

                                    </table>

$sql = "SELECT SUM(IF(`submitdate` IS NULL , 1 , 0 )) as 'Survey Started But Not Completed' FROM `survey_$surveyid`";
$statement = $dbh->prepare($sql);
$statement->execute();
$result = $statement->fetch(PDO::FETCH_OBJ); //pass that data to an object

stdClass Object
(
    [Survey Started But Not Completed] => 
)

$result  = 0;

$result = array(['Survey Started But Not Completed'] => "0");

2 PART QUESTION (so I don't have to ask two questions 😀  )

1.)
My script has tons of PHP variables. Is it better to declare them all at the top of my page like this:
$cat = $words = $day = $lunch = $user = $address = $potato = $_SESSION["goodpeople"] = $dumbquestionList = $b = $hundredMoreVariables = "";
-or-
as needed, like this:
$cat = "";  if($day == 'Thursday') { $cat = 'time_to_feed'; }
$words = "";  $school = array('teachers'=>'boring','lunch'=>'free'); $words = $school['teachers'];
$_SESSION["goodpeople"] = ""; if($_SESSION["goodpeople"] == "" { $_SESSION["goodpeople"] = 'Gandhi'; }

2.)
What are the pros and cons of declaring variables as NULL and using " isset " versus declaring variables as "" and using ==
Example:
$cat = $words = $day = $lunch = $user = $address = NULL;
if( !isset($cat)) { $cat = 'black'; }.........................if(isset($day)) { $sleep = 'none'; }..............etc., etc.
-or-
$cat = $words = $day = $lunch = $user = $address = "";
if($cat == "") { $cat = 'black'; }............................ if($day != "") { $sleep = 'none'; }................etc., etc.

On my particular coding, I can use either the NULL/isset style, or, the ""/== style, with no errors or ill effects.... but something tells me the expert PHP coders prefer one over the other...

Thank you!!

SELECT IFNULL(trans, 0) AS 'Transactions', customer.cID AS 'Customer ID', customer.surname AS 'Surname'
FROM customer
LEFT JOIN account
ON (account.cID = customer.cID)
WHERE IFNULL(trans, 0);