MySQL Login System

[Quinton B.]

Hello, I need some help. I've been trying to create a Login card in livecode that will log the user out after 15 logins. Thus meaning I need help to create a MySQL SELECT statement that I can use variables such as tUserName, and tPassword in a WHERE format to find a row within my table that matches with what the user input into a login field in the picture bellow. I've been stuck on this for a while. I also need a way to search that same database for an existing UserName, and email address as they are both apart of the signup process. Along with the ability to use an Update statement to change the users password if they forget it. Plus on top of that I need to be able to upload an image into the signup process, then display it when they login, and when they are chating with another member, which I need to create the chat part of the app. Then to be able to make a pdf and send it via email to anyone for a build log that includes pictures, plus I need the ability to add videos along with picture to the database and be able to then dispaly then to the user when they tap on a list of their videos through a list of them through a data grid. I'm done with all the UI, and have made some other portions that add the device name, current location, and current os to the database for security. I've been trying to do all of these for 6 months, I had some down using slqite but need to move everything to MySQL, this move has been for 5 months. It's been a troublesome process. If I could get some help with this, I would like to launch this app before September as I have joined the Air Force and leave for it soon. Thank you for your time, and have a wonderful day.

[Curry]

I would be happy to tutor you on these issues - contact me if you still need help.

[Klaus]

Hi Curry,

shouldn't you be working on "FieldTrip"?
After 4 years, I think it is about time to deliver something.


Best

Klaus

[Curry]

Klaus old boy, I don't love being stalked when I offer help in the forum. Shouldn't you be happy to see me here? It's all connected. Helping people and conducting all of my various work allows me to fund other work, including - you guessed it - updating FieldTrip for LiveCode changes and OS changes compared to the previously delivered version. Some projects have been better funded and received faster attention, but I haven't forgotten FT and it will have its day! Thanks for the reminder.

[Klaus]

Hi Curry,

sorry, if you feel like being stalked by me, I don't, but this forum is my second home, so this is no wonder that we finally met here.

...but I haven't forgotten FT and it will have its day!

Sure, but I am way over 60 now and really would like to see this happen in my lifetime.

Jokes aside, 4 years IS a very long time, too long in my opinion.


Best

Klaus

[Quinton B.]

Good to see you two have a sense of humor. I would appreciate it if you could help me with creating a MySQL statement Insert statement that uses variables for each value. That's step one that I can't seem to get. Thank you guys so much already for helping me further my LiveCode knowledge.

[Curry]

Klaus, it did look almost like stalking, and maybe not the best reflection of your semi-moderator status here, in my opinion. Notice that your comment was off-topic, and also an attempt to divert or control my personal activities. I can't post something if you feel that I should be concentrating on something else at the moment? Wow, that's a fairly unique understanding of moderation and forum participation. But I appreciate your enthusiasm. It's coming.

I am extremely proud of what FieldTrip accomplished in its first round of development, and I will be just as proud of it this time. You may not realize the amount of work involved and the level of features, including the UI options, gradients for the old UI, and even its own toolbar formatting "language" or markup. It's not a quick hack. Those features were funded and delivered. The funds were fully utilized and then some. Soon after, LiveCode and the operating systems underwent extensive (and very expensive) changes. I had some other situations to deal with including the death of one parent and taking care of the other parent to this day. Getting LiveCode itself smoothed out after version 7 took a while, and I had to put first priority on LC and my more established flagship products. I've donated a lot of unpaid time to supporting those changes, and also invested a lot of my previous "bundles" money into LC testing and bug reporting.

My work has its own reality, its finances, its internal and external priorities, its pace. I don't make all the decisions either. Because of the recent bundle, WordReport is going to get the first update, period. That's what has to happen, and it is going to happen! Some other addons also have features already completed and just waiting for documentation and public release - there's a pipeline. I'm very eager for the upcoming FieldTrip update! It will be exciting. But I'm not going to cut corners because of your forum post. That's not the way I operate. Thanks for your interest! It has been a while. I wish it had been quicker, but it's going to be a good one and I hope you will still be with us on launch day!

[Curry]

Quinton - you can concatenate the contents of your variables with the overall text of your SQL statement. For MySQL, you also have to include single quotes around contents for the columns that are NOT numbers and vice versa! The ampersand symbol is your friend here to build the statement. I have an appointment right now and will post an example afterwards.

