multi-record insert to mySQl from (flash) PHP help?

[whispers]

Hey gang-

I am trying to insert multiple records into my DB and am having trouble..

1 record insert seemed to work fine.. then I needed to band-aid fix for multiple records being inserted..

I use a LoadVards object form FLASH to sendAndLoad and bunch of data.

most of this data is 1 piece of data..

ie:

loadvars_obj.name = name.text;
loadvars_obj.id = id.text;

etc..etc..

however.. 2 pieces are "ARRAYS". I am trying to send to PHP..

I realize these are transmitted as comma separated strings..

and I implode them on the PHP side to arrays..


here I am testing with hardcoded strings: (to quickly test rest of insert script)

$procDate2='2012-10-16,2012-11-16,2012-12-16';
$procDate = explode(',', $procDate2);

$paymentAmount2='250,250,25';
$paymentAmount = explode(',', $paymentAmount2);


I guess my logic might be flawed? but I though I could use 1 query and 'build' up the values to be inserted by looping and concatenating the VALUE (string variable) based on the array length.. and then using that 'as' the VALUE in the query command? and get 1 status/return for the whole thing? (success or not?)


like so:

function insertRecord(){

    echo('INSERT CALLED<br><br>');
    
    echo('INSERTING NEW RECORD<br><br>');
    echo('TOTAL RECORDS: ' . count($paymentAmount) .'<br>');

    //for($i=0; $i < count($_POST['paymentAmount']); $i++) {
    for($i=0; $i < count($paymentAmount); $i++) {
        
        $allValues .= "('','$subDate','$collectorID','$pFirst','$pMiddle','$pLast','$pAddress','$pCity','$pState','$pZip','$hPhone','$wPhone','$dFirst','$dMiddle','$dLast','$maiden','$clientNum','$debtorNum','$procDate[i]','$paymentType','$paymentAmount[i]','$bankName','$checkNum','$routingNum','$bankAccountNum','$ccNum','$securityNum','$expDate','$creditType','$accountType'),";
    }  
    echo($allValues);
        
    $insert = mysql_query("INSERT INTO payments(transID, subDate, collectorID, pFirst, pMiddle, pLast, pAddress, pCity, pState, pZip, hPhone, wPhone, dFirst, dMiddle, dLast, maiden, clientNum, debtorNum, procDate, paymentType, paymentAmount, bankName, checkNum, routingNum, bankAccountNum, ccNum, securityNum, expDate, creditType, accountType) VALUES" . $allValues);
    
    $status2 = $insert;
    sendResults();
    
}


function sendResults(){
    global $status2;
    echo "&status2=$status2";
} 

thats really the gist of it...


1.) not sure WHY building the $allValues var isnt working (had to declare it at top of script? and other messages/warnings)
2.) I cant seem to grab dynamic values form the array when building the VALUE part of the query.. (I put in [0] instead of [i] as it throws errors..)

keep seeing/getting alot of these too: (when trying to grab arrayIndex in the VALUE string building line..??

Notice: Undefined index: i in C

bull script..

<?php


$allValues = '';
$subDate='2012-10-16';
$collectorID='JMD';

$pFirst='John';
$pMiddle='M';
$pLast='Doe';

$pAddress='1313 mocking Bird LN';
$pCity='Kenosha';
$pState='WI';
$pZip='53140';
$hPhone='4145551212';
$wPhone='';

$dFirst='John';
$dMiddle='M';
$dLast='Doe';
$maiden='Johnson';

$clientNum='AH10N';
$debtorNum='0000113532';


$paymentType='Credit';

$procDate2='2012-10-16,2012-11-16,2012-12-16';
$procDate = explode(',', $procDate2);


$paymentAmount2='250,250,25';
$paymentAmount = explode(',', $paymentAmount2);


$bankName='Bank of America';
$checkNum='0012';
$routingNum='123456789';
$bankAccountNum='00012345602018871';

$ccNum='';
$SecurityNum='';
$expDate='';
$creditType='';
$accountType='';

$status2 = false;



$myDB = mysql_connect('localhost', 'root', '') or die('Connection failed!');

mysql_select_db('fac_paymentdb', $myDB);

function insertRecord(){

    echo('INSERT CALLED<br><br>');
    
    echo('INSERTING NEW RECORD<br><br>');
    echo('TOTAL RECORDS: ' . count($paymentAmount) .'<br>');

    //for($i=0; $i < count($_POST['paymentAmount']); $i++) {
    for($i=0; $i < count($paymentAmount); $i++) {
        
        $allValues .= "('','$subDate','$collectorID','$pFirst','$pMiddle','$pLast','$pAddress','$pCity','$pState','$pZip','$hPhone','$wPhone','$dFirst','$dMiddle','$dLast','$maiden','$clientNum','$debtorNum','$procDate[i]','$paymentType','$paymentAmount[i]','$bankName','$checkNum','$routingNum','$bankAccountNum','$ccNum','$securityNum','$expDate','$creditType','$accountType'),";
    }  
    echo($allValues);
        
    $insert = mysql_query("INSERT INTO payments(transID, subDate, collectorID, pFirst, pMiddle, pLast, pAddress, pCity, pState, pZip, hPhone, wPhone, dFirst, dMiddle, dLast, maiden, clientNum, debtorNum, procDate, paymentType, paymentAmount, bankName, checkNum, routingNum, bankAccountNum, ccNum, securityNum, expDate, creditType, accountType) VALUES" . $allValues);
    
    $status2 = $insert;
    sendResults();
    
}


function sendResults(){
    global $status2;
    echo "&status2=$status2";
}

what am I doing wrong here?

thanks

[Musicman]

Hi,

I just tried

insert into test1 (nam) values ('John'), ('Mark'), ('Mary');

I guess it would not like an extra comma after the last set of values, so you should probable code

for($i = 0 ; $i < .... ; $i++)
{   if($i) $allvalues .= ',';
    $allvalues .= ....
}

You have some $paymentAmount[i] around, this should be $paymentAmount[$i]

Musicman

[whispers]

ahh...


perfect! thank you Musicman.

it was that last comma that was causing the major problem(s)..

also thanks for telling me how to us the variable for the array index properly...

-whispers