HTML table from Multi Record Loader with custom input

Hello all,
OK I have an html table that gets populated with form data from the multi record loader. I also placed a custom input box inside this table to collect info. What I am having trouble with is when the form is submitted, I don't know how to grab each separate row of input data to use and then update the corresponding record. Here is the table code:

<table border='2' cellpadding='15' table id='countit'  >
<tr><th>Service Address</th><th>Account Number</th><th>Status</th><th>Balance</th><th>Amount to Pay</th></tr>
<?php
foreach($form->data['details'] as $detail):
?>
<tr><td><?php echo $detail['servicestreet1']; ?> </td>
<td><?php echo $detail['accountnumber']; ?></td> //This is the Primary Key for the table
<td><?php echo $detail['jobstatus']; ?> </td>
<td><?php echo $detail['balance']; ?> </td>
<td><input type='text' name='<?php echo $detail['accountnumber']; ?>' value='<?php echo $detail['balance']; ?>' class='pound' id='<?php echo $detail['accountnumber']; ?>' onblur='sum()' /> </td></tr>
<?php
endforeach
?>
</table>

Any pointers would be much appreciated. Thank you much, Gabe

Hi Gabe,

This will be a bit complicated, since you will need to have multi DB Save, and you don't know the number of rows to update, the current DB Save doesn't support multi record save.

You will need to write an update SQL statement and execute it using a "Custom code" action.

If you have some patience then you may contact me through the "Contact Us" page and I can try to add the "multi save" feature and send a new patch to you, but development time varies based on my schedule🙂

Regards,
Max

No problem Max,
I will try to find a work-around in the meantime.

Hi gabedaly,

If you change the input name to be an array value then it should be simple to code the save:

<input type='text' name='ac[<?php echo $detail['accountnumber']; ?>]' value='<?php echo $detail['balance']; ?>' class='pound' id='<?php echo $detail['accountnumber']; ?>' onblur='sum()' />

<?php
$values = array();
foreach ( $form->data['ac'] as $k => $v ) {
  $values[] = " ( {$k}, {$v} ) ";
}
$values = implode(', ', $values);
$db =& JFactory::getDBO();
$query = "
  INSERT 
    INTO `#__table_name`
      (`account_number`, `amount`)
    VALUES {$values}
    ON DUPLICATE KEY 
      UPDATE `#__table_name`
        SET `amount` = '{$amount}'
        WHERE `account_number` = '{$account_number}' ;
";
$db->setQuery($query);
$db->query();
?>

Bob

Hey Bob,

I modified your code a little to try and get it to work.

 <?php
    $values = array();
    foreach ( $form->data['ac'] as $k => $v ) {
      $values[] = " ( {$k}, {$v} ) ";
}
    $values = implode(', ', $values);
    $db =& JFactory::getDBO();
$query = "
      UPDATE my_table
        SET balance = '{$v}'
        WHERE accountnumber = '{$k}' ;
";
$db->setQuery($query);
$db->query();
?>

But it is only updating one account (seems like the last one).

Ultimately what I am trying to do is take the input field amount and subtract that from the existing balance field and then save the new amount to the balance field.

I am guessing I could do something like
$newbalance = $balance - $v;
$form->data['balance'] = $newbalance;
but I don't know how to update each balance per record.

PS Thanks for always trying to help!

Gabe

Hi gabedaly,

Hmmm . . . the bit you've taken out was the part that should have let it save multiple records. Now there is just a single save of the last entry.

Bob

Hey Bob,

I have tried many different configurations of your code to try and get it to work but I just don't know enough to make it happen.

    <?php
    $values = array();
    foreach ( $form->data['ac'] as $k => $v ) {
      $values[] = " ( {$k}, {$v} ) ";
}
    $values = implode(', ', $values);
    $db =& JFactory::getDBO();
$query = "
INSERT
    INTO mtg_customer_info
      ('accountnumber', 'balance') //I tried changing this with and without single quotes and also changed from amount to balance???
    VALUES {$values}
    ON DUPLICATE KEY 
      UPDATE mtg_customer_info
        SET balance = '{$balance}'//I also tried changing this from $amount
        WHERE accountnumber = '{$accountnumber}' ;
";
$db->setQuery($query);
$db->query();
?>

