SOLVED - Displaying the fields from the DB

[admin12]

I want to populate an existing form with the already existing data from the database. Here is the code I have already written:

put "SELECT FirstName, MiddleName, SurName, Gender, DOB, EmailAddr, PrimTele, skype, ecn, promo_code FROM candidatecc where CandidateCCID = " &numtochar(39) &CurUserID &numtochar(39) into theQuery
put revDataFromQuery(,,dbID,theQuery) into theData

How do I then put all this data (in theData) into the appropriate fields?

Mike

[Mark]

Hi Mike,

You could use a repeat loop to copy the data from your variable theData to an array variable, but it would be faster and easier to put the data directly into the appropriate fields, unless you want to keep a global array variable, which you are going to use very frequently.

Assuming that you have a return and tab delimited table in theData variable, you could just loop through that table using a repeat loop. I'm also assuming that the first column contains field labels and the second column contains data, but you probably have a slightly different scenario.

Code: Select all

set the itemDel to tab
repeat for each line myLine in theData
  put item 2 of myLine into fld item 1 of myLine
end repeat

If you want to produce an array, you might use this

Code: Select all

split theData by return and tab
repeat for each key myKey in theData
   put theData[myKey] into fld myKey
end repeat

Best,

Mark

[admin12]

Mark,

The data is simply separated by spaces when I do an answer to theData. How would I put them in their requisite fields?

Fields being:

FirstName
MiddleName
SurName
Gender
DOB Month
DOB Day
DOB Year
Email
Phone
Skype
Emergency Number
Promo Code
created

Thanks for your help.

Mike

[Mark]

Mike,

There are a billion ways to do this. I've given you a few examples, which should be sufficient to get you started. If your list of fields and your data list are in the same order, you could use a counter.

Code: Select all

put 0 into myCounter
set the itemDel to tab
repeat for each line myFld in myFieldList
  add 1 to myCounter
  put item myCounter of theData into fld myFld
end repeat

Why do you think that your data contains spaces? Because they look like spaces?

Best,

Mark

[admin12]

Mike,

There are a billion ways to do this. I've given you a few examples, which should be sufficient to get you started. If your list of fields and your data list are in the same order, you could use a counter.

Code: Select all

put 0 into myCounter
set the itemDel to tab
repeat for each line myFld in myFieldList
  add 1 to myCounter
  put item myCounter of theData into fld myFld
end repeat

Why do you think that your data contains spaces? Because they look like spaces?

Best,

Mark

Mark,

I'm just not used to the syntax used by Live Code yet.

I get an error: card "ReturnRegistration": execution error at line 39 (Chunk: no such object) near "myFieldList", char 33
on this line: put item myCounter of theData into fld myFld

theData is the correct name of the query. Does myFld need to be changed to the actual names of the fields, or should the fields just populate from the loop?

Mike

[Mark]

Mike,

You shouldn't quote a message, if you're just quoting the message above. Quoting for for messages that appear at the beginning of the thread.

The variable myFieldList needs to contain a list of field names, in the right order. There are several ways to do this, but a simple way could be to keep your list in a field and put the text of this list into the variable at the start of your script:

Code: Select all

put fld "This is your list" into myFieldList

Do you still get an error now?

Apparently you don't understand how the repeat loop works. Please, read in the manual and the dictionary about the repeat for each command.

Kind regards,

Mark

[admin12]

Mark,

I will read up on looping in Live Code.

Here is the fields:

put "FirstName,MiddleName,SurName,Gender,dobmonth,dobday,dobyear,DOB,EmailAddr,PrimTele,skype,ecn,promo_code" into myFieldList

Is that what you meant?

Mike

[Mark]

Well, Mike, I said you should make a field that contains the list, because we are assuming a return-delimited list. You can do it the way you showed in your previous post, but then you have to accommodate for the different delimiter by adjusting the script.

Mark

[admin12]

Well, Mike, I said you should make a field that contains the list, because we are assuming a return-delimited list. You can do it the way you showed in your previous post, but then you have to accommodate for the different delimiter by adjusting the script.

Mark

This is all very confusing for me. Sorry. I read the entire dictionary entry on repeat loops - and I am still not sure I get it.

Am I supposed to make an invisible field that contains all the field names as my list?

I thought this was going to be easy -

put someVariable into the text of field "fldFirstName"
put someVariable2 into the text of field "MiddleName"
etc...

