$mysql_fetch_array value in LiveCode

[markdub]

Hello Everyone.
It's my first time to make a thread here in runrev forums.
I would like to ask how to select or check a specific data(ex. username and password)
Just like in a simple login module.
here is my code.

Code: Select all

on mouseUp
   global gConnectionID
   global tteacherID
   local tUsername
   local tPassword
   
   put revDataFromQuery(tab, cr, gConnectionID, "Select teacherID from teachers") into tteacherID
   put revDataFromQuery(tab, cr, gConnectionID, "Select teacherUsername from teachers") into tUsername
   put revDataFromQuery(tab, cr, gConnectionID, "Select teacherPassword from teachers") into tPassword
   
   if tUsername = the text of field "FieldUsername" and tPassword = the text of field "FieldPassword" then
      answer "succesfully login"
      go to card "main"
   else
      answer "fail"
      end if
end mouseUp

[Simon]

Hi markdub,
Welcome to the forum

Does anything get placed in the variables?
Your code looks correct, what happens if you place "answer the result" after each query?

Simon

[bangkok]

Welcome.

You should take an easier way with only 1 MySQL query.

Code: Select all

put "select teacherID from teachers where teacherUsername='"&field "FieldUsername"&"' and teacherPassword='"&field "FieldPassword"&"'" into tSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tResult

if tResult is not empty then
answer "Welcome "&tResult
go to card "main"
else
answer "Fail"
end if

[markdub]

Hi markdub,
Welcome to the forum

Does anything get placed in the variables?
Your code looks correct, what happens if you place "answer the result" after each query?

Simon

if put answer tteacherID to it.
it will retrieve all the id.
i think i shuould put check it first

Code: Select all

 put revDatabaseQuery "Select *from teachers where teacherUsername = "&FieldUsername&" and teacherPassword ="&FieldPassword into tQuery

[markdub]

Welcome.

You should take an easier way with only 1 MySQL query.

Code: Select all

put "select teacherID from teachers where teacherUsername='"&field "FieldUsername"&"' and teacherPassword='"&field "FieldPassword"&"'" into tSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tResult

if tResult is not empty then
answer "Welcome "&tResult
go to card "main"
else
answer "Fail"
end if

Thank you sir. I think it will work fine. I'll check it first.

[markdub]

Thank you sir bangkok.

[SparkOut]

I was typing this and being distracted, and in between the original post and finishing there are tonnes of replies! Oh well, fwiw here's my suggestions

Code: Select all

on mouseUp
   global gConnectionID
   global tteacherID
   local tUsername
   local tPassword
   local tTeacherData
   
   --let's put the query into a variable so you can scan it easier
   put "SELECT teacherID,teacherUsername,teacherPassword FROM teachers" into tSQL
   --you can select the specific columns (fields) from the table you want all in one query
   --but the query above will return all the id,username and password entries in the table
   --to return specific items you need to have some criteria, it depends what you already
   --have in the structure of your application what you can do
   --for instance, let's add a selection based on the teacherUsername you want to match in the table
   --assuming the teacher has input the username and password into the fields
   put the text of field "FieldUsername" into tUsername
   --note we are adding the criteria to the end of the variable in which we already started building our query
   --hence the leading space
   put " WHERE teacherUsername = '" & tUsername & "'" after tSQL
   --see http://www.w3schools.com/sql/sql_where.asp for information about selection criteria
   
   --for testing,  show the whole SQL string to make sure it looks OK
   put tSQL
   
   --get the (single) record matching the teacherUsername
   put revDataFromQuery(, , gConnectionID, tSQL) into tTeacherData
   
   set the itemDelimiter to tab
   put item 1 of tTeacherData into tteacherID
   put item 3 of tTeacherData into tPassword
   --note that even with a small / non-sensitive database it is VERY VERY bad practice to have unencrypted passwords
   --stored in the table
   --this may again not be "best practice" but when storing the password you could
   --wrap it in some "salt" and save only the md5Digest to the table
   put md5Digest ("mySecret" & the text of field "FieldPassword" & "moreSecretSalt") into tPwdDigest
   
   if tPassword is tPwdDigest then
      answer "succesfully login"
      go to card "main"
   else
      answer "fail"
      end if
end mouseUp