[RESOLVED] INSERT from database1 to database2

netfrugal

I am pulling 20 rows from SQL server. It is an easy query. I want to put all the data into a form and upon submitting, the next page must insert it all into a secondary table in a mysql database.

Arrays always elude me, and I've only figured out the foreach() function with just one column of data. But 9 columns of data to insert is frustrating me.

Below is code:

PAGE1

<form name="form1" method="post" action="action.php">
<?

$connect = odbc_connect("xyz", "xyz", "xyz");

$query = "SELECT * FROM ITSMSurveys ORDER BY CreatedDateTime DESC";
$result = odbc_exec($connect, $query);

while(odbc_fetch_row($result)) {
$IncidentNumber = odbc_result($result, 'IncidentNumber');
$CreatedBy = odbc_result($result, 'CreatedBy');

?>
<tr>
<td><?php echo $IncidentNumber;?><input name="IncidentNumber[]" type="hidden" value="<?php echo $IncidentNumber;?>"></td>
<td><?php echo $Subject;?></td>
<td><?php echo $CreatedBy;?></td>

          </tr>
<?   } ?>
   </table>

</form>

I've placed the first column with Incident[], and the code on the action.php inserts all 20 rows just fine. But I would like to know how to enter more than 1 column at a time!

ACTION.PHP

<?
require('includes/configure.php');

foreach($_POST['IncidentNumber'] as $key1) {

$sql = "INSERT INTO results(IncidentNumber)
VALUES ('$key1')";
$result = mysql_query($sql,$connection) or die(mysql_error());

}

leatherback

Hi,

Step one: The form:
Build the form exactly as you have done for incidentnumber. So you name the variable with the [] at the end to create an array. This will result in an array of 20 values, for each of the 6 variables, I will refer to those as var1 -> var6

On the next page, we are going to modify your code slightly..


require('includes/configure.php');

// for ease of understanding, first get each variable ot of the posted array:
// You should consider using some sort of validation of the data, unless it is just a form you use for management purposes, which I feel it will be..

$var1 = $_POST['var1'];
$var2 = $_POST['var2'];
$var3 = $_POST['var3'];
// and so on

// We will pull the key and the value of the array, the key is used to retrieve the other vars:
foreach($var1 as $key => $value) {

$sql = "INSERT INTO results(col1, col2, col3, col4, col5, col6)
VALUES (".$var1[$key].", ".$var2[$key].", ".$var3[$key].", ".$var4[$key].", ".$var5[$key].", ".$var6[$key].")";

$result = mysql_query($sql,$connection) or die(mysql_error());

}

netfrugal

I kind of see your logic, but the foreach() function is still confusing me. Do I add new 'foreach()' statements for every column that needs entering? for now I have only entered 2 columns.

This is how I understood it:

<?
require('includes/configure.php');

$var1 = $POST['var1'];
$var2 = $POST['var2'];

foreach($var1 as $key => $value) {

$sql = "INSERT INTO results(IncidentNumber, Subject)
VALUES (".$var1[$key].", ".$var2[$key].")";

$result = mysql_query($sql,$connection) or die(mysql_error());

}
?>

leatherback

Hi Het,

An array consist of e series of values. But these are not described as just a list (a, b, c), they actually 'know' their position:

$an_array = (
1 => "apple",
2 => "falls",
3 => "in",
4 => "lovers",
5 => "lap");

So you can echo out the 5th element of the array :

echo $an_array[5];

which will give you lap.

This you can use:
foreach($an_array as $key => $value)

tells php to loop through the array $an_array, and 'name' the index values $key, and the actual values $value.

Since you have the same index values in all your variables (They came from a form, where you didn't specifiy the keys, and the formn created them for you), you can use ONE foreach loop, and use the index ($KEY) to grab the values for each variable.

In my code, all you should have to do is replace the var1-var6 and col1-col6 with the actual vames as used in the form, and the matching colums in your database

netfrugal

I replaced your code as you said, but I know I'm missing something. So far I keep getting an error.

<?
require('includes/configure.php');

$var1 = $POST['IncidentNumber'];
$var2 = $POST['Subject'];

foreach($var1 as $key => $value) {

$sql = "INSERT INTO results(IncidentNumber, Subject)
VALUES (".$var1[$key].", ".$var2[$key].")";

$result = mysql_query($sql,$connection) or die(mysql_error());

}
?>

stolzyboy

what IS the error?

netfrugal

I get this:

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 ')' at line 2

Line 2 is "require('includes/configure.php');

stolzyboy

it's not meaning in your script... in your sql...

echo out your $sql variable to see what the full statement is

netfrugal

well, I echoed out the $sql and got this:

INSERT INTO results(IncidentNumber, Subject) VALUES (WX368109, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368124, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368165, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368210, Time Sync Tasks)
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368228, )

INSERT INTO results(IncidentNumber, Subject) VALUES (WX368255, Added Tom's name to project database)
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368314, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368471, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368482, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368488, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX368779, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX369003, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX367067, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX367460, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX367676, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX367678, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX366799, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX366907, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX366213, )
INSERT INTO results(IncidentNumber, Subject) VALUES (WX366220, )

Am I supposed to be getting the actual INSERT statement? Or just the results?

leatherback

hi,

this shows you that the value for Subject is not passe to your script. Check with print_r($POST['Subject'] that it is actually passed to the page.

A second point is: In the sql you should havequotes around all textfields (My bad):

$sql = "INSERT INTO results(IncidentNumber, Subject)
VALUES ('".$var1[$key]."', '".$var2[$key]."')";

netfrugal

The subject is not passing because it is an optional field. I should have used a different field for demo. I changed the subject field to a different column: 'ActualSubCategory'

Here's the code:

$var1 = $POST['var1'];
$var2 = $POST['var2'];

foreach($var1 as $key => $value) {

$sql = "INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('".$var1[$key]."', '".$var2[$key]."')";

//$result = mysql_query($sql,$connection) or die(mysql_error());

echo $sql;
}

And I get this:

INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368109', 'Printer / Fax')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368124', 'Software / Application Request')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368165', 'Printer / Fax')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368210', 'Computer Hardware')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368228', 'Database Administration')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368255', 'Lotus Notes')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368314', 'Lotus Notes')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368471', 'Lotus Notes')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368482', 'Phone')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368488', 'Ergonomic Equipment')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX368779', 'Database Administration')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX369003', 'Software / Application Request')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX367067', 'Phone')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX367460', 'Frontrange Configuration')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX367676', 'Lotus Notes')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX367678', 'New Rights Necessary')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX366799', 'Database Administration')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX366907', 'Printer / Fax')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX366213', 'Ghosted Computer')
INSERT INTO results(IncidentNumber, ActualSubCategory) VALUES ('WX366220', 'Computer Hardware')

does this mean the values are passing correctly?

stolzyboy

yeah, they look to be passing correctly... try and run the query now...

netfrugal

Uh oh, it gave me another error.

I ran the query and this is what I got:

Unknown column 'ActualSubCategory' in 'field list'

Which is odd to me, since I know there is a column in the table.

Any ideas why?

stolzyboy

you need to make certain there is a column name called that, and i believe it's case sensitive, i guess i've never really tried to use a different case, lol...

and yeah, use the [ PHP ][ /PHP ] without teh spaces and that'll color code the code for you

netfrugal

Found the error!

It was a problem with misspelling.

Nice job Guys, you're awesome!

stolzyboy

i figured it was 🙂