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
$524.94
Apple Inc.
+5.93
MSFT
$40.01
Microsoft Corpora
-0.39
GOOG
$536.10
Google Inc.
-20.44

MacTech Search:
Community Search:

Software Updates via MacUpdate

Mac DVDRipper Pro 4.1.7 - Copy, backup,...
Mac DVDRipper Pro is the DVD backup solution that lets you protect your DVDs from scratches, save your batteries by reading your movies from your hard disk, manage your collection with just a few... Read more
PDFpenPro 6.2 - Advanced PDF toolkit for...
PDFpenPro allows users to edit PDF's easily. Add text, images and signatures. Fill out PDF forms. Merge or split PDF documents. Reorder and delete pages. Even correct text and edit graphics! Create... Read more
PDFpen 6.2 - Edit and annotate PDFs with...
PDFpen allows users to easily edit PDF's. Add text, images and signatures. Fill out PDF forms. Merge or split PDF documents. Reorder and delete pages. Even correct text and edit graphics! Features... Read more
Monolingual 1.5.9 - Remove unwanted OS X...
Monolingual is a program for removing unnecesary language resources from OS X, in order to reclaim several hundred megabytes of disk space. It requires a 64-bit capable Intel-based Mac and at least... Read more
Maya 2015 - Professional 3D modeling and...
Maya is an award-winning software and powerful, integrated 3D modeling, animation, visual effects, and rendering solution. Because Maya is based on an open architecture, all your work can be scripted... Read more
Starcraft II: Wings of Liberty 1.1.1.180...
Download the patch by launching the Starcraft II game and downloading it through the Battle.net connection within the app. Starcraft II: Wings of Liberty is a strategy game played in real-time. You... Read more
Sibelius 7.5.0 - Music notation solution...
Sibelius is the world's best-selling music notation software for Mac. It is as intuitive to use as a pen, yet so powerful that it does most things in less than the blink of an eye. The demo includes... Read more
Typinator 5.9 - Speedy and reliable text...
Typinator turbo-charges your typing productivity. Type a little. Typinator does the rest. We've all faced projects that require repetitive typing tasks. With Typinator, you can store commonly used... Read more
MYStuff Pro 2.0.16 - Create inventories...
MYStuff Pro is the most flexible way to create detail-rich inventories for your home or small business. Add items to MYStuff by dragging and dropping existing information, uploading new images, or... Read more
TurboTax 2013.r17.002 - Manage your 2013...
TurboTax guides you through your tax return step by step, does all the calculations, and checks your return for errors and overlooked deductions. It lets you file your return electronically to get... Read more

Latest Forum Discussions

See All

Zynga Launches Brand New Farmville Exper...
Zynga Launches Brand New Farmville Experience with Farmville 2: Country Escape Posted by Tre Lawrence on April 18th, 2014 [ permalink ] | Read more »
David. Review
David. Review By Cata Modorcea on April 18th, 2014 Our Rating: :: MINIMALISTIC IN A DIFFERENT WAYUniversal App - Designed for iPhone and iPad David is a minimalistic game wrapped inside of a soothing atmosphere in which the hero... | Read more »
Eyefi Unveils New Eyefi Cloud Service Th...
Eyefi Unveils New Eyefi Cloud Service That Allows Users to Share Media Across Personal Devices Posted by Tre Lawrence on April 18th, 2014 [ permalink ] | Read more »
Tales from the Dragon Mountain: The Lair...
Tales from the Dragon Mountain: The Lair Review By Jennifer Allen on April 18th, 2014 Our Rating: :: STEADY ADVENTURINGiPad Only App - Designed for the iPad Treading a safe path, Tales from the Dragon Mountain: The Lair is a... | Read more »
Yahoo Updates Flickr App with Advanced E...
Yahoo Updates Flickr App with Advanced Editing Features and More Posted by Tre Lawrence on April 18th, 2014 [ permalink ] | Read more »
My Incredible Body - A Kid's App to...
My Incredible Body - A Kid's App to Learn about the Human Body 1.1.00 Device: iOS Universal Category: Education Price: $2.99, Version: 1.1.00 (iTunes) Description: Wouldn’t it be cool to look inside yourself and see what was going on... | Read more »
Trials Frontier Review
Trials Frontier Review By Carter Dotson on April 18th, 2014 Our Rating: :: A ROUGH LANDINGUniversal App - Designed for iPhone and iPad Trials Frontier finally brings the famed stunt racing franchise to mobile, but how much does its... | Read more »
Evernote Business Notebook by Moleskin I...
Evernote Business Notebook by Moleskin Introduced – Support Available in Evernote for iOS Posted by Tre Lawrence on April 18th, 2014 [ permalink ] | Read more »
Sparkle Unleashed Review
Sparkle Unleashed Review By Jennifer Allen on April 18th, 2014 Our Rating: :: CLASSY MARBLE FLINGINGUniversal App - Designed for iPhone and iPad It’s a concept we’ve seen before, but Sparkle Unleashed is a solidly enjoyable orb... | Read more »
Tilt to Live 2: Redonkulus Receives its...
Tilt to Live 2: Redonkulus Receives its First Update – Gets New Brimstone Pinball DLC Posted by Rob Rich on April 18th, 2014 [ permalink ] | Read more »

