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.

   SET publication = "2000-00-00",
       title = "Object Oriented Perl";
   SET publication = "1999-00-00",
       title = "MyEssQueEll";
   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.

| 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
| 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.

<h1>MySQL Database Access in PHP</h1>
   $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>

   $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:

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

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


   // 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

Kevin Hemenway, coauthor of Mac OS X Hacks and Spidering Hacks, is better known as Morbus Iff, the creator of, 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, the ever ignorable Nonsense Network, etc.), he'd rather be nursing his wounds with a swift kick to the head. Contact him at


Community Search:
MacTech Search:

Software Updates via MacUpdate

Paperless 2.3.7 - $49.95
Paperless is a digital documents manager. Remember when everyone talked about how we would soon be a paperless society? Now it seems like we use paper more than ever. Let's face it - we need and we... Read more
DEVONthink Pro 2.9.6 - Knowledge base, i...
DEVONthink Pro is your essential assistant for today's world, where almost everything is digital. From shopping receipts to important research papers, your life often fills your hard drive in the... Read more
Safari Technology Preview 10.1 - The new...
Safari Technology Preview contains the most recent additions and improvements to WebKit and the latest advances in Safari web technologies. And once installed, you will receive notifications of... Read more
VueScan 9.5.60 - 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
Civilization VI 1.0.0 - Next iteration o...
Sid Meier’s Civilization VI is the next entry in the popular Civilization franchise. Originally created by legendary game designer Sid Meier, Civilization is a strategy game in which you attempt to... Read more
Adobe Flash Player - Plug-in...
Adobe Flash Player is a cross-platform, browser-based application runtime that provides uncompromised viewing of expressive applications, content, and videos across browsers and operating systems.... Read more
RestoreMeNot 2.0.4 - Disable window rest...
RestoreMeNot provides a simple way to disable the window restoration for individual applications so that you can fine-tune this behavior to suit your needs. Please note that RestoreMeNot is designed... Read more
Persecond 1.0.7 - Timelapse video made e...
Persecond is the easy, fun way to create a beautiful timelapse video. Import an image sequence from any camera, trim the length of your video, adjust the speed and playback direction, and you’re done... Read more
iShowU Instant 1.1.0 - Full-featured scr...
iShowU Instant gives you real-time screen recording like you've never seen before! It is the fastest, most feature-filled real-time screen capture tool from shinywhitebox yet. All of the features you... Read more
Spotify - Stream music, creat...
Spotify is a streaming music service that gives you on-demand access to millions of songs. Whether you like driving rock, silky R&B, or grandiose classical music, Spotify's massive catalogue puts... Read more

Latest Forum Discussions

See All

The Forgotten Room (Games)
The Forgotten Room 1.0.1 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0.1 (iTunes) Description: Play as paranormal investigator John “Buster of Ghosts” Murr as he explores yet another mysteriously creepy house. This... | Read more »
5 Halloween mobile games for wimps
If you're anything like me, horror games are a great way to have nightly nightmares for the next decade or three. They're off limits, but perhaps you want to get in on the Halloween celebrations in some way. Fortunately not all Halloween themed... | Read more »
The 5 scariest mobile games
It's the most wonderful time of the year for people who enjoy scaring themselves silly with haunted houses, movies, video games, and what have you. Mobile might not be the first platform you'd turn to for quality scares, but rest assured there are... | Read more »
Lifeline: Flatline (Games)
Lifeline: Flatline 1.0.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0.0 (iTunes) Description: The Lifeline series takes a terrifying turn in this interactive horror experience. Every decision you make could help... | Read more »
Game of Dice is now available on Faceboo...
After celebrating its anniversary in style with a brand new update, there’s even more excitement in store for Game of Dice has after just being launched on Facebook Gameroom. A relatively new platform, Facebook Gameroom has been designed for PC... | Read more »
4 addictive clicker games like Best Fien...
Clickers are passive games that take advantage of basic human psychology to suck you in, and they're totally unashamed of that. As long as you're aware that this game has been created to take hold of your brain and leave you perfectly content to... | Read more »
Smile Inc. Guide: How not to die on the...
As if Mondays weren't bad enough, at Smile Inc. you have to deal with giant killer donuts, massive hungry staplers, and blasting zones. It's not exactly a happy, thriving work environment. In fact, you'll be lucky to survive the nine to five.... | Read more »
Oh...Sir! The Insult Simulator (Games)
Oh...Sir! The Insult Simulator 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: | Read more »
WitchSpring2 (Games)
WitchSpring2 1.27 Device: iOS Universal Category: Games Price: $3.99, Version: 1.27 (iTunes) Description: This is the story of Luna, the Moonlight Witch as she sets out into the world. This is a sequel to Witch Spring. Witch Spring 2... | Read more »
4 popular apps getting a Halloween makeo...
'Tis the season for all things spooky. So much, so, in fact, that even apps are getting into the spirt of things, dressing up in costume and spreading jack o' lanterns all about the place. These updates bring frightening new character skins, scary... | Read more »

