PHP 0 to hero pt.10 - SQL basics

When we have SQL database ready - let's start learning language itself

SQL, as I said, stands for Structured Query Language

It looks a bit like a sentence in English

Now, we need to learn actual syntax and how to send those syntax

So, what do we do today?

1. What is query and how to execute them in phpMyAdmin
2. Different query types and their syntax

Let's go

Running queries

Before everything - what is query?

Query is a "sentence" in SQL, that tells database to interact (Get, edit, delete) with records.

You will see a lot of examples of them today

But, how do we execute that query in phpMyAdmin?

When we select table there is a little box above records:

When we click Edit or Edit inline we can run our own queries on the table

When we know, how to execute that query - let's learn some basic ones

Queries

Don't get shocked - it's a best and very common practice to write SQL keywords in uppercase (with caps lock)

So, when we write query

• values defined by us, like table/column names - lowercase
• SQL keywords (stuff built-in language) - uppercase

When we choose the table, this one query is executed.

SELECT * FROM `users`;

What is it?

SELECT

SELECT allows us to get every row that meets specific conditions

For example: we want to select value from table users from columns named id and username where email value equals test@test.com
Then, we'd write query like this

SELECT `id`, `username` FROM `users` WHERE `email` = "test@test.com"

Remember - conditions can be stacked using AND keyword

SELECT `id`, `username` FROM `users` WHERE `email` = "test@test.com" AND `password` = "Some hashed Password"

You can place as many AND as you want

So, formula for SELECT query looks like this

SELECT [Columns] FROM [table] WHERE [conditions]

If we want to select values from every column for each record we use * symbol

SELECT * FROM `users` WHERE `email` = "test@test.com"

That's pretty much it - we'll get used to it when we'll be actually working with SQL

INSERT

Let's say we want to add some record to the table - that's what INSERT is for

So, to add a new user to our users table, just run

INSERT INTO `users` VALUES(NULL, "user1", "$2y$10$7uKWbeYe7X/oZyQT/fxxfOp8ichcShxejqSXAOSYbMFiNCwkpe70.")

Last value is bcrypt hash of test123 string

Try it - click Edit or Edit inline (eventually SQL in top nav) and paste that query

After execution, we should see one record

Why is first value NULL? That's why we set up Auto increment for this column - now when we pass null it replaces it with numerical value of previous record and adds 1

So, if we run this query couple of times - then we get ids equal to 2, 3, 4 and so on

Here is the formula for INSERT query

INSERT INTO [table] VALUES([Values separated by comma])

Where number of values passed has to match number of columns

DELETE query

Our user doesn't really like his new task - he want to get rid of it

That's where DELETE comes in handy.

With this task - let's see a quick example

DELETE FROM `tasks` WHERE `task_id` = 1

1 here is just an example - in reality it would be value passed by PHP

Whole "recipe" for this query looks like this

DELETE FROM [table] WHERE [conditions]

UPDATE query

But if a user made a typo in it's title? Will we get whole contents of that record, then delete it and insert new?

That's additional 3 queries. Multiplied by 1 million users it gives us 3 million queries - too much

That's why we have UPDATE

UPDATE `tasks` SET `title` = "Something new" WHERE `task_id` = 1

It will set title to Something new in record with task_id equal to 1 from tasks table

And basic syntax looks like this

UPDATE [table] SET [updated values] WHERE [conditions]

Dangers in UPDATE and DELETE

While working with DELETE and UPDATE we need to be sure, that WHERE conditions are valid - otherwise we might wake up with whole table looking identical or with no table at all

Unless we are specifically sure, that we want to update every record in table or delete whole content of the table, don't forget WHERE clause

Order of results

So, we have a bunch of posts but if we want to show them - it will display the oldest ones first (as that's the order in database)

Do we have to reverse that table in PHP itself? No - just use ORDER BY

For example with tasks

SELECT * FROM `tasks` WHERE `creator` = "johnny" ORDER BY `task_id` DESC

DESC will order the results alphabetically reversed (So here, from the greatest to lowest id)

And just ORDER BY (or ORDER BY [column] ASC) will return results in alphabetical order

LIKE keyword

SQL LIKE keyword allows us to search for specific pattern in column

So, I'll give an example

Let's say we create a shop and we have a database of cities

We want to see every city that starts with the letter L

So - we write this query

SELECT * FROM `Cities` WHERE `name` LIKE `L%`

From SQL to english

select all the columns from records from Cities table where name begins with L

% is so-called wildcard and it means any number of characters or even no at all

There is also one more useful wildcard - _
It represents a single character

So if in DB we'd have these 2 values

1. Lon
2. Lo

With %, both of those meet condition
But is we use _ - only the second one will get returned

Conclusion

That's pretty much it - I hope you have learnt something, as I'll require that knowledge in the next part

I thought, this part will have a delay (as I was working on something) but luckily it was short so it got posted in time

What I was working on? On my own blog

So yeah, most of the articles will get posted there - especially non-coding ones

If you want to have access to this course earlier - check my blog, as I'll post those articles as soon as I finish writing them

Also, check out my other articles and another parts of this course - and see you in the next ones