[RESOLVED] insert not working

[Thikho]

Hi All

I am selecting information from a one database and adding it to the other database but my insert function does not work , im testing with only one function one if i am retrieving many information/records how do i add them into the new table lets say i got like five rows in my first resource and i want to add all those row to the new tbale

It always add the first record on the list , How can i add all of them pls help

<?php

require_once ('paths.php');
require (CONNECTIONS.'conn.php');
require (CONNECTIONS.'connection.php');

 $i=0;

$resource1 = "SELECT * FROM (
	SELECT
	Customer_Information.Branch AS Branch,
	Waybill_Information.Waybill_Date AS Waybill_Date,
	Rate_Information.WaybillNUmber AS WaybillNumber,
	Waybill_Information.Customer_Reference AS Customer_Reference,
	Waybill_Information.To_Ops_Branch AS To_Ops_Branch,
	DeliverySheet_Information.Route AS Route,
	Rate_Information.Account_Number AS Account_Number,
	Customer_Information.Customer_Name AS Customer_Name,
	Waybill_Information.Service_Type AS Service_Type,
	Waybill_Information.Scan_Back_Reason_Code AS Scan_Back_Reason_Code,
	POD_Information.POD_Date AS POD_Date
	FROM
	Rate_Information)
	 AS Data LIMIT 20";

	$result1=mysql_query($resource1);
	$keys=array_keys(mysql_fetch_array($result1));
	while ($rows = mysql_fetch_array($result1)) {

	$branch = $rows['Branch'];
	$waybilldate = $rows['Waybill_Date'];
	$waybillnumber = $rows['WaybillNumber'];
	$cus_ref= $rows['Customer_Reference'];
	$to_ops_branch= $rows['To_Ops_Branch'];
	$route= $rows['Route'];
	$acc_num= $rows['Account_Number'];
	$cus_name= $rows['Customer_Name'];
	$service_type= $rows['Service_Type'];
	$scan_back_reason_code= $rows['Scan_Back_Reason_Code'];
	$pod_date = $rows['POD_Date'];	

	foreach ($keys as $speckey) {
		$got[$i][$speckey]=$rows[$speckey];
	}
	$i++;
	}
	return $got;

	require (CONNECTIONS.'db_conn.php');

	$resource2 = "INSERT IGNORE INTO Internal_Docs_waybills_outstanding
	(Branch, Waybill_Date, WaybillNumber, Customer_Reference, To_Ops_Branch, Route, Account_Number, Customer_Name, Service_Type, Scan_Back_Reason_Code, POD_Date, Date_Invoice_Received) VALUES (
	'".$branch."', '".$waybilldate."', '".$waybillnumber."', '".$cus_ref."', '".$to_ops_branch."', '".$route."', '".$acc_num."',  '".$cus_name."', '".$service_type."', '".$scan_back_reason_code."', '".$pod_date."', NOW())";
	$result2=mysql_query($resource2) or die("Cannot insert into Internal Docs waybills outstanding");


?>

I have printed out my result it shows perfectly but my issue is adding to a new table , How do i insert multiple records at onces

[NogDog]

If the databases are on the same database server, you should be able to do it with one query, something like:

INSERT INTO database_1.table_name (col1, col2, col3,...colN)
SELECT col1, col2, col3,...colN FROM database_2.table_name

(Make sure that the number and order of column names in the SELECT exactly matches that of the column list in the INSERT portion.)

[Thikho]

Hi NogDog

tnks for the reply , my database are on different servers, so how can i tackle that .

and the other thing I am getting the result lets say i have ten records retrived from my resource one my insert only execute or add one row how can i make it to add all records retrieved form resource 1 to the new table.

[Thikho]

Can anyone help me pls with doing multiple insert i have tried to create a for loop that keep on looping on my result fetched until all records are added my script is not doing anything pls help me out Im a fetching information from a separate database to another one which are both on different servers and i need to add all records to a new table your assistance will be highly appriciated

this is what i got now