On your original code you have "$amount" but I don't know where that comes into play. Also I was assuming that "account_number" was a typo. So I tried changing those two to $balance and accountnumber.
What am I doing wrong?

Hi gabedaly,

Well, these two (`account_number`, `amount`) are column names and so should have backticks `` not single quotes - though no quotes will probably work.

It does look like I messed up the query though. Please try this version:

$query = "
  INSERT
    INTO `#__table_name`
      (`account_number`, `amount`)
    VALUES {$values}
    ON DUPLICATE KEY
      UPDATE `amount` = VALUES(`amount`);
";

If it works I'll go back and update my original post.

Bob

Hey Bob,
I'll try but can you please tell me how "amount" comes into play? Is this suppose to be "balance"? (Balance is a column name in the db.) Just trying to learn. Thanks

Hi gabedaly,

It should be whatever the correct column name is :-(

Bob

Hey Bob,

I tried this to no avail

$query = "
  INSERT
    INTO `my_table`
      (`accountnumber`, `balance`)
    VALUES {$values}
    ON DUPLICATE KEY
      UPDATE `balance` = VALUES(`balance`);
";

Should it be $v instead of the `balance`?

Hi gabedaly,

$values is correct there.

Exactly what code do you now have? And did you change the input name to the array version?

Bob

Hey Bob,

This is what I have now in a custom code box as the first onSubmit event

    <?php
    $values = array();
    foreach ( $form->data['ac'] as $k => $v ) {
      $values[] = " ( {$k}, {$v} ) ";
}
    $values = implode(', ', $values);
    $db =& JFactory::getDBO();
$query = "
  INSERT
    INTO `my_table`
      (`accountnumber`, `balance`)
    VALUES {$values}
    ON DUPLICATE KEY
      UPDATE `balance` = VALUES(`balance`);
";
$db->setQuery($query);
$db->query();
?>

The input is as an array

<input type='text' name='ac[<?php echo $detail['accountnumber']; ?>]' value='<?php echo $detail['balance']; ?>' id='<?php echo $detail['accountnumber']?>'class='pound' onblur='sum()' />

Hey Bob, I know it's Sunday so please no pressure at all. Thank you!!

Hi gabedaly,

I ran a test and this code updates multiple records OK here. Here's the query that was generated

INSERT
INTO `#__chronoforms_data_account` (`accountnumber`, `balance`)
VALUES ( 1, 199 ) , ( 2, 1048 ) , ( 3, 1899 ) , ( 4, 9899 ) , ( 5, 5655 )
ON DUPLICATE KEY UPDATE `balance` = VALUES(`balance`);

Bob

Hey Bob,

Sorry to be a pain...I'm getting the same output via echo but it's not updating the database. The `accountnumber` is the primary key but all of the account numbers have dashes in them like a phone number. Could this be an issue since it is not only numbers?

Thanks for looking into this,
Gabe

Hi Gabe,

Yes it could, please change this line to add the extra quotes

$values[] = " ( '{$k}', {$v} ) ";

Bob

Hey Bob,
That was the ticket😀
Now, how can I perform a basic calculation before it saves to the db.
I need to subtract the payment amount (from the input field) from the balance. I'll try playing around but....

I figured it out, it was very basic actually.

`balance` = `balance` - Values(`balance`)

I didn't think MYSQL would do that but it does😀
Now to try and get it to email nicely.

Bob, thanks again for always helping.
Gabe

I can't figure out how to display the results with a custom code or email the results from the arrays.
This is what I have tried and failed

<table border='2' cellpadding='5' table id='countit'  >
<tr><th>Service Address</th><th>Account Number</th><th>Status</th><th>Balance</th></tr>
<?php
foreach ($form->data['ea'] as $k=>$v):
?>
<?php
foreach ($form->data['details'] as $detail):
?>
<tr><td>
<?php echo $detail['servicestreet1'];?>
</td><td>
<?php echo $detail['accountnumber'];?>
</td><td>
<?php echo $detail['jobstatus'];?>
</td><td>
<?php echo $v;?></td></tr>
<?php endforeach; ?>
<?php endforeach; ?>
</table>
</br>
</br>

Any suggestions?
Thank you

Hi Gabe,

The custom code should be AFTER the DBMRL, you should also use a debugger to see how the data is structured under the form->data array.

Regards,
Max