notta Posted October 2, 2008 Share Posted October 2, 2008 (edited) With the help from Weapon a while back and learning from dmob's cash register program I have been incorporating the use of SQLite to display some data, but I'm a little confused. It seems to me that I still need to create large arrays and test for a lot of conditions if I want to display my data. Let me give an example Let's say my data looks this: firstname, lastname, building,model,room,.....25 . . . . . 1000 lines Let's say I have a fixed(10) amount of models to choose from, I still need to have case statements for each model to build the new table, correct? I have 25 columns, but I'm trying to write only 4 specific columns to a web page. expandcollapse popupWhile _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK $ProdArr[$d][0] = $aRow[0] $ProdArr[$d][1] = $aRow[1] $ProdArr[$d][2] = $aRow[2] $ProdArr[$d][3] = $aRow[3] $ProdArr[$d][4] = $aRow[4] $ProdArr[$d][5] = $aRow[5] $ProdArr[$d][6] = $aRow[6] $ProdArr[$d][7] = $aRow[7] $ProdArr[$d][8] = $aRow[8] $ProdArr[$d][9] = $aRow[9] $ProdArr[$d][10] = $aRow[10] $ProdArr[$d][11] = $aRow[11] $ProdArr[$d][12] = $aRow[12] $ProdArr[$d][13] = $aRow[13] $ProdArr[$d][14] = $aRow[14] $ProdArr[$d][15] = $aRow[15] $ProdArr[$d][16] = $aRow[16] $ProdArr[$d][17] = $aRow[17] $ProdArr[$d][18] = $aRow[18] $ProdArr[$d][19] = $aRow[19] $ProdArr[$d][20] = $aRow[20] $ProdArr[$d][21] = $aRow[21] $ProdArr[$d][22] = $aRow[22] $ProdArr[$d][23] = $aRow[23] $ProdArr[$d][24] = $aRow[24] $ProdArr[0][0] += 1 $d += 1 WEnd $modelColumns = 'lastname,firstname,model,computername' $SQL1 = StringFormat("CREATE TABLE model (%s);",$modelColumns) _SQLite_Exec(-1,$SQL1) ;Build table to gx280 For $X = 1 to Ubound($ProdArr)-1 if $ProdArr[$X][3] = "GX280" Then ;$ProdArr[$X][3] = "GX150" Then $SQL1 = _SQLite_Exec(-1,"INSERT INTO model (lastname, firstname, model, computername) VALUES ('" & $prodArr[$X][0] & "', '" & $prodArr[$X][1] & "', '" & $prodArr[$X][3] & "', '" & $prodArr[$X][7] & "' );",$SQL1) EndIf Next _SQlite_Query (-1, "SELECT * FROM model ORDER BY lastname DESC", $SQL1) I'm just learning SQL commands and it seems to be working very well, but it seems like I'm going to have to check for each case. Am I right or am I thinking of this all wrong? Edited October 2, 2008 by notta Link to comment Share on other sites More sharing options...
PsaltyDS Posted October 2, 2008 Share Posted October 2, 2008 (edited) With the help from Weapon a while back and learning from dmob's cash register program I have been incorporating the use of SQLite to display some data, but I'm a little confused. It seems to me that I still need to create large arrays and test for a lot of conditions if I want to display my data. Let me give an example Let's say my data looks this: firstname, lastname, building,model,room,.....25 . 1000 lines Let's say I have a fixed(10) amount of models to choose from, I still need to have case statements for each model to build the new table, correct? I have 25 columns, but I'm trying to write only 4 specific columns to a web page. While _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK $ProdArr[$d][0] = $aRow[0] $ProdArr[$d][1] = $aRow[1] $ProdArr[$d][2] = $aRow[2] $ProdArr[$d][3] = $aRow[3] $ProdArr[$d][4] = $aRow[4] $ProdArr[$d][5] = $aRow[5] $ProdArr[$d][6] = $aRow[6] $ProdArr[$d][7] = $aRow[7] $ProdArr[$d][8] = $aRow[8] $ProdArr[$d][9] = $aRow[9] $ProdArr[$d][10] = $aRow[10] $ProdArr[$d][11] = $aRow[11] $ProdArr[$d][12] = $aRow[12] $ProdArr[$d][13] = $aRow[13] $ProdArr[$d][14] = $aRow[14] $ProdArr[$d][15] = $aRow[15] $ProdArr[$d][16] = $aRow[16] $ProdArr[$d][17] = $aRow[17] $ProdArr[$d][18] = $aRow[18] $ProdArr[$d][19] = $aRow[19] $ProdArr[$d][20] = $aRow[20] $ProdArr[$d][21] = $aRow[21] $ProdArr[$d][22] = $aRow[22] $ProdArr[$d][23] = $aRow[23] $ProdArr[$d][24] = $aRow[24] $ProdArr[0][0] += 1 $d += 1 WEnd Most would just do: While _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK For $n = 0 To 24 $ProdArr[$d][$n] = $aRow[$n] Next $ProdArr[0][0] += 1 $d += 1 WEnd $modelColumns = 'lastname,firstname,model,computername' $SQL1 = StringFormat("CREATE TABLE model (%s);",$modelColumns) _SQLite_Exec(-1,$SQL1) ;Build table to gx280 For $X = 1 to Ubound($ProdArr)-1 if $ProdArr[$X][3] = "GX280" Then ;$ProdArr[$X][3] = "GX150" Then $SQL1 = _SQLite_Exec(-1,"INSERT INTO model (lastname, firstname, model, computername) VALUES ('" & $prodArr[$X][0] & "', '" & $prodArr[$X][1] & "', '" & $prodArr[$X][3] & "', '" & $prodArr[$X][7] & "' );",$SQL1) EndIf Next _SQlite_Query (-1, "SELECT * FROM model ORDER BY lastname DESC", $SQL1) I'm just learning SQL commands and it seems to be working very well, but it seems like I'm going to have to check for each case. Am I right or am I thinking of this all wrong? Each case of what? If you don't like assembling the 2D array yourself, you could use _SQLite_GetTable2D() vice _SQlite_Query() and _SQLite_FetchData(). Edited October 2, 2008 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
zorphnog Posted October 2, 2008 Share Posted October 2, 2008 We need a little more information of the overall picture. What are these 'cases' you are speaking of? What are you trying to accomplish? The great thing about using sql is that you can use it to do most of the 'heavy lifting'. You don't need separate tables for each format, you just need to use proper queries on your existing table. Link to comment Share on other sites More sharing options...
notta Posted October 2, 2008 Author Share Posted October 2, 2008 (edited) I'm sure the problem is that I don't know SQL enough yet. New example, here is the data once again: buildings could range from 1 - 25 and that's it. expandcollapse popupDim $columns = 'lastName,firstname,phone,building,model,serial,domain,comment' _fileReadToArray("data.csv",$data);for simplicity all my data is in a csv file in the form lastName,firstname,phone,building,model,serial,domain. 1 series like this per line. Dim $prodArr[$data[0] + 1][7]; Create 2 dimensional array [number of machines][7 columns] $ProdArr[0][0] = 1; Increment [0][0] counter element for $k = 1 To $data[0];Loop through each line (single csv file) Local $temp = "" $tempArray = "" $tempArray = StringSplit($data[$k],",");create temporary array for the current line(single csv file) and split on the comma for $l = 1 To 25 if $tempArray[$l] = "" Then $tempArray[$l] = "-------";if any element is empty filll with --------- to avoid SQL issues $temp &= $tempArray[$l] & ",";7 columns, but only keep 6 since the comments column was left blank a lot of time. Was causing SQL issues. Next $temp = StringTrimRight($temp,1); Trim off the comma $data[$k] = $temp;same line as when we started except for empty cells were filled, date was adjusted into the format needed, and comment element was stripped off Next _SQLite_Startup() _SQLite_Open() ;Create table $SQL = StringFormat("CREATE TABLE tblData (%s);",$columns);creates the table and sets the columns _SQLite_Exec(-1,$SQL) For $X = 1 to Ubound($data)-1 ;Escape single quotes $result = StringReplace($data[$X],"'", "''");if any lines contain ' then another ' is added to escape it ;Encapsulate all values with single quotes $result = StringRegExpReplace($result,"[^,]+", "'$0'") $SQL = StringFormat("INSERT INTO tblData VALUES (%s);",$result) _SQLite_Exec(-1,$SQL) Next $d = 1 _SQlite_Query (-1, "SELECT * FROM tblData ORDER BY building DESC", $SQL) While _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK;run the insert statement on each row $ProdArr[$d][0] = $aRow[0] $ProdArr[$d][1] = $aRow[1] $ProdArr[$d][2] = $aRow[2] $ProdArr[$d][3] = $aRow[3] $ProdArr[$d][4] = $aRow[4] $ProdArr[$d][5] = $aRow[5] $ProdArr[0][0] += 1;increment the counter of the array $d += 1;the row number Wend _SQLite_QueryFinalize($SQL) _buildingTable();Create table the listing the buildings Func _buildingTable() $buildingColumns = 'lastName,firstname,phone,building,model,serial,domain' $SQL1 = StringFormat("CREATE TABLE buildings (%s);",$buildingColumns) _SQLite_Exec(-1,$SQL1) For $X = 1 to Ubound($ProdArr)-1 if $ProdArr[$X][3] = "building 10" Then;create table for building 10 $SQL1 = _SQLite_Exec(-1,"INSERT INTO building (lastname, firstname,building,model) VALUES ('" & $prodArr[$X][0] & "', '" & $prodArr[$X][1] & "', '" & $prodArr[$X][3] & "', '" & $prodArr[$X][4] & "' );",$SQL1) [b];Only want to display these 4 columns[/b] EndIf Next _SQlite_Query (-1, "SELECT * FROM buidlings ORDER BY model DESC", $SQL1);only going to be machines for building 10 so sort by model $y = 1 While _SQLite_FetchData ($SQL1, $aRow1) = $SQLITE_OK $sText = $aRow1[0] & "," & $aRow1[1] & "," & $aRow1[2] & "," & $aRow1[3] $text = StringSplit($sText,",") If $SBUDebugIt Then ConsoleWrite(StringFormat(" %-2s %-2s %-30s %-8s %-10s %-5s %-5s %-5s %-5s %-5s",$y, $aRow1[0], $aRow1[1], _ $aRow1[2], $aRow1[3]) & @CR) EndIf $y += 1 WEnd _SQLite_QueryFinalize($SQL1) EndFunc So I want to create a web page that shows all the computers I have listed for each buidling. So create a page for each building, but it seems I have to create a table for each building. I know I'm looking at this wrong, so I could use some help. Right now I'm just using straight HTMl, but I'll change that later. [Edit] I guess after reading this it's still difficult for people to understand. If my data is sorted by building and there are 25 buildings the huge array will be sorted, but how can I pull out the data for specific buildings? lastName,firstname,phone,building,model,serial,domain day,ron,444-3433,1,gx280,34343,home davi,asdd,444-3433,1,gx150,42343,home sdsd,asd,444-3433,1,gx150,34366,home sad,adas,444-3433,2,gx240,64334,home asda,adasd,444-3433,2,gx260,43534,home dsfd,asd,444-3433,2,gx620,12331,home sad,asd,444-3433,2,gx260,56354,home adas,asd,444-3433,2,gx280,45313,home asdaron,sad,444-3433,3,gx280,34521,home asdas,adas,444-3433,3,gx240,74344,home asd,asd,444-3433,4,gx240,43542,home asdhdf,dfd,444-3433,4,gx250,23423,home What if I want to generate all page that just lists machine in building 2? I want to also only display lastname, firstname,building,model. See what I mean? Edited October 2, 2008 by notta Link to comment Share on other sites More sharing options...
zorphnog Posted October 2, 2008 Share Posted October 2, 2008 You can do all of this with one table, you just need to learn how to use some more complex queries. For instance, if you want only the machines in building 10 and sorted by model you could use this query: SELECT * FROM tblData WHERE building = 'building 10' ORDER BY model DESC; And then you could write the HTML with something like this: Dim $sSQL, $aResult, $iRows, $iColumns For $i=1 To 25 $sSQL = "SELECT * FROM tblData WHERE building = 'building " & $i & "' ORDER BY model DESC;" If _SQLite_GetTable2d(-1, $sSQL, $aResult, $iRows, $iColumns) = $SQLITE_OK Then ;Write HTML using $aResult EndIf Next Link to comment Share on other sites More sharing options...
notta Posted October 2, 2008 Author Share Posted October 2, 2008 (edited) Considering my jibberish you answered this perfectly zorphnog. I have been fooling with this since you posted it and I think this is exactly what I need. I plan on trying to implement this all night tonight. Two more question if you have time, I have some entries that the team entered some data in the wrong case. I tried using COLLATE NOCASE but it doesn't seem to be working. SELECT * FROM tblData WHERE building = '1' COLLATE NOCASE I guess I could convert everything to lower or upper case, but the collate nocase would be easier. The second question, I'm trying to use the date between clause, but it's not returning the correct items. $startDate = "2008/08/01" $endDate = "2008/10/01" SELECT * FROM tblData WHERE Date BETWEEN '" & $startDate & "' & AND & '" $endDate & "' ORDER BY date;", $Query) My date data is also in the form of yyyy/mm/dd. This is for another page I'm trying to generate. The table is returned with 2 entries and their from 2008/1/23 and 2008/1/18 or something like that. I have more entries between that date range and plus the 2 dates returned are not even in the range specified. Any idea on these two questions? Thanks. You can do all of this with one table, you just need to learn how to use some more complex queries. For instance, if you want only the machines in building 10 and sorted by model you could use this query: Edited October 2, 2008 by notta Link to comment Share on other sites More sharing options...
zorphnog Posted October 2, 2008 Share Posted October 2, 2008 (edited) First of all, if you haven't already found it by now http://www.sqlite.org/lang.html. This is the syntax page and contains a lot of information. Also, you may want to buy an sql introduction book (I use O'Reilly's Learning SQL) for quick referencing of the more complex SQL queries.For case sensitivity, you can use the upper or lower function of sqlite. If you want the results to be formatted a then use it in your result definition, or use it when comparing in WHERE statements:SELECT upper(lastName),upper(firstname),building,model FROM tblData WHERE building = '1' ORDER BY upper(model) DESC;As for the date question. Are you specifying the date column as a date type when you create the table? Dates should be in the format of YYYY-MM-DD.CREATE TABLE myTable (lname tinytext, fname tinytext, model tinytext, recvd_date date); Edited October 2, 2008 by zorphnog Link to comment Share on other sites More sharing options...
notta Posted October 3, 2008 Author Share Posted October 3, 2008 (edited) I'm loving this sqlLite. After creating the initial array it generated my pages in a fraction of a second. What do I need to know about memory? I have a lot of pages that I need to generate, so does the array created by _SQLite_GetTable2d get deleted when I move onto the next one or do I manually need to clear the memory? I know _SQLite_QueryFinalize($sql) ends the query, but does it free the memory as well? The reason I ask is that it blew through that 26 list in like 5 seconds, so I was thinking of taking it a step further and generating pages for each user that would list all the computers in their name. expandcollapse popup;Names of different date items I'm searching for. Stuff1 = GX260; Stuff2 = GX240... Dim $List[27] = ["26", "stuff1", "stuff2", "stuff3", "stuff4", "stuff5", "stuff6", "stuff7", "stuff8", "stuff9", "stuff10", "stuff11", "stuff12", "stuff13", "stuff14", "stuff15", "stuff16", "stuff17", "stuff18", "stuff19", "stuff19", "stuff20", "stuff21", "stuff22", "stuff23", "stuff24", "stuff25", "stuff26" ] for $listNumber = 1 To $List[0] $fOpen = FileOpen($List[$listNumber] & ".html",2);create new document;ie stuff5.html _genheader($fOpenStuff);Create the standard header for each page ;html Stuff FileWriteLine($fOpenStuff,"<div id=""CenterContainer"">") FileWriteLine($fOpenStuff,"<div id=""Number"">" & $List[$sbuNumber] & "</div>") FileWriteLine($fOpenStuff,"<div id=""b"">") FileWriteLine($fOpenStuff,"<table class=""bTable"" border=""1"" width=""90%"">") FileWriteLine($fOpenStuff,"<tr>") FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Computer Name</td>") FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Last Name</td>") FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Fisrt Name</td>") FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Phone</td>") FileWriteLine($fOpenStuff,"</tr>") $SQL = "SELECT * FROM tblData WHERE stuff1 = '" & $List[$listNumber] & "' ORDER BY computerName ASC;" $iRval = _SQLite_GetTable2d (-1,$SQL, $stuffList, $iRows, $iColumns) If $iRval = $SQLITE_OK Then for $j = 1 To UBound($stuffList) - 1 FileWriteLine($fOpenStuff,"<tr>") FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][7] & "</td>") FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][0] & "</td>") FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][1] & "</td>") FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][2] & "</td>") FileWriteLine($fOpenStuff,"</tr>") Next Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf FileWriteLine($fOpenStuff,"</table>") FileWriteLine($fOpenStuff,"</div>") FileWriteLine($fOpenStuff,"</div>") _genFooter($fOpenStuff) FileClose($fOpenStuff) _SQLite_QueryFinalize($sql) Next Edited October 3, 2008 by notta Link to comment Share on other sites More sharing options...
zorphnog Posted October 3, 2008 Share Posted October 3, 2008 If you're using the same array variable then its cleared out each time you call _SQLite_GetTable2d. You shouldn't have any memory issues with any of the things your trying to do. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now