Page 2 of 2

Posted: Wed Jul 07, 2004 1:12 am
by th3monk3y
pablo,
on the right track now!

rather than the unix time stamp .. how can i convert to yyyy-mm-dd

instead of doing a foreach, how can i insert the data in the same insert statement i am already using (because I am already looping through data)
-Paul

Posted: Wed Jul 07, 2004 2:05 am
by scorphus
The same script with some modifications:

Code: Select all

<?php
// This array was grabbed from db1:
$array = array('indicator1', 'data1', '12/21/03',
	'indicator2', 'data2', '12/15/03',
	'indicator3', 'data3', '12/27/03',
	'indicator4', 'data4', '12/02/03',
	'indicator5', 'data5', '12/19/03');
$size = sizeof($array) / 3;
$data_sets = array();
for ($i = 0; $i < $size; $i++) {
	$slice = array_slice($array, 3*$i, 3);
	$date = explode('/', $slice[2]);
	$slice[2] = date('Y-m-d', strtotime($slice[2]));
	$data_sets[$slice[2]] = $slice;
}
ksort($data_sets);
$size = sizeof($data_sets);
$i = 0;
$values = '';
foreach($data_sets as $set)
	$values .= "'${set[0]}', '${set[1]}', ${set[2]}" . ((++$i < $size - 1) ? ', ' : '');
$sql = "INSERT INTO table_name_on_db2 VALUES(null, $values);";
echo "$sql\n";
?>
Output:

Code: Select all

INSERT INTO table_name_on_db2 VALUES(null, 'indicator4', 'data4', 2003-12-02, 'indicator2', 'data2', 2003-12-15, 'indicator5', 'data5', 2003-12-19, 'indicator1', 'data1', 2003-12-21'indicator3', 'data3', 2003-12-27);
Hope that works, I'm tired. Too late here... gonna fall in sleep.

See ya.

Posted: Wed Jul 07, 2004 7:47 am
by feyd
scorphus wrote::snip:
Output:

Code: Select all

INSERT INTO table_name_on_db2 VALUES(null, 'indicator4', 'data4', 2003-12-02, 'indicator2', 'data2', 2003-12-15, 'indicator5', 'data5', 2003-12-19, 'indicator1', 'data1', 2003-12-21'indicator3', 'data3', 2003-12-27);
wouldn't that insert try to insert the integer values 1989, 1976, 1970, and 1964 respectively? I think you'll need some quotes around the values..

Posted: Wed Jul 07, 2004 12:04 pm
by th3monk3y
sweet coding! thanks again!! I will try it with the quotes...
-Paul

Posted: Wed Jul 07, 2004 12:42 pm
by scorphus
feyd wrote:wouldn't that insert try to insert the integer values 1989, 1976, 1970, and 1964 respectively? I think you'll need some quotes around the values..
I was really tired. Of course it has to be around slashes! feyd and his bull eyes ;)

Paul, I'm sure you can do the trick... sorry for the bug in the code.

Regards,
Scorphus.

Posted: Wed Jul 07, 2004 12:45 pm
by feyd
It's all good. :)

Posted: Wed Jul 07, 2004 12:51 pm
by scorphus
I wrote:(...) Output:

Code: Select all

INSERT INTO table_name_on_db2 VALUES(null, 'indicator4', 'data4', 2003-12-02, 'indicator2', 'data2', 2003-12-15, 'indicator5', 'data5', 2003-12-19, 'indicator1', 'data1', 2003-12-21'indicator3', 'data3', 2003-12-27);
(...)
I found another error in the code: 2003-12-21'indicator3'. Where is the comma?

The correction:

Code: Select all

<?php
// This array was grabbed from db1:
$array = array('indicator1', 'data1', '12/21/03',
	'indicator2', 'data2', '12/15/03',
	'indicator3', 'data3', '12/27/03',
	'indicator4', 'data4', '12/02/03',
	'indicator5', 'data5', '12/19/03');
$size = sizeof($array) / 3;
$data_sets = array();
for ($i = 0; $i < $size; $i++) {
	$slice = array_slice($array, 3*$i, 3);
	$date = explode('/', $slice[2]);
	$slice[2] = date('Y-m-d', strtotime($slice[2]));
	$data_sets[$slice[2]] = $slice;
}
ksort($data_sets);
$size = sizeof($data_sets);
$i = 0;
$values = '';
foreach($data_sets as $set)
	$values .= "'${set[0]}', '${set[1]}', '${set[2]}'" . ((++$i < $size) ? ', ' : '');
