The SQL update table_name... statement is used to update existing database tables, which can be useful when we want to create an edit feature in an application. But, another concrete example of when this is useful is when creating a simple view counter, which I will cover later in this tutorial.
The update statement allows us to update all the rows in a database table at once, so it is extremely powerful, and you could break things if you are not careful when using it. Normally you would probably only want to update specific rows in the table.
To only update specific rows and/or columns, we should include a where clause in the query.
To update a specific row:
update my_table_name set column1='new value' where name_or_id="something unique :-)";
Note. Using a unique index will improve performance when working with large tables.
Updating specific rows
A very useful feature of the SQL language, is the ability to work with date ranges, and entire tables at a time. The update statement makes it possible to update multiple columns with a single query—the same syntax works when using select queries.
To update a rows within a date range:
update my_table_name set column1='new value' where datetime_column between '01/01/2019' and '01/01/2020';
To update all rows in a table:
update my_table_name set column1='new value';
Incrementing a counter in SQL
A good example for beginners is probably how to create a counter that tracks the number of visitors on individual articles, for this, you need to keep a counter column in a table, and then increment the counter each time a page is requested. Often you will also want to filter invalid traffic, such as repeat views, and users hitting refresh within a short period of time—I am not going to show how to do that in this tutorial, but I might show it in one of the other tutorials.
Before we start, know that databases are useful for creating applications that handle concurrent users. This makes them ideal for creating a simple web page counter. In this tutorial I am using MySQL, but it should also work with other databases that support SQL.
You may be wondering if you can use a text file located somewhere in your file system for creating the counter. The answer is that you can, and it might even be faster than using a database, since your app does not have to spend time connecting to the database. The problem is, you will have to find a way to deal with concurrency on your own, and you therefor run the risk of corrupting the data. When you use a database, concurrency handling is build-in.
Consider the below table layout for a simple web-page counter:
+------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | url | varchar(128) | NO | PRI | NULL | | | view_count | int | YES | | NULL | | +------------+--------------+------+-----+---------+-------+
This table also allows for adding new features related to articles. So, if you later want to add a word count feature, you, just create a column to store the word count in each article, this can then be updated whenever an article is posted or edited. Such a feature would allow you to keep track of your content better, and thereby identify content that does not meat your guidelines for minimum content-length.
Each article is stored in the article_stats table, and identified by the URL of the article. If we do a select on the table to return its contents, we would get something like this:
select * from article_stats; +-----------------+------------+ | url | view_count | +-----------------+------------+ | /about | 29311 | | /privacy-policy | 12322 | +-----------------+------------+ 2 rows in set (0.00 sec)
To create this table, we can use this query:
create table article_stats (url varchar(128) primary key, view_count int);
And finally, to update the view_count on each page view we can use the update statement:
update article_stats SET view_count=view_count+1 where url='/about';
Of course, to get the requested URL we should use REQUEST_URI with parse_url to get the correct part of the URL that was requested by the client. But, that, I will save for another tutorial!