TweetFollow Us on Twitter

Your First MySQL Data Entry

Volume Number: 20 (2004)
Issue Number: 1
Column Tag: Programming

Untangling the Web

by Kevin Hemenway

Your First MySQL Data Entry

Finally, let's define your database tables and enter some data.

In our past few issues, we've explored installing MySQL and creating a database and user with mysql_setpermission. Along with their access permissions and capabilities, we've verified that things had gone smoothly by using /Library/MySQL/bin/mysqlshow -u root -p to show the list of MySQL databases currently available (in which mactech, our in-progress database, was visible). What we've yet to do is actually define our database (what sort of data it'll be representing) or insert any of the data itself.

Before we do, allow me to demonstrate another timesaving tip: .my.cnf.

Saving Keystrokes With Your Personal .my.cnf

As we've been issuing various MySQL commands, we've always had to be careful to pass MySQL username and password - certifying to MySQL that even though we're the OS X morbus user, we really want to act as the MySQL root user. Depending on how confident you are with your computer's security, you can remove the need for the username and password for most future MySQL related commands.

To do so, open up a blank text document and add the following lines:

[client]
user=root
password=yourMySQLrootpassword
# The following configuration definition is optional,
# but becomes useful if you were connecting to a MySQL
# server on an entirely different machine. We'll
# leave it commented here, since we'll only be
# accessing the local MySQL installation.
#
# hostname=some.other.server.com

You'll want to save this file as .my.cnf in your home directory (/Users/morbus/.my.cnf, in my case). Be sure to include the beginning dot: that's required for the configuration to be read properly, but it will also (depending on your settings) hide it from view of the OS X Finder and prying, but unskilled, eyes.

Since this new file contains the MySQL root password in plain text, we want to ensure that only our account can read the file. To do this from the Terminal, run the following command, which gives read and write permissions only to the morbus user: chmod 600 ~/.my.cnf. Alternatively, if you can see the file in the Finder, "Get Info" on it, and expand "Owners & Permissions", then "Details". Give the "Owner" "Read & Write" access, and the "Group" and "Others" should have none. Your final results should look like Figure 1.


Figure 1: The access permissions of the .my.cnf file.

With the .my.cnf file in place, you should now be able to run /Library/MySQL/bin/mysqlshow without requiring a username and password. Likewise, some other MySQL utilities can have other options preset to save keystrokes. For instance, mysqlshow test will show you the defined tables of the test database:

:~ > mysqlshow test 
Database: test
+--------+
| Tables |
+--------+
| testac |
| testad |
| testae |
| testaf |
+--------+

But adding the --status flag will give you a much healthier dose of information (which is so wide we can't easily replicate it here). If you find yourself issuing the --status flag a lot, you can save yourself time by adding that to .my.cnf under an application-specific block:

[mysqlshow]
status

The basic rule is: if the MySQL utility accepts a double-dash command line flag (like --status, --username, --password, etc.), then you can define it as a preset within .my.cnf under a block named after the application. This can occasionally be useful, but I find myself using shell aliases far more frequently (which are outside the jurisdiction of this column.)

Defining the MacTech Database

Anytime you interact with a MySQL database, you'll be talking to it in a language called SQL, or "Structured Query Language". SQL is an industry standard for database communication, and all known databases support it in some way (and occasionally, differently than what the standard suggests). We won't be teaching you a lot of SQL in these columns, instead focusing on just enough to get you started. The complete reference of the SQL implementation of MySQL can be found at http://www.mysql.com/.

The first bit of SQL you'll learn is how to define the database you're creating. When these SQL definitions are included along with other documentation on how you've approached the database design, you've created a "database schema". Schema's can be immensely helpful as a reference while your writing code, as well as when you later need to define a new query.

Take a look at Listing 1, which contains the SQL statements to define our mactech database. Save this into mactech.sql--we'll be piping it to a command line utility shortly.

Listing 1: Our database definition, in SQL.

mactech.sql
CREATE TABLE person (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  name VARCHAR(255),
  date_of_birth DATE,
  date_of_death DATE,
  title VARCHAR(50),
  designation VARCHAR(255)
);
CREATE TABLE books (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title TINYTEXT,
  publication DATE,
);
CREATE TABLE relationships (
  person_id INT NOT NULL,
  book_id INT NOT NULL,
  INDEX (person_id,book_id)
);

There are three SQL statements in Listing 1, and the semi-colon that completes them is required for all statements you write. Each individual statement creates one table, being a set of columns that describe your data, and the rows that contain it. If you know HTML, they're exactly the same as the <table> tag (assuming it's not used for layout).

