How do i get individual fields out

[davidbuckleyni]

I am using this example which someone from runrev proivded a link to the getting started database while i do have it working and retreiving the info from the database its doing it as one long text how do i get to the indiviudal results ie coloums id name title etc.

Code: Select all

 -- use a global variable to hold the connection ID so other scripts can use it
    global gConnectionID
    
    -- set up the connection parameters - edit these to suit your database
    put "cmsni" into tDatabaseAddress
    put "databasename" into tDatabaseName
    put "username" into tDatabaseUser
    put "password" into tDatabasePassword
    
    -- connect to the database
    put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
    put tResult into gConnectionID
  
    put "town" into tTableName    -- set this to the name of a table in your database
    put "SELECT * FROM " & tTableName & " WHERE id=2" into tSQL
   
   
    put "foodplace" into tTableName2    -- set this to the name of a table in your database
    put "SELECT * FROM " & tTableName2 & " WHERE townid=1" into tSQL2
   
   
     -- query the database
    put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
  
  
  
   -- query the database
    put revDataFromQuery(tab, cr, gConnectionID, tSQL2) into tData2
  
    put tData into field "Data"
    put tData2 into field "Data2"
    
    
    answer line 1 of tData i  was told this was one way of achieveing this but this just prints each line instead of the field id or title contents

[Mark]

Hi,

Maybe you only think that it is one long text. You aren't using any punctuation in your question. Therefore, I would think that you don't understand how tabs and returns work either.

The data returned by your script contain returns and tab characters. You can use these to retrieve particular parts of the data. The use of tabs and returns is demonstrated by the script you posted. Just look and your own script and see how it is done.

You write that you don't want to have the entire text, but in the script you write that you want to print the entire field. Perhaps you could explain a little more about what you are trying to accomplish and I can help you to adjust the script.

Kind regards,

Mark

[davidbuckleyni]

Hi,

Maybe you only think that it is one long text. You aren't using any punctuation in your question. Therefore, I would think that you don't understand how tabs and returns work either.

The data returned by your script contain returns and tab characters. You can use these to retrieve particular parts of the data. The use of tabs and returns is demonstrated by the script you posted. Just look and your own script and see how it is done.

You write that you don't want to have the entire text, but in the script you write that you want to print the entire field. Perhaps you could explain a little more about what you are trying to accomplish and I can help you to adjust the script.

Kind regards,

Mark

Mark I do understand how tabs work of course i do im a programmer of over ten years expierence learning a new language they sent me the following code

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
put tResult into gConnectionID

put "town" into tTableName -- set this to the name of a table in your database
put "SELECT * FROM " & tTableName & " WHERE id=2" into tSQL


put "foodplace" into tTableName2 -- set this to the name of a table in your database
put "SELECT * FROM " & tTableName2 & " WHERE townid=1" into tSQL2


-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData



-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQL2) into tData2

put tData into field "Data"
put tData2 into field "Data2"


if item 1 of tData = "revdberr" then
answer error "There was a problem querying the database:" & cr & tData
else
put tData into sArray
split sArray by return
put sArray ["1"] into field "data"

answer sArray ["1"]
end if

To ry and sep the elements but it is still briniging out one line answer everyone needs to learn when its a new language to them

regards
david buckley

[dave_probertGA6e24]

Hi David,

I have to agree with Mark here. You might be a programmer for ten years, but you really need to learn the concept of punctuation and sentence structure in your questions. Following your message is very complicated with no punctuation in there.

For example what the heck does this 'sentence' actually mean??? :

To ry and sep the elements but it is still briniging out one line answer everyone needs to learn when its a new language to them

Please try to construct your questions with more concern for the reader - it will make it easier for them to reply to you.

What is your native language BTW?

Cheers,
Dave

PS. Read up on 'itemDelimiter' in the Livecode Dictionary.

[Mark]

Dave, I'm glad that I'm not the only one who finds it difficult to read texts without punctuation. Thanks.

David, you basically reposted your original script. Could you post a sample of the kind of output you would like to see?

