[Inori]

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:

1. Check if the user arrived via POST
   
2. Attempt to connect to the db
   
3. Check for and abort on errors
   
4. Create and bind parameters to a prepared statement that inserts our data into the table
   
5. Execute and close the bound statement
   
6. Commit the changes
   
7. 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.

[mothered]

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.

[Inori]

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.

[pvnk]

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

[Oni]

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

[Inori]

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.

[Oni]

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]

[mothered]

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.

[Esoterith]

Great introduction for new people ^.^/

[Inori]

Great introduction for new people ^.^/

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