The first table, person, contains fields (or columns) that define an individual: their name, date of birth and possible death, title (which could be ranks like "Major", "President", or addresses like "Sir", "Ms", etc.) and designation (which could be succession like "Jr.", "III", "the Brave", etc.). The second table gives a rather meager definition for books, containing only their title and publication date.

You'll notice that each column is also described by what sort of data they'll contain. A person's name can be a variable length, with a maximum of 255 characters, whereas strings like date_of_birth, date_of_death, and publication are DATEs, funnily enough. A person's title has been limited to a maximum of 50 characters, whereas designations could be much longer (assuming extenuating circumstances like "the Brave Sir Knight of Camelot who has Sleweth the Dragon of Ill Contempt!") There are over a dozen different types of database columns; definitions of each can be found in the MySQL web documentation.

Our person and books tables each contain a column called id, which auto increments by 1 for every new row of data entered into that table. These serve to uniquely identify that particular record--person #15 today is going to be the same person #15 three weeks from now. As such, these columns must always have an integer value, as defined by INT and NOT NULL. Since AUTO_INCREMENT is set, MySQL will happily fill this data in for us--we'll only need to worry about it when we're selecting that record ("gimme person #134!"). A PRIMARY KEY, which is similar to the INDEX in our so-far-ignored third table, will make these lookups of data more efficient.

Our final table, relationships, contains two columns that represent ids from our other two. This is what makes a "relational database" relational: they're designed to relate records in one table with records in another, creating a communal result. Entire books have been written on how to smartly design databases, but let's go over a quick example to show how a relational database can ensure data integrity. Say you've the database in Listing 2, with a sampling of the data within shown in Listing 3.

Listing 2: A non-relational database.

non-relational.sql
CREATE TABLE works (
  person_name VARCHAR(255),
  book_title TINYTEXT
);

Listing 3: Some sample data from our non-relational database.

non-relational.txt
+----------------+-----------------------------------+
|  person_name   |            book_title             |
+----------------+-----------------------------------+
| Kevin Hemenway | Mac OS X Hacks                    |
| Kevin Hemenway | Spidering Hacks                   |
| Kevin Hemenway | Advanced Procrastination          |
| Kevin Hemenway | Caffeine: Nature's Sleeping Pill  |
+----------------|-----------------------------------+

Listing 3 contains a critical "database no-no": duplicate data. For every book I write, my name is repeated, meaning there are that many more chances for misspellings or similar errors to occur. And what happens if I suddenly become "Kevin Hemenway, Sr."? Suddenly, someone has to update four records before the correction is complete. This updating of multiple records can also bring the same possibility of corrupted data. Likewise, duplication of data is a waste of space: if I write a hundred books, you've got one hundred strings worth of "Kevin Hemenway", an after-school chalkboard experience that's just useless.

