Inserting records into Filemaker

[SFDonovan]

I have exam grades stored in a MySQL server and want to insert these into a FileMaker table. I have made the connection and queried my records but am unsure just how to insert into FileMaker. I ran my query and now have everything in a while loop.

while($row = $db->sql_fetchrow($pullgrades)) {

		$quiz_id = $row['quiz_id'];

		$quiz_name = $row['quiz_name'];

		$exam_type = $row['exam_type'];

		$course_number = $row['course_number'];

		$academic_year = $row['academic_year'];

		$uid = $row['uid'];

		$somkey = $row['somkey'];

		$username = $row['username'];

		$grade = $row['grade'];	

I have not found much documentation on what to do here.

$request = $fm->newAddCommand('addGrades');

Do I use something like...

$request->setField(something, $something);

$request->execute();

[ttread]

I think you would do something like:

$arr_data = array('quiz_id' => $quiz_id, 'quiz_name' => $quiz_name);

$newAdd = $fm->newAddCommand('layout_name', $arr_data);

$result = $newAdd->execute();

You would add your desired fields to the array $arr_data. Replace 'layout_name' with the name of your layout.

Take a look at the documentation at http://www.filemaker.com/downloads/pdf/fms9_cwp_php_en.pdf

[Joel Shapiro]

Hi SFDonovan

As ttread said, you can push a whole array at once into FM -- as long as the keys in the array are the FM field names. So you might even be able to use the $row array if the field names match, but make sure there are no keys in the array that are not on the layout.

You can also set fields individually, as you'd suggested:

$arr_data = array('quiz_id' => $quiz_id, 'quiz_name' => $quiz_name);



$newAdd = $fm->newAddCommand('layout_name', $arr_data);

$newAdd->setField('otherFieldName', $otherFieldValue);

$result = $newAdd->execute();

HTH,

-Joel

~~~~~~~~~~~~~~~~~~~~~~~

Joel Shapiro

FileMaker Pro

database & web design

http://www.jsfmp.com

415-269-5055

~~~~~~~~~~~~~~~~~~~~~~~

[SFDonovan]

I wonder why this will not insert.

$pullgrades = $db->sql_query("SELECT 

							 quiz_id,

							 quiz_name,

							 exam_type,

							 course_number,

							 academic_year,

							 uid,

							 somkey,

							 username,

							 grade

							 FROM ".EXPORT_GRADE_TABLE." ORDER BY uid");



$arr_data = array();

$i = 0;

while($row = $db->sql_fetchrow($pullgrades)) {

 $arr_data[$i]['quiz_id'] = $row['quiz_id'];

 $arr_data[$i]['quiz_name'] = $row['quiz_name'];

 $arr_data[$i]['exam_type'] = $row['exam_type'];

 $arr_data[$i]['course_number'] = $row['course_number'];

 $arr_data[$i]['academic_year'] = $row['academic_year'];

 $arr_data[$i]['uid'] = $row['uid'];

 $arr_data[$i]['somkey'] = $row['somkey'];

 $arr_data[$i]['username'] = $row['username'];

 $arr_data[$i]['grade'] = $row['grade']; 

  $i++;

}



//echo"

";

//print_r($arr_data); //echo""; //exit(); $newAdd = $fm->newAddCommand('Grades', $arr_data); $result = $newAdd->execute();

When I uncomment print_r I get...

Array ( [0] => Array ( [quiz_id] => 432 [quiz_name] => POD Exam 1 2010 [exam_type] => NM [course_number] => 530 [academic_year] => 0910 [uid] => U00012222 [somkey] => 781 [username] => w108yyy [grade] => 92.0 ) [1] => Array ( [quiz_id] => 432 [quiz_name] => POD Exam 1 2010 [exam_type] => NM [course_number] => 530 [academic_year] => 0910 [uid] => U00011111 [somkey] => 724 [username] => w001xxx [grade] => 77.0 )

etc etc about 100 records. But my layout table is empty. Before I was doing this...

But was only getting one record written to the layout so I made this change to loop into an array.

$arr_data = array('quiz_id' => $quiz_id, 'quiz_name' => $quiz_name, 'exam_type' => $exam_type, 'course_number' => $course_number, 'academic_year' => $academic_year, 'uid' => $uid, 'somkey' => $somkey, 'username' => $username, 'grade' => $grade);

[SFDonovan]

Well come to find out if I load the array each loop

Array

(

[quiz_id] => 432

[quiz_name] => POD Exam 1 2010

[exam_type] => NM

[course_number] => 530

[academic_year] => 0910

[uid] => U0061111

[somkey] => 788

[username] => w006xxx

[grade] => 86.0

)

Then use the $newAdd->setField it works. Probably slower that dirt.

while($row = $db->sql_fetchrow($pullgrades)) {

$quiz_id = $row['quiz_id'];

$quiz_name = $row['quiz_name'];

$exam_type = $row['exam_type'];

$course_number = $row['course_number'];

$academic_year = $row['academic_year'];

$uid = $row['uid'];

$somkey = $row['somkey'];

$username = $row['username'];

$grade = $row['grade'];



$arr_data = array('quiz_id' => $quiz_id, 'quiz_name' => $quiz_name, 'exam_type' => $exam_type, 'course_number' => $course_number, 'academic_year' => $academic_year, 'uid' => $uid, 'somkey' => $somkey, 'username' => $username, 'grade' => $grade); 



#### Add one at a time

$newAdd = $fm->newAddCommand('Grades', $arr_data);

$newAdd->setField('quiz_id', $quiz_id);

$newAdd->setField('quiz_name', $quiz_name);

$newAdd->setField('exam_type', $exam_type);

$newAdd->setField('course_number', $course_number);

$newAdd->setField('academic_year', $academic_year);

$newAdd->setField('uid', $uid);

$newAdd->setField('somkey', $somkey);

$newAdd->setField('username', $username);

$newAdd->setField('grade', $grade);

$result = $newAdd->execute(); 



}

Why it would not work with this array I don't know.

$arr_data = array();

$i = 0;

while($row = $db->sql_fetchrow($pullgrades)) {

 $arr_data[$i]['quiz_id'] = $row['quiz_id'];

 $arr_data[$i]['quiz_name'] = $row['quiz_name'];

 $arr_data[$i]['exam_type'] = $row['exam_type'];

 $arr_data[$i]['course_number'] = $row['course_number'];

 $arr_data[$i]['academic_year'] = $row['academic_year'];

 $arr_data[$i]['uid'] = $row['uid'];

 $arr_data[$i]['somkey'] = $row['somkey'];

 $arr_data[$i]['username'] = $row['username'];

 $arr_data[$i]['grade'] = $row['grade']; 

  $i++;

}

[Genx]

It's not working because you have a multidimensional array...

the FileMaker Add Command expects an associative array of field name / value for a single record so the following would work...

$arr_data = array();

$i = 0;

while($row = $db->sql_fetchrow($pullgrades)) {

 $arr_data[$i]['quiz_id'] = $row['quiz_id'];

 $arr_data[$i]['quiz_name'] = $row['quiz_name'];

 $arr_data[$i]['exam_type'] = $row['exam_type'];

 $arr_data[$i]['course_number'] = $row['course_number'];

 $arr_data[$i]['academic_year'] = $row['academic_year'];

 $arr_data[$i]['uid'] = $row['uid'];

 $arr_data[$i]['somkey'] = $row['somkey'];

 $arr_data[$i]['username'] = $row['username'];

 $arr_data[$i]['grade'] = $row['grade']; 

  $i++;

} 



foreach($arr_data as $dataset){

$newAdd = $fm->newAddCommand('Grades', $dataset);

$result = $newAdd->execute(); 

}

[almedajohnson]

Thanks for the useful code posting. Though my problem was not exact same but i got the solution of my problem from this. so thanks guys