Price Scanner via MacPrices.net

iMacs on sale for up to $160 off MSRP this we...
Best Buy has iMacs on sale for up to $160 off MSRP for a limited time. Choose free home shipping or free instant local store pickup (if available). Prices are valid for online orders only, in-store... Read more
iPad Airs on sale this weekend for up to $100...
Best Buy has WiFi iPad Airs on sale for $50 off MSRP and WiFi + Cellular iPad Airs on sale for $100 off MSRP on their online store for a limited time, with prices now starting at $449. Choose free... Read more
Apple restocks refurbished Mac minis starting...
The Apple Store has restocked Apple Certified Refurbished Mac minis for up to $150 off the cost of new models. Apple’s one-year warranty is included with each mini, and shipping is free: - 2.5GHz Mac... Read more
Hyundai Brings Apple CarPlay To The 2015 Sona...
Hyundai Motor America has announced it will bring Apple CarPlay functionality to the 2015 Sonata. CarPlay is pitched as a smarter, safer and easier way to use iPhone in the car and gives iPhone users... Read more
Updated iPads Coming Sooner Than We Had Thoug...
MacRumors, cites KGI securities analyst Ming Chi Kuo, well-respected as an Apple product prognisticator, saying that Apple will introduce an upgraded iPad Air and iPad mini in 2014/Q3, meaning the... Read more
Toshiba Unveils New High And Low End Laptop M...
Toshiba has announced new laptop models covering both the high-end and low-end of the notebook computer spectrum. Toshiba 4K Ultra HD Laptop Toshiba’s new Satellite P55t features one of the world’s... Read more
Save up to $270 with Apple refurbished 13-inc...
The Apple Store has Apple Certified Refurbished October 2013 13″ Retina MacBook Pros available starting at $1099, with models up to $270 off MSRP. Apple’s one-year warranty is standard, and shipping... Read more
Apple now offering refurbished iPad mini with...
The Apple Store has Certified Refurbished 2nd generation iPad minis with Retina Displays now available starting at $339. Apple’s one-year warranty is included with each model, and shipping is free.... Read more
Microsoft Blinks – Drops Microsoft Office 365...
Microsoft has dropped the annual subscription fee for Microsoft Office 365 Personal – which is needed in order to create and edit documents in Microsoft Office for iPad. However, Apple’s iOS and OS X... Read more
New AVG Vault Apps for iOS and Android Help K...
AVG Technologies N.V. an online security company for 177 million active users, has announced the launch of its latest mobile application, AVG Vault. The free app introduces an innovative user... Read more

Jobs Board

*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
*Apple* Retail - Manager - Holyoke - Apple I...
Job Summary Keeping an Apple Store thriving requires a diverse set of leadership skills, and as a Manager, you’re a master of them all. In the store’s fast-paced, Read more
*Apple* Retail - Manager - Apple (United Sta...
Job SummaryKeeping an Apple Store thriving requires a diverse set of leadership skills, and as a Manager, you're a master of them all. In the store's fast-paced, dynamic Read more
*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
*Apple* Retail - Market Leader - Cincinnati...
…challenges of developing individuals, building teams, and affecting growth across Apple Stores. You demonstrate successful leadership ability - focusing on excellence Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.