karthicbabu Posted February 8, 2020 Share Posted February 8, 2020 (edited) 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 Edited February 8, 2020 by karthicbabu Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8, 2020 Share Posted February 8, 2020 (edited) You should be normalizing your data and put the miles in a separate table +---------------------+ | invoice | +---------------------+ | inv_id (PK) | | order_no (UQ) |-----+ | order_date | | +---------------------+ | +----------------------+ | | state_miles | | +----------------------+ | | state_miles_id (PK) | +-----<| order_no (FK) | | state | | miles | +----------------------+ Are you getting any pdo errors? - I don't see you checking for any (have you set the ERRMODE attribute when connecting?) [edit] PS Why are you getting the last insert id before you have executed the insert? Why do you need the last insert id? Why is there a subsequent update immediately after the insert? Edited February 8, 2020 by Barand Quote Link to comment Share on other sites More sharing options...
karthicbabu Posted February 8, 2020 Author Share Posted February 8, 2020 Dear @Barand here below i attached screenshot of form submission page for your reference. As per screenshot i want to insert datas like following (textarea comma seperated inserted values need to split two columns in mysql table PS Why are you getting the last insert id before you have executed the insert? ---i wrongly done it..please leave it -------------------------------- Why is there a subsequent update immediately after the insert? Because seperately i show inserted records in another page by flag update setting id order_no order_date state miles 310 IN/10-11/011 02-07-2020 OH 106.69 310 IN/10-11/011 02-07-2020 WV 153.25 310 IN/10-11/011 02-07-2020 VA 71.25 310 IN/10-11/011 02-07-2020 NC 107.04 310 IN/10-11/011 02-07-2020 SC 228.05 310 IN/10-11/011 02-07-2020 GA 112.76 310 IN/10-11/011 02-07-2020 FL 376.12 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8, 2020 Share Posted February 8, 2020 6 minutes ago, karthicbabu said: Because seperately i show inserted records in another page by flag update setting The presence of the state mileage data would be sufficient for most people to indicate when it's there. The mileage data itself is your flag. And the error checking...? Quote Link to comment Share on other sites More sharing options...
karthicbabu Posted February 8, 2020 Author Share Posted February 8, 2020 following code split comma separated values into columns <?php if(isset($_POST["add"])) { $order_no=$_POST['order_no']; $comma = preg_replace('/,\\s*/', "', '", $_POST["stateandmileslist"]); $row = preg_replace('/\\n/', "'), \n('", $comma); $query = "insert into tis_satesmiles (states, miles) values ('" . $row . "')"; $statement = $dbh->prepare($query); $statement->execute(); } ?> Now how can i insert order_no and order_date using above insert query or need to update it..any help thanks pls.. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.