thirteen

mysql databases

a database is a place to store large amounts of highly-structured data. if what you're storing is either not a lot of data or not highly-structured, a filesystem is likely a better place for it.

if you have some highly-structured data and some unstructured data -- like a huge table of user information and a set of photographs and videos for each user like instagram or tiktok -- you may want to store the structured data in a database and the unstructured data in a filesystem.

most databases you deal with on the web are going to use sql (structured query language), pronounced the same as the sequel to a movie, to communicate so we'd better start with some sql and table structure basics.

a database table is exactly what it sounds like. it's a set of specific questions (columns) and individual groups of answers (records). each group contains an answer to every question. if you're familiar with a spreadsheet, it functions in much the same way. if you're not familiar with a spreadsheet, you may want to remedy that before continuing because that's a good visual representation of how database tables work. you can start with a table looking something like this called user...

this looks just like a spreadsheet table but that's where the similarity ends. the point of a spreadsheet is to be able to deal with the entirety of a set of data easily while a database is designed to do exactly the opposite -- deal with specific subsets of data, often either one or zero records at a time -- based on parameters (queries). you can think of a database query the same way you would about a web request. you send a specific request to the database and it returns a result.

for most things, you'll probably create your database structure using an interface, not sql code. either way, we're not going to get into the intricacies of the entire sql scripting language, only the part that's typically used in php. in other words, we'll look at four commands -- select, insert, update and delete. select gets information from a database. insert adds it. update changes it. delete removes it. this is somewhat self-explanatory but, given how words are sometimes used in technical situations, it's best to be precise.

even with such a simple table, we can ask many questions so let's look at some and the sql queries to perform them.

it's good to be aware that, unlike php, sql commands are not case-sensitive. i recommend always writing them in lowercase for readability. it's also good for consistency because you're used to php, which uses lowercase commands.

what are the names of the users whose accounts were created since the beginning of 2018?

the column we want is userName. midnight january 1, 2018 in unix time is 1514777400.

select userName from user where userCreatedDatetime >= 1514777400

what are the user ids and names of inactive users?

select userId, userName from user where userActive = 0

you've likely noticed two things already. first, sql uses = instead of == for equality. the second is that true and false are replaced by 0 and 1.

is sophie koh's password "puppy7639$@"?

now we have hit a limitation of standard sql and it's important we address this now. a lot of introductions to php/sql will show you something like this...

select count(userId) from user where userActive = 1 and userId = "5c54435e-56b8-4a13-8247-ee457e93388a" and userPassword = "puppy7639$@"

this works for unhashed passwords. in other words, passwords you can look up in a database. that leaves the database open to severe repercussions from anyone seeing the passwords inside. the solution is to "hash" the passwords, encrypting them so they can only be easily transformed in one direction and compared. that means a single password can generate many possible hashes but they all follow a specific algorithm so they can be checked to make sure they match without allowing the hashed version to be transformed back into the original password.

the password we're checking, "puppy7639$@", can produce all these hashes...

$2a$12$F36pFyaly/k7riOE5Mx7Yufk4I5idC96tSoC1Y.nUW8T6qgwdmtc2
$2a$12$e2QXdBA3jkjITqrS1ds6.e72f7ZxagfwTnSV43hEUqjpVC3CuFdQS
$2a$12$sOENw/grIrkBB25ZjuOGI.Muow9W2EYMdvnU6OQ4zYE/cprVedRKO
$2a$12$Z9fvo8hHPeX/pb5L4zufH.wjHargkJrY9w7QDIcEpOn0fbYAfrcoa
$2a$12$LnKs5EFglypJYBOKJzNgWest04Z9vjlx0dhmkystG4Nu5w7rNDxEy

and a nearly limitless quantity more. the specific format (starting with $2a$12$) is unimportant for our purposes at the moment.

what we need to do instead of relying on the database to check the password is this...

select userPassword from user where userActive = 1 and userId = "5c54435e-56b8-4a13-8247-ee457e93388a"

now that we have the hashed password, we can use php to check that password.

password_verify("puppy7639$@", $hashedPassword)

returning true or false -- in this case, false, as that's not a valid match to the hash in the table.

you may already have noticed something else about this example. more than one condition being required together is joined by and. or is also possible, these being the sql equivalents of && and || from php.