$sql = "INSERT INTO table_name_on_db2 VALUES(null, $values);";
echo "$sql\n";
?>
-- Scorphus

Posted: Wed Jul 07, 2004 12:58 pm
by scorphus
Also, a bit simpler solution:

Code: Select all

<?php
// ...
foreach($data_sets as $set)
	$values .= implode("', '", $set) . ((++$i < $size) ? "', '" : "");
$sql = "INSERT INTO table_name_on_db2 VALUES(null, '$values');";
// ...
?>
This turns the code independent on the number of elements in a data set, just need to set the $size variable ($size = sizeof($array) / 3) :wink:

-- Scorphus

Posted: Wed Jul 07, 2004 5:45 pm
by th3monk3y
o.k.
we are close! So I tried the new code you posted.. but it chops off the null values if they are not present .. I actually need null values inserted in db2 if they are not present. so I hard wired some values as you will see below ... except it is trying to insert the current date in one of them, and it is doubling up in one column(6 from the end) and giving me 17 columns instead of 18... the "data sets" will vary between 1 and all 6 ....below is the code...

Code: Select all

<?php
<?php 
	$td1_docNum = "a32516t"; // instrument
	$td1 = "150000"; // amount
	$td1_date = "12/17/73"; // date
	$td2_docNum = "eeet53421"; // instrument2
	$td2 = "15000"; // amount2
	$td2_date = "12/17/79"; // date2	
	$td3_docNum = "";
	$td3 = "";
	$td3_date = "";	
	$td4_docNum = "";
	$td4 = "";
	$td4_date ="";	
	$td5_docNum = "";
	$td5 = "";
	$td5_date = "";	
	$td6_docNum = "";
	$td6 = "";
	$td6_date = "";


// This array was grabbed from db1: 
$array = array($td1_docNum, $td1, $td1_date, 
   $td2_docNum, $td2, $td2_date, 
   $td3_docNum, $td3, $td3_date, 
   $td4_docNum, $td4, $td4_date,
   $td5_docNum, $td5, $td5_date,
   $td6_docNum, $td6, $td6_date,); 
$size = sizeof($array) / 3; 
$data_sets = array(); 
for ($i = 0; $i < $size; $i++) { 
   $slice = array_slice($array, 3*$i, 3); 
   $date = explode('/', $slice[2]); 
   $slice[2] = date('Y-m-d', strtotime($slice[2])); 
   $data_sets[$slice[2]] = $slice; 
} 
ksort($data_sets); 
$size = sizeof($data_sets); 
$i = 0; 
$values = ''; 
foreach($data_sets as $set) 
   $values .= "'${set[0]}', '${set[1]}', '${set[2]}', '${set[3]}', '${set[4]}', '${set[5]}'" . ((++$i < $size - 1) ? ', ' : ''); 
$sql = "INSERT INTO table_name_on_db2 VALUES($values);"; 
echo "$sql\n"; 
?> 
?>

Code: Select all

