Jump to content

how to insert two input values and multi data textarea values in one query?


karthicbabu

Recommended Posts

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 by karthicbabu
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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

 

click_mileagebutton.jpg

Link to comment
Share on other sites

 

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..

 

separated_columns.jpg

click_mileagebutton.jpg

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.