mySql data display blues.....

[steve_nam]

Hi LiveCde/Database Gurus
I urgently and desperately need help. I am still learning LiveCode although I am reasonably confident, but this time I have spend many weeks trying to figure out what and how to do a specific action required for my app.

Here is my situation, stack and card information as well as scripts used on the various elements of my android application.

Here goes.....
Stack Script to deploy on various screen sizes is in the main stack. The code is

on preOpenStack
set the fullScreenMode of this stack to "exactFit"
end preOpenStack

The first card
Here I have a database connection button with the following code in/on it;

on mouseUp
-- 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 "www*********" into tDatabaseAddress
put "fishingpermits" into tDatabaseName
put "*******" into tDatabaseUser
put "*******" into tDatabasePassword

(Please note, for the purposes of this post, I replaced the database address, my username and password with ***)
-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
end if
go to next card
end mouseUp


The connection to the MySql database is successfull and the application proceeds to the second card. No issues there.


Second Card
On the second card I have a field named "idNumber", a button to display ALL data in the database, and a search button to display a specific dataset based/queried on the idNumber of a specific individual.

The script on the "Display all data" button is
----------------------------------
on mouseUp
-- 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 "permitholders" into tTableName -- set this to the name of a table in your database
put "SELECT * FROM " & 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 field "Data"
end if
end mouseUp

Again this script loads ALL data (as requested) from the MySql database successfully, display all data in a field called "data". The application proceeds to the third card. Again no issues there.

The script on the "Search" button to load data based on the id request is as follows and here I have ISSUES and DESPERATELY need help from the LiveCode database gurus.

The Third Card
On this card I have a label field called "ID" where the idNumber as requested must be inserted from the database.

The code on the "Search" button on the second card is as follows:


-- construct the SQL (this selects all the data from the specified table)
put "employee" into tTableName -- set this to the name of a table in your database


put "SELECT * FROM" & tTableName into tSQLQuery
put "WHERE holderID =' '" & pHolderID & " " after tSQLQuery

-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQLQuery) into tRecordID


-- 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
go to next card
revDatabaseConnectResult(gConnectionID)
set the text of field "emlpoyeeId" of card 1016 to revDatabaseColumnNamed(tRecordID, "employeeId [,tRecordID]")
put holderId into field "ID"

end if
end mouseUp

This is where I issues and desperatley need help.
The application proceeds to the third card, but DOES NOT display the required data from the database. It must be something, which I unfortunately do not see, in my script'

LiveCoders, PLEASE come to my rescue. I am still learning LiveCode....

Steve

[bangkok]

put "employee" into tTableName -- set this to the name of a table in your database

put "SELECT * FROM" & tTableName into tSQLQuery
put "WHERE holderID =' '" & pHolderID & " " after tSQLQuery

-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQLQuery) into tRecordID

[....]
go to next card
revDatabaseConnectResult(gConnectionID)
set the text of field "emlpoyeeId" of card 1016 to revDatabaseColumnNamed(tRecordID, "employeeId [,tRecordID]")
put holderId into field "ID"

I see 5 potential problems here :

1-your query is wrong : a space is missing between the table name and FROM, and between the table name and WHERE

2-the WHERE is wrong : a single quote is missing at the end and you have 2 single quotes at the beginning !

Code: Select all

   put " WHERE holderID ='" & pHolderID & "'" after tSQLQuery

3-the script after looks strange and too complex. You try to use revDatabaseColumnNamed function (for record set) with revDataFromQuery
Avoid that at the beginning.

4-you mix pHolderID with holderId : not the same variable

5-set the text of field "emlpoyeeId", I guess error in field name "employeeId"

--> one advice : make it more simple.

Code: Select all

put "SELECT * FROM employee "WHERE holderID ='" & pHolderID & "'" into tSQLQuery

-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQLQuery) into tResult

-- check the result and display the data or an error message
if tResult begins with "revdberr" then
answer error "There was a problem querying the database:" & cr & tResult
exit to top
end if

go to next card
put tResult into fld "employeeId"
put pholderId into field "ID"

[steve_nam]

Bangkok ,
Thanks a million for the response and the guidance. I will use your recommended syntax in my scripts and let you know how I am doing. I try to battle and figure out things myself; not because I don't want to ask for assistance, but doing, trying and failing on your own helps a great deal with learning LiveCode. When you get help in the Forum things tend to fall in place. One get really understand why the error occur and why the suggested code/help solves you issue. I, as a non-programmer, find this way of learning more beneficial for me. The Forum is a fantastic resource with very helpful Forum members when one gets stuck. In a year's time I will, hopefully, also be able to guide and assist new LiveCoders.

Thanks a million
Steve

[Klaus]

Hi Steve,

I wrote 2 little functions that will make database stuff with quotes and single quotes a bit easier, check this:
http://forums.livecode.com/viewtopic.ph ... tes#p98090

Best

Klaus

[steve_nam]

Klaus
Thank you very much for the advice.
I follow your guidance in the Forum and appreciate it very much. Thanks for replying to my query/question. I read the post,, but unfortunately, I am not any wiser. I don't understand what you mean in the post of http://forums.livecode.com/viewtopic.ph ... tes#p98090.

In my original post/question I indicated that "I am still learning LiveCode although I am reasonably confident, but this time I have spend many weeks trying to figure out what and how to do a specific action required for my app."

Hope you are not frustrated with me for being totally stupid with database stuff.

Regards,
Steve

[Klaus]

Hi Steve,

the functions will save you a lot of typing!

Like this line, which is hard to read and error prone when typing:
...
put "SELECT * FROM employee "WHERE holderID ='" & pHolderID & "'" into tSQLQuery
...