Price Scanner via

Worldwide Smartphone Shipments Up 1.0% Year o...
According to preliminary results from the International Data Corporation (IDC) Worldwide Quarterly Mobile Phone Tracker, vendors shipped a total of 362.9 million smartphones worldwide in the third... Read more
TuneBand Arm Band For iPhone 7 and 7 Plus Rel...
Grantwood Technology has added the TuneBand for iPhone 7 and 7 Plus to its smartphone armband series. The TuneBand provides a lightweight and comfortable way to wear the iPhone while running,... Read more
1.4GHz Mac mini on sale for $449, save $50
Adorama has the 1.4GHz Mac mini on sale for $50 off MSRP including free shipping plus NY & NJ sales tax only: - 1.4GHz Mac mini (Apple sku# MGEM2LL/A): $449 $50 off MSRP To purchase a mini at... Read more
21-inch 1.6GHz iMac on sale for $999, save $1...
B&H has the 21″ 1.6GHz Apple iMac on sale for $999 including free shipping plus NY sales tax only. Their price is $100 off MSRP. Read more
Macs’ Superior Enterprise Deployment Cost Eco...
IBM’s debunking of conventional wisdom and popular mythology about the relative cost of using Apple Mac computers as opposed to PCs running Microsoft Windows at the sixth annual Jamf Nation User... Read more
12-inch WiFi Apple iPad Pros on sale for $50-...
B&H Photo has 12″ WiFi Apple iPad Pros on sale for $50-$70 off MSRP, each including free shipping. B&H charges sales tax in NY only: - 12″ Space Gray 32GB WiFi iPad Pro: $749 $50 off MSRP -... Read more
Apple refurbished 12-inch 128GB iPad Pros ava...
Apple has Certified Refurbished 12″ Apple iPad Pros available for up to $160 off the cost of new iPads. An Apple one-year warranty is included with each model, and shipping is free: - 32GB 12″ iPad... Read more
Apple refurbished iPad minis and iPad Air 2s...
Apple recently dropped prices on several Certified Refurbished iPad mini 4s and 2s as well as iPad Air 2s. An Apple one-year warranty is included with each model, and shipping is free: - 16GB iPad... Read more
MacHTTP-js Preview Full-featured Web Server f...
MacHTTP.Org has released MacHTTP-js Preview for macOS, a full-featured Web server for 21st Century desktops and servers. MacHTTP-js is a modern take on the classic stand-alone, desktop computer Web... Read more
Samsung Galaxy Tab A 10.1 with S Pen Makes US...
Samsung Electronics America, Inc. has announced the release of the Galaxy Tab A 10.1 with S Pen in a highly mobile, lightweight tablet. “With an embedded S Pen, consumers can discover more ways to... 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
Software Engineering Intern: UI Applications...
Job Summary Apple is currently seeking enthusiastic interns who can work full-time for a minimum of 12-weeks between Fall 2015 and Summer 2016. Our software Read more
Security Data Analyst - *Apple* Information...
…data sources need to be collected to allow Information Security to better protect Apple employees and customers from a wide range of threats.Act as the subject 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
*Apple* Solutions Consultant - Apple (United...
# Apple Solutions Consultant Job Number: 52812872 Houston, Texas, United States Posted: Oct. 18, 2016 Weekly Hours: 40.00 **Job Summary** As an Apple Solutions Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.