Assign each value from an Array queried from a database table to each variables

[PnD]

Dear all,

Currently, I am running a _EzMySql_GetTable2d function below 

$result = _EzMySql_GetTable2d("select externalID,locationName,street1,city from orders where Id ='1113602'")

_ArrayDisplay($result)

and this is what I got 

My question is that how would I able to assign the results above to the following variables 

Local $Ref = the result from Col 0, Row1

Local $Euname = the result from Col 1, Row1

Local $street = the result from Col 2, Row1

Local $city = the result from Col3, Row1

Your help is greatly appreciated! Thank you. 

[pixelsearch]

Hi PnD,
I would go on something like this :

$result = _EzMySql_GetTable2d("select externalID,locationName,street1,city from orders where Id ='1113602'")

Local $iRet = _ArrayDisplay($result)

If $iRet And Ubound($result) > 1 Then
    Local $Ref = $result[1][0]
    Local $Euname = $result[1][1]
    Local $street = $result[1][2]
    Local $city = $result[1][3]
EndIf

Maybe also check the Array got 2 dimensions and 4 columns (in case you won't always use _ArrayDisplay)

[Nine]

But the real question is why do you need to assign array result to simple variables ?  I do not think it is necessary as you can access to individual cell by specifying its indexes.  Make sure you understand arrays before spending lots of time creating variables uselessly. 

[mLipok]

@PnD  to  forum.

Why to assign variables at all ?
Did you found what Enum are for ?

_Example()

Func _Example()
    Local Enum _
            $_COMMONNAME_C0_externalID, _
            $_COMMONNAME_C1_locationName, _
            $_COMMONNAME_C2_steet1, _
            $_COMMONNAME_C3_city, _
            $_COMMONNAME_MAX
            
    Local $a_Locations = _EzMySql_GetTable2d("select externalID,locationName,street1,city from orders where Id ='1113602'")
    _ArrayDisplay($a_Locations)
    For $i_row_idx =1 To UBound($a_Locations) -1
        If StringInStr($a_Locations[$i_row_idx][$_COMMONNAME_C1_locationName], 'Christopher') Then
            ConsoleWrite($a_Locations[$i_row_idx][$_COMMONNAME_C0_externalID] & @CRLF)
            ConsoleWrite($a_Locations[$i_row_idx][$_COMMONNAME_C1_locationName] & @CRLF)
            ConsoleWrite($a_Locations[$i_row_idx][$_COMMONNAME_C2_steet1] & @CRLF)
            ConsoleWrite($a_Locations[$i_row_idx][$_COMMONNAME_C3_city] & @CRLF)
            
        EndIf
    Next
    
EndFunc

 

Edited April 13, 2021 by mLipok

[PnD]

Hi PixelSearch, Nine and MLipok,

Thank you so much for your quick reply and provided solutions. The reason that I choose to assign the results to variables so that I can parse those variable to  matched fields on a html file so later I can print that html out (such as Label which has Eu's name, address and so on)

From mLipok's solution, i have modify it to be

    Local Enum _
            $_COMMONNAME_C0_externalID, _
            $_COMMONNAME_C1_locationName, _
            $_COMMONNAME_C2_steet1, _
            $_COMMONNAME_C3_city, _
            $_COMMONNAME_MAX

    Local $a_Locations = _EzMySql_GetTable2d("select externalID,locationName,street1,city from orders where Id ='1113602'")
    _ArrayDisplay($a_Locations)
    For $i_row_idx =1 To UBound($a_Locations) -1
            local $Ref= ($a_Locations[$i_row_idx][$_COMMONNAME_C0_externalID])
            local $Euname=($a_Locations[$i_row_idx][$_COMMONNAME_C1_locationName])
            local $street= ($a_Locations[$i_row_idx][$_COMMONNAME_C2_steet1])
            local $city= ($a_Locations[$i_row_idx][$_COMMONNAME_C3_city])
    Next

MsgBox($MB_SYSTEMMODAL, "Here we go", $Ref& @CRLF & $Euname &@CRLF & $street & @CRLF & $city)

and it works great!

From PixelSearch's solution, it also works perfectly as well

If $iRet And Ubound($result) > 1 Then
    Local $Ref = $result[1][0]
    Local $Euname = $result[1][1]
    Local $street = $result[1][2]
    Local $city = $result[1][3]
EndIf
MsgBox($MB_SYSTEMMODAL, "Here we go", $Ref& @CRLF & $Euname &@CRLF & $street & @CRLF & $city)

 

Nine is absolutely right. I do not have to assign the value to a variable. Instead, just simply parse $result[1][0], and so on to any field I want to replace in that html file.

However, is it helpful and convenience just to assign Global variables to those values so that we can use those variables for others functions instead of just using $result[1][0], and so on without knowing unique names?

I also have a follow up question since it involves array. Would you please recommend any solutions to shorten the codes below to make it works faster

(I have to filedelete first because filewire only append write)

Local $html = FileRead(@ScriptDir & "\Ship LabelV2.html")
    Local $new_html = StringReplace($html,"EUNAME#",$EUName)
    Local $new_html1= StringReplace($new_html,"#ITIorder#",$ITIOrder)
    Local $new_html2 = StringReplace($new_html1,"#Model#",$TVModel)
    Local $new_html3 = StringReplace ($new_html2,"#Ref#",$ITIRef)
    Local $new_html4 = StringReplace ($new_html3,"#Serial#",$Input1)
    Local $new_html5 = StringReplace ($new_html4,"#Sale#",$Input)

    FileDelete(@ScriptDir & "\Ship LabelV3.html")
    FileWrite(@ScriptDir & "\Ship LabelV3.html",$new_html5)

 

 

 

 

 

 

 

 

[mLipok]

@PnD please read:

https://www.autoitscript.com/wiki/Forum_FAQ#How_can_I_edit_my_post_on_the_forum_.3F

and:

* How to post code on the forum *

and modify your previous post accordingly.

 

[mLipok]

as to your code
Why do you use brackets in this part:

Local $Ref= ($a_Locations[$i_row_idx][$_COMMONNAME_C0_externalID])

I mean instead such form:

Local $Ref= $a_Locations[$i_row_idx][$_COMMONNAME_C0_externalID]

?

Edited April 13, 2021 by mLipok

[PnD]

Hi mLipok,

Thank you for your instruction. I have just repost my quote here as I could not find any edit button from my previous response. If you have the admin right, please help me delete it.

Hi PixelSearch, Nine and MLipok,

Thank you so much for your quick reply and provided solutions. The reason that I choose to assign the results to variables so that I can parse those variable to  matched fields on a html file so later I can print that html out (such as Label which has Eu's name, address and so on)

From mLipok's solution, i have modify it to be

Local Enum _
            $_COMMONNAME_C0_externalID, _
            $_COMMONNAME_C1_locationName, _
            $_COMMONNAME_C2_steet1, _
            $_COMMONNAME_C3_city, _
            $_COMMONNAME_MAX

    Local $a_Locations = _EzMySql_GetTable2d("select externalID,locationName,street1,city from orders where Id ='1113602'")
    _ArrayDisplay($a_Locations)
    
    ; Bracket Removed as typo from previous post.
    
    For $i_row_idx =1 To UBound($a_Locations) -1
            local $Ref= $a_Locations[$i_row_idx][$_COMMONNAME_C0_externalID]
            local $Euname=$a_Locations[$i_row_idx][$_COMMONNAME_C1_locationName]
            local $street= $a_Locations[$i_row_idx][$_COMMONNAME_C2_steet1]
            local $city= $a_Locations[$i_row_idx][$_COMMONNAME_C3_city]
    Next

From PixelSearch's solution, it also works perfectly as well

If $iRet And Ubound($result) > 1 Then
    Local $Ref = $result[1][0]
    Local $Euname = $result[1][1]
    Local $street = $result[1][2]
    Local $city = $result[1][3]
EndIf
MsgBox($MB_SYSTEMMODAL, "Here we go", $Ref& @CRLF & $Euname &@CRLF & $street & @CRLF & $city)

Nine is absolutely right. I do not have to assign the value to a variable. Instead, just simply parse $result[1][0], and so on to any field I want to replace in that html file.

However, is it helpful and convenience just to assign Global variables to those values so that we can use those variables for others functions instead of just using $result[1][0], and so on without knowing unique names?

I also have a follow up question since it involves array. Would you please recommend any solutions to shorten the codes below to make it works faster

(I have to filedelete first because filewire only append write)

Local $html = FileRead(@ScriptDir & "\Ship LabelV2.html")
    Local $new_html = StringReplace($html,"EUNAME#",$EUName)
    Local $new_html1= StringReplace($new_html,"#ITIorder#",$ITIOrder)
    Local $new_html2 = StringReplace($new_html1,"#Model#",$TVModel)
    Local $new_html3 = StringReplace ($new_html2,"#Ref#",$ITIRef)
    Local $new_html4 = StringReplace ($new_html3,"#Serial#",$Input1)
    Local $new_html5 = StringReplace ($new_html4,"#Sale#",$Input)

    FileDelete(@ScriptDir & "\Ship LabelV3.html")
    FileWrite(@ScriptDir & "\Ship LabelV3.html",$new_html5)

 

[mLipok]

30 minutes ago, PnD said:

Thank you for your instruction. I have just repost my quote here as I could not find any edit button from my previous response. If you have the admin right, please help me delete it.

[mLipok]

32 minutes ago, PnD said:

Nine is absolutely right. I do not have to assign the value to a variable. Instead, just simply parse $result[1][0], and so on to any field I want to replace in that html file.

However, is it helpful and convenience just to assign Global variables to those values so that we can use those variables for others functions instead of just using $result[1][0], and so on without knowing unique names?

this is called MAGIC NUMBER

[JockoDundee]

4 hours ago, mLipok said:

Edit option shows up only when you have 5 or more posts.

[PnD]

I see now. Thanks JockoDundee!

[JockoDundee]

On 4/12/2021 at 3:51 PM, PnD said:

$result = _EzMySql_GetTable2d("select externalID,locationName,street1,city from orders where Id ='1113602'")

_ArrayDisplay($result)

and this is what I got 

Question, why are the column names returned in row 0?

[PnD]

On 4/13/2021 at 6:05 PM, JockoDundee said:

Question, why are the column names returned in row 0?

I really have no idea. That's exactly what I got from the query's result.

[JockoDundee]

Whose UDF is this function from:

_EzMySql_GetTable2d

[PnD]

this is the au3 (see attachment) that I used

EzMySql.au3