A relational database helps you store data just once: instead of a hundred Hemenway's running around on the moon, you'll only have one because you'll be relating a single record in the person's table with a single record in the book table. Since the relationships are keyed toward the unique id of the tables, adding "Sr." to my name affects only that single row of that person table: the linkage defined in relationships remain unchanged. Listing 4 shows a partial (for brevity's sake) example of two of my books Listing 1's database.

Listing 4: An example of data in a relational database.

mactech.txt
Table: person
+----+----------------+-------+-------------+
| id |      name      | title | designation |
+----+----------------+-------+-------------+
| 1  | Kevin Hemenway | Mr.   |             | 
+----+----------------+-------+-------------+

Table: books
+----+-----------------+
| id | title           |
+----+-----------------+
| 1  | Mac OS X Hacks  |
| 2  | Spidering Hacks |
+----+-----------------+

Table: relationships
+-----------+----------+
| person_id | book_id  |
+-----------+----------+
| 1         | 1        |
| 1         | 2        |
+-----------+----------+

Even though it's more tables and appears to be more "work", you've segregated and categorized your data so that it stands alone without duplication, and related it to one another via the unique ids assigned to each table. You can modify the data of a book without having to worry about corrupting the data in person, and vice versa.

Running SQL Commands Against Your Database

This is all fine and dandy, but we've still yet to send the SQL commands in Listing 1 to the mactech database we created last issue. To do so, make sure you've got your mactech.sql file nearby (the following steps assume it's located on your Desktop) and open a Terminal. Since we've got our commands in one file, we're going to send them to MySQL in one big batch. If you've not created a .my.cnf, be sure to add -u username -p:

:~ > mysql mactech < ~/Desktop/mactech.sql

If there are no syntax errors in your SQL (like a missing comma, semi-colon, incorrect column type, etc.), then an error will be reported; otherwise, MySQL will succeed silently. So how do you really know if things went all right? Just like we confirmed our database creation with mysqlshow, we can pass the database name to get specific information, or even a table name to get more (output has been truncated, and again, beware of your username and password if you've not set them in .my.cnf):

:~ > mysqlshow mactech
Database: mactech
+---------------+
|    Tables     |
+---------------+
| books         |
| person        |
| relationships |
+---------------+

:~ > mysqlshow mactech books

Database: mactech  Table: books  Rows: 0
+-------------+----------+------+-----+
| Field       | Type     | Null | Key |
+-------------+----------+------+-----+
| id          | int(11)  |      | PRI |
| title       | tinytext | YES  |     |
| publication | date     | YES  |     |
+-------------+----------+------+-----+

Since we've yet to add any data into our database, the status of the books table reports 0 rows. Let's go ahead and add the data as seen in Listing 4. Open up a new text file called mactech-insert.sql, and transcribe the contents of Listing 5 into it.

Listing 5: Adding data to our relational database.

mactech-insert.sql
INSERT INTO books
   VALUES ("", "Spidering Hacks", "2003-11-01");
INSERT INTO books (publication, title)
   VALUES ("2003-04-01", "Mac OS X Hacks");
INSERT INTO books
   SET publication = "1998-03-01",
       title = "Caffeine Dreams";
INSERT INTO person
   VALUES ("", "Kevin Hemenway", "", "", "Mr.", "");
INSERT INTO relationships VALUES (1,1);
INSERT INTO relationships VALUES (1,2);
UPDATE books SET publication = "2003-03-01" WHERE id = '2'; 
DELETE from books where title = "Caffeine Dreams";

There are a lot of different things happening here, so we'll step through each one. The first three statements are different ways of saying the same thing: "we want to insert this data into the books table." The first assumes you know, exactly and without fail, the order of the columns within your table. Three years from now, if you find yourself adding a new column between the id and title, this SQL statement will break. You'll also notice that the first column, being id, is left blank: this tells MySQL (as per our database definition in Listing 1) to insert the unique ID automatically, in this case "1" (since this is the first row we've added).

The second statement solves all the problems of the first by specifying, in any order, which columns the data should go in. The first column corresponds to the first argument to VALUES, the second column to the second, and so on. Since we're specifying columns by name, we don't need to mention id at all as MySQL will handle that for us. The third statement is yet another way of INSERTing data, and is my currently preferred method (see UPDATE, below).

The fourth statement is another INSERT, only into our person's table. I've left most of the fields blank, primarily so that it wouldn't word-wrap on the printed page. If they were defined, they'd just be dates and strings, something I'm sure all readers are familiar with.

The fifth and sixth columns insert IDs into our relationships table. Since I know that these were the first two books entered, I know what IDs they've received: 1, and 2, respectively. Once we get into interacting with MySQL with PHP and Perl (next article), I'll show you how to programmatically find out what IDs just-added data receives (which is a much smarter way of doing things than hard-coding, as we do here).

The seventh statement shows how to update data you've already added. It is very similar to our third INSERT statement (the syntax I prefer) save for two changes: instead of INSERT, it's UPDATE, and we specify which rows to update with a WHERE clause. You'll be using WHERE's an awful lot with SQL as it's the primary method of selecting relevant results. Because the syntax of UPDATE is very similar to the syntax of our third INSERT, it's easy to minimize duplicated code within any of our programs. I use the logic in Listing 6... with this style, if the columns in books ever change, I only worry about one location (for both INSERT or UPDATEs) and not two (necessary if we were using differing syntaxes).

Listing 6: Example logic to minimize code and SQL duplication

pseudocode-for-update-inserts.txt
$sql = "";
if    ($updating)  { $sql = "UPDATE books SET "; }
elsif (!$updating) { $sql = "INSERT INTO books SET "; }
# assume variables are userinput
$sql .= "title       = '$title', " .
         publication = '$publication' ";
# same assumption with $id - user specified it.
if ($updating) { $sql .= " WHERE id = '$id';" }

The eighth and final SQL statement in Listing 5 is an example of deleting a record, again determined by a WHERE clause. Be especially careful with DELETE statements: once your data is gone, it's "gone" gone.

To execute this batch of statements, we run the same command line as before, only with our new filename: mysql mactech < mactech-insert.sql. As before, the command will succeed silently, otherwise an error will be spit to the Terminal. You can verify that things went smoothly by checking the number of rows displayed in a mysqlshow statement: if there are two for books, two for relationships, and one for person, you did jim-dandy.

Homework Malignments

Next issue, we'll talk about how to SELECT data from our database, as well as how to perform more INSERT, UPDATEs, and DELETEs within a programming language like PHP or Perl. If we have space, we'll throw together a few steps on installing applications like phpMyAdmin and CocoaMySQL. Until then, contact the teacher at morbus@disobey.com.

Self, don't forget to put funny stuff here.

Uhhh... should we leave this in? -Editor

Kevin said he'd email us. What about our deadline? -Layout

He's never let us down before. He'll come through. -Editor

Kevin Hemenway, coauthor of Mac OS X Hacks and Spidering Hacks, is better known as Morbus Iff, the creator of disobey.com, which bills itself as "content for the discontented." Publisher and developer of more home cooking than you could ever imagine (like the popular open-sourced aggregator AmphetaDesk, the best-kept gaming secret Gamegrene.com, the ever ignorable Nonsense Network, etc.), he has started his indexing and cataloging project two months earlier than he intended. Contact him at morbus@disobey.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

TextSoap 8.3.2 - Automate tedious text d...
TextSoap can automatically remove unwanted characters, fix up messed up carriage returns, and do pretty much anything else that we can think of to text. Save time and effort. Be more productive. Stop... Read more
Apple Remote Desktop 3.9 - Remotely cont...
Apple Remote Desktop is the best way to manage the Mac computers on your network. Distribute software, provide real-time online help to end users, create detailed software and hardware reports, and... Read more
Paragraphs 1.1.4 - Writing tool just for...
Paragraphs is an app just for writers. It was built for one thing and one thing only: writing. It gives you everything you need to create brilliant prose and does away with the rest. Features... Read more
Amazon Chime 4.0.5528 - Amazon-based com...
Amazon Chime is a communications service that transforms online meetings with a secure, easy-to-use application that you can trust. Amazon Chime works seamlessly across your devices so that you can... Read more
Apple Final Cut Pro X 10.3.2 - Professio...
Apple Final Cut Pro X is a professional video editing solution.Completely redesigned from the ground up, Final Cut Pro adds extraordinary speed, quality, and flexibility to every part of the post-... Read more
ForkLift 3.0 - Powerful file manager: FT...
ForkLift is a powerful file manager and ferociously fast FTP client clothed in a clean and versatile UI that offers the combination of absolute simplicity and raw power expected from a well-executed... Read more
VueScan 9.5.70 - Scanner software with a...
VueScan is a scanning program that works with most high-quality flatbed and film scanners to produce scans that have excellent color fidelity and color balance. VueScan is easy to use, and has... Read more
SoftRAID 5.5.6 - High-quality RAID manag...
SoftRAID allows you to create and manage disk arrays to increase performance and reliability. SoftRAID allows the user to create and manage RAID 4 and 5 volumes, RAID 1+0, and RAID 1 (Mirror) and... Read more
Sparkle 2.1.0 - $79.99
Sparkle will change your mind if you thought building websites wasn't for you. Sparkle is the intuitive site builder that lets you create sites for your online portfolio, team or band pages, or... Read more
Logic Pro X 10.3.1 - Music creation and...
Logic Pro X is the most advanced version of Logic ever. Sophisticated new tools for professional songwriting, editing, and mixing are built around a modern interface that's designed to get creative... Read more

Blasty Bubs is a colorful Pinball and Br...
QuickByte Games has another arcade treat in the works -- this time it's a mishmash of brick breaking and Pinball mechanics. It's called Blasty Bubs, and it's a top down brickbreaker that has you slinging balls around a board. [Read more] | Read more »
Corsola and Heracross are the new region...
Generation 2 finally launched in Pokémon GO, unleashing a brand new batch of Pokémon into the wild. Even before the update went live people were speculating on how to catch elusive Pokémon like the legendary "dogs", Unknown, and whether or not... | Read more »
The Warlock of Firetop Mountain (Games)
The Warlock of Firetop Mountain 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: An epic adventure through a mysterious mountain filled with monsters, magic and mayhem! “...it looks downright... | Read more »
Fantasy MMORPG MU Origin’s receives a hu...
Developer Webzen are looking to take their highly popular fantasy battler MU Origin to the next level this month, with its most ambitious overhaul yet. The latest update introduces the long sought after Server Arena, new treasure dungeons, and much... | Read more »
RPG Djinn Caster (Games)
RPG Djinn Caster 1.0.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0.0 (iTunes) Description: SPECIAL PRICE 38% OFF(USD 7.99 -> USD 4.99)!!!A Fantasy Action RPG of far foreign lands! Summon the Djinns and rise to... | Read more »
Alto's Odyssey gets its first trail...
There's finally video evidence of Alto's Odyssey, the follow up to the 2015 App Store hit, Alto's Adventure. It looks just as soothing and atmospheric as Alto's last outing, but this time players will be journeying to the desert. Whereas Alto's... | Read more »
Last week on Pocket Gamer
What’s going on in the wider world of portable gaming? Each week we ask that question of our sister website Pocket Gamer. The PG team covers iOS gaming, just like 148Apps, but it also strays into the world of Android games and handheld consoles... | Read more »
Pokémon GO Generation 2 evolution guide
At long last, Niantic Labs finally unleashed the Generation 2 Pokémon into the wild. Pokémon GO trainers are scrambling to grab up this new set of 80 Pokémon. There are some special new tricks required to catch all of these new beasties, though.... | Read more »
The best new games we played this week
It feels as though the New Year got off to a creaking start as far as mobile games go, but that's changed over the past few weeks. The last few days alone have seen the debut of a number of wonderful games, so we thought we'd take the time to... | Read more »
Recruit more scallywags and discover new...
Get ready to show off your sea legs all over again in Oceans & Empires’ new grand update, which aims to make the act of rising to the role of seven seas ruler even more fresh and appealing, thanks to a richness of new content on both iOS and... | Read more »

Price Scanner via MacPrices.net

Apple restocks refurbished 2015 and 2016 13-i...
Apple has Certified Refurbished 2015 and 2016 13″ MacBook Airs available starting at $759. An Apple one-year warranty is included with each MacBook, and shipping is free: - 2016 13″ 1.6GHz/8GB/128GB... Read more
13-inch 2.5GHz MacBook Pro (Apple refurbished...
Apple has Certified Refurbished 13″ 2.5GHz MacBook Pros (MD101LL/A) available for $829, or $270 off original MSRP. Apple’s one-year warranty is standard, and shipping is free: - 13″ 2.5GHz MacBook... Read more
QuickerTek Announces 5TB Apple AC AirPort Tim...
QuickerTek Inc. has announced their new 5TB hard drive upgrade for Apple’s AC AirPort Time Capsule. By customer request, this upgrade also features six external antennas and offers the highest... Read more
Apple Certified Refurbished iMacs available f...
Apple has Certified Refurbished 2015 21″ & 27″ iMacs available for up to $350 off MSRP. Apple’s one-year warranty is standard, and shipping is free. The following models are available: - 21″ 3.... Read more
Apple offering Certified Refurbished Series 1...
Apple is now offering Certified Refurbished Series 1 and Series 2 Apple Watches for 14-16% off MSRP, starting at $229. An Apple one-year warranty is included with each watch. Shipping is free: Series... Read more
1.4GHz Mac mini on sale for $449, save $50
B&H Photo has the 1.4GHz Mac mini on sale for $50 off MSRP including free shipping plus NY sales tax only: - 1.4GHz Mac mini: $449 $50 off MSRP Read more
12-inch Retina MacBooks on sale for $200-$250...
Newegg has the 12″ 1.2GHz Space Gray Retina MacBook (sku MLH82LL/A) on sale for $1349.99 including free shipping. Their price is $250 off MSRP, and it’s the lowest price available for this model.... Read more
13-inch 2.0GHz Space Gray MacBook Pro on sale...
Adorama has the non-Touch Bar 13″ 2.0GHz Space Gray MacBook Pro in stock today for $100 off MSRP. Shipping is free, and Adorama charges NY & NJ sales tax only: - 13″ 2.0GHz MacBook Pro Space Gray... Read more
13-inch Touch Bar MacBook Pros on sale for $1...
B&H Photo has select 2016 Apple 13″ Touch Bar MacBook Pros in stock today and up to $100 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 2.9GHz/512GB Touch Bar MacBook... Read more
KSI-1802R SX Disinfect-able Keyboard With Wav...
KSI has unveiled a new, innovative medical keyboard, the KSI-1802R SX, at HIMSS 2017, running February 19-22 in Orlando, Florida. KSI-1802R SX is the only keyboard that combines dual factor... Read more

Jobs Board

*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Manager *Apple* Systems Administration - Pu...
Req ID 3315BR Position Title Manager, Apple Systems Administration Job Description The Manager of Apple Systems Administration oversees the administration and Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Manager *Apple* Systems Administration - Pu...
Req ID 3315BR Position Title Manager, Apple Systems Administration Job Description The Manager of Apple Systems Administration oversees the administration and Read more
*Apple* Technician - nfrastructure (United S...
Let’s Work Together Apple Technician This position is based in Portland, ME Life at nfrastructure At nfrastructure, we understand that our success results from our Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.