INSERT INTO table_name_on_db2 VALUES('a32516t', '150000', '1973-12-17', '', '', '', 'eeet53421', '15000', '1979-12-17', '', '', '''', '', '2004-07-07', '', '', '');

Posted: Wed Jul 07, 2004 6:24 pm
by scorphus
Null values placed before the non-null ones:

Code: Select all

<?php
$td1_docNum = "a32516t"; // instrument
$td1 = "150000"; // amount
$td1_date = "12/17/73"; // date
$td2_docNum = "eeet53421"; // instrument2
$td2 = "15000"; // amount2
$td2_date = "12/17/79"; // date2   
$td3_docNum = "";
$td3 = "";
$td3_date = "";   
$td4_docNum = "";
$td4 = "";
$td4_date ="";   
$td5_docNum = "";
$td5 = "";
$td5_date = "";   
$td6_docNum = "";
$td6 = "";
$td6_date = "";
// This array was grabbed from db1:
$array = array($td1_docNum, $td1, $td1_date,
   $td2_docNum, $td2, $td2_date,
   $td3_docNum, $td3, $td3_date,
   $td4_docNum, $td4, $td4_date,
   $td5_docNum, $td5, $td5_date,
   $td6_docNum, $td6, $td6_date);
$size = sizeof($array) / 3;
$data_sets = array();
for ($i = 0; $i < $size; $i++) {
	$slice = array_slice($array, 3*$i, 3);
	$date = explode('/', $slice[2]);
	if (empty($slice[2]))
		$data_sets[] = $slice;
	else {
		$slice[2] = date('Y-m-d', strtotime($slice[2]));
		$data_sets[$slice[2]] = $slice;
	}
}
ksort($data_sets);
$size = sizeof($data_sets);
$i = 0;
$values = '';
foreach ($data_sets as $set)
	$values .= implode("', '", $set) . ((++$i < $size) ? "', '" : "");
$sql = "INSERT INTO table_name_on_db2 VALUES(null, '$values');";
echo "$sql\n";
?>

Code: Select all

INSERT INTO table_name_on_db2 VALUES(null, '', '', '', '', '', '', '', '', '', '', '', '', 'a32516t', '150000', '1973-12-17', 'eeet53421', '15000', '1979-12-17');
Null values placed after the non-null ones:

Code: Select all

<?php
$td1_docNum = "a32516t"; // instrument
$td1 = "150000"; // amount
$td1_date = "12/17/73"; // date
$td2_docNum = "eeet53421"; // instrument2
$td2 = "15000"; // amount2
$td2_date = "12/17/79"; // date2   
$td3_docNum = "";
$td3 = "";
$td3_date = "";   
$td4_docNum = "";
$td4 = "";
$td4_date ="";   
$td5_docNum = "";
$td5 = "";
$td5_date = "";   
$td6_docNum = "";
$td6 = "";
$td6_date = "";
// This array was grabbed from db1:
$array = array($td1_docNum, $td1, $td1_date,
   $td2_docNum, $td2, $td2_date,
   $td3_docNum, $td3, $td3_date,
   $td4_docNum, $td4, $td4_date,
   $td5_docNum, $td5, $td5_date,
   $td6_docNum, $td6, $td6_date);
$size = sizeof($array) / 3;
$data_sets = array();
$empty_data_sets = array();
for ($i = 0; $i < $size; $i++) {
	$slice = array_slice($array, 3*$i, 3);
	$date = explode('/', $slice[2]);
	if (empty($slice[2]))
		$empty_data_sets[] = $slice;
	else {
		$slice[2] = date('Y-m-d', strtotime($slice[2]));
		$data_sets[$slice[2]] = $slice;
	}
}
ksort($data_sets);
$data_sets = array_merge($data_sets, $empty_data_sets);
$size = sizeof($data_sets);
$i = 0;
$values = '';
foreach ($data_sets as $set)
	$values .= implode("', '", $set) . ((++$i < $size) ? "', '" : "");
$sql = "INSERT INTO table_name_on_db2 VALUES(null, '$values');";
echo "$sql\n";
?>

Code: Select all

INSERT INTO table_name_on_db2 VALUES(null, 'a32516t', '150000', '1973-12-17', 'eeet53421', '15000', '1979-12-17', '', '', '', '', '', '', '', '', '', '', '', '');
-- Scorphus

Posted: Wed Jul 07, 2004 6:43 pm
by th3monk3y
scorphus,
You da man! So i hear the surf is awesome in Brazil!
-Paul

Posted: Thu Jul 08, 2004 10:14 pm
by th3monk3y
scorphus,
So I managed to break it :? ...

if I put another row in the array... it breaks?

Code: Select all

<?php
$array = array($td1_docNum, $td1, $td1_date, 
   $td2_docNum, $td2, $td2_date, 
   $td3_docNum, $td3, $td3_date, 
   $td4_docNum, $td4, $td4_date, 
   $td5_docNum, $td5, $td5_date, 
   $td6_docNum, $td6, $td6_date,
   $td7_docNum, $td7, $td7_date); // added one more row here...
?>
I am actually using your code in my script now, and it was working great until I added to the array??? I actually need to add up to td10.

I added to my insert statement as well, since I am calling out all the columns rather than doing a straight insert ... It doesn't error out .. it just doesn't insert anything in db2

thanks,
Paul

Posted: Fri Jul 09, 2004 5:28 pm
by scorphus
Hi Paul!

Well, I tested it here and got results as expected, see:

Code: Select all

<?php
$td1_docNum = "a32516t"; // instrument
$td1 = "150000"; // amount
$td1_date = "12/17/73"; // date
$td2_docNum = "eeet53421"; // instrument2
$td2 = "15000"; // amount2
$td2_date = "12/17/79"; // date2
$td3_docNum = "333";
$td3 = "";
$td3_date = "";
$td4_docNum = "444";
$td4 = "";
$td4_date ="";
$td5_docNum = "555";
$td5 = "";
$td5_date = "";
$td6_docNum = "666";
$td6 = "";
$td6_date = "";
$td7_docNum = "777";
$td7 = "";
$td7_date = "";
$td8_docNum = "888";
$td8 = "";
$td8_date = "";
$td9_docNum = "999";
$td9 = "";
$td9_date = "";
$td10_docNum = "101010";
$td10 = "";
$td10_date = "";
// This array was grabbed from db1:
$array = array($td1_docNum, $td1, $td1_date,
   $td2_docNum, $td2, $td2_date,
   $td3_docNum, $td3, $td3_date,
   $td4_docNum, $td4, $td4_date,
   $td5_docNum, $td5, $td5_date,
   $td6_docNum, $td6, $td6_date,
   $td7_docNum, $td7, $td7_date,
   $td8_docNum, $td8, $td8_date,
   $td9_docNum, $td9, $td9_date,
   $td10_docNum, $td10, $td10_date);
$size = sizeof($array) / 3;
$data_sets = array();
for ($i = 0; $i < $size; $i++) {
	$slice = array_slice($array, 3*$i, 3);
	$date = explode('/', $slice[2]);
	if (empty($slice[2]))
		$data_sets[] = $slice;
	else {
		$slice[2] = date('Y-m-d', strtotime($slice[2]));
		$data_sets[$slice[2]] = $slice;
	}
}
ksort($data_sets);
$size = sizeof($data_sets);
$i = 0;
$values = '';
foreach ($data_sets as $set)
	$values .= implode("', '", $set) . ((++$i < $size) ? "', '" : "");
$sql = "INSERT INTO table_name_on_db2 VALUES(null, '$values');";
echo "$sql\n";
?>


<?php
$td1_docNum = "a32516t"; // instrument
$td1 = "150000"; // amount
$td1_date = "12/17/73"; // date
$td2_docNum = "eeet53421"; // instrument2
$td2 = "15000"; // amount2
$td2_date = "12/17/79"; // date2
$td3_docNum = "333";
$td3 = "";
$td3_date = "";
$td4_docNum = "444";
$td4 = "";
$td4_date ="";
$td5_docNum = "555";
$td5 = "";
$td5_date = "";
$td6_docNum = "666";
$td6 = "";
$td6_date = "";
$td7_docNum = "777";
$td7 = "";
$td7_date = "";
$td8_docNum = "888";
$td8 = "";
$td8_date = "";
$td9_docNum = "999";
$td9 = "";
$td9_date = "";
$td10_docNum = "101010";
$td10 = "";
$td10_date = "";
// This array was grabbed from db1:
$array = array($td1_docNum, $td1, $td1_date,
   $td2_docNum, $td2, $td2_date,
   $td3_docNum, $td3, $td3_date,
   $td4_docNum, $td4, $td4_date,
   $td5_docNum, $td5, $td5_date,
   $td6_docNum, $td6, $td6_date,
   $td7_docNum, $td7, $td7_date,
   $td8_docNum, $td8, $td8_date,
   $td9_docNum, $td9, $td9_date,
   $td10_docNum, $td10, $td10_date);
$size = sizeof($array) / 3;
$data_sets = array();
$empty_data_sets = array();
for ($i = 0; $i < $size; $i++) {
	$slice = array_slice($array, 3*$i, 3);
	$date = explode('/', $slice[2]);
	if (empty($slice[2]))
		$empty_data_sets[] = $slice;
	else {
		$slice[2] = date('Y-m-d', strtotime($slice[2]));
		$data_sets[$slice[2]] = $slice;
	}
}
ksort($data_sets);
$data_sets = array_merge($data_sets, $empty_data_sets);
$size = sizeof($data_sets);
$i = 0;
$values = '';
foreach ($data_sets as $set)
	$values .= implode("', '", $set) . ((++$i < $size) ? "', '" : "");
$sql = "INSERT INTO table_name_on_db2 VALUES(null, '$values');";
echo "$sql\n";
?>
output:

Code: Select all

INSERT INTO table_name_on_db2 VALUES(null, '333', '', '', '444', '', '', '555', '', '', '666', '', '', '777', '', '', '888', '', '', '999', '', '', '101010', '', '', 'a32516t', '150000', '1973-12-17', 'eeet53421', '15000', '1979-12-17');


INSERT INTO table_name_on_db2 VALUES(null, 'a32516t', '150000', '1973-12-17', 'eeet53421', '15000', '1979-12-17', '333', '', '', '444', '', '', '555', '', '', '666', '', '', '777', '', '', '888', '', '', '999', '', '', '101010', '', '');
Um grande abraço,
Pablo.