Login Script Using MySQL Database

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
tomyeto
Posts: 3
Joined: Fri Oct 02, 2015 5:22 pm

Login Script Using MySQL Database

Post by tomyeto » Sat Oct 03, 2015 5:19 pm

Hi,

I try to create a login page for my simple application. I have a MySQL database on a hosting and connected to my application once the stack is opened.

I have table name "staff" on that database consist of staffId and Password.

I have created a text field "sID" and "password" on the interface, and login button.

I am not sure the correct code to write for calling and compare the data from the table.

I used the following codes but it is not working:

Code: Select all

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 "staff" 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 field "sID" = item 1 of tTableName and field "sIC" = item 3 of tTableName then
       go to card "gUser"
    else
       answer error "There was a problem querying the database:" & cr & tData
    end if
end mouseUp
Please help, or point me to guide on creating login using MySQL database.

Klaus
Posts: 14199
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Login Script Using MySQL Database

Post by Klaus » Sun Oct 04, 2015 2:17 pm

Hi Tom,

1. welcome to the forum! :D

2. "does not work/is not working" are definitively NOT a valid error description, so please give some details
the next time, what part does not work, what error do you get, if at all etc...

3. It looks like the solution is simple, I'm sure you can spot it, too 8)
...
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
if field "sID" = item 1 of tTableName and field "sIC" = item 3 of tTableName then
...

Best

Klaus

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Login Script Using MySQL Database

Post by AxWald » Sun Oct 04, 2015 4:05 pm

Hi,

be welcome! In addition to Klaus I'd like to give some remarks:
  • Don't store plain text passwords in the database! At least hash (and salt) them!
  • Don't grab the whole staff table!
    Better "SELECT PWHash FROM STAFF WHERE SID = '" & MyStaffID & "';"
  • This returns the password hash (if MyStaffID has been found). Compare, done!
  • When working with hosted MySQL they are usually configured for web server service. Means optimized for quick, many small queries, but not for long sessions! So always connect, query, disconnect - this way you save you the trouble of dying connectionIDs!
Hope I cold help a bit, have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

tomyeto
Posts: 3
Joined: Fri Oct 02, 2015 5:22 pm

Re: Login Script Using MySQL Database

Post by tomyeto » Sun Oct 04, 2015 6:37 pm

Thanks Klaus and AxWald.

I am very new to LiveCode and not familiar with it syntax.

I create a connection to database and select 2 field from the table which are sID and sIC. I displayed it on field.

Here are the codes:

Code: Select all

-- construct the SQL (this selects all the data from the specified table) 
   put "staff" into tTableName    -- set this to the name of a table in your database
   put "sID,sIC" into tFields
   put "SELECT sID,sIC 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
I worked fine, but I am confuse on how to use the correct syntax on SELECT and compare login and password on my interface with sID and sIC on my table.

I have no idea how to write the

Code: Select all

"SELECT PWHash FROM STAFF WHERE SID = '" & MyStaffID & "';"
as said by AxWald :roll: :roll: :roll:

Please help

Klaus
Posts: 14199
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Login Script Using MySQL Database

Post by Klaus » Wed Oct 07, 2015 1:40 pm

Hi Tom,

sorry for coming back a bit late, but your problem will take some time to explain.

You should first check these stacks to get more of the basics of Livecode:
http://www.hyperactivesw.com/revscriptc ... ences.html

And knowing some SQL is also essential!
I learned a lot of the basics of SQL here: http://www.w3schools.com/sql/default.asp

When you use "SELECT x,y from anytable" this will fetch ALL records in that table,
so here an important question: Does your table "staff" have more than ONE record?

Please answer this question first, this is essential before we can continue!


Best

Klaus

Post Reply