Tutorial SQL introduction - Printable Version

Tutorial SQL introduction - Printable Version

+- Sinisterly (https://sinister.li)
+-- Forum: Coding (https://sinister.li/Forum-Coding)
+--- Forum: PHP (https://sinister.li/Forum-PHP)
+--- Thread: Tutorial SQL introduction (/Thread-Tutorial-SQL-introduction)

Pages: 1 2

SQL introduction - Inori - 10-13-2016

Among other relational databases, SQL is a fundamental part of nearly every website. As a prime example, SL's own database stores the contents of threads, posts, user statistics, and virtually everything dynamic about the site. I'll just be doing a quick (object oriented) tutorial of how create and interact with an SQL database using a very simple message board as our example.

Quick preface

Due to some restrictions within MyBB and CloudFlare, I can't post inline PHP or SQL commands, so check out the github gist I posted for the complete code.

First, after MySQL is installed or made available by your host, run "mysql -u <admin username> -p" in your shell. You should be prompted for your administrative password, so go ahead and enter it. It should all look somewhat like below:

Code:
$ mysql -u ao -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Creating the db

Before anything further, we need to actually create the database and table (we only need one for this). I'm using MySQL for this tutorial, so access to that is preferred here.

So let's make our first db! Use the "create database" and "use" commands to create the db and switch to it.

Code:
mysql> create database msgboard;
Query OK, 1 row affected (0.07 sec)

mysql> use msgboard;
Database changed

Creating the table

Now we need a table to store our posts in. We need a post id, a username, post content, and a timestamp to make the complete row. If you want, check out this page and this one for a full explanation of data types, but we won't be using everything listed.

When creating tables, you need to specify the type (as in most programming languages) along with the column name. Our columns will be an INT for the id, a VARCHAR (variable length string) for the username, a TEXT (up to 65536 character string) for the content, and a DATETIME for the timestamp. There's a few special things that I'll comment in the code (code). (you can input multiple lines in the CLI as long as there's no semicolon)

Writing the interface

To add a post to the database, we need a form to send it from and a page to receive it (and that can be done all in one php page, but we're going to do something a bit different). First, we need our form.

Since the ID and timestamp are taken care of automatically, all we need is an alias and body to send off to SQL via POST. (the action attribute is where the data is sent off to)

Now we need something to process and send the data on posts.php (code). This will, in order:

Check if the user arrived via POST
Attempt to connect to the db
Check for and abort on errors
Create and bind parameters to a prepared statement that inserts our data into the table
Execute and close the bound statement
Commit the changes
Close the connection to the SQL server

Displaying posts

Say we want to display only the last 10 posts from the database on the site. We can do this with a single select statement in the first PHP block (code) and some looping in our html section (code).

End result

When all is said and done, you can use PHP's built in server to run the site:

Code:
$ php -S 127.0.0.1:1337
PHP 7.1.0RC2 Development Server started at Wed Oct 12 21:49:43 2016
Listening on http://127.0.0.1:1337
Document root is .
Press Ctrl-C to quit.

Then point a browser to <whatever address you specified>/submit.php and try it out.

RE: SQL introduction - mothered - 10-13-2016

I love reading articles and tutorials relating to SQL, namely MySQL server.

Very well documented and formatted. If I may add, when creating column names, If the name Is two separate words, don't forget to enclosed It In backticks. If not, you'll experience a syntax error.
Much appreciate the guide, thanks.

RE: SQL introduction - Inori - 10-13-2016

(10-13-2016, 02:24 PM)mothered Wrote: If I may add, when creating column names, If the name Is two separate words, don't forget to enclosed It In backticks. If not, you'll experience a syntax error.

In almost every situation, using snake case (lowercase and underscores) is easier to work with, but nonetheless a valid point.

RE: SQL introduction - pvnk - 10-13-2016

Ty 4 informative tutorial. Your efforts are greatly appreciated by the citizens of SL.

RE: SQL introduction - Oni - 10-13-2016

If you know how to use PHP/MySQL well enough, you could essentially write the next MyBB. Tongue

RE: SQL introduction - Inori - 10-13-2016

(10-13-2016, 08:35 PM)Oni Wrote: If you know how to use PHP/MySQL well enough, you could essentially write the next MyBB.

Forums are actually pretty easy and have fairly minimal relational columns. It's the plugin API that would suck to manage.

RE: SQL introduction - Oni - 10-14-2016

(10-13-2016, 10:34 PM)Ao- Wrote:
(10-13-2016, 08:35 PM)Oni Wrote: If you know how to use PHP/MySQL well enough, you could essentially write the next MyBB. [emoji14]

Forums are actually pretty easy and have fairly minimal relational columns. It's the plugin API that would suck to manage.

Managing plugins is a whole different kettle of fish. [emoji14]

RE: SQL introduction - mothered - 10-14-2016

(10-13-2016, 04:26 PM)Ao- Wrote: In almost every situation, using snake case (lowercase and underscores) is easier to work with, but nonetheless a valid point.

I certainly agree.

I particularly tend to reference my tables with underscores. It makes It easier to Identify, but that's just me. In terms of column names, I separate the entries, hence the backticks must be used. There's no right or wrong, each to their own.

Thanks again.

RE: SQL introduction - Esoterith - 12-17-2016

Great introduction for new people ^.^/

RE: SQL introduction - Inori - 12-18-2016

(12-17-2016, 05:24 PM)Jay_mybb_import25698 Wrote: Great introduction for new people ^.^/

Thanks

unrelated, you should contact Oni to get your username fixed.