Kind regards,

Mark

[StevenK]

Since there are people responding to this thread and my question might be what he is asking, I'll ask it here, maybe it will help david as well.

I am a beginner. I can figure out how to display the results of a query that displays the whole table in columns, but what i want is to display individual items from a record(a line) of a table. So, for example, if I have a table that has two columns, I would like to display the first item from the first record on one card, and the second column from the second record on another card. Then move to the next record and do the same thing.

Can someone give me an idea of the syntax to do this or point me to some examples that might help?

Thanks!

[sturgis]

Assuming your data is coming back in as a tab and return delimited list..

you can set the itemdelimiter to tab
then if you want the first item of the first record it would be

item 1 of line 1 of <container>

if you want item 2, of record 2 it would be

item 2 of line 2 of <container>

There are other ways of course. You can split it into a numerically indexed array for example.

So if you "put item 2 of line 6 of myData into field "whateverfield" it will do just that. item 2, line (record) 6 will be placed into the specifed field. Make sure you set the correct itemdelimiter for your data as mentioned above.

[Mark]

Hi,

Do you really want to show item 1 of record 1 on card 1 and column 2 of record 2 on card 2? I assume you want to show record 1 on card 1 and record 2 on card 2, but I don't understand how you want to format these records. Anyway, once you have executed your query with return and tab as the delimiters and having the data in variable myData, you can do this:

Code: Select all

go cd 1
lock screen
repeat for each line myLine in myData
  replace tab with cr in myLine
  put myLine into fld 1
  create cd
end repeat
delete last cd
go cd 1
unlock screen

This example works if you have stack with only a background group containing one field and if you have the backgroundBehaviour of the group set to true. This script puts the items of a record on separate lines of the field.

Kind regards,

Mark

[StevenK]

Thanks for the responses. . . . yes, Mark, I do want to place each item of each record on a different card. It is a flashcard application so I want the questions to be in column 1 and the answers to be in column 2, and I want the user to be able to read the question and then "flip" (click) to see the answer.

[Mark]

Hi,

I understand it now. You have only 2 cards. So, suppose you have the data stored in a global variable gVar, you can use the following simple command:

Code: Select all

on showFlashCard theInteger
  set the itemDel to tab
  put item 1 of line theInteger of gVar into fld 1 of cd 1
  put item 2 of line theInteger of gVar into fld 1 of cd 2
end showFlashCard

Best,

Mark

[StevenK]

Thanks Mark,

That's helpful. I got a rudimentary version of what you suggested working (woo hoo! . . . small victories), as follows.

on showFlashCard
## Query all details
## Put the result into a variable
put "SELECT * from contact_details" into tSQLQuery
put revDataFromQuery(tab, return, sDatabaseID, tSQLQuery) into gVar

## Display the result on the card
set the itemDel to tab
put item 1 of line 1 of gVar into field "Details" of card "Flashcard Main"
put item 2 of line 1 of gVar into field "detailsBack" of card "Flashcard Back"
end showFlashCard

So, in my simplified version, what I accomplished was to pick two specific items out of the table and display them each on a card. That was a good step for me, but I still have a ways to go to fully understand how to make things work. Could you explain what the "theInteger" variable is doing in your code? Thanks!

[snm]

It's number of record from your database, which must be integer value.

Marek

[StevenK]

So do you just out that into a loop to move through the records?

[snm]

You can extract and put part of this code into separate handler:

Code: Select all

command displayResult pLineNumber, pItemNumber1, pItemNumber2
  ## Display the result on the card
  set the itemDel to tab
  put item pItemNumber1 of line pLineNumber of gVar into field "Details" of card "Flashcard Main"
  put item pItemNumber2 of line pLineNumber of gVar into field "detailsBack" of card "Flashcard Back"
end display result

and then call it from the button or from any part of other script in message path using integer numbers of line and items in that line:

Code: Select all

displayResult 1, 1, 2 

or using variables:

Code: Select all

displayResult tLineNumber, tItemNumber1, tItemNumber2

Marek