[Klaus]

Hi Curry,

Klaus, it did look almost like stalking, and maybe not the best reflection of your semi-moderator status here, in my opinion. Notice that your comment was off-topic,...

yes, that is true and I apologize for this. It was a very spontaneous "action" and I really should have thought it over.

..and also an attempt to divert or control my personal activities. I can't post something if you feel that I should be concentrating on something else at the moment? Wow, that's a fairly unique understanding of moderation and forum participation.

That was really not my intention, it was rather an attempt to give you a more or less humorously side-kick, which obviously did not work out too well.
Again, I am avery sorry for all this, really did not want to offend you!


Best

Klaus

[Curry]

Klaus - no worries. Always glad to have your input! Thanks again.

Quentin - You've probably already seen this page, but it can be a handy shared reference point:

http://lessons.livecode.com/m/4071/l/70 ... l-database

About one-fourth down the page:

put "Table1" into tTableName
put "SELECT * FROM " & tTableName into tSQL

That's a good simple example to start with. Whatever is in the variable tTableName will be appended to the "SELECT" statement and placed in the variable tSQL. The literal text "SELECT * FROM " was in quotes and the ampersand (&) performs the joining of the text. BTW, even though we're doing it for SQL, this is simply building a text string with concatenation and a variable.

You likely already have something like that in your code. Doing the INSERT statement is the same thing, just with a larger number of segments that we need to join when creating our SQL statement's text string.

On that same lesson web page, the next code quote is:

put "Table1" into tTableName
put "firstName, lastName, birthDate" into tFields
put "Mary" into tFirstName
put "Smith" into tLastName
put the short date into tBirthDate

-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL

-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tBirthDate"

Kind of a big jump, but now we are filling in 5 variables rather than just one. They are using ampersands to concatenate text with the first two variables (tTableName and tFields) but then half-way they switch and are demonstrating a completely different method for the last three variables - using :1 and letting the revExecuteSQL command perform the substitutions, filling in the contents of tFirstName in place of the :1 and so on.

All we need to do is end up with a valid SQL statement, so either method of joining the text is fine! Whichever you prefer. I usually keep using ampersands and put single quotes around the contents of text columns, then I know for sure what's in tSQL and can examine it if desired before calling revExecuteSQL, but I'm sure many people find the :1, :2 easier to work with.

Does that help, or were you already further along and ran into another problem?

[Quinton B.]

The Insert Statement works:

put "UserName, First_LastName, Email, Password, Birthday, SecurityQuestion, Answer" into tSignUpList
put "INSERT INTO account_info (" & tSignUpList & ") VALUES (:1, :2, :3, :4, :5, :6, :7)" into tSQL
revExecuteSQL gDatabaseID, tSQL, "tUserName", "tFirst_LastName", "tEmail", "tPassword", "tBirthday", "tSecurityQuestion", "tAnswer"
if the result is a number then
answer info "New record added."
wipeSignUp
else
answer error "There was a problem adding the record to the database:" & cr & the result
end if

Now how would I add a picture to the INSERT Statement? (in my database it would be a blob( I will need to retrieve this image later using a SELECT statement))

Now I need this to work:

put "SELECT UserName FROM account_info WHERE UserName =(" & tUserName & ")" into tSQL
put revDataFromQuery(gDatabaseID, tSQL) into tUserNameAvalibility

The error for the SELECT Statement replys:
button "button_SignUp": execution error at line n/a (External handler: exception) near "revdberr,syntax error"

Thanks guys, you have already helped me out so much, now

[SparkOut]

Can't stay to get too in depth but very quickly looking at the UserName question, the select query will need to be provided with the value to match enclosed with single quotes.

... WHERE UserName = ('" <--- note single quote included inside the double quotes. Add a single quote in the later part of the concatenation too.

[Curry]

No need for parentheses in the SELECT statement! Put single quotes instead of those parentheses.

And revDataFromQuery needs a couple of commas before the DB ID - see Dictionary, those delimiter values are optional but the commas are not.

It's unusual to SELECT UserName WHERE UserName; depending on your next step you may want to grab something more useful?