TweetFollow Us on Twitter

Programming and MySQL

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

Untangling the Web

by Kevin Hemenway, Imitating Conspirator

Programming and MySQL

Modifying our MySQL database with the shell and PHP.

We finally added information into our MySQL database last issue, but in a rather infantile way: by writing all the SQL statements (like those seen in Listing 1) into a text file and then passing them to MySQL with a command line, mysql mactech < mactech-insert.sql, which completes silently when successful. This is certainly helpful if we're passing default information to be initialized in newly created databases, but not very useful if we want to programmatically access the information within.

Listing 1: Three new SQL INSERT statements for our database.

mactech-insert.sql
INSERT INTO books
   SET publication = "2000-00-00",
       title = "Object Oriented Perl";
       
INSERT INTO books
   SET publication = "1999-00-00",
       title = "MyEssQueEll";
       
INSERT INTO books
   SET publication = "2003-00-00",
       title = "PHP and MySQL Web Development";

In this article, we'll talk about two more ways to manipulate data: through the MySQL interactive shell, and via PHP's built in database functions. First up, let's walkthrough the interactive shell, which is quite helpful for quickly testing out new SQL statements or applying immediate fixes without new code overhead and development.

The MySQL Interactive Shell

Starting up the MySQL interactive shell is mindlessly simple. Just type mysql and you'll be shown a mysql> prompt, similar to the shell prompt you see when you first enter the Terminal. Since we've yet to specify a database, we're in a sort of limbo: MySQL knows we're here, but we've yet to tell it anything of import. First we'll show which databases have been configured, choose which one we'd like to work with, and then ask for a table listing:

:~ > mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.15

Type 'help;' or '\h' for help.
Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| mactech            |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.26 sec)

mysql> USE mactech;
Reading table information for completion of
table and column names. You can turn off this
feature to get a quicker startup with -A.

Database changed
mysql> SHOW TABLES;
+------------------------------------------+
| Tables_in_mactech                        |
+------------------------------------------+
| books                                    |
| person                                   |
| relationships                            |
+------------------------------------------+
3 rows in set (0.05 sec)

The most obvious fact from the above is that all SQL commands must end with a semi-colon. If you happen to forget that termination, MySQL will change its shell prompt to show you it's waiting patiently for a complete command. The following is the same command as before, only split with new lines. Notice the prompt indicating an incomplete statement:

mysql> SHOW
    -> TABLES
    -> ;

