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

OnyX 3.8.6 - Maintenance and optimizatio...
OnyX is a multifunction utility that you can use to verify the startup disk and the structure of its system files, to run miscellaneous maintenance and cleaning tasks, to configure parameters in the... Read more
Bean 3.3.3 - Fast and uncluttered word p...
Bean is no longer being actively developed, but will be updated as necessary to patch bugs and maintain OS X compatibility Bean is lean, fast, and uncluttered. If you get depressed at the thought of... Read more
Acorn 6.6.2 - Bitmap image editor.
Acorn is a new image editor built with one goal in mind - simplicity. Fast, easy, and fluid, Acorn provides the options you'll need without any overhead. Acorn feels right, and won't drain your bank... Read more
Tor Browser 10.0 - Anonymize Web browsin...
The Tor Browser Bundle is an easy-to-use portable package of Tor, Vidalia, Torbutton, and a Firefox fork preconfigured to work together out of the box. It contains a modified copy of Firefox that... Read more
EtreCheck Pro 6.3.2 - For troubleshootin...
EtreCheck is an app that displays the important details of your system configuration and allow you to copy that information to the Clipboard. It is meant to be used with Apple Support Communities to... Read more
Macs Fan Control 1.5.8.22 - Monitor and...
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
Iridient Developer 3.4 - Powerful image-...
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
Motion 5.4.7 - Create and customize Fina...
Motion is designed for video editors, Motion 5 lets you customize Final Cut Pro titles, transitions, and effects. Or create your own dazzling animations in 2D or 3D space, with real-time feedback as... Read more
calibre 5.0.1 - Complete e-book library...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital librarian... Read more
Compressor 4.4.8 - Adds power and flexib...
Compressor adds power and flexibility to Final Cut Pro X export. Customize output settings, work faster with distributed encoding, and tap into a comprehensive set of delivery features. Features:... Read more

Latest Forum Discussions

See All

Blastomancer is a comfortable puzzle gam...
Blastomancer arrived on Google Play for Android at the weekend. It’s a 2D puzzle game involving cartoon stick figure wizards and lots of bombs. [Read more] | Read more »
Undercrawl is a procedurally generated r...
Undercrawl is a roguelike dungeon crawler from indie developer Monster Shop Games. It's a genre that's popular in gaming in general but features even more frequently on mobile devices since the shorter, 'run' style of playthrough suits playing in... | Read more »
Distract Yourself With These Great Mobil...
There’s a lot going on right now, and I don’t really feel like trying to write some kind of pithy intro for it. All I’ll say is lots of people have been coming together and helping each other in small ways, and I’m choosing to focus on that as I... | Read more »
BTS Universe Story, the social game that...
Netmarble's highly anticipated social game, BTS Universe Story, is available now for iOS and Android. It's the second collaboration between the hugely successful mobile developer and the K-pop superstars following BTS World. [Read more] | Read more »
The 5 Best Mobile Games Like Hades
Supergiant Games finally released Hades upon the world this week, and we’re loving it. The game plays to all of the studio’s strengths while still retaining a strong sense of identity. It also just so happens to play rather well using the Steam... | Read more »
A Year of Apple Arcade: The Good, The Ba...
Apple Arcade has persisted for just over a year at this point, and although that means I've been busy ranking and re-ranking every game on the service for just about as long, I haven't done much reflection on the service as a whole. [Read more] | Read more »
Animal Restaurant anniversary event team...
Animal idle simulator Animal Restaurant is celebrating its first-year anniversary with a crossover event with popular YouTube series Aaron’s Animals. [Read more] | Read more »
Raziel: Dungeon Arena is a hack 'n...
Raziel: Dungeon Arena is available now on mobile and will appeal to fans of both comic books and old school dungeon crawlers. Not only will you hack 'n' slash your way through mobs of enemies but there's also fully-narrated animated comic to enjoy... | Read more »
Steam Link Spotlight - Hades
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 on Disco Elysium. Read about how it plays using Steam Link over here. | Read more »
Microsoft has acquired ZeniMax Media and...
In the latest of a series of blockbuster moves, Microsoft has now acquired Zenimax Media and its subsidiary, Bethesda Softworks, for $7.5 billion. [Read more] | Read more »

