get results from option box

[vedus]

hello.
i have 1 table "table1" and 3 fields "field1" "field2" "field3".
from the mysql database i get the results that i want in the option box from the "table1" and the field1" as result from the query.
now my problem is.
How i get the result from "field2" and "field3"when i pick something from option box and pass it to a textbox?
like "textbox2" and "textbox3"

bellow is the code

Code: Select all

on menuPick pItemName
    -- check the global connection ID to make sure we have a database connection
    global gConnectionID
    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if
    
    -- construct the SQL (this selects all the data from the specified table) 
    put "embassie_address" into tTableName    -- set this to the name of a table in your database
    put "SELECT `embassie_address`.title FROM embassie_address " & tTableName into tSQL
    
    -- query the database
    put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
    
    -- check the result and display the data or an error message
    if item 1 of tData = "revdberr" then
        answer error "There was a problem querying the database:" & cr & tData
    else
       put tData into button "emb"
     end if
   
end menuPick

the above code is on button >option box

[Simon]

Hi vedus,
I'm not really sure about your request but I'll point out something.
In your combobox script you see that "pItemName"? When you pick using a combobox the label of the item you selected ends up in there.

Really unsure if that helps you at all.
If not maybe you could elaborate a bit more on what you are trying to do.

Simon

[vedus]

Hi vedus,
I'm not really sure about your request but I'll point out something.
In your combobox script you see that "pItemName"? When you pick using a combobox the label of the item you selected ends up in there.

Really unsure if that helps you at all.
If not maybe you could elaborate a bit more on what you are trying to do.

Simon

maybe my question is wrong.
like i say in the first post.
i have a table with name "embassies" and 3 columns>"title" "address" "phone"
i get the results from "embassies" table and field "title" in the option menu..
When i pick a name from the option menu like "Germany" i want to get the results from fields "address" and "phone" into 2 text fields...
now if get it right you say i use the label result from the combobox?

[Simon]

Hi vedus,

you say i use the label result from the combobox?

Yes.

Now the rest of it I don't know I'm not really a mysql guy. But if you can query from the name "Germany" and get the correct results "address" "phone" then, yes the combobox will do that.

Simon

[vedus]

ok i have use the bellow code to pass the results from the combo box to the field1

Code: Select all

on menuPick pItemName
put the label of button "mymenu" into fld "namefield"
end menuPick

my question is how to pass the result from the field1 into database query,so i use it with "where" statement or similar?

[Klaus]

Hi vedus,

Code: Select all

on menuPick pItemName
   put the label of button "mymenu" into fld "namefield"
end menuPick

In this code snippet "pItemName" IS IN FACT what you are looking for!
That parameter is the menu item that the user selected (and the new label for this button, what you put into field "namefield"!

So you can use this parameter for your WHERE clause:
on menupick pItemName
...
put "SELECT address,phone FROM embassie_address" WHERE" && title=" & pItemName into tSQL
...

Hint: You have the TABLENAME two times in your script:
...
put "embassie_address" into tTableName
put "SELECT `embassie_address`.title FROM embassie_address " & tTableName into tSQL
## tSQL -> SELECT `embassie_address`.title FROM embassie_address embassie_address
...

I highly recommend to check these stacks to get some more basics of Livecode:
http://www.hyperactivesw.com/revscriptc ... ences.html


Best

Klaus

[vedus]

...
put "SELECT address,phone FROM embassie_address" WHERE" && title=" & pItemName into tSQL
...

Klaus

thank you for the answer at last i thing i am in the right path.
and my last question and i am sure i done all the question after this.
When i put the this one from your post i get the error bellow

Code: Select all

   put "embassie_address" into tTableName    -- set this to the name of a table in your database
  put "Select Title, Address, PhoneNumber from embassie_address" WHERE" && title=" & pItemName into tSQL

button "optionmenu": compilation error at line 6 (Commands: missing ',') near "into", char 86
i change the above with bellow code,but then i get this error <revdberr,Unknown column 'PERU' in 'where clause'>
And i see in the script debug is looking for Column name

Code: Select all

on menuPick pItemName
   global gConnectionID
   put the label of button "optionmenu" into fld "namefield"
   
   put "embassie_address" into tTableName    -- set this to the name of a table in your database
   
   put "Select Title, Address, PhoneNumber from embassie_address where title=" &&pItemName into tSQL
    
    -- query the database
     put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
 put tdata into fld "myfield"
 
end menuPick

i do the query in the server with Mysqlworkbench and the results is perfect
select Title, Address, PhoneNumber from embassie_address where title ='peru'
i try to pass the 2 single quotes but still try to figure out how.every test i do give me the single quotes left of the pItemName something like this
select Title, Address, PhoneNumber from embassie_address where title =''peru
regards
john

[Klaus]

Hi John,

yes, sorry, I forgot some quotes and added some other quotes at wrong place:
...
put "Select Title, Address, PhoneNumber from embassie_address WHERE title=" & QUOTE & pItemName & QUOTE into tSQL
...

I alway use these 2 little functions which will return a given string in DOUBEL or SINGLE Quotes:

Code: Select all

## Double quotes:
function q tString
  return QUOTE & tString & QUOTE
end q

## Single quotes
function q2 tString
  return "'" & tString & "'"
end q2

Saves a LOT of typing and reduces typos!

Use it like this:
...
put "Select Title, Address, PhoneNumber from embassie_address WHERE title=" & q2(pItemName) into tSQL
...

Best

Klaus