PHP Tutorials: Register & Login (Part 1): Database

Download the full series and complete source code at https://phpacademy.org/course/registe...

Setting up the database table for our users, so we can store their information. This will allow us to process their login using their username and password, and store any additional information about them.

Register & Login series playlist is available at: http://www.youtube.com/playlist?list=...

Official website
http://phpacademy.org

Support Forum
http://phpacademy.org/forum

Follow us on Twitter!
http://twitter.com/phpacademy

---

Closed Caption:

ok so in this part of the series we're
going to be creating the database table
that stores the data for our users which
is obviously the initial step that we
take to work out which data that we want
to capture for my users the days that we
want to store and then eventually the
data that we're going to give back out
to them in this part we're just going to
be creating the user's table and this is
going to store the registered accounts
later on will
without more tables that deal with
different things but in this part we're
just going to focus on the user's table
itself and that will allow us to create
our overall
login and register system so I've only
created the database table and I've just
call this lr4 login on register
you know you can clear database table
anything you like
usually relating to your site in some
way i'm going to go ahead and create
table on this database i'm using PHP my
admin which is an interface year to out
my stool
server and our databases and this is
available in the browser so you can go
ahead and download this and it's
available with pre-made up packages like
example while I'm anything like that
ok so let's go ahead and create this
table so i'm going to call this user's
now the number of columns
I know because I've already planned this
out and it's going to be seven but if
you don't know that's fine we can just
go back to put in any value here and we
can add fields as we go but I know that
it's seven so I'm gonna 27
you can see that just down here we can
all add one or however many columns on
here
so that's not a problem just a popular
number if you're not too sure
and you might choose to store more data
as well then I am
you might choose to expand your
registration page to let your users say
ask if they want to sign up for my
mailing list or you want to capture that
might want to capture their gender
anything like that
ok so the first field that we're going
to create is a will be the primary key
and this will identify the user is
uniquely and also provide us with an
index for our table
so this is going to use underscore ID
now the type is going to be an integer
this just means it's a whole number
we're never going to have . values in
here and the others were what they were
going to look at our chart and text
eventually but for now we you know
really don't worry too much about what
these me i'll try to explain its like I
belong
so if we scroll all the way along I I
stands for auto increment you can see
that it's just popped up there is a
little tool tip that we want to check
this essentially what this means is this
value will increment for every record
that we add so the first users to alex
is one
this field will automatically reject the
value 1 if we create another record say
Billy will be to do 3 4 and so on and
the index here is primary
so we always have a primary key for a
table to keep it in next
ok so the next one is straightforward as
user name
we obviously don't miss to be a number
we don't use this to be signing up with
numbers for username so we want of their
of our chart which means variable
characters and we have to provide a
length here so I'm just going to go
ahead and say 32 will will incorporate
the length checking into the back end of
our about code eventually we also want a
password and i'm going to set this again
to a bar chart and this is gonna be 32
now we're going we're going to be still
storing this as an encrypted string so
bear this in mind or initially be using
md5 which equates to a 32 character
string
however long the password is of the user
so 35 and upwards
you'll be safe but this may need a bit
of tinkering around with if you're using
different encryption methods obviously
md5 isn't to secure but we're just going
to be using it for
for simplicity for now ok so we want to
store the users first name and we also
want to use this store the users last
name
and these will both be variable
characters again I'm just going to set
these 2 32
you might want to increase this over see
as you go along feel free to email
I'm gonna set to have our channel - it
to a very strange number 1024 1025
either one is the maximum characters for
an email address i always do this out of
habit but it's entirely up to you can
you can choose a short of value it is
unlikely to be but i always like to just
put it in that
and lastly active and this is going to
be the state of the user's account
initially what we're going to do is
we're going to create a login and
register system without email activation
then we're going to go ahead and
incorporate email activation into the
back end and a ask users to activate
their account by email
now when you use a initially registers
this int is going to be set to zero to
mean that they haven't activated their
account so if we look at the top you can
see our
of table labels here you can see that if
we scroll
where is it I'll default here so this
default value here we can set as defined
and then choose a value
what I'm going to do is for active i'm
going to choose as defined and 0 so by
default when we insert a new record we
don't need to specify this value will
automatically be 0 and then thus
requiring the user to activate their
account now we can go ahead and change
this while we're testing we don't
actually need to you know ask the user
to activate their email address i
activate via email but we can go ahead
and change this so let's go ahead and
save this table that you see that we've
got a success
what happened here okay here we are
and so we've created the the table we
can seal all of our information in here
as well
our structure essentially here at the
Browse tab here will allow you to browse
the data and so in this case you can see
a big red app across which means that
the table is empty so it says table
seems to be empty
so what we're going to do is initially
inserts and dummy data into here because
we're going to be building the the log
in part of the system first because it's
the easiest part to do so once we have
inserted some dummy data we can actually
log ourselves into the website and start
to play around with how am I feel and
then really the registration is an
entirely separate entity from the system
because it involves inserting and data
in place into this database table
so let's go ahead and insert some some
dummy data into here and I've click the
insert tab at the top of PHP my admin
and i'm going to essentially just fill
this form out here you can see here that
we've got a second
value but we we've got ignore tip to me
don't actually need to take this one
into account so i'm going to choose a
username and that's just going to be
Alex now passwords are slightly tricky
one because i'm i'm not sure what my
password is going to be encrypted
however mysql has a built in md5 hashing
function
so what we can actually do is we can go
ahead and type in a value here so I'm
just going to choose password is my
password and from this drop-down list
here we have a list of functions that we
can use and i'm going to choose md5 so
essentially we're saying this value is
going to be the result of the md5
function passing this value which will
give us an md5 string will see this in a
minute when we browse after we finished
so first name nothing special it's just
going to my first name and my last name
as well and also going to give my email
address so i'll accept PHP academy' to
org and active
I'm going to leave set to 0 just for now
but we we can you know we can come in
here anytime and go ahead and change
that
i'm going to go ahead and click go go
back to browse and you can see that I've
got a user ID 1 notice that when i click
on insert I didn't actually enter any
data here is automatically given me the
value of 1 i've got my username which I
provided my md5 hash the password which
you can see there doesn't represent the
string password first name last name
email and active so everything is there
that we need to actually log ourselves
in create a session allow us to browse
around and actually authenticate a user
to protect protect against specific
pages
so essentially we've created our
database table or database our database
table and we fill this in with some some
dummy data and in the next part will
actually look at building the site from
there