With my functions in your stack script you can do somethong like this:
...
put "SELECT * FROM employee "WHERE holderID =" & q2(pHolderID) into tSQLQuery
...
See the difference? Cleaner and better readable!

And sometoime you need to have a string in Quotes, so instead of:
...
put QUOTE & "This is a quoted string..." & QUOTE into tQStr
...
Use:
...
put q("This is a quoted string...") into tQStr
...

Best

Klaus

[steve_nam]

Klaus,
Thanks for your response. I have definitely learned something new in your post and will surely use it in my next app with a database backend.....so we learn and grow in our LiveCode learning curve.

I will try to be as clear as possible with regards my challenges NOW with the specific app I'm working on.

What is working
1. The app connects to the database successfully.
2. The app display ALL records from the database....namely SURNAME, NAME, IDNUMBER, CITIZENSHIP, etc in a field called "all data". This is to check that the app is indeed retrieving info from the database. This section will eventually be deleted/left out of the app if I can be successful with no 3 below

What is NOT working
3. Here comes my challenge.
I want to display NAME of the person in a field called "name" and display;
Display SURNAME of the person in another field called "surname";
Display the IDNUMBER of the person in a field called "ID";
Display the CITIZENSHIP of the person in a field called "citizenship" and

Later to Display a PHOTO (.jpg) of the person in field called "photo".

What I need help on to succeed is help with the code/script to do exactly what I explained in 3 above..... to display different pieces of data from the database in differenct fields on one card. The primary key is the person's IDNUMBER.

I hope the picture is a bit clearer now.

Kind Regards from a hot Namibia(Africa)
Steve

[Klaus]

Hi Steve,

OK, WHEN do you want to display the (person) specific info?
I mean are the complete data already in the stack in a datagrid or field and the user clicks a line?
Know what I mean?

That is important to know first, the rest is easy-peasy


Best

Klaus

[bangkok]

I want to display NAME of the person in a field called "name" and display;

You need to go through the basic of revDataFromQuery.

When you use :

Code: Select all

put revDataFromQuery(tab, cr, gConnectionID, tSQLQuery) into tRecordID

It means, that in the data sent back by the SQL server, columns (fields) will be separated by a TAB, and lines (records) will be separated by a CR (carriage return).

Let's imagine your table "employee" has 3 columns and 2 records.
-NAME
-SURNAME
-IDNUMBER

Record 1
-Name Chris 1, Surname1, 1

Record 2
-Name Chris 2, Surname2, 2

Code: Select all

put "SELECT NAME,SURNAME,IDNUMBER from employee" into tSQLQuery
put revDataFromQuery(tab, cr, gConnectionID, tSQLQuery) into tRecordData

set itemdelimiter to tab

answer the number of lines of tRecordData & " record have been selected"

repeat with i = 1 to the number of lines of tRecordData
answer "record number "&i
put item 1 of line i of tRecordData into field "name"
put item 2 of line i of tRecordData into field "surname"
put item 3 of line i of tRecordData into field "idnumber"
end repeat

In that manner, you are able to control what and where you will put into your fields.

[steve_nam]

Hi Klaus,
You are quick.... Let me give you the information you need.

On question 1: "WHEN do you want to display the (person) specific info?"
The info must be displayed after I fill the the person's ID number in a field and press the "Search" button.

On question 2: "....are the complete data already in the stack in a datagrid or field and the user clicks a line
No, I did not build it to give similar names or surnames for the user to select one with a click. I query the database on an id number only. It's like the user input your id number in the field, press the "Search" button on the same card of the stack and moves to the next card where it only displays your data as requested by using only your id number.

Let me quickly walk you through the three (3) cards in the stack.
On Card 1: A "Connect to Database" button. This is to establish and verify a connection to the database. Move to card 2.
On Card 2: An input field to enter the unique id number (citizen number) of the person and a "Search" button. After pressing "Search" button move to card 3.
On Card 3: Four (4) or more fields to display specific info from the database linked to the id number (eg Surname, name, id number, citizenship etc).

Assist me with at least on field eg surname to display. I will do (and learn) the rest.
Hope this is ok. Ask more if need be.

Steve

[Klaus]

Hi Steve,

ah, OK, got it

Please see what bangkok already posted, the solutuon to your problem is similar.

OK, in the script of your "Search" button you do something like this, given you already connected to your database:
...
## Get content of your "search" field
put fld "IDNUMBER" into tID

## Now create correct SQL string, add all the fields you want to retrieve, I only use "surname" and "name" in my example
put "SELECT surname, name from YourTableName WHERE IDNUMBER=" & tID into tSQL

put revDataFromQuery(TAB,CR, gConnectionID, tSQL) into tRecord
put item 1 of tRecord into fld "surname"
put item 2 of tRecord into fld "name"
...
You get the picture
You surely need to check for errors like "revdberr" and other inconvenineces!

BTW, this got ME started with SQL: http://www.w3schools.com/sql/default.asp


Best

Klaus

[steve_nam]

Klaus & Bangkok,
You guys are great ! Thanks for all the help and guidance. I still have a problem/issue, but, I think, not from LiveCode. It is probably the database. I will follow my learning path (i.e. try find the problem BEFORE shouting for help; best way to learn !), until I solve it.

Another piece of advise you Gurus can give non-programmers (like myself) is to take a break from your problem. Sleep on it and come back with a fresh mind to look at the problem and 9 out of 10 times you will get it. Saves a lot of time.

I don't want to bother you guys for now. I will shout when I am banging my head against the wall.

Cheers
Steve

[steve_nam]

Klaus & Bangkok,
Just to let you know that I conquered my data display blues .....

Thanks for the assistance; I really appreciate it.

Steve