A good portion of readers will know that the shell opened by the Terminal gives you command line history (press the UP arrow to see commands you've previously typed) as well as file or directory completion (press TAB after typing the first few letters). MySQL also supports these time-savers: pressing UP will show you SQLs you've typed previously, and pressing TAB when typing a table or column name will auto-complete the nearest match.

In our previous work with SQL, we've touched on the INSERT, UPDATE, and DELETE commands. We've yet to touch on the most often used, however, which is SELECT. While our previous statements have focused on adding, modifying, or deleting data, the sole purpose of SELECT is for displaying:

mysql> SELECT * FROM books;
+----+-------------------------------+-------------+
| id | title                         | publication |
+----+-------------------------------+-------------+
|  1 | Spidering Hacks               | 2003-11-01  |
|  2 | Mac OS X Hacks                | 2003-04-01  |
|  3 | Object Oriented Perl          | 2000-00-00  |
|  4 | MyEssQueEll                   | 1999-00-00  |
|  5 | PHP and MySQL Web Development | 2003-00-00  |
+----+-------------------------------+-------------+
5 rows in set (0.00 sec)

mysql> SELECT title, name FROM person;
+-------+----------------+
| title | name           |
+-------+----------------+
| Mr.   | Kevin Hemenway |
+-------+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM relationships WHERE book_id = '2';
+-----------+----------+
| person_id | book_id  |
+-----------+----------+
|         1 |       2  |
+-----------+----------+
1 row in set (0.05 sec)

The above shows three different variants of a SELECT statement--many more are possible. The first is the easiest to understand: "select everything from the books table". The second is an example of specifying only the columns you want to see, in any order. Even though title came after name in our original CREATE statement (last issue), SELECT allows us to reorder things however we decide best. The third statement is an example of more intimately specifying which exact rows you'd like to retrieve.

Comparing the above walkthrough to the command line we entered last issue (mysql mactech < mactech-insert.sql) gives us a better understanding of what's going on. We specify the database to connect to (mactech; similar to USE mactech in the MySQL shell), and then send a bunch of SQL commands in a batch, as opposed to manually entering them one at a time. It doesn't take a giant leap of faith to realize that we can save the USE database step by specifying it on the command line:

~ > mysql mactech
Reading table information for completion of
table and column names. You can turn off this
feature to get a quicker startup with -A.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.15

Type 'help;' or '\h' for help.
Type '\c' to clear the buffer.

mysql> UPdAtE books SET title = "MySQL"
    -> where title = "MyEssQueEll";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SeLeCT id, title FRoM
    -> books WHERE title LIKE '%SQL%';
+----+-------------------------------+
| id | title                         |
+----+-------------------------------+
| 4  | MySQL                         |
| 5  | PHP and MySQL Web Development |
+----+-------------------------------+
2 rows in set (0.30 sec)

The previous listing shows an example of entering the MySQL interactive shell with a database already selected, updating a row of misspelling in our previous INSERT (Listing 1), and then getting a pattern match with LIKE. The % characters are boundary placeholders for "anything or nothing", so our final statement finds the letters "SQL" in the beginning, middle, or end of a title. Changing "SQL" to "my" would return the same set of results, confirming that LIKE searches are case-insensitive.

Up until now, all our SQL commands have used capital letters, but you'll notice that they too are case-insensitive. For clarity, I prefer uppercase SQL: it just makes things easier to mentally and visually parse after long hours of fevered coding.

Accessing MySQL From PHP

Our next step is to access our mactech database programmatically with PHP. You'll notice some similarities with the above interactive shell process: we connect to MySQL, choose a database, and issue some SQL queries. You'll note that we're passing the MySQL username and password we created with mysql_setpermission a few columns back. This is important for security: just like you don't want your sister messing with your personal files, you don't want to make it too easy for web interlopers to affect the various databases you maintain. In most cases, once you're finished developing an application, you'd tighten the user's permission even further (say, to restrict DELETE and DROP access).

Save the contents of Listing 2 into /Library/WebServer/Documents:

Listing 2: PHP code for accessing our MySQL database.

db_access.php
<h1>MySQL Database Access in PHP</h1>
<?php
   $dbh = mysql_connect("localhost","favemarksman","***")
     or die ("There was an error connecting to MySQL.");
?>

Before we delve deeper into our code, I wanted to show you a one-character-difference that can be used to improve security. Astute readers will notice that even though I meant davemarksman (the MySQL user created a few issues ago) I mistyped as favemarksman:


Figure 1: An error occurred during the database connection.

Here's the problem: if someone comes to our site and triggers this error message, we've freely given them four pieces of valuable information that can be used against us for exploitive purposes. We've told them we're using MySQL as a database, that it's installed on the same machine as the web server, that there's (possibly) a user named favemarksman (a similar error would occur if the username was correct, but not the password), and revealed a directory path (which, in this case, infers we're using OS X, a fifth fact).

Listing 3 contains a much stronger version of our code, which removes any mention of the technology being used, and stops MySQL from spitting useful information to our visitors. The real magic happens with the @ symbol before our function name: using it will silence any automatically visible errors. This makes for more secure code, as well as a more professional web site (i.e., what impression does it leave visitors if they see nothing but errors?)

Listing 3: Revised PHP code for accessing our MySQL database.

db_access.php, revised
<h1>Database Access in PHP</h1>

<?php
   $dbh = @mysql_connect("localhost","favemarksman","***")
     or die ("An error has occurred. Please report this.");
?>

The revised output is shown in Figure 2:


Figure 2: No more information disclosure. Much better.

With that out of the way, we can now accomplish something. Listing 4 contains complete (but simple) PHP code to insert some information into the mactech database, view it, and then DELETE a bit. As with most programming, there are many ways this can be done: more information about the different mysql_ functions we've not shown can be found at the PHP web site: http://www.php.net/manual/en/ref.mysql.php.

Listing 4: Finished PHP code for accessing our MySQL database.

db_access.php, finished
<h1>Database Access in PHP</h1>

<?php

   // connect to the database server.
   $dbh = @mysql_connect("localhost","davemarksman","***")
     or die ("ERROR: Could not connect to the database!");

   // choose our database.
   @mysql_select_db( "mactech" )
      or die ("ERROR: Could not select our database!");

   // create a SQL statement. Notice that through
   // PHP, the SQL terminating semi-colon is optional.
   $statement = "INSERT INTO person SET
                  name          = 'Dave Mark',
                  date_of_birth = '1901-03-31',
                  title         = 'Intern', 
                  designation   = 'Bullseye Hole Filler'";

   // standard way of executing SQL through PHP.
   $response = @mysql_query( $statement, $dbh );
   
   // mysql_error() would give too much information for
   // a production site, but this is just an example. 
   if (!$response) { print mysql_error () . "\n"; }

   // create and execute another SQL statement.
   $sg_made_out_of = "SELECT * FROM person;";
   $response = @mysql_query( $sg_made_out_of, $dbh );

   // this is one of a few ways to iterate through rows.
   print "<h3>People, List #1</h3>";
   while ( $person = @mysql_fetch_array( $response ) ) {

       // column name is array key.
       print "$person[id], $person[title], 
              $person[name], $person[designation]<br />"; 
   }

   // one more SQL statement, this time a delete.
   $freedom = "DELETE FROM person WHERE name LIKE '%Mar%'";
   $response = @mysql_query( $freedom, $dbh );
 
   // and make sure he's really gone.
   $sg_made_out_of = "SELECT * FROM person;";
   $response = @mysql_query( $sg_made_out_of, $dbh );

   // another way of iterating through rows, only
   // as variables, not hash keys. Could get messy.
   print "<h3>People, List #2</h3>";
   while ( $person = @mysql_fetch_array( $response ) ) {
       extract ( $person ); // make columns variables.
       print "$id, $title $name, $designation<br />"; 
   }

?>

The results of running this script are in Figure 3. Since we're inserting a new record, then deleting it, successive loads will assign the new record an ever-increasing sequential ID, even though the data we're entering is exactly the same.


Figure 3: The final results of our database access in PHP.

Homework Malignments

As is typical in a four-page article, we've barely touched the surface of what PHP can do in regards to database access and handling. Next month, we'll change gears and see how Perl handles the same logic. Until then, contact the teacher at morbus@disobey.com.


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'd rather be nursing his wounds with a swift kick to the head. Contact him at morbus@disobey.com.

 
AAPL
$108.00
Apple Inc.
+1.02
MSFT
$46.95
Microsoft Corpora
+0.90
GOOG
$559.08
Google Inc.
+8.77

MacTech Search:
Community Search:

Software Updates via MacUpdate

Vitamin-R 2.20b1 - Personal productivity...
Vitamin-R creates the optimal conditions for your brain to work at its best by structuring your work into short bursts of distraction-free, highly focused activity alternating with opportunities for... Read more
Dropbox 2.10.44 - Cloud synchronization...
Dropbox is an application that creates a special Finder folder that automatically syncs online and between your computers. It allows you to both backup files and keep them up-to-date between systems... Read more
Sandvox 2.9.2 - Easily build eye-catchin...
Sandvox is for Mac users who want to create a professional looking website quickly and easily. With Sandvox, you don't need to be a Web genius to build a stylish, feature-rich, standards-compliant... Read more
Cocktail 8.0.1 - General maintenance and...
Cocktail is a general purpose utility for OS X that lets you clean, repair and optimize your Mac. It is a powerful digital toolset that helps hundreds of thousands of Mac users around the world get... Read more
LibreOffice 4.3.3.2 - Free Open Source o...
LibreOffice is an office suite (word processor, spreadsheet, presentations, drawing tool) compatible with other major office suites. The Document Foundation is coordinating development and... Read more
VMware Fusion 7.0.1 - Run Windows apps a...
VMware Fusion allows you to create a Virtual Machine on your Mac and run Windows (including Windows 8.1) and Windows software on your Mac. Run your favorite Windows applications alongside Mac... Read more
OneNote 15.3.2 - Free digital notebook f...
OneNote is your very own digital notebook. With OneNote, you can capture that flash of genius, that moment of inspiration, or that list of errands that's too important to forget. Whether you're at... Read more
Audio Hijack Pro 2.11.4 - Record and enh...
Audio Hijack Pro drastically changes the way you use audio on your computer, giving you the freedom to listen to audio when you want and how you want. Record and enhance any audio with Audio Hijack... Read more
Iridient Developer 3.0.0 beta 3 - Powerf...
Iridient Developer (was RAW Developer) is a powerful image conversion application designed specifically for OS X. Iridient Developer gives advanced photographers total control over every aspect of... Read more
TextWrangler 4.5.11 - Free general purpo...
TextWrangler is the powerful general purpose text editor, and Unix and server administrator's tool. Oh, and also, like the best things in life, it's free. TextWrangler is the "little brother" to... Read more

Latest Forum Discussions

See All

Monster Flash Review
Monster Flash Review By Jordan Minor on October 31st, 2014 Our Rating: :: ALONE IN THE DARKUniversal App - Designed for iPhone and iPad Solid shooting and a surprising amount of spooky tension make Monster Flash a great portable... | Read more »
Retry Review
Retry Review By Rob Thomas on October 31st, 2014 Our Rating: :: SOARING HIGHUniversal App - Designed for iPhone and iPad Flappy who? Let Retry wash all those bad bird-related memories away on a cool retro-flavored flight… right... | Read more »
Dementia: Book of the Dead Review
Dementia: Book of the Dead Review By Lee Hamlet on October 31st, 2014 Our Rating: :: A TOUGH READUniversal App - Designed for iPhone and iPad A witch hunter is sent after a demonic book in the spooky but short-lived Dementia: Book... | Read more »
Card Dungeon, the Semi-Board Game Roguel...
Card Dungeon, the Semi-Board Game Roguelike, Has Been Renovated Posted by Jessica Fisher on October 31st, 2014 [ permalink ] | Read more »
Logitech Protection + Power iPhone5/5S C...
Made by: Logitech Price: $99.99 Hardware/iOS Integration Rating: 3 out of 5 stars Usability Rating: 0.5 out of 5 stars Reuse Value Rating: 0.75 out of 5 stars Build Quality Rating: 0.75 out of 5 stars Overall Rating: 1.25 out of 5 stars | Read more »
This Is Not a Test Goes Free, Permanentl...
This Is Not a Test Goes Free, Permanently Posted by Jessica Fisher on October 31st, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Swap Heroes Review
Swap Heroes Review By Campbell Bird on October 31st, 2014 Our Rating: :: STRATEGIC SWAPPINGUniversal App - Designed for iPhone and iPad Rotate a cast of heroes to fend of waves of monsters in this difficult, puzzle rpg.   | Read more »
Night Sky Pro™ (Reference)
Night Sky Pro™ 3.0.1 Device: iOS Universal Category: Reference Price: $2.99, Version: 3.0.1 (iTunes) Description: Night Sky Pro™Wonder No More™ Night Sky Pro™ is the ultimate stargazing experience. From the creators of the original... | Read more »
Audio Defence : Zombie Arena (Games)
Audio Defence : Zombie Arena 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: A zombie shooter audio game. Made from gut-wrenching 3D binaural sound, for a new kind of weird immersion. You... | Read more »
RPG Asdivine Hearts (Games)
RPG Asdivine Hearts 1.1.0 Device: iOS Universal Category: Games Price: $3.99, Version: 1.1.0 (iTunes) Description: SPECIAL PRICE50% OFF (USD 7.99 -> USD 3.99)!!! Travel alongside four companions and a cat in the adventure of a... | Read more »

Price Scanner via MacPrices.net

Tablets Ascendent Again; Global Tablet Market...
The worldwide tablet grew 11.5% year over year in the third quarter of 2014 (3Q14) with shipments reaching 53.8 million units according to preliminary data from the International Data Corporation (... Read more
Apple now offering refurbished 2014 13-inch R...
The Apple Store is now offering Apple Certified Refurbished 2014 13″ Retina MacBook Pros for up to $270 off the cost of new models. An Apple one-year warranty is included with each model, and... Read more
Apple Regains Momentum As Windows Stutters An...
The latest smartphone sales data from Kantar Worldpanel ComTech, for the three months to March 2014, shows Apple performing strongly in the first quarter of the year, with sales bouncing back in... Read more
Worldwide Smartphone Shipments Increase 25.2%...
New smartphone releases and an increased emphasis on emerging markets drove global smartphone shipments above 300 million units for the second consecutive quarter, according to preliminary data from... Read more
Apple now offering refurbished 2014 15-inch M...
The Apple Store is now offering Apple Certified Refurbished 2014 15″ Retina MacBook Pros for up to $400 off the cost of new models. An Apple one-year warranty is included with each model, and... Read more
Apple drops prices on refurbished 2013 Retina...
The Apple Store has dropped prices on 2013 Apple Certified Refurbished 13″ and 15″ Retina MacBook Pros, with Retina models now available starting at $999. Apple’s one-year warranty is standard, and... Read more
New 2.8GHz Mac mini on sale for $949, save $5...
Abt Electronics has the new 2.8GHz Mac mini in stock and on sale for $949.05 including free shipping. Their price is $50 off MSRP, and it’s the lowest price available for this model from any reseller... Read more
Sale! 3.7GHz Quad Core Mac Pro available for...
 B&H Photo has the 3.7GHz Quad Core Mac Pro on sale for $2649 including free shipping plus NY sales tax only. Their price is $350 off MSRP, and it’s the lowest price for this model from any... Read more
Mujjo Steps Up The Game With Refined Touchscr...
Netherlands based Mujjo have just launched their Refined Touchscreen Gloves, stepping up their game. The gloves feature a updated elegant design that takes these knitted gloves to the next level. A... Read more
Sale! Preorder the new 27-inch 5K iMac for $2...
 Abt Electronics has the new 27″ 3.5GHz 5K iMac on sale and available for preorder for $2374.05 including free shipping. Their price is $125 off MSRP, and it’s the lowest price available for this... Read more

Jobs Board

Position Opening at *Apple* - Apple (United...
…Summary** As a Specialist, you help create the energy and excitement around Apple products, providing the right solutions and getting products into customers' hands. You Read more
Position Opening at *Apple* - Apple (United...
**Job Summary** Being a Business Manager at an Apple Store means you're the catalyst for businesses to discover and leverage the power, ease, and flexibility of Apple Read more
Position Opening at *Apple* - Apple (United...
**Job Summary** As more and more people discover Apple , they visit our stores seeking ways to incorporate our products into their lives. It's your job, as a Store Read more
Position Opening at *Apple* - Apple (United...
**Job Summary** At the Apple Store, you connect business professionals and entrepreneurs with the tools they need in order to put Apple solutions to work in their Read more
Solutions Specialist with *Apple* Knowledge...
Company Description: We are an Apple Authorized Sales and Service Provider. We have been selling and servicing Apple computers in the Fairfield County area for over Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.