Price Scanner via MacPrices.net

AT&T now offering $1000 discount on Apple...
AT&T is offering a $1000 discount on any Apple iPhone 11 Pro or 11 Pro Max with trade-in and new line of service. Discount applied monthly over a 30 month period. Eligible iPhone trade-ins are... Read more
New 2020 12.9″ iPad Pros on sale for up to $8...
Apple reseller Expercom has new 2020 Apple 12.9″ iPad Pros on sale today for $60-$85 off MSRP, with prices starting at $939. Not every model is in stock, but you can still order an iPad at these... Read more
Apple offers clearance 2019 27″ 5K iMacs for...
Apple has Certified Refurbished 2019 27″ 5K iMacs available starting at $1439 and up to $520 off their original MSRP. Apple’s one-year warranty is standard and shipping is free. The following... Read more
Weekend’s Best MacBook Deal: These 2020 13″ M...
Apple has a full line of Certified Refurbished 2020 13″ 1.4GHz 4-Core Touch Bar MacBook Pros available starting at $1099 and up to $230 off original MSRP. Apple’s one-year warranty is included,... Read more
Clearance 8-core iMac Pro available for $3819...
Apple has Certified Refurbished, clearance, 27″ 3.2GHz 8-Core iMac Pros available $3819 including free shipping. Their price is $1180 off the original MSRP of new models. A standard Apple one-year... Read more
How The Upcoming Mac Transition To Apple Sili...
FEATURE: 09.25.20 – Apple’s plan to transition all of its desktop and notebook computers away from Intel processors to Apple silicon, chips designed by the company itself, has been eclipsed by the... Read more
New low price! Apple Watch SE for only $269
B&H Photo is reporting limited stock of Apple’s new Apple Watch SE GPS models for $10 off MSRP and including free shipping. Their $269 price for the 40mm model is the lowest price we’ve seen so... Read more
Lowest price anywhere: New 13″ 2.0GHz MacBook...
Amazon has new 2020 13″ 2.0GHz/512GB MacBook Pros with 10th generation Intel processors back in stock on sale today for $200 off Apple’s MSRP. Shipping is free. Be sure to purchase the MacBook Pro... Read more
Apple Pro Display XDR with Nano-Texture Glass...
Amazon Apple Premier Partner GatorTec has the Apple Pro Display XDR with Nano-Texture Glass on sale for $5599 shipped, on Amazon. Their price is $400 off Apple’s MSRP, and it’s the cheapest price... Read more
Get a 2019 13″ MacBook Air for only $779 toda...
Apple has clearance, Certified Refurbished, 2019 13″ 1.6GHz/128GB MacBook Airs available again for $779. Each MacBook features a new outer case, comes with a standard Apple one-year warranty, and is... Read more

Jobs Board

Senior Product Manager, *Apple* Platforms -...
…technical/operational expertise of our employees. **Role Details:** The Senior Product Manager, Apple Platform will report to the Senior Director of Product and Read more
Tier 2 Technical Support Analyst - ( *Apple*...
…Support Analystiwho will analyze and determine user software needs on all Apple devices (first support contact), Windows devices, and support printers in the Read more
Medical Assistant - Internal Medicine *Apple...
Schedule & Location Full Time Day (80 Hours every two weeks) Apple Hill Medical Center 25 Monument Road York PA Job Description: Under the general supervision of the Read more
*Apple* Technology Journalist - Valnet Freel...
…freelance basis. Valnet Inc. is looking for journalists with strong knowledge of Apple technology for our website MakeUseOf.com MakeUseOf is one of the largest Read more
Bookseller- *Apple* Shop Support - Penn Sta...
…combination of education, certification and experience will be considered. + A+ and/or Apple certified or able to achieve certification within 60 days required. + Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.