what about more information, though? do we just add more columns? yes, that's a valid possibility but rarely the best answer. for example, lots of users will have multiple emails or addresses. so we can't just add an email or address field. you could say they have a maximum potential number of them of three but that's unnecessarily complex. this is where the idea of a relational database comes in in its simplest form. the key to a database's usefulness isn't just in being able to return answers to queries but in being able to understand the relationships between multiple tables. let's add another table called email.

before we look at the relationship between user and email, we should talk about id fields. sql databases have the ability to automatically number fields and this is a common way to generate unique ids. they start at 1 and increment every time a new row is added so there is never any duplication. that's typically how people tell you how to do it.

they're wrong.

first, it adds another step if you want to know what that unique identifier is. you have to get it back from the database instead of already knowing it in your program if you create it in your code. second -- and far more importantly in most cases -- it doesn't scale or work asynchronously. if you're linking databases asynchronously across regions or servers, one has no idea what the next number is in another's storage. generate unique ids, though, and you never have to worry.

they also don't hash nicely for creating split data. once your data gets large enough, you'll probably want to have different servers for different users. but you don't want to just say "all users below a million go on this server and we start a new one here" because that's both unbalanced and unsustainable. the best way to do it is to use a unique id and have all users starting with a particular pattern on a specific server. need to add a new server? just change one breakpoint and you divide one server into two and the data doesn't have to change on any of the others.

there are lots of ways to generate unique ids. i suggest using uuid v4 or v7 depending on your situation. there are commonly-used functions you can borrow for either of these or write your own. it usually only takes a half-dozen lines and you already know all you need to write one if you prefer.

you may be wondering why it's important to have an id in your tables. that's a completely valid question because it's not immediately self-evident. it's because you need to be able to change or remove a specific row and it might not be unique in other ways. for an email table, for example, you might imagine those emails will be unique. but is that really the case? what if you have children's accounts that need to be linked to their parents' emails? what if you add a feature allowing users to have backup contacts in case they get locked out or for use in case of emergency or death? you may never need to use your id field but it's good practice to have one. i don't think i've ever used a database where i didn't address records by id at some point in the life of the project. you probably won't, either.

let's modify our earlier query for users since the beginning of 2018 and add returning their primary emails.

select user.userName, email.emailAddress from user, email where user.userCreatedDatetime >= 1514777400 and user.userId = email.userId and email.emailPrimary = 1

there are certainly other ways to do this using join but there's no need to add complexity for something so simple. you just need to determine the case where everything you want is true. the user's creation was after a particular date and time and the email address is linked to the user id attached to that user. the relationship here is what we can refer to as "one to many". there's a single user with each userId in users but potentially many emails for a single userId in emails. we only want their primary emails so we filter by that boolean. remember, booleans are 0/1 instead of false/true in sql.

two other sql keywords are particularly useful along with select. one changes the order of the results while the other limits the number of results.

as you might expect, given the english-friendly nature of the sql you've seen so far, these are order by and limit. i'm not certain sql is the most english-like scripting language but, if it's not, it must be close. these two are usually used in combination. there is no particular reason why it's order by instead of order. i assume this was a simplification oversight that just never got corrected.

let's say you want to send an email to only your first ten users.

select user.userName, email.emailAddress from user, email where user.userId = email.userId and email.emailPrimary = 1 order by user.userCreatedDatetime asc limit 10

first, you sort the data that matches by their creation date/time ascending (the alternative is desc for descending) then you limit it to ten results. that way, whatever the order of the data in the table, it always chooses the ten oldest users. limit also has an extra parameter for potentially skipping some results. if you wanted the next twenty...

select user.userName, email.emailAddress from user, email where user.userId = email.userId and email.emailPrimary = 1 order by user.userCreatedDatetime asc limit 10, 20

it's important to put the skipping number before the limiting number. yes, this is counterintuitive. that's why i'm specifically pointing it out.

inserting, updating and deleting should now be easy by comparison because most of the syntax is the same as what you've already seen.

we'll start with deleting because it's the most similar to selecting. let's say you want to delete all the email addresses for evelyn silk. we already know the user id so...

delete email where userId = "5779e2c8-328d-40c5-a1db-72deed688a34"

