LiveCode Forums.

Posted: **Thu May 17, 2012 5:23 am**

Hi,
I connected my code to a MySQL database, created a full table and everything, and am trying to deal with the data I get back from the database without having to run a million queries. Basically, the table I get back (placed in a basic Table Field) is 12 rows with 17 columns (the first column being simply 1-12). I'm having issues putting them into arrays. I can't select less of the table, because I'm taking every cell of the table and putting it into the code somewhere, and I need to be able to call it easily. I'm convinced I'm doing something wrong. I'm putting the table call below in case it's important. Any help would be appreciated!

Code: Select all

put "RowNum VARCHAR(40), CharName VARCHAR(40), PlayerName VARCHAR(40), GroupNum VARCHAR(999), CharNum VARCHAR(999), G" & tGroupNum & "C1Text VARCHAR(999), G" & tGroupNum & "C2Text VARCHAR(999), G" & tGroupNum & "C3Text VARCHAR(999), G" & tGroupNum & "C4Text VARCHAR(999), G" & tGroupNum & "C5Text VARCHAR(999), G" & tGroupNum & "C6Text VARCHAR(999), G" & tGroupNum & "C7Text VARCHAR(999), G" & tGroupNum & "C8Text VARCHAR(999), G" & tGroupNum & "C9Text VARCHAR(999), G" & tGroupNum & "C10Text VARCHAR(999), G" & tGroupNum & "C11Text VARCHAR(999), G" & tGroupNum & "C12Text VARCHAR(999)" into tFields

   put  "G" & tChosenTable & "TextTable" into tTableName    -- get table in form "G1TextTable"
   put "SELECT * FROM " & tTableName into tSQL
   put revDataFromQuery(tab, cr, gConnectionID, tSQL, tFields) into tData
   if item 1 of tData = "revdberr" then
      answer error "There was a problem querying the database:" & cr & tData
      exit to top
   else
      put tData into field "DataText"
   end if

Posted: **Thu May 17, 2012 3:27 pm**

Your tFields variable is the issue. Remove it from your revDataFromQuery call.

Posted: **Thu May 17, 2012 3:48 pm**

The table is being called correctly (although I'll look into correcting that). I'm trying to take the existing table and create a list of arrays from it without having to create 204 different variables by hand.

Posted: **Thu May 17, 2012 4:00 pm**

To build an array from tData you can use a repeat loop.

First set the item delimiter to tab so that your itemdelimiter matches your revqueryfromdata results (you specified tab and return as delimiters)

Code: Select all

set the itemdelimiter to tab

Then a repeat loop:

Code: Select all

repeat for each line tLine in tData -- grab each line in turn
put item 1 of tLine into tRecordKey -- use your numbered key as the toplevel array key
repeat with i = 2 to the number of items in tLine -- another repeat to cycle through the items on the line
put item i of tLine into tDataArray[tRecordKey]["col" && i] --create an array entry for the current line and column
end repeat
end repeat

This will create an array structure like the following

Code: Select all

tDataArray
               1
                        Col 1 = "yourdata"
                        Col 2 = "Your column 2 data"
                        .........
               2       
                        Col 1 = "the data for row 2 column 1
                        ...........

So to pull a value out you can
put tDataArray[1]["Col 1"]

Code: Select all

Which would put "yourdata" into the message box

Code: Select all

put tDataArray[2]["Col 1"]

would put "the data from row 2 column 1" into the message box.

Posted: **Thu May 17, 2012 9:21 pm**

sturgis' method is the way i would go at it as well.. the way you were trying to do it, should work, except the fact you have all that extra data "varchar(999)" etc.. this should work with your initial code and thinking, but not too certain (give it a try)

Code: Select all

put "RowNum, CharName, PlayerName, GroupNum, CharNum, G" & tGroupNum & "C1Text, G" & tGroupNum & "C2Text, G" & tGroupNum & "C3Text, G" & tGroupNum & "C4Text, G" & tGroupNum & "C5Text, G" & tGroupNum & "C6Text, G" & tGroupNum & "C7Text, G" & tGroupNum & "C8Text, G" & tGroupNum & "C9Text, G" & tGroupNum & "C10Text, G" & tGroupNum & "C11Text, G" & tGroupNum & "C12Text" into tFields

Posted: **Thu May 17, 2012 9:31 pm**

Shaosean, remind me..

The variable after tSQL would be used as a substitution in the query right? Its not a variable to be filled, its a variable to be read to fill in place holders in the query? (Which would be why the query works as is, there are no place holders in tSql so tFields would never come in to play)

Code: Select all

 put revDataFromQuery(tab, cr, gConnectionID, tSQL, tFields) into tData

As far as I know there isn't way way to do a query that returns an array (except with sqlYoga) only a flat list is returned I think?

Posted: **Thu May 17, 2012 11:03 pm**

ahh.... You are correct. Been a while since I have used DB features in Rev. Looks like sturgis' method is the correct way to go about it.

Posted: **Thu May 17, 2012 11:05 pm**

Wasn't sure, I suffer from a rare disease "foggybrain" that crops up at the most inopportune times. Usually right after a bout of "justcan'trememberitous'

Posted: **Thu May 24, 2012 2:54 am**

I'm not sure I fully understand what you are trying to do so I'll assume:

- SELECT all the columns from all the rows in your table.
- put the result into an array with the first level key being the row number and second level keys being the column names

To read the data, all you need is "SELECT * from <tablename>". You can add a WHERE statement if you don't need all the rows, or an ORDER BY if you need to sort them, or if you don;t need all the columns, just list them out separated by commas instead of "*". Use revQueryDatabase to execute the SELECT. It will return a recordset ID into a variable, lets call it tID.

Then you need a loop something like this

Code: Select all

put revDatabaseColumnCount(tID) into tRowCount
put revDatabaseColumnNames(tID) into tColumnNames
revMoveToFirstRecord(tID)
repeat with x=1 to tRowCount
  repeat for each item tColumnName in tColumnNames
    put revDatabaseColumnNamed(tID,tColumnName) into tArray[x][tColumnName]
  end repeat
  --Edited, forgot this next line
  revMoveToNextRecord(tID)
end repeat

Hope that helps.

Pete

LiveCode Forums.

Arrays from Basic Table Field

Arrays from Basic Table Field

Re: Arrays from Basic Table Field

Re: Arrays from Basic Table Field

Re: Arrays from Basic Table Field

Re: Arrays from Basic Table Field

Re: Arrays from Basic Table Field

Re: Arrays from Basic Table Field

Re: Arrays from Basic Table Field

Re: Arrays from Basic Table Field