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.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Garmin Express 7.0.0.0 - Manage your Gar...
Garmin Express is your essential tool for managing your Garmin devices. Update maps, golf courses and device software. You can even register your device. Update maps Update software Register your... Read more
ClipGrab 3.8.12 - Download videos from Y...
ClipGrab is a free downloader and converter for YouTube, Vimeo, Facebook and many other online video sites. It converts downloaded videos to MPEG4, MP3 or other formats in just one easy step Version... Read more
VMware Fusion 11.5.5 - Run Windows apps...
VMware Fusion and Fusion Pro - virtualization software for running Windows, Linux, and other systems on a Mac without rebooting. The latest version includes full support for Windows 10, macOS Mojave... Read more
Civilization VI 1.3.0 - Next iteration o...
Civilization® VI is the award-winning experience. Expand your empire across the map, advance your culture, and compete against history’s greatest leaders to build a civilization that will stand the... Read more
Google Earth 7.3.3.7721 - View and contr...
Google Earth gives you a wealth of imagery and geographic information. Explore destinations like Maui and Paris, or browse content from Wikipedia, National Geographic, and more. Google Earth combines... Read more
Corel Painter 20.1.0.285 - Digital art s...
Corel Painter lets you advance your digital art style with painted textures, subtle glazing brushwork, interactive gradients, and realistic Natural-Media. Easily transition from traditional to... Read more
iTubeDownloader 6.5.19 - Easily download...
iTubeDownloader is a powerful-yet-simple YouTube downloader for the masses. Because it contains a proprietary browser, you can browse YouTube like you normally would. When you see something you want... Read more
OmniFocus 3.8 - GTD task manager with iO...
OmniFocus is an organizer app. It uses projects to organize tasks naturally, and then add tags to organize across projects. Easily enter tasks when you’re on the go, and process them when you have... Read more
Hazel 4.4.5 - Create rules for organizin...
Hazel is your personal housekeeper, organizing and cleaning folders based on rules you define. Hazel can also manage your trash and uninstall your applications. Organize your files using a familiar... Read more
Macs Fan Control 1.5.7 - Monitor and con...
Macs Fan Control allows you to monitor and control almost any aspect of your computer's fans, with support for controlling fan speed, temperature sensors pane, menu-bar icon, and autostart with... Read more

Latest Forum Discussions

See All

