How do Databases work?

[kirkkaf]

Hi everyone,

How do databases work and how can I create a database to store user names and passwords of my site members so I can create a members area.

If someone could show me a simple example that would great.

Thanks.

[StefanPendl]

There are examples of using a database included with your RB installation and on the wiki.

There is even a thread here on the boards, where some user applications are listed, where on is a simple forum.

[kirkkaf]

Hi,

I have spent most of the day trying to figure out how to make a simple signup and login program. This is my first program to use a database and i am struggling to get my head around it. I was wondering if someone could show me a simple example I have looked at the exmaple programs as a reference but they are more complex than I would like. E.G the wiki.

Thanks.

[StefanPendl]

What should the program offer the user to enter and what should be stored in the database?

[kirkkaf]

I just wanted the user to enter a username and password and store them for the user to login and just show a message logged in if succesful

Thanks.

[metro]

Hi Kirk

I started by steep learning curve by dissecting this example

runbasic.wikispaces.com/Simple+Database+Log

try posting the code you have created so far and I'm sure you will get some direction from the guru's here

regards

Laurie

[kirkkaf]

Hi,

Here is the code I've been working with;
[database]
'sqliteconnect #records, "Records.db"
'query$ = "create table Users (Username, Password)"
'#records execute(query$)
'#records disconnect()

textbox #username, username$
passwordbox #password, password$
button #signup, "Accept", [Accept]
wait

[Accept]
un$ = #username contents$()
pw$ = #password contents$()
sqliteconnect #records, "Records.db"
query$ = "insert into Users (Username, Password) values (un$, pw$)"
#records execute(query$)
#records disconnect()


I have commented the database part out cause the table has already been created on my computer I will add a check to see if database exists after.

I think I am almost there with this simple example the problem is on this line;
query$ = "insert into Users (Username, Password) values (un$, pw$)"


ERROR: column un$ doesn't exist.

As you probably can tell I am trying to get the textbox and passwordbox content into the table.

Hope this makes sense.
Thanks.

[kirkkaf]

The error is fixed doing this:
query$ = "insert into Users (Username, Password) values (""un$"", ""pw$"")"


But when I print out the information stored on each row it appears as un$ and pw$

[kokenge]

Nothing wrong with your code.
Just a little glitch in you insert

query$ = "insert into Users (Username, Password) values (""un$"", ""pw$"")"


It should be

query$ = "insert into Users (Username, Password) values ('";un$;"', '";pw$;"')"



However the rest of your code seems to work.
I changed it so that the SQLite tables are in memory and added a few users for testing, but other than that it works:

sqliteconnect #records, ":memory:"

query$ = "
CREATE TABLE users(
userNum		integer,
name		text,
username	text,
password	text);"
#records execute(query$)

query$ = "
INSERT INTO users VALUES(1,'Bob Backbone','bob','bob');
INSERT INTO users VALUES(2,'Little John','john','john');
INSERT INTO users VALUES(3,'The Grench','grench','grench');
INSERT INTO users VALUES(4,'Tom Thumb','tom','tom');
"
#records execute(query$)

[getUser]
html "<TABLE border=1><TR align=center><TD colspan=2 bgcolor=wheat>Sign In</TD></TR>"
html "<TR><TD>UserName</TD><TD>"
	textbox 	#username, username$
html "</TD></TR><TR><TD>Password</TD><TD>"
	passwordbox 	#password, password$

html "</TD></TR><TR><TD align=center colspan=2 bgcolor=wheat>"
	button #signup, "Accept", [Accept]
html "</td></tr></table>"
wait

[Accept]
un$	= #username contents$()
pw$	= #password contents$()
query$	= "SELECT * FROM users WHERE username = '";un$;"' AND password = '";pw$;"'"
#records execute(query$)
rows	= #records ROWCOUNT()	'Get the number of rows
if rows < 1 then
	cls
	print "Invalid Username or Password"
	goto [getUser]
end if
#row = #records #nextrow()
Print "Hello ";#row name$()
wait