$resource1 = "SELECT * FROM (
		SELECT
		Customer_Information.Branch AS Branch,
		Waybill_Information.Waybill_Date AS Waybill_Date,
		Rate_Information.WaybillNUmber AS WaybillNumber,
		Waybill_Information.Customer_Reference AS Customer_Reference,
		Waybill_Information.To_Ops_Branch AS To_Ops_Branch,
		DeliverySheet_Information.Route AS Route,
		Rate_Information.Account_Number AS Account_Number,
		Customer_Information.Customer_Name AS Customer_Name,
		Waybill_Information.Service_Type AS Service_Type,
		Waybill_Information.Scan_Back_Reason_Code AS Scan_Back_Reason_Code,
		POD_Information.POD_Date AS POD_Date
		FROM
		Rate_Information
		) AS Data LIMIT 10";

	$result1=mysql_query($resource1);

	$count = mysql_num_rows($result1);

	while ($rows = mysql_fetch_array($result1)) {

	$branch = $rows['Branch'];
	$waybilldate = $rows['Waybill_Date'];
	$waybillnumber = $rows['WaybillNumber'];
	$cus_ref= $rows['Customer_Reference'];
	$to_ops_branch= $rows['To_Ops_Branch'];
	$route= $rows['Route'];
	$acc_num= $rows['Account_Number'];
	$cus_name= $rows['Customer_Name'];
	$service_type= $rows['Service_Type'];
	$scan_back_reason_code= $rows['Scan_Back_Reason_Code'];
	$pod_date = $rows['POD_Date'];	

	require (CONNECTIONS.'db_conn.php');

	for($i=0;$i<$count;$i++){


	$insertShift_sql = "INSERT IGNORE INTO Internal_Docs_waybills_outstanding
	(Branch, Waybill_Date, WaybillNumber, Customer_Reference, To_Ops_Branch, Route, Account_Number, Customer_Name, Service_Type, Scan_Back_Reason_Code, POD_Date, Date_Invoice_Received) VALUES (
	'".$branch."', '".$waybilldate."', '".$waybillnumber."', '".$cus_ref."', '".$to_ops_branch."', '".$route."', '".$acc_num."',  '".$cus_name."', '".$service_type."', '".$scan_back_reason_code."', '".$pod_date."', NOW())";
	echo $insertShift_sql;
	exit();
	}
	$result=mysql_query($insertShift_sql);

	if( @mysql_query ($insertShift_sql) ){ 
		echo 'You\'re shift has been processed. Thank you. ';

	} 
	else {
	echo 'An error has been encountered. Please refresh the page. ';
	}

	}

[HalfaBee]

Remove th lines I have marked below.

The second server needs to have a defined link_identifier
and the mysql functions need to reference this.
eg
Definition from the manual
resource mysql_query ( string $query [, resource $link_identifier] )

        for($i=0;$i<$count;$i++){ // remove this line


    $insertShift_sql = "INSERT IGNORE INTO Internal_Docs_waybills_outstanding
    (Branch, Waybill_Date, WaybillNumber, Customer_Reference, To_Ops_Branch, Route, Account_Number, Customer_Name, Service_Type, Scan_Back_Reason_Code, POD_Date, Date_Invoice_Received) VALUES (
    '".$branch."', '".$waybilldate."', '".$waybillnumber."', '".$cus_ref."', '".$to_ops_branch."', '".$route."', '".$acc_num."',  '".$cus_name."', '".$service_type."', '".$scan_back_reason_code."', '".$pod_date."', NOW())";
    echo $insertShift_sql;
    exit(); // remove this line
    }// remove this line

[Thikho]

I have removed them i was testing if im getting my values , my issue is the bulk insert

It is adding only one record to the new database i need it to insert all records like multiple insert /bulk insert

[Thikho]

any one to help me with multiple insert of records from one table to the other (diff servers)
I need to fetch this records
mysql> SELECT * from test_mysql;
+----+--------+----------+------------------------+
| id | name | lastname | email |
+----+--------+----------+------------------------+
| 1 | Billly | Blueton | [e-mail] |
| 2 | Jame | Campbell | [e-mail] |
| 3 | Mark | Jackson | [e-mail] |
| 4 | Linda | Travor | [e-mail] |
| 5 | Joey | Ford | [e-mail] |
| 6 | Sidney | Gibson | [e-mail] |
+----+--------+----------+------------------------+
6 rows in set (0.00 sec)

and insert them into my new table

mysql> describe main_mysql;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(65) | NO | | | |
| lastname | varchar(65) | NO | | | |
| email | varchar(65) | NO | | | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

here is my code

<?php

require_once ('paths.php');
require (CONNECTIONS.'db_conn.php');

$resource1 = "SELECT * FROM test_mysql LIMIT 2";


	$result1=mysql_query($resource1);

	while ($rows = mysql_fetch_array($result1)) {

	$id = $rows['id'];
	$name = $rows['name'];
	$lname= $rows['lastname'];
	$mail= $rows['email'];

	$insertShift_sql = "INSERT IGNORE INTO main_mysql
	(id, name, lastname, email) VALUES ('".$id."', '".$name."', '".$lname."', '".$mail."')";

	$result=mysql_query($insertShift_sql);

	if( @mysql_query ($insertShift_sql) ){ 
		echo 'You\'re shift has been processed. Thank you. ';

	} 
	else {
	echo 'An error has been encountered. Please refresh the page. ';
	}

	}

?>

Nogdog Thanks, when i use your method it works fine on the same servers how do i do i add on different servers