Autoit and MySql assistance requested

[mrtweaver]

Ok here is what i am attempting to do. I want to take the table phase3, read into an array any value from the column stored where the value complete = 0, then using this array write another Sql code where it will located in phase3 the stored number where it is equal to the first array then it will do something, then equal to the second array and do something, then equal to the third array and do something, and so on until the array has complete running. This is the code that i have written so far.

expandcollapse popup

Func Appendtrayinfo()


$aOk = _EzMySql_GetTable2d("SELECT stored FROM phase3 WHERE complete = 0;")
$error=@error
If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error)

;_ArrayDisplay($aOk, "2d Array Names of certain eyecolour")

For $i = 1 To _EzMySql_Rows() step 1
    ConsoleWrite(" ID = " & $aOk[$i][0] & @CRLF)
    $id = $aOk[$i][0]
    runquery($id)
Next

    ;ConsoleWrite("Tray = " & $tray & " Pallet = " & $pal & @CRLF)


Exit
EndFunc

Func runquery($id)

    Local $sSql= "select a.stored, a.linking, b.trayact,b.traytype, IFNULL(b.pallet,a.backed) from ezmysqltest.phase3 a join ezmysqltest.cqt b on a.linking = b.trayid where a.stored = " & $id

    IF NOT _EzMySql_Query ($sSql & ";") Then
        $error = _EzMySql_ErrMsg()
        AddAppLogEntry(" Database Error Occurred " & $error ,"0xFFFF00")

    Else
        $aok = _EzMySql_FetchData()

        $tray = $aok[2]
        $ttype = $aok[3]
        $pal= $aok[4]
        ConsoleWrite("Tray = " & $tray & " Pallet = " & $pal & @CRLF)
        ;Sleep(1000)
    EndIf

EndFunc

The problem seems to be when i run the second query the one inside the function runquery it interferes with the query that created the array because when i add in the call to the function runquery it will run thru the look 1 time then come back with an array out of bounds type error. I thought maybe be seperating it and using the word local it would not interfere. So what part of the code do i have that is incorrect?

I did initially try to do the query and the updates on phase3 from just a single query but it would take like 18 min or so to run and that is unacceptable.

So in a nut shell i will have two tables, the first table is Phase3 in that table i will have two values they are stored and linking, stored is the key index field. Then i have another table called csm that contains linking, trayid, pallet, and traytype. I want to update the blank fields tray and pallet located in phase3 with the values from csm. The initial query i wrote was:

Update phase3 as a join csm as b on a.linking = b.linking set a.tray=b.tray, a.pallet=b.pallet where complete = 0;

But like i said to run this query took like 18 min so was trying to break it down by first loading the stored numbers where complete = 0 into an array then since this column is key index then run my query against it using the array values and update accordingly. With out the additional SQL query in the runquery if i write to console it does what it is suppose to. Its only when i add in the additonal query i get the array out of bounds error. Hope this is clear and someone can help.

 

Thanks and have a great day.