Pull data from MySQL database and generate report

[michaelslamet]

Not sure how to do this:

I'm going to pull out data from MySQL database sorted on username and
save it to a variable, like this:

Local $data = ""
$data &= "user1 :: 1" & @CRLF
$data &= "user1 :: 0" & @CRLF
$data &= "user1 :: 1" & @CRLF
$data &= "user1 :: 0" & @CRLF
$data &= "user1 :: 1" & @CRLF
$data &= "user1 :: 1" & @CRLF
$data &= "user1 :: 1" & @CRLF
$data &= "user2 :: 1" & @CRLF
$data &= "user2 :: 0" & @CRLF
$data &= "user2 :: 1" & @CRLF
$data &= "user2 :: 0" & @CRLF
$data &= "user2 :: 1" & @CRLF
$data &= "user2 :: 0" & @CRLF
$data &= "user2 :: 1" & @CRLF
$data &= "user2 :: 1" & @CRLF
$data &= "user2 :: 1" & @CRLF
$data &= "user2 :: 1" & @CRLF
$data &= "user2 :: 0" & @CRLF
$data &= "user3 :: 1" & @CRLF
$data &= "user3 :: 1" & @CRLF
$data &= "user3 :: 1" & @CRLF
$data &= "user3 :: 1" & @CRLF
$data &= "user3 :: 0" & @CRLF

From above data, I would like my script to generate a report like this:

User1
Total record = 7
Percentage 1 versus 0 = 5 / 7 x 100% = 71.42%

User2
Total record = 11
Percentage 1 versus 0 = 7 / 11 x 100% = 63.63%

User3
Total record = 5
Percentage 1 versus 0 = 4 / 5 x 100% = 80.00%

Of course "user1", "user2", "user3" could be anything like "Bill", "Susan" and
not limited to 3 users.

I am even not sure should I pull the MySQL data and save it to a variable as a long string (like above)
or as an array, or should I use MySQL command to count total record for each user.

Totally lost on how to do it.

[jchd]

Use SQL to grab information in arrays and AutoIt code to format it the way you want.

[michaelslamet]

Use SQL to grab information in arrays and AutoIt code to format it the way you want.

 

I solved it this way. What are another ways that is faster, more efficient and shorter code?

expandcollapse popup

#include <array.au3>

Local $sData = ""

$sData  = "user1 :: 1" & @CRLF
$sData &= "user1 :: 0" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user1 :: 0" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user1 :: 1" & @CRLF
$sData &= "user2 :: 1" & @CRLF
$sData &= "user2 :: 0" & @CRLF
$sData &= "user2 :: 1" & @CRLF
$sData &= "user2 :: 0" & @CRLF
$sData &= "user2 :: 1" & @CRLF
$sData &= "user2 :: 0" & @CRLF
$sData &= "user2 :: 1" & @CRLF
$sData &= "user2 :: 1" & @CRLF
$sData &= "user2 :: 1" & @CRLF
$sData &= "user2 :: 1" & @CRLF
$sData &= "user2 :: 0" & @CRLF
$sData &= "user3 :: 1" & @CRLF
$sData &= "user3 :: 1" & @CRLF
$sData &= "user3 :: 1" & @CRLF
$sData &= "user3 :: 1" & @CRLF
$sData &= "user3 :: 0" & @CRLF

Local $aData = StringSplit($sData, @CRLF, 3)
Local $sSaveUser = "", $sOut = "", $i0, $i1 = 0

For $a = 0 To UBound($aData) - 1
    If $sSaveUser = StringLeft($aData[$a], StringInStr($aData[$a], ' ::') - 1) Then
        If StringMid($aData[$a], StringInStr($aData[$a], ':: ') + 3) = "1" Then
            $i1 += 1
        Else
            $i0 += 1
        EndIf
    Else
        If $a <> 0 Then $sOut &= $sSaveUser & @CRLF & "sum of 1 = " & $i1 & @CRLF & "sum of 0 = " & $i0 & @CRLF & @CRLF
        $sSaveUser = StringLeft($aData[$a], StringInStr($aData[$a], ' ::') - 1)
        $i0 = 0
        $i1 = 0
        If StringMid($aData[$a], StringInStr($aData[$a], ':: ') + 3) = "1" Then
            $i1 += 1
        Else
            $i0 += 1
        EndIf
    EndIf
    If StringLen($aData[$a]) = 0 Then ExitLoop
Next

ConsoleWrite($sOut)

The result is:

user1

sum of 1 = 9

sum of 0 = 2

user2

sum of 1 = 7

sum of 0 = 4

user3

sum of 1 = 4

sum of 0 = 1

 

Edited January 5, 2014 by michaelslamet

[jchd]

What I said, but I'm not familiar enough with MySQL to provide code.

[michaelslamet]

What I said, but I'm not familiar enough with MySQL to provide code.

 

In fact, I solved it using your advice

Nevermind about the MySQL part. I wonder about the "AutoIT part". Although my code run just fine, I'm not confident it's the best way to do it.

That is why I'm asking for advices, suggestions, codes to tweak it.

So, please lead me to the light

[jchd]

Post your code please

[michaelslamet]

Post your code please

 

My code is on post #3, please scroll down to see it

[kylomas]

ms,

Your code runs 100,000 entries in 1.2 seconds.  Seems pretty fast to me

[michaelslamet]

ms,

Your code runs 100,000 entries in 1.2 seconds.  Seems pretty fast to me

 

Good to hear that

As you might see, I learn from you from >this post and borrow the logic (and the vars!)

Anyway, I always wonder, cant we shorten this code:

If StringMid($aData[$a], StringInStr($aData[$a], ':: ') + 3) = "1" Then
    $i1 += 1
Else
    $i0 += 1
EndIf

 

To only 1 line, something like:

If StringMid($aData[$a], StringInStr($aData[$a], ':: ') + 3) = "1" Then $i1 += 1 Else $i0 += 1 EndIf

 

It doesn't work with 3.8.8.1, but why? Why dont we make it run at 3.3.10 ? Because writing it that way is a bad practice?