PHP 0 to hero pt.10 - SQL basics
Tags: webdev, beginners, tutorial, php
Published: 2024-01-25 15:00:00

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:

![Box](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y5it63tk8hnc8r259a9o.png)
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.
```sql
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
```sql
SELECT `id`, `username` FROM `users` WHERE `email` = "test@test.com"
```

Remember - conditions can be stacked using `AND` keyword
```sql
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

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

If we want to select values from every column for each record we use `*` symbol
```sql
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
```sql
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

![Records from `users`](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vbj5qybi6yze3sz7pb9d.png)

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
```sql
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
```sql
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
```sql
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`

```sql
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
```sql
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
```sql
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
```sql
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](https://wizarddos.github.io/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