there are two things to note. first, you can only delete whole records so there's no need to specify specific columns. second, delete is a potentially-dangerous command. you won't be prompted and there's no undo. if you issue delete email without the where, it won't delete nothing. it will delete everything. the where is the limiting factor, not the searching factor. i can't emphasize enough how important it is to double-check your commands if they include delete before allowing them to run on your databases.

something else that's important to pay attention to that you may already have noticed is that, if you're only referencing a single table, you don't need to add the table name before the column name. you certainly can. it's just unnecessary. if you're using more than one table, it might be able to guess which one you mean if the column names are unique but it's always a good idea to be specific. if you prefer to user the table name in every query, that's totally ok. this command could also be delete email where email.userId = ....

to add a row to a table, the syntax looks a bit unusual compared to what you've already seen.

insert user (userId, userName, userPasswordHash, userActive, userCreatedDatetime) values ("985a98e0-1773-43cc-81dc-646908176c76", "Jennifer Bull", "$2a$12$DyAHmWQhzvJvs/XxwMWmXeJ6Vj497StR4GAcJAQFvKavipqWoxXpe", 0, 3397790590)

this will look more like what you're used to with php functions than typical select or delete commands in sql. the insert command can also be written as insert into but the into is optional for mysql so there doesn't seem to be any particular reason to add an extra word. the syntax is probably self-explanatory at this point. first the name of the table. next a list of columns you want to set in (). then the keyword values, followed by the values for those columns in the same order you listed them.

if columns have default values or can accept having no value at all (null), they don't need to be in your list if you don't want to set them. you'll get an error if you leave something out that needs to be set, though. note that we're storing the date and time as unix time instead of the built-in datetime option within sql. some people still use the built-in version but, when working with php and most other modern languages, it's much easier to use the date and time values the language understands natively, storing them as integers.

you can add more than one set of values at a time by listing them, comma-separated.

insert user (userId, userName, userPasswordHash, userActive, userCreatedDatetime) values ("985a98e0-1773-43cc-81dc-646908176c76", "Jennifer Bull", "$2a$12$DyAHmWQhzvJvs/XxwMWmXeJ6Vj497StR4GAcJAQFvKavipqWoxXpe", 0, 3397790590), ("dc123c07-029d-4271-8217-79223d985096", "Andrea Ye", "$2a$12$1QaFl1Ldd39BL0P1p8DeXug3qa4sFvYOlpH.fkYwUSexxMF8ZK1ce", 0, 3397790824), ("e50fd9f9-f55c-43de-8610-24102dedeace", "Claire Sorrow", "$2a$12$ngCXh5RxyGZWzfnMDacgXeTw..R9yczI8nKot1qNlqljjkZD7pa2W", 0, 3397797710)

sql commands are like php in that they ignore whitespace. you could add line breaks and spaces to make this command more readable and, if you were writing it by hand, you probably should. writing sql by hand, as a php developer, though, is probably something you'll almost never do. you'll create your sql commands using php so there's no point in adding extra whitespace. even the spaces between the commas are optional and i've included them here to make it easier for you to read. i often do include the spaces because it makes database log files easier to read if there's a problem and i generally recommend that as a good practice. there's no point in making your life more difficult in the stressful debugging situations.

the last of our commands is update, to change existing values in tables. while that might sound like something that happens all the time, in most cases, it's relatively rare. for example, you generally let users add and remove emails rather than changing existing ones because they require verification and you don't want them to be left without one that's verified at all. in cases where it's necessary, though, it's good to know how it's done.

the one place it tends to show up is changing things between active and inactive. to deactivate harper wren's account...

update user set userActive = 0 where userId = "ee14df7c-49ea-46bd-b407-87ecc5e0fbb7"

if you've gotten to this point and find yourself thinking "what's the catch?" or "this is too good to be true", you're not alone.

the catch is that there are very complex queries you can perform in sql to join tables together or select calculated values. but it's only a catch in the most technical sense because you probably won't actually encounter any of them in practice as a junior php developer. in the practical sense, most things come down to storing things in databases one piece at a time and retrieving them one or a few pieces at a time based on some very simple conditions like keyword, membership in a list or date. it's good to be aware of the fact that complex sql exists but, if it is necessary for something you're writing, you'll likely be dealing with database administrators who do the sql side of things. this is only an introduction to sql but it will hopefully be enough to get you started.