Need help with LiveCode and MySQL

[admin12]

I have been able to login to the remote server's MySQL database. I have been able to do a simple query and put the result into a combo box.

Now, I need to go way further. The end result needs to be the following:

List all the Recruiters in the table RecruiterData (select UserName from RecruiterData)

The result is put in a combo box or drop down box.

I need the user to select a name. As a result of the selection, the database needs to be queried and show a host of data about the Recruiter.

How would I do this?

Mike

[dglass]

After your combo selection you'll need to do something like:

SELECT <<whatever data you need>> FROM <<whatever tables you need>> WHERE UserName <<or whatever field is in the other tables>> = '" & the result of your selection & "'"

Then do whatever you are going to do with the returned data.

[admin12]

After your combo selection you'll need to do something like:

SELECT <<whatever data you need>> FROM <<whatever tables you need>> WHERE UserName <<or whatever field is in the other tables>> = '" & the result of your selection & "'"

Then do whatever you are going to do with the returned data.

Thank you for the help. Here is what I created, but I get errors. Any way to help me find the erroneous syntax (I'm a Live Code total noob)?

theResult2 is a combo box.

put "SELECT Recruiting_Company_Address FROM RecruiterData WHERE UserName = "& the result of your selection into theQuery
put revDataFromQuery(,,dbID,theQuery) into theData
put theData into fld "theResult2"

Also, how difficult would it be to use a data grid object instead of lots of combo boxes and populate it with various field data?

Thanks.

Mike

[dglass]

Blind leading the blind here, but....

You need to look up how the selected item is returned from a combobox. Actually, we both do, but my guess is it's going to be a 'menupick' handler, or maybe the field's value with a check to ensure it's not blank.

After that you need to replace 'the result of your selection' in your SQL with whatever the selected item is, whether it is a variable you defined, or maybe 'the result', or possibly 'it'.

One of the failures of LC's English-like syntax is that pseudo-code can easily be mistaken for real code.

[dglass]

Also, how difficult would it be to use a data grid object instead of lots of combo boxes and populate it with various field data?

Dunno. I imagine not very difficult provided we both knew a bunch more than we currently do.

[admin12]

I pretty much have it figured out - but I need one more piece of help from someone.

Here is what needs to be in the query:

SELECT Recruiting_Company_Address FROM RecruiterData WHERE UserName = "mikerecruiter"

Now, how do I enclose mikerecruiter in quotes programmatically?

Here is what I have:

put "select Recruiting_Company_Address from RecruiterData where UserName = " into newselection
put theQuery into newresult

Now, it would be more proper to make it the following:

put "select Recruiting_Company_Address from RecruiterData where UserName = " & theQuery into newselection

However, the data in theQuery (which equals, say mikerecruiter) needs to be enclosed in double quotes. Is there a char command in Live Code?

Not sure what the ASCII number is for the double quote, but it probably would be something like:

put "select Recruiting_Company_Address from RecruiterData where UserName = " & char(44) & theQuery &char(44) into newselection

Can anyone help me get that line right?

Mike

[dglass]

'quote' is equivalent to the double quote character (ASCII 34).

[admin12]

'quote' is equivalent to the double quote character (ASCII 34).

Thank you. That did it.

For anyone who needs it, here is the correct code:

put "select Recruiting_Company_Address from RecruiterData where UserName = " &quote & the selection &quote into newselection
put newselection into theQuery
put revDataFromQuery(,,dbID,theQuery) into theData

If theData contains "revdberr" then
answer warning "There is an error "&theData
exit to top
end if

put theData into fld "theResult2"

Mike

[Klaus]

Hi Mike,

I use these two li'l functions especially when doing database stuff:

Code: Select all

## Put QUOTES around a given string:
function q tString
  return QUOTE & tString & QUOTE
end q

## Put SINGLE QUOTES ' around a given string:
function q2 tString
  return "'" & tString & "'"
end q2

Very handy, much less typing :
...
put "select Recruiting_Company_Address from RecruiterData where UserName = " & q(the selection) into newselection
...


Best

Klaus

[admin12]

Thanks Klaus.

I will probably need hints like this a lot as I delve deeper into MySQL programming in Live Code.

Mike