Read from mysql and write to excel problems

[skybax]

Im using mysql.au3 UDF

i have this code and i read from 5 tables (Sala, tehnician, piesa, cantitate,date) and i want to write the result from $Rezultat_raport_2 to a excel file and i dont know how to make a brake between the results

all the data is writen in only one cell

I have tried @LF and @CRLF with no success

Can someone help me?

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance,$SQLCode_raport_2)
With $TableContents_raport_2
     While Not .EOF
     $Rezultat_raport_2 &= .Fields ("Sala").value &" "&.Fields ("tehnician").value &" "&.Fields ("piesa").value &" "&.Fields ("cantitate").value &" "&.Fields ("data").value & @LF
     .MoveNext
     WEnd
EndWith

[water]

Write the data to an array. Excel has a function to read an array and write the data into cells.

[skybax]

i know that, but to write the data from a array i still net to brake the string of data

[water]

Something like this:

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance,$SQLCode_raport_2)
Global $iRecords = mysql_num_rows($TableContents_raport_2)
Global $aResult[$iRecords+1] = [$iRecords]
Global $iIndex = 1
With $TableContents_raport_2
While Not .EOF
     $aResult[$iIndex] = .Fields ("Sala").value & @TAB & .Fields ("tehnician").value & @TAB & .Fields ("piesa").value & @TAB & .Fields ("cantitate").value & @TAB & .Fields ("data").value
     $iIndex = $iIndex + 1
     .MoveNext
WEnd
EndWith

I'm not sure about property "recordcount" set by MySQL for the number of returned records. Maybe it's just called "count" or something like that.

Edited July 13, 2012 by water

[skybax]

Variable must be of type "Object".:
Global $aResult[$TableContents_raport_2.recordcount+1]
Global $aResult[$TableContents_raport_2^ ERROR

i tried with "count" and i get the same error

[water]

I altered my example above. According to Google you have to use function mysql_num_rows to get the record count.

[skybax]

it still doesn't work

mysql_num_rows function not recognized

[water]

I know nothing about AutoIt's MySQL implementation, so we need another approach.

How many records does the MySQL query return? 100, 1000, 10000, more?

[kpuk]

in php after query need to fetch result. I think here need it too.

I seed in UDF some func named like _fetch_ - try one of it

ps: sorry for my English

[skybax]

for now because is in the test faze it return anly 6 but it will return over 100. If you select to Select * Form Sala it will show all those records and it can grow to over 1000 in a year

[skybax]

I use this made by cdkid

[water]

This code should work for up to 1000 records.

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '" & GUICtrlRead($data_de_la_2) & "' AND '" & GUICtrlRead($data_pana_la_2) & "' AND `tehnician` LIKE '" & GUICtrlRead($tehnician_raport_2) & "' AND `Sala` =" & GUICtrlRead($sala_raport_2) & " ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance, $SQLCode_raport_2)
Global $aResult[1001] = [1000]
Global $iIndex = 1
With $TableContents_raport_2
    While Not .EOF
        $aResult[$iIndex] = .Fields("Sala").value & @TAB & .Fields("tehnician").value & @TAB & .Fields("piesa").value & @TAB & .Fields("cantitate").value & @TAB & .Fields("data").value
        $iIndex = $iIndex + 1
        .MoveNext
    WEnd
EndWith
ReDim $aResult[$iIndex]
$aResult[0] = $iIndex - 1

[skybax]

now it works it splits the result but the @TAB doesn't work

[water]

OK. Then we need a two dimensional array:

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '" & GUICtrlRead($data_de_la_2) & "' AND '" & GUICtrlRead($data_pana_la_2) & "' AND `tehnician` LIKE '" & GUICtrlRead($tehnician_raport_2) & "' AND `Sala` =" & GUICtrlRead($sala_raport_2) & " ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance, $SQLCode_raport_2)
Global $aResult[1001][5] = [[1000, 5]]
Global $iIndex = 1
With $TableContents_raport_2
While Not .EOF
     $aResult[$iIndex][0] = .Fields("Sala").value
$aResult[$iIndex][1] = .Fields("tehnician").value
$aResult[$iIndex][2] = .Fields("piesa").value
$aResult[$iIndex][3] = .Fields("cantitate").value
$aResult[$iIndex][4] = .Fields("data").value
     $iIndex = $iIndex + 1
     .MoveNext
WEnd
EndWith
ReDim $aResult[$iIndex][5]
$aResult[0][0] = $iIndex - 1

Edited July 13, 2012 by water

[skybax]

ihet a error here

$aResult[0] = $iIndex - 1

^ ERROR

[water]

Fixed the above example.

[skybax]

$aResult[0][0] = $iIndex - 1 is no good

C:\Program Files\AutoIt3\Include\Excel.au3 (526) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$oExcel.Activesheet.Cells($iStartRow, ($xx - $iIndexBase) + $iStartColumn).Value = $aArray[$xx]

$oExcel.Activesheet.Cells($iStartRow, ($xx - $iIndexBase) + $iStartColumn).Value = ^ ERROR

[water]

Can you post your complete script or least the part where you call _ExcelWriteSheetFromArray?

[skybax]

the whole haves over 4000 lines and its to big to post

expandcollapse popup

Case $arata_raport_2
$contor_2 = 0
If GUICtrlRead($sala_raport_2) >0 Then
$contor_2=$contor_2+1
EndIf
If GUICtrlRead($tehnician_raport_2) >"" Then
$contor_2=$contor_2+10
EndIf
If GUICtrlRead($bifa) =1 Then
$contor_2=$contor_2+100
EndIf

If $contor_2 = 0 Then
$SQLCode_raport_2 =0
EndIf

If $contor_2 = 1 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 10 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 100 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 11 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `Sala` ="&GUICtrlRead($sala_raport_2)&" AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' ORDER BY `data` ASC"
EndIf
If $contor_2 = 101 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 110 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 111 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
EndIf

If $contor_2 > 0 Then
$TableContents_raport_2 = _Query($SQLInstance,$SQLCode_raport_2)
Global $aResult[1001][5] = [[1000, 5]]
Global $iIndex = 1
With $TableContents_raport_2
While Not .EOF
;~ $Rezultat_raport_2 &= .Fields ("Sala").value &" "&.Fields ("tehnician").value &" "&.Fields ("piesa").value &" "&.Fields ("cantitate").value &" "&.Fields ("data").value & @LF
$aResult[$iIndex][0] = .Fields("Sala").value
$aResult[$iIndex][1] = .Fields("tehnician").value
$aResult[$iIndex][2] = .Fields("piesa").value
$aResult[$iIndex][3] = .Fields("cantitate").value
$aResult[$iIndex][4] = .Fields("data").value
$iIndex = $iIndex + 1
.MoveNext
WEnd
EndWith
EndIf
ReDim $aResult[$iIndex][5]
$aResult[0][0] = $iIndex - 1
Local $WExcel = _ExcelBookNew()
_ExcelWriteArray($WExcel, 1, 1, $aResult)

Edited July 13, 2012 by skybax

[water]

Replace _ExcelWriteArray with

_ExcelWriteSheetFromArray($WExcel, $aResult, 1, 1, 0, 0)