Foreach Loop to Insert into table

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
skoch
Veteran Member
Posts: 49
Joined: 2020-01-27 14:20

Foreach Loop to Insert into table

Post by skoch » 2022-05-10 07:43

Hello,
I have a little problem ;)
I have a Persons table and a Letters table. With the help and addition of the Mass Update plugin, after selecting people and selecting a letter (dropdown via More option), the addressed letters are displayed directly in TCPDF - this works wonderfully :D
In the second function of the MassUpdate plugin, an ajax file is now called up, to which the selected person IDs (array IDS) and the ID of the letter (new_value) are transferred.
The person IDs should now be entered as new data records in the table testtable using a foreach loop, see the following code (customized code from the mass-update-plugin):

Code: Select all

/* receive and validate calling parameters */
$ids = Request::val('ids');
if(empty($ids) || !is_array($ids)) {
	@header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented');
	exit;
}

$new_value = makeSafe(Request::val('newValue'));

foreach($ids as $id) {
	$tn2 = 'testtable';
	$field2 = 'field2';
	$field3 = 'field3';
	
	$query = "INSERT INTO `{$tn2}` (`{$field2}`) VALUES ('{$id}')";

	$e = ['silentErrors' => true];
	sql($query, $e);
}

if($e['error']) {
	@header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented');
}
This works so far, each individual person ID is entered as a new data record :D
Now the letter ID (new_value) should also be transferred to field3 in every record.

Than I change the Query command to:

Code: Select all

$query = "INSERT INTO `{$tn2}` (`{$field2}`, `{$field3}`) VALUES ('{$id}', '{$new_value}')";
Now only one person ID is entered in field2 and the letter ID in field3, but not for all person IDs :(

Can someone give me a tip here?

kind regards
Stefan

skoch
Veteran Member
Posts: 49
Joined: 2020-01-27 14:20

Re: Foreach Loop to Insert into table

Post by skoch » 2022-05-11 15:30

Hello,

I have now rewritten the foreach loop as follows:

Code: Select all

$new_value = makeSafe(Request::val('newValue'));

foreach($ids as $id) {
	$id_letter = array($id=>$new_value);
		foreach($id_letter as $id => $letter) {
		
		$tn2 = 'testtable';
		$field2 = 'field2';
		$field3 = 'field3';
	
		$query = "INSERT INTO `{$tn2}` (`{$field2}`) VALUES ('{$id}')";

		$e = ['silentErrors' => true];
		sql($query, $e);
	}
}
Now I get a new record in the table for every ID of the selected person. :)

If I swap the variables in the query as follows...

Code: Select all

$query = "INSERT INTO `{$tn2}` (`{$field2}`) VALUES ('{$letter}')";
...then I get a number of new record in the table with the same ID of the letter in the number of selected people. :)

Now if I want to connect the two like this...

Code: Select all

$query = "INSERT INTO `{$tn2}` (`{$field2}`, `{$field3}`) VALUES (`{$id}`, '{$letter}')";
...the table remains empty! :(

Someone an idea?

kind regards
Stefan

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Foreach Loop to Insert into table

Post by pbottcher » 2022-05-11 19:21

Hi,

you may try:

Code: Select all

$query = "INSERT INTO `{$tn2}` set `{$field2}`='{$id}', `{$field3}`='{$letter}' ";
Also, did you check the datatype of your fields that they match and can hold the data?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

skoch
Veteran Member
Posts: 49
Joined: 2020-01-27 14:20

Re: Foreach Loop to Insert into table

Post by skoch » 2022-05-12 05:24

Hello,

thank you for the feedback.

First of all, field3 was actually with the data type "unique", ie no repetitions.
Secondly, there were problems with the abostrophe in the query, only the following arrangement works:

Code: Select all

$query = "INSERT INTO `{$tn2}` (`{$field2}`, `{$field3}`) VALUES ('{$id}', '{$new_value}')";
The reduced code below now works and writes the data as desired as new data records in the table:

Code: Select all

/* receive and validate calling parameters */
$ids = Request::val('ids');
if(empty($ids) || !is_array($ids)) {
	@header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented');
	exit;
}

$new_value = makeSafe(Request::val('newValue'));

foreach($ids as $id) {
	$tn2 = 'testtable';
	$field2 = 'field2';
	$field3 = 'field3';
	
	$query = "INSERT INTO `{$tn2}` (`{$field2}`, `{$field3}`) VALUES ('{$id}', '{$new_value}')";

	$e = ['silentErrors' => true];
	sql($query, $e);
}

if($e['error']) {
	@header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented');
}
kind regards
Stefan

skoch
Veteran Member
Posts: 49
Joined: 2020-01-27 14:20

Re: Foreach Loop to Insert into table

Post by skoch » 2022-05-14 09:38

Hello,

I have now created a new table with the corresponding fields in AP for productive operation.
After the above code has been adjusted according to the new table name ($tn2) and the fields ($field2, $field3), the data is written to the database (checked with phpmysql!) but is not displayed in the application.
What have I done wrong here?

kind regards
Stefan

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Foreach Loop to Insert into table

Post by pbottcher » 2022-05-15 08:52

Hi,

did you check that your definitions within AppGini match the definitions within your database. Without knowing all these informations, it is impossible the get deeper help.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

skoch
Veteran Member
Posts: 49
Joined: 2020-01-27 14:20

Re: Foreach Loop to Insert into table

Post by skoch » 2022-05-16 04:55

Hi,

I will check that und give you feedback.

skoch
Veteran Member
Posts: 49
Joined: 2020-01-27 14:20

Re: Foreach Loop to Insert into table

Post by skoch » 2022-05-16 16:32

Hi,
a default value was included in a lookup field - after this was fixed, everything works great now :D
Many thanks for the help.

kind regards
Stefan

Post Reply