Dungonian is a card-based dungeon crawle...
Dungonian is a card-based dungeon crawler from developer SandFish Games that only recently launched as a free-to-play title. It offers an extensive roster of playable heroes to collect and enemies to take down, and it's available right now for iOS... | Read more »
Steam Link Spotlight - Signs of the Sojo...
Steam Link Spotlight is a feature where we look at PC games that play exceptionally well using the Steam Link app. Our last entry was XCOM: Chimera Squad. Read about how it plays using Steam Link's new mouse and keyboard support over here. | Read more »
Steampunk Tower 2, DreamGate's sequ...
Steampunk Tower 2 is a DreamGate's follow up to their previous tower defence game. It's available now for both iOS and Android as a free-to-play title and will see players defending their lone base by kitting it out with a variety of turrets. [... | Read more »
Clash Royale: The Road to Legendary Aren...
Supercell recently celebrated its 10th anniversary and their best title, Clash Royale, is as good as it's ever been. Even for lapsed players, returning to the game is as easy as can be. If you want to join us in picking the game back up, we've put... | Read more »
Pokemon Go Fest 2020 will be a virtual e...
Niantic has announced that Pokemon Go Fest will still take place this year although understandably it won't be a physical event. Instead, it will become a virtual celebration and is set to be held on 25th and 26th July. [Read more] | Read more »
Marvel Future Fight's major May upd...
Marvel Future Fight's latest update has now landed, and it sounds like a big one. The focus this time around is on Marvel's Guardians of the Galaxy, and it introduces all-new characters, quests, and uniforms for players to collect. [Read more] | Read more »
SINoALICE, Yoko Taro and Pokelabo's...
Yoko Taro and developer Pokelabo's SINoALICE has now opened for pre-registration over on the App Store. It's already amassed 1.5 million Android pre-registrations, and it's currently slated to launch on July 1st. [Read more] | Read more »
Masketeers: Idle Has Fallen's lates...
Masketeers: Idle Has Fallen is the latest endeavour from Appxplore, the folks behind Crab War, Thor: War of Tapnarok and Light A Way. It's an idle RPG that's currently available for Android in Early Access and will head to iOS at a later date. [... | Read more »
Evil Hunter Tycoon celebrates 2 million...
Evil Hunter Tycoon has proved to be quite the hit since launching back in March, with its most recent milestone being 2 million downloads. To celebrate the achievement, developer Super Planet has released a new updated called Darkness' Front Yard... | Read more »
Peak's Edge is an intriguing roguel...
Peak's Edge is an upcoming roguelike puzzle game from developer Kenny Sun that's heading for both iOS and Android on June 4th as a free-to-play title. It will see players rolling a pyramid shape through a variety of different levels. [Read more] | Read more »

Price Scanner via MacPrices.net

Sale! $200 off on select 2020 13″ MacBook Pro...
Amazon has select 2020 13″ MacBook Pro configurations on sale today for $200 off Apple’s MSRP. Shipping is free. Be sure to purchase the MacBook Pro from Amazon, rather than a third-party seller, and... Read more
June 1 only: $100 off Apple’s iPhones at Boos...
Boost Mobile is offering Apple iPhone 11, 11 Pro, and iPhone 11 Pro Max models for $100 off MSRP with service. Their discount reduces the cost of an iPhone 11/64GB to $599, iPhone 11 Pro to $899 for... Read more
Sams Club Sales Event: $100 off every Apple W...
Sams Club is discounting all Apple Watch Series 5 models by $100 off Apple’s MSRP through June 3, 2020. Choose free shipping or free local store pickup (if available). Sale prices for online orders... Read more
New 16″ MacBook Pros now on sale for up to $2...
Apple reseller DataVision is now offering new 16″ Apple MacBook Pros for up to $255 off MSRP, each including free shipping. Prices start at $2194. DataVision charges sales tax for NY, NJ, PA, and CA... Read more
Apple now offering Certified Refurbished iPho...
Apple is now offering Certified Refurbished iPhone Xr models in the refurbished section of their online store starting at $499. Each iPhone comes with Apple’s standard one-year warranty, ships free,... Read more
Sale! Get a 10.2″ 32GB WiFi iPad for only $27...
Walmart has new 10.2″ 32GB WiFi iPads on sale for $50 off Apple’s MSRP, only $279. These are the same iPads sold by Apple in their retail and online stores. Be sure to select Walmart as the seller... Read more
Apple resellers offer new 2020 Mac minis for...
Apple resellers are offering new 2020 Mac minis for up to $50 off Apple’s MSRP with prices available starting at $759. Shipping is free: (1) B&H Photo: – 2020 4-Core Mac mini: $759 $40 off MSRP... Read more
Sprint is offering the Apple iPhone 11 free t...
Did you miss out on Sprint’s recent free iPhone SE promotion? No worries. Sprint has the 64GB iPhone 11 available for $0 per month for new lines when you trade-in a qualifying phone in any condition... Read more
Apple has clearance 2019 13″ 1.4GHz MacBook P...
Apple has Certified Refurbished 2019 13″ 1.4GHz 4-Core Touch Bar MacBook Pros available today starting at $979 and up to $440 off original MSRP. Apple’s one-year warranty is included, shipping is... Read more
Apple restocks 2019 MacBook Airs starting at...
Apple has clearance, Certified Refurbished, 2019 13″ MacBook Airs available again starting at $779. Each MacBook features a new outer case, comes with a standard Apple one-year warranty, and is... Read more

Jobs Board

*Apple* Mac Desktop Support - Global Dimensi...
…Operate and support an Active Directory (AD) server-client environment for all Apple devices operating on the BUMED network + Leverage necessary industry enterprise Read more
Surgical Technologist III, *Apple* Hill Sur...
Surgical Technologist III, Apple Hill Surgical Center - Full Time Tracking Code D5.29.2020 Job Description Surgical Technologist III Apple Hill Surgical Center Read more
Security Officer - *Apple* Store - NANA (Un...
**Security Officer \- Apple Store** **Description** About NMS Built on a culture of safety and integrity, NMSdelivers award\-winning, integrated support services to Read more
Transition Into Practice Program (TIP) - Sept...
…Academy-Transition into Practice (TIP) Residency program at St Mary Medical Center in Apple Valley, CA. **We are seekingRegistered Nurses who are:** + New graduate Read more
Essbase Developer - *Apple* - Theorem, LLC...
Job Summary Apple is seeking an experienced, detail-minded Essbase developer to join our worldwide business development and strategy team. If you are someone who Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.