However, I am totally stuck as to how to get the data from the database and store them somehow in somVariable. In Basic, a loop with an array would be used:

for i=1 to 20
someVariable(i) = someData
next i

However, Live Code does not work that way.

So, how do I store the field names after I do a successful query - the query, in this case, stores all the data in one variable - theData. It's all in that one variable. So, how do I get it OUT of that one variable? It would be simpler for me if I could store the results in an array theData(array)

Or

Do I do the stupid way and do each field as a query - line by line. I could do it and it would work, but elegant it aint.

Mike

[Mark]

Mike,

Don't quote.

First, make a field with that return-delimited list. It will work. Just try it. If you get an error, post the complete error here.

You said you are new to LiveCode, so we'll do it the inefficient but easy way first. You can change it later on.

How would you copy the data from the MySQL database to the array in Basic? That's a lot of work with offset functions, at least two additional counters and you'll still need a list of field names somewhere! In LiveCode you simply use items and lines and you're done! Much easier.

Best,

Mark

[admin12]

Here is the entire opencard script with your suggestions (I have an invisible field called fldList that contains the all the fields separated by a return):

====================================

on opencard
--Create a global variable for the db
global dbID
global curUserID
global UN

put numtochar(39) into SQ
put the text of field "fldList" into myFieldList

-- put the Ip address of the MySQL Server
--put the name of your DB
-- then your login
--then your password

--this connects to the database
put revOpenDatabase("MySQL","www.website.com","user_database","username","password") into dbID

if dbID is not a number then
answer warning "Error "&dbID
exit to top
else
--answer "connection successful"
end if

--grab UserID and put it into CurUserID
put "SELECT UserID FROM UserValidation WHERE Username = " &SQ &UN &SQ into theUID
put revDataFromQuery(,,dbID,theUID) into theUIDData
put theUIDData into CurUserID

--load in data for person logged in
--answer CurUserID
--load existing data
put "SELECT FirstName,MiddleName,SurName,Gender,dobmonth,dobday,dobyear,DOB,EmailAddr,PrimTele,skype,ecn,promo_code FROM candidatecc where CandidateCCID = " &SQ &CurUserID &SQ into theQuery
put revDataFromQuery(,,dbID,theQuery) into theData

put 0 into myCounter
set the itemDel to return
repeat for each line myFld in myFieldList
add 1 to myCounter
put item myCounter of theData into fld myFld
end repeat

--answer theData

end opencard

===========================================

I get an error in this line - put item myCounter of theData into fld myFld

What should go in place of fld myFld? A list of fields on the card? I tried putting "fldList" in place of myFld, but it does nothing. No errors, but I guess it's populating field "fldList" with the data. Not the intended result.

Mike

[Mark]

Mike: If you get an error, post the complete error here!!!

[karmacomposer]

When I get back to my office, i'll post it. I do know it's a no such field error.

Mike

[admin12]

OK. I got it to work - except for one thing.

Here is the code:

set the itemDel to tab
repeat for each line myLine in theData
put item 1 of myLine into field "fldFirstName"
put item 2 of myLine into field "fldMiddleName"
put item 3 of myLine into field "fldSurName"
put item 4 of myLine into field "fldGender"
put item 5 of myLine into field "fldEmailAddr"
put item 6 of myLine into field "fldPhoneNum"
put item 7 of myLine into field "fldSkype"
put item 8 of myLine into field "fldEmergencyNum"
put item 9 of myLine into field "fldPromoCode"
put item 10 of myLine into field "MonthEntry"
put item 11 of myLine into field "DayEntry"
put item 12 of myLine into field "YearEntry"

end repeat

Now, fldGender is a drop down combo box control. I assume it is treated differently. I get an error: card "ReturnRegistration": execution error at line 48 (Chunk: no such object) near "fldGender", char 28.

What is the correct syntax for combo boxes?

Mike

[admin12]

Anyone? For some ODD reason, fldGender (a simple combobox with Male and Female in it) keeps causing me errors.

This is the put statement that puts the choice in the variable Gender:

put line (the menuhistory of btn "fldGender") of btn "fldGender" into Gender

This does not work, it always returns "Male", even if "Female" is chosen. Someone suggested this code, but I am confused since the combobox is not a button - why the btn?

Totally confuses me. I just need this correct once and then I can use it for every combo box thereafter.

Mike