PHP - Multi Insert

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

I'm missing something here. I have a form, and when the submit is pressed, the relevant post data inserts into table one, then I want the last insert id to insert along with other form data into a second table. The first table's still inserting fine, but I can't get that second one to do anything. It leapfrogs over the query and doesn't give an error. EDIT: I forgot to add an error: I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'usage, why VALUES ('14', '', '123', '','1234', '', '')' at line 1

query:INSERT INTO tbl_donar (donar_fname, donar_name, donar_address, donar_address2, donar_city, donar_state, donar_zip, donar_email, donar_phone, donar_fax, donar_company) VALUES ('test 14', 'asdfa', 'asdf', 'adf','asdf', '', '', '', '123', '', '')


Code: [Select]
if (empty($errors)) {

require_once ('dbconnectionfile.php');

$query = "INSERT INTO tbl_donar (donar_fname, donar_name, donar_address, donar_address2, donar_city, donar_state, donar_zip, donar_email, donar_phone, donar_fax, donar_company)
 VALUES ('$description12', '$sn', '$description4', '$cne','$description5', '$description6', '$description7', '$description8',
'$description9', '$description10', '$description11')";

$result = @mysql_query ($query);


if ($result) {

$who_donated=mysql_insert_id();

$query2 = "INSERT INTO tbl_donation (donor_id, donor_expyear, donor_cvv, donor_cardtype, donor_authorization, amount, usage, why)
 VALUES ('$who_donated', '$donate2', '$donate3', '$donate4','$donate5', '$donate6', '$donate7')";
$result2 = @mysql_query ($query2);

if ($result2) {echo "Info was added to both tables! yay!";}

echo "table one filled. Table two was not.";
echo $who_donated;

//header ("Location: http://www.twigzy.com/add_plant.php?var1=$plant_id");

exit();
} else {
echo 'system error. No donation added';

Can anyone tell me why this is not INSERTing? My array data is coming out just fine.. I've tried everything I can think of and cannot get anything to insert.. Ahhhh!

<?php
  $query = "SELECT RegionID, City FROM geo_cities WHERE RegionID='135'";
  $results = mysqli_query($cxn, $query);
  $row_cnt = mysqli_num_rows($results);
  echo $row_cnt . " Total Records in Query.<br /><br />";
  if (mysqli_num_rows($results)) {
      while ($row = mysqli_fetch_array($results)) {
          $insert_city_query = "INSERT INTO all_illinois SET state_id=$row[RegionID], city_name=$row[City] WHERE id = null" or mysqli_error();
          $insert = mysqli_query($cxn, $insert_city_query);
          if (!$insert) {
              echo "INSERT is NOT working!";
              exit();
          }
          
          echo $row['City'] . "<br />";
          
          echo "<pre>";
          echo print_r($row);
          echo "</pre>";
      }
      //while ($rows = mysqli_fetch_array($results))
      
      } //if (mysqli_num_rows($results))
      
      else {
          echo "No results to get!";
      }
?>

Here is my all_illinois INSERT table structu

CREATE TABLE IF NOT EXISTS `all_illinois` (
  `state_id` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is my source table geo_cities structu

CREATE TABLE IF NOT EXISTS `1` (
  `CityId` varchar(255) NOT NULL,
  `CountryID` varchar(255) NOT NULL,
  `RegionID` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `Latitude` varchar(255) NOT NULL,
  `Longitude` varchar(255) NOT NULL,
  `TimeZone` varchar(255) NOT NULL,
  `DmaId` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Hello, I'm having a bit of a problem here, all help to this issues would be much appreciated

I am trying to use text boxes to insert numbers into the database based on what is inputed.
If I have a string, like this for example:
$variable = 09385493;

And I want to insert it into the database like this:
mysql_query("INSERT INTO integers(number)
VALUES ('$variable')");

When checking the integers table in my database, looking at the number field, the $variable that was inserted is outputted as
9385493

Notice the number zero was taken out of the front of the number. If the number is double 0's (009385493), both of those zero's would disappear, too.

Thanks

Hello friends,

I've tried to search over the Internet but didn't found any tutorial how to do the following :-

let say we have form with input username and input email just 2 field

HTML Code

Code: [Select]
<form>
<table>
<tr>
<td>Username :</td>
<td><input id="username" size="20" type="text" name="username"></td>
</tr>
<tr>
<td>Email :</td>
<td><input id="email" size="20" type="text" name="email"></td>
</tr>
</table>
</form>

As you can see no submit button cause we will only check out if username and/or email is not stored already, by using ajax

My problem : i can only check out for username (1 field) but i can not check for 2 fields or more and i want to know how to apply it for 2 fields (username and email)

Here is my idea for only one field (username)

I'll add in the HTML code  this

Code: [Select]
<td><div id="status"></div></td>
and will add this java code

Code: [Select]
<SCRIPT type="text/javascript">
pic1 = new Image(16, 16);
pic1.src = "loader.gif";
$(document).ready(function(){
$("#username").change(function() {
var usr = $("#username").val();
if(usr.length >= 4)
{
$("#status").html('<img src="loader.gif" align="absmiddle">&nbsp;Checking availability...');
$.ajax({
type: "POST",
url: "check.php",
data: "username="+ usr,
success: function(msg){
$("#status").ajaxComplete(function(event, request, settings){
if(msg == 'OK')
{
$("#username").removeClass('object_error');
$("#username").addClass("object_ok");
$(this).html('&nbsp;<img src="tick.gif" align="absmiddle">');
}
else
{
$("#username").removeClass('object_ok');
$("#username").addClass("object_error");
$(this).html(msg);
}
});
}
});
}
else
{
$("#status").html('<font color="red">The username should have at least <strong>4</strong> characters.</font>');
$("#username").removeClass('object_ok');
$("#username").addClass("object_error");
}
});
});
</SCRIPT>

Explain : it will get the input of username and will send it to check.php

Now check.php code (should have all usernames that will compare with it)

Code: [Select]
<?php
if(isSet($_POST['username']))
{
$usernames = array('john','michael','terry', 'steve', 'donald');
$username = $_POST['username'];
if(in_array($username, $usernames))
{
echo '<font color="red">The username<STRONG>'.$username.'</STRONG> is already in use.</font>';
}
else
{
echo 'OK';
}
}
?>

Now it is very clear , if will automatic check the username

now my problem is how to apply it for also email

Hi Chaps,

I'm trying to put together a Nested Set Model (Hierarchical Data), but I have having problems when it comes to deleting/updating the 'nested' table.

I'm using this as a guide:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

But when trying to run a query, such as:

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;


I get a MySQL syntax error.

This code works fine in something such as Windows Command Prompt, but not in PHP.

Is there a special way to run multiple queries in 1 PHP script?

Many thanks

hello all, i have been sitting here googleing and googling but i can't seem to find what im looking for.

i am tring to create a personal site that lets me note services i do for my cars. I have multiple tables (cars, color, mfg, ...) in the main table cars i insted of putting the color black i put and int(11) of 1 which is suppose to "if statement" to the table colors and produce the correct color. same for mfg. although nothing i have tried has helped i always just have the else of the if echoed out.

i have heard of union and join left right i am so unsure of what is need any insite would be useful.

thanks in advance.

How i can delete record from table `order` and move it to `order2` the table `order2` structure is exactly like table `order`.here is the code that i made. 

<?
	include("connection.php");
	$id_product=$_GET["pid"];
	
	$sql_delete="DELETE FROM `order` WHERE id_product='$id_product'";
	
				
	$result=mysql_query($sql_delete) or die("Error in sql due to ".mysql_error());
	
	if ($result)
	header("Location: order_list.php");
	
?>

common.php
Code: [Select]

<?php
session_start();
header('Cache-control: private'); // IE 6 FIX

if(isSet($_GET['lang']))
{
$lang = $_GET['lang'];

// register the session and set the cookie
$_SESSION['lang'] = $lang;

setcookie("lang", $lang, time() + (3600 * 24 * 30));
}
else if(isSet($_SESSION['lang']))
{
$lang = $_SESSION['lang'];
}
else if(isSet($_COOKIE['lang']))
{
$lang = $_COOKIE['lang'];
}
else
{
$lang = 'en';
}

switch ($lang) {
  case 'en':
  $lang_file = 'lang.en.php';
  break;

  case 'de':
  $lang_file = 'lang.de.php';
  break;

  case 'el':
  $lang_file = 'lang.el.php';
  break;

  default:
  $lang_file = 'lang.en.php';

}

include_once 'languages/'.$lang_file;
?>
ip2locationlite.class.php
Code: [Select]
<?php
final class ip2location_lite{
protected $errors = array();
protected $service = 'api.ipinfodb.com';
protected $version = 'v3';
protected $apiKey = '';

public function __construct(){}

public function __destruct(){}

public function setKey($key){
if(!empty($key)) $this->apiKey = $key;
}

public function getError(){
return implode("\n", $this->errors);
}

public function getCountry($host){
return $this->getResult($host, 'ip-country');
}

public function getCity($host){
return $this->getResult($host, 'ip-city');
}

private function getResult($host, $name){
$ip = @gethostbyname($host);

if(preg_match('/^(?:25[0-5]|2[0-4]\d|1\d\d|[1-9]\d|\d)(?:[.](?:25[0-5]|2[0-4]\d|1\d\d|[1-9]\d|\d)){3}$/', $ip)){
$xml = @file_get_contents('http://' . $this->service . '/' . $this->version . '/' . $name . '/?key=' . $this->apiKey . '&ip=' . $ip . '&format=xml');

try{
$response = @new SimpleXMLElement($xml);

foreach($response as $field=>$value){
$result[(string)$field] = (string)$value;
}

return $result;
}
catch(Exception $e){
$this->errors[] = $e->getMessage();
return;
}
}

$this->errors[] = '"' . $host . '" is not a valid IP address or hostname.';
return;
}
}
?>



index.php

Code: [Select]
<?php
include_once('ip2locationlite.class.php');
 
//Set geolocation cookie
if(!$_COOKIE["geolocation"]){
  $ipLite = new ip2location_lite;
  $ipLite->setKey('*****************');
 
  $visitorGeolocation = $ipLite->getCountry($_SERVER['REMOTE_ADDR']);
  if ($visitorGeolocation['statusCode'] == 'OK') {
    $data = base64_encode(serialize($visitorGeolocation));
    setcookie("geolocation", $data, time()+3600*24*7); //set cookie for 1 week
  }
}else{
  $visitorGeolocation = unserialize(base64_decode($_COOKIE["geolocation"]));
}
var_dump($visitorGeolocation);
?>


im using this http://ipinfodb.com/

in index.php i got this message

array(5) { ["statusCode"]=> string(2) "OK" ["statusMessage"]=> string(0) "" ["ipAddress"]=> string(13) "94.68.211.211" ["countryCode"]=> string(2) "GR" ["countryName"]=> string(6) "GREECE" }

how to switch automatic if GR go to index.php?lang=el
if DE go to  index.php?lang=de etc...

hi , i want to make 5 forms with 1 submit button , how i do that?
thanks , Mor.

Hi guys
I got an issue that when a link on the site is loading, the other links does not load until the previous is complete but it will work if i try on 2 different browsers. How can i browse multiple links silmultanously?
Thanks

Code: [Select]
$i=1;
$selected=0;
$uservalue=20;
$numCols = 3;
$numPerCol = ceil(20 / $numCols);
echo "<table class=tuble><tr>";
for($col = 1; $col <= $numCols; $col++) {
   echo "<td>";
   for($row = 0; $row < $numPerCol; $row++) {
      $resultRow = 20;
       if ($i<=$uservalue) {
if ($i == $selected) {
   $checked = 'checked';
  }else{
  $checked = '';
  }
 $styles = array(
 
'Blue Essense' => array('#2B8EFF', '#63CBFF', '50 Forum Gold'),
'veggie' => array('carrot', 'collard', 'pea')
 
 
  );
 
 
 
    echo '<input type="radio" value="'.$i.'" name="form[color]">Blue Essence <b><a style="color:#2B8EFF;text-shadow:#63CBFF 2px 1px 1px" href="u.php?id='.$pun_user['id'].'">'.$pun_user['username'].'</a></b><br>';
  }
  else {
    echo '<label for="s'.$i.'"><input type="radio" value="'.$i.'" name="form[star]" disabled><b><a href="u.php?id='.$pun_user['id'].'">'.$pun_user['username'].'</a></b><br>';
  }
  $i++;
   }
   echo "</td>";
}
echo "</tr></table>";
Okay,  see the $styles array?

Code: [Select]
$styles = array(
 
'Blue Essense' => array('#2B8EFF', '#63CBFF', '50 Forum Gold'),
'veggie' => array('carrot', 'collard', 'pea')
 
 
  );
The first is hex code, background, and 2nd is #63CBFF (Front color) and the 3rd is how much it costs

I need those arrays to echo out correspondent with my

Code: [Select]
echo '<input type="radio" value="'.$i.'" name="form[color]">Blue Essense<b><a style="color:#2B8EFF;text-shadow:#63CBFF 2px 1px 1px" href="u.php?id='.$pun_user['id'].'">'.$pun_user['username'].'</a></b> (50 Forum Gold)<br>';
See how the style="color etc/etc" I need to grab the data from $styles to go into the correct spots, Is this feasable?

Long story short, I need to explode those arrays out somehow, any idea?

Hi, i webmaster multi sites and at the moment run php code to block unwanted ip addresses because of bad submittings.   I am running this code at the moment <?php

Hello!   I need small help. Need php script for changinh milti lines in myslq database. In database are fiekds: date, ip, status 1. 2014-09-01, 123, 0 2. 2014-09-01, 444, 0 3. 2014-09-01, 123, 0 4. 2014-08-31, 888, 0 5. 2014-09-01, 444, 0 6. 2014-09-01, 123, 0   Now script must search ip and date.   1. record is OK, 2. record is OK, 3. record NOT OK, because date and ip are in first record, must change status to 1 4. record is OK, 5. record NOT OK, because date and ip are in second record, must change status to 1 6. record NOT OK, because date and ip are in 1 and 3 record, must change status to 1  

I am trying to make a database application. I encounterd a problem that. suppose multiple users tryto access data it may cause problem. to clearly understand please see the attachment. How can I resolve it?