The LAMP Stack :: Part III :: M Is For MySQL

As mentioned before, this should be sounded out My-S-Q-L. A lot of people I have run across call it My Sequel, because it's a continuing movie about themselves. This is not correct. I looked it up a while ago and will find the link to explain. Here it is; states:

So you could locally call it SpaghettiDB if really wanted to. There is some background about calling it "sequel" though that I found out when writing this, even! Back in the 70's the original SQL had a different acronym of "SEQUEL" - which I would pronounce "sequel" instead of spelling it out.

"Did you get that out of the S-E-Q-U-E-L server, Todd?"

"No Freddy, I D-I-D-N-apostrophe-T."

"Well, you don't have to be a jerk about it"

I learned that SEQUEL was not used because it was a trademark of Hawker Siddeley, an aircraft company of the time. So, also, from now on, if someone says "My sequel" I will say "You hold your data in an old airliner from the 70s? Odd."

One more added note, the My part is for the co-founder's daughter who's named My. According to what I think is her Facebook page, she enjoys listening to Michael Jackson. Not unlike the rest of us. Jamona.

MySQL is a program that runs a server kind of like Apache (in a matter of sorts). You cannot just cruise over to MySQL in your web browser, but you can connect to it from outside of your system, configurations permitting. By default, the MySQL server will be listening on port 3306. With that theoretical server IP address from before, you could connect to your MySQL server externally via 192.168.0.7:3306. How would you do that? Like this:

<\?php

$dbhost = "192.168.0.7";

$dbuser = "ImaUser";

$dbpass = "SecretPassword!!";

$dbname = "ImasDB";

$dbh = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die ('no connect '.mysqli_error($dbh));

Wait a second! mysqli? If you haven't heard yet, the whole suite of PHP's mysql connect and commands have been depreciated. Sure they will still be around for quite some time, but they have been replaced with the mysqli versions of the commands. Big difference here, the older mysql commands assumed a connection started while the new mysqli creates a resource object that you must reference when you are using MySQL in PHP.

Here are some examples of Structured Query Language:

-- comments in mysql are two dashes

-- This would create a quick table that simply holds a unique ID and some data

CREATE TABLE mytable (

`id` int(16) unsigned auto_increment primary key,

`stuff` varchar(128)

);

This would create a table to hold your data. Every MySQL table should have one unique column. Most of the time, this is done with an ID column that just holds a number for the row. the auto_increment flag tells MySQL to bump the number up one every time a new row gets added. That way your program doesn't have to worry about it. In PHP if you need that ID from the row you just entered, you can use mysqli_insert_id() and get it.

SELECT * FROM mutable WHERE id > 300 LIMIT 10;

This would be an example of a query that you would submit to the database to receive a selection of rows where the id would be more than 300, but would only return 10 rows.

As time goes on I'm still amazed at all of the ways you can manipulate the data just using the SQL part of the communication process. The more you can process using SQL, the less you'll have to code for once you get it to your higher level programming language like PHP.

Moving on . . . Part IV :: P is for PHP

Comments