TweetFollow Us on Twitter

PHP And MySQL, Together At Last

Volume Number: 21 (2005)
Issue Number: 6
Column Tag: Programming

Getting Started

PHP And MySQL, Together At Last

by Dave Mark

Interwoven in my last six columns or so were columns that showed you how to install and test PHP and MySQL. The PHP and MySQL columns all dealt with PHP and MySQL in isolation. The PHP code did not access a MySQL database, and the MySQL database access was all done via the Terminal and not via a PHP encrusted web page. In this month's column, we'll verify that both are installed and available, then see if we can't make them play nicely together. Let's start by verifying our install.

Checking Your PHP Install

If you are new to PHP, make your way over to http://www.php.net and browse through their documentation. If you've installed even a reasonably recent version of Mac OS X on your machine, you should have PHP installed. One solid clue that you do have PHP installed can be obtained by typing this command in Terminal:

$ ls /usr/local/

If the result of this command includes the text "php5", chances are, you're all set. If it doesn't, visit Mark Liyanage's wonderful PHP site:

http://www.entropy.ch/software/macosx/php/

Mark has put together an excellent PHP resource for Mac OS X and he does a nice job keeping it up to date. Though, at this writing, the site still lists Mac OS X 10.3 as the most recently supported, I had no trouble using his install package on my Tiger machine. Obviously, you'll want to backup your machine first, just in case something does go wrong.

Hopefully, you do have PHP installed on your machine already. Regardless of how you got there, once you've got PHP 5 installed, here's a simple test to make sure you're all good.

Launch your favorite text editor, be sure it is set to save as plain-text, create a new file, then enter this text:

<?php
phpinfo()
?>

Save the file as test.php and place it in the Sites subfolder of your home folder (inside ~/Sites/, aka, /Users/xxxx/Sites/).

To test your new PHP file, launch Safari and type:

http://127.0.0.1/~davemark/test.php

Obviously, you'll replace "davemark" with your own user name. Safari will ask your Apache server to pass the referenced file on to the PHP pre-processor. If all is kosher, a giant table will appear in a Safari window. The beginning of my giant table is shown in Figure 1.


Figure 1. The PHP info table.

Note that my computer is running PHP version 5.0.4. You'll definitely want to check on http://php.net to see if there's a later version available.

Checking Your MySQL Install

If you are new to MySQL, check out http://www.mysql.com. In the March, 2005 Getting Started column, I went through the process of hunting down the proper package for your version of Mac OS X, installing MySQL, starting and shutting down the MySQL server, and setting up the accounts and passwords.

Then, in the April Getting Started, we used the MySQL monitor application, running in Terminal, to build a database and, within that, a table. We added to and deleted rows from the table, and ran some queries to report on the table data. Obviously, I don't want to repeat all that here, but I thought it would be worth repeating a few of the basics, just to make sure we were on the same page.

Start by making sure you have an alias set up for mysql. In Terminal, type this command:

alias

This will list your aliases. Hopefully, one of your aliases will be:

alias mysql='/usr/local/mysql/bin/mysql'

If not, edit the file .profile in your home directory and add this line to the end of it:

alias mysql='/usr/local/mysql/bin/mysql'

Now quit and re-launch Terminal, which will re-execute the commands in .profile. Now, when you type the alias command, your alias should appear. This lets us type mysql to launch the MySQL monitor.

Launch the monitor by typing:

mysql -u root -p

As a reminder, you are launching the monitor with a user of root. You'll be prompted for a password. Type in the password you created when you created your account. Remember, this root is not the same root as your Mac root account. If you are having trouble logging in as root, go back to your setup instructions or to the March Getting Started.

Here's what my monitor looks like:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.12-standard

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

mysql>

If mysql does not launch your MySQL monitor, either your alias is not set correctly (in which case, go check to see if you've got a file called mysql in the directory /usr/local/mysql/bin/) or you did not install MySQL correctly. If MySQL was not installed correctly, get hold of the March MacTech and follow the installation process, or make your way through the MySQL installation instructions on http://mysql.com.

Assuming your monitor comes up as mine did, type this command:

show databases;

Don't foget the ending semicolon. Here's my result:

mysql> show databases;
+----------+
| Database |
+----------+
| test     |
+----------+
1 row in set (0.18 sec)

mysql>

If you've played with MySQL, you may find yourself with a different set of databases. Not a problem. As long as the command works, you're fine, no matter the result.

Driving MySQL From Within PHP

In April's column, we used the MySQL monitor to create a database, add rows, delete rows, and update values in a table. In the remainder of this month's column, we're going to do the same sorts of things, but do them from within PHP, instead of from the monitor.

Before we get into our PHP example, let's use the monitor to set up a database and table, then populate the table. This approach is pretty typical: Set up the database and table using the monitor, then query/populate the table from your PHP/web interface.

In the monitor, type this command:

mysql> create database products;
Query OK, 1 row affected (0.06 sec)

Next, check to make sure the database got created:

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| products |
| test     |
+----------+
3 rows in set (0.00 sec)

Yup, there it is. Now, let's set products as our database and then create a new table:

mysql> use products;
Database changed
mysql> show tables;
Empty set (0.00 sec)

No tables exist yet. Let's create one:

mysql> create table cables(
   -> name varchar(60),
   -> lengthInCm int(2),
   -> cableType ENUM( 'firewire', 'usb' ),
   -> cableID int(10) auto_increment primary key );
Query OK, 0 rows affected (0.15 sec)

Imagine a database set up for an e-commerce web site, designed to store info on all the site's products. The site sells firewire and usb cables of various lengths. The cables table will store info on the various cables sold by the site. Obviously, this is a very simple example. If this were a real database, we'd want to create multiple tables and have them reference each other. For example, we'd probably create a manufacturer table and have a field in the cables table refer to an entry in that table. This table is not very complicated and would not be terrifically useful in the real world, but it will serve to demonstrate the connection between PHP and MySQL. Read on...

Now let's populate the table:

mysql> insert into cables values ('Varco DX100', 100, 'firewire', 0);
Query OK, 1 row affected (0.13 sec)

Note that we embedded the manufacturer's name in the cable name field. Ick. As I mentioned, this should be a reference to a separate, manufacturer table. For now, this'll do.

Notice that we created an enumerated field. The cableType field can only take on one of two values, either 'firewire' or 'usb'.

We used a value of 0 for the cableID. This asks MySQL to create an index for this entry automatically.

Let's retrieve what we just put in:

mysql> select * from cables;
+------------- +------------+----------- + ------- +
| name         | lengthInCm | cableType  | cableID |
+------------- +------------+----------- + ------- +
| Varco DX100  | 100        | firewire   | 1       |
+------------- +------------+----------- + ------- +
1 row in set (0.04 sec)
Let's add a few more:
mysql> insert into cables values ('Genenco VT100', 100, 'firewire', 0);
Query OK, 1 row affected (0.38 sec)

mysql> insert into cables values ('Genenco VT50', 50, 'firewire', 0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cables values ('Genenco U100', 100, 'usb', 0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cables values ('Plexicor uShorty', 20, 'usb', 0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cables values ('Plexicor fShorty', 20, 'firewire', 0);
Query OK, 1 row affected (0.00 sec)
Now let's take a look at what we've got in the table:
mysql> select * from cables;
+ ------------------+------------ +---------- + --------- +
| name              | lengthInCm  | cableType | cableID   |
+ ------------------+------------ +---------- + --------- +
| Varco DX100       | 100         | firewire  |    1      |
| Genenco VT100     | 100         | firewire  |    2      |
| Genenco VT50      | 50          | firewire  |    3      |
| Genenco U100      | 100         | usb       |    4      |
| Plexicor uShorty  | 20          | usb       |    5      |
| Plexicor fShorty  | 20          | firewire  |    6      |
+ ------------------+------------ +---------- + --------- +
6 rows in set (0.39 sec)

Accessing Your Data from PHP

So now we have a database and a table filled with data. Our next step is to access this data and make it appear on a web page.

As a reminder, your PHP statements will be embedded within your html code. Once your .php file has been handed off to the PHP pre-processor, the pre-processor will interpret the PHP code and replace the code with the output generated by the code. If this confuses you, here's a very short example from last November's column, just to refresh your memory.

Using your plain-text text editor, create a new file called mysqltest.php and save it in your Sites folder, right alongside your test.php file we created earlier. Enter this code in the file and save:

<html>
   <head>
      <title>PHP Test</title>
   </head>
   <body>
      <p>This is some pure HTML loveliness.</p>
      <?php
         echo "<p>Hello, World!</p>\n";
      ?>
      <p>Did we echo properly?</p>
      <?php
         echo date("r");
         echo "\n";
      ?>
      <p>It works!!!</p>
   </body>
</html>

Once your file is saved, go into Safari and enter this address:

http://127.0.0.1/~davemark/mysqltest.php

Make sure you replace "davemark" with your own user name. Figure 2 shows what I saw when my page loaded. The first line was produced by the HTML. The second line was the result of the output of the PHP echo function. The echo function produced some HTML which was added to the stream. Next came another line of HTML ("Did we echo properly?"), followed by another pair of echos, echoing a date string and a return. This is all polished off by a last line of HTML, generating the string "It works!!!".


Figure 2. A simple PHP test.

If you view source on this output, here's what you get:

<html>
   <head>
      <title>PHP Test</title>
   </head>
   <body>
      <p>This is some pure HTML loveliness.</p>
      <p>Hello, World!</p>
      <p>Did we echo properly?</p>
      Wed, 15 Jun 2005 19:43:02 -0400
      <p>It works!!!</p>
   </body>
</html>

Go back and look at the original PHP. Make sure you understand how the original PHP got translated into this source. Remember, every chunk of PHP code in the original source was replaced by its output to achieve this HTML listing.

Connecting to the Database

OK, now we're ready to fetch our data. Our first step is to connect to our database. Open mysqltest.php and replace the contents with this code:

<html>
   <head>
      <title>MySQL Test</title>
   </head>
   <body>
      <p>Connecting to the database...</p>
      <?php
         $host = 'localhost';
         $user = 'root';
         $pw = '';
         $db = 'products';
	
         $link = mysql_connect( $host, $user, $pw )
            or die( 'Could not connect: ' . mysql_error() );
         echo 'Connected successfully';
         mysql_select_db( $db );
      ?>
      <p>If we got here, we've connected!</p>
   </body>
</html>

In the above code, replace the $user and $pw string values with whatever user and password you used to create the database. Save the code and reload this page in Safari: http://127.0.0.1/~davemark/mysqltest.php. Figure 3 shows my results.


Figure 3. Connecting to the database.

In a nutshell, we used the function mysql_connect() to connect to the database and the function mysql_select_db() to select the database. This is like logging in using the MySQL monitor, then saying use products.

One line of code worth taking a second look at is this one:

         $link = mysql_connect( $host, $user, $pw )
            or die( 'Could not connect: ' . mysql_error() );

Notice the use of "or" here. This is a pretty common technique in PHP. The second part of the or clause will only execute if the first part fails. The die() function is equivalent to exit(). die() will post the passed in string as output, then exit.

Notice the use of the "." operator to concatenate two strings together. This is another common PHP technique. In this case, the "." operator will build a single string from 'Could not connect: ' and the string returned by mysql_error(). mysql_error() returns the error message from the previous MySQL operation.

Our next step is to query the database and to print the data we retrieved.

Querying the Database

Back in your text editor, open the file mysqltest.php and replace its contents with this:

<html>
   <head>
      <title>MySQL Test</title>
   </head>
   <body>
      <p>Connecting to the database...</p>
      <?php
         $host = 'localhost';
         $user = 'root';
         $pw = '';
         $db = 'products';

         $link = mysql_connect( $host, $user, $pw )
            or die( 'Could not connect: ' . mysql_error() );
         echo 'Connected successfully';
         mysql_select_db( $db );
      ?>
      <p>Here's the table data:</p>
      <?php
         $sql_statement = "SELECT * FROM cables";
         $results = mysql_query( $sql_statement )
            or printf( "Query error: %s", mysql_error() );

         while ( $row = mysql_fetch_assoc( $results ) )
         {
            echo $row['cableID'] . ": ";
            echo "\"" . $row['name'] . "\"" . ", ";
            echo $row['lengthInCm'] . ", ";
            echo $row['cableType'];
            echo "<p>";
         }
			
         mysql_close( $link );
      ?>
   </body>
</html>

Save the file and reload it from Safari. Figure 4 shows my version of this run. Notice that the data is lightly formatted, with a colon (":") after the item number and commas between each of the fields.


Figure 4. Retrieving the data from the database.

Let's take a look at the code. We started with the original code, connecting to and selecting the database.

<html>
   <head>
      <title>MySQL Test</title>
   </head>
   <body>
      <p>Connecting to the database...</p>
      <?php
         $host = 'localhost';
         $user = 'root';
         $pw = '';
         $db = 'products';

         $link = mysql_connect( $host, $user, $pw )
            or die( 'Could not connect: ' . mysql_error() );
         echo 'Connected successfully';
         mysql_select_db( $db );
      ?>

Next, we spit out a line of HTML, preparing us for the table data to follow.

      <p>Here's the table data:</p>

We load our query into a PHP string, then pass the string into mysql_query(). This is the equivalent of typing the string into the MySQL monitor as a query. Once again, we use the "or" operator and this time, instead of exiting with die(), we'll display an error message using printf(). printf() is derived from its C forbearer, but is a part of the PHP library. As a point of interest, echo is not a function, but is a language construct. You can use echo as a statement, but can't pass it as a function. Use printf() instead.

<?php
         $sql_statement = "SELECT * FROM cables";
         $results = mysql_query( $sql_statement )
            or printf( "Query error: %s", mysql_error() );

Next, we enter a while loop, using mysql_fetch_assoc() to fetch one row of the table at a time. mysql_fetch_assoc() returns an associative array, which is an array indexed by name instead of by number. Instead of $row[3], you'd refer to $row['cableType']. Associative arrays are one of my favorite parts of PHP. Note that you could have also used mysql_fetch_row(), which would have returned a more tradition, numerically indexed array.

while ( $row = mysql_fetch_assoc( $results ) )
         {

For each row of data, we echo the field value, interspersed with colons, spaces and commas. Note that we also make frequent use of the "." operator.

            echo $row['cableID'] . ": ";
            echo "\"" . $row['name'] . "\"" . ", ";
            echo $row['lengthInCm'] . ", ";
            echo $row['cableType'];
            echo "<p>";
         }

Finally, we close the database using the value returned by mysql_connect(), then exit our PHP area and return to HTML.

mysql_close( $link );
      ?>
   </body>
</html>

Until Next Month...

Once again, seems we just get started when I've run out of room to write. <sigh>. Your assignment for this month is to first do a bit of research, then take the result of this month's query and build a nice HTML table instead of just dumping the data using echo. On the research side of things, go to http://php.net and dig down through the PHP manual, looking up the various functions we played with in this month's code. For example, here's a link to the page that talks about mysql_fetch_assoc():

http://php.net/manual/en/function.mysql-fetch-assoc.php

Take some time to get to know the PHP documentation. You'll find it chock full of examples and incredibly useful. Enjoy!


Dave Mark is a long-time Mac developer and author and has written a number of books on Macintosh development. Dave has been writing for MacTech since its birth! Be sure to check out the new Learn C on the Macintosh, Mac OS X Edition at http://www.spiderworks.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Tunnelblick 3.7.4b - GUI for OpenVPN.
Tunnelblick is a free, open source graphic user interface for OpenVPN on OS X. It provides easy control of OpenVPN client and/or server connections. It comes as a ready-to-use application with all... Read more
Carbon Copy Cloner 5.0.5 - Easy-to-use b...
Carbon Copy Cloner backups are better than ordinary backups. Suppose the unthinkable happens while you're under deadline to finish a project: your Mac is unresponsive and all you hear is an ominous,... Read more
Postbox 5.0.22 - Powerful and flexible e...
Postbox is a new email application that helps you organize your work life and get stuff done. It has all the elegance and simplicity of Apple Mail, but with more power and flexibility to manage even... Read more
Ortelius 2.0.8 - Vector drawing app espe...
Ortelius is a full-featured vector drawing application especially for map design. Draw directly with features such as roads, rivers, coastlines, buildings, symbols and contours. Ortelius is known for... Read more
Bartender 3.0.32 - Organize your menu-ba...
Bartender lets you organize your menu-bar apps by hiding them, rearranging them, or moving them to Bartender's Bar. You can display the full menu bar, set options to have menu-bar items show in the... Read more
Adobe Animate CC 2018 18.0.1.115 - Anima...
Animate CC 2018 is available as part of Adobe Creative Cloud for as little as $19.99/month (or $9.99/month if you're a previous Flash Professional customer). Animate CC 2018 (was Flash CC) lets you... Read more
Adobe Lightroom Classic CC 7.1 - Import,...
Adobe Lightroom is available as part of Adobe Creative Cloud for as little as $9.99/month bundled with Photoshop CC as part of the photography package. Lightroom 6 is also available for purchase as a... Read more
ExpanDrive 6.1.8 - Access cloud storage...
ExpanDrive builds cloud storage in every application, acts just like a USB drive plugged into your Mac. With ExpanDrive, you can securely access any remote file server directly from the Finder or... Read more
ExpanDrive 6.1.8 - Access cloud storage...
ExpanDrive builds cloud storage in every application, acts just like a USB drive plugged into your Mac. With ExpanDrive, you can securely access any remote file server directly from the Finder or... Read more
DiskCatalogMaker 7.2.7 - Catalog your di...
DiskCatalogMaker is a simple disk management tool which catalogs disks. Simple, light-weight, and fast Finder-like intuitive look and feel Super-fast search algorithm Can compress catalog data for... Read more

Latest Forum Discussions

See All

Amazing Katamari Damacy guide - beginner...
Amazing Katamari Damacy brings the bizarro world of the original games to mobile and shifts them into an endless format that's just as addictive as the PlayStation entries. Your goal is still to roll as much random stuff as you possibly can, though... | Read more »
Portal Knights guide - crafting tips and...
In Portal Knights, you're only as strong as the items you have at your disposal. This sandbox adventure is all about crafting and building up the next big thing. Whether you're an avid explorer or collector, crafting will likely play a large part... | Read more »
The best deals on the App Store this wee...
A new week means new discounts on the App Store. This week's deals run the gamut of action-adventure titles, puzzle games, and one of the best narrative adventure series out there. If you're looking to fill out your mobile gaming library on a... | Read more »
What you need to know about Animal Cross...
We hope you've been hard at work on collecting all of those holiday items in Animal Crossing: Pocket Camp, because you're about to get a whole new list of fun things to do as the game receives its first big update sometime soon. There are a lot of... | Read more »
Reigns: Her Majesty guide - how to use e...
Ruling a kingdom isn't easy--doubly so for a queen whose every decision is questioned by the other factions seeking a slice of power. Reigns: Her Majesty builds on the original game's swipey tactics, adding items that you can use to move the story... | Read more »
The best new games we played this week -...
Friday has crept up on us once again, so it's time to honor the best new games we've played over the past few days. This past week was a pretty exciting one, with the debut of lots of beautiful new indies and some familiar faces returning to the... | Read more »
Portal Knights guide- beginner tips and...
Portal Knights is finally making the jump to iOS and Android, and it's already climbing the ranks to become the next big MMO experience on mobile. This sprawling sandbox game will let you pursue any adventure you wish, whether you want to sling... | Read more »
Reigns: Her Majesty guide - how to swipe...
Reigns: Her Majesty is storming the App Store this week, bringing more tinder-esque kingdom building to eager players everywhere. If you've played the original Reigns, you'll know that leading a kingdom is never easy. It's a careful balancing act... | Read more »
Getting Over It (Games)
Getting Over It 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: A game I madeFor a certain kind of person To hurt them. • Climb up an enormous mountain with nothing but a hammer and a pot.•... | Read more »
Reigns: Her Majesty (Games)
Reigns: Her Majesty 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: | Read more »

Price Scanner via MacPrices.net

Apple Watch Series 2, Certified Refurbished,...
Apple has Certified Refurbished Apple Watch Nike+ Series 2s, 42mm Space Gray Aluminum Case with Anthracite/Black Nike Sport Bands, available for $249 (38mm) or $279 (42mm). The 38mm model was out of... Read more
Apple offers Certified Refurbished 2016 12″ R...
Apple has Certified Refurbished 2016 12″ Retina MacBooks available starting at $949. Apple will include a standard one-year warranty with each MacBook, and shipping is free. The following... Read more
B&H drops price on 13″ 256GB MacBook Air...
B&H has the 13″ 1.8GHz/256GB Apple MacBook Air (MQD42LL/A) now on sale for $1079 including free shipping plus NY & NJ sales tax only. Their price is $120 off MSRP, and it’s the lowest price... Read more
Holiday sale: 9″ iPads starting at $299, take...
MacMall has 9″ WiFi iPads on sale for $30 off including free shipping: – 9″ 32GB WiFi iPad: $299 – 9″ 128GB WiFi iPad: $399 Read more
Green Monday deal: 15″ 2.8GHz MacBook Pro on...
B&H Photo has the 15″ 2.8GHz Space Gray MacBook Pro on sale for $250 off MSRP for today only as part of their Green Monday/Holiday sale. Shipping is free, and B&H charges sales tax for NY... Read more
Green Monday sale: B&H offers 12″ Apple i...
B&H Photo has 12″ iPad Pros on sale for up to $150 off MSRP as part of their Green Monday/Holiday sale. Shipping is free, and B&H charges sales tax in NY & NJ only: – 12″ 64GB WiFi iPad... Read more
Holiday deal: 21″ and 27″ Apple iMacs on sale...
MacMall has 2017 21″ and 27″ Apple iMacs on sale for up to $200 off MSRP. Shipping is free: – 21″ 2.3GHz iMac: $999 $100 off MSRP – 21″ 3.0GHz iMac: $1199 $100 off MSRP – 21″ 3.4GHz iMac: $1379 $120... Read more
Holiday deal: Apple Mac minis for up to $150...
MacMall has Mac minis on sale for up to $100 off MSRP, each including free shipping: – 1.4GHz Mac mini: $399 $100 off MSRP – 2.6GHz Mac mini: $599 $100 off MSRP – 2.8GHz Mac mini: $949 $50 off MSRP... Read more
Beats by Dr. Dre – BeatsX Earphones on sale f...
Best Buy has BeatsX Earphones on sale for $109, $40 off, on their online store. Sale price for online orders only. Choose free store pickup, if available, or choose free shipping. Read more
10″ 64GB WiFi Apple iPad Pros on sale for $59...
MacMall has 10.5″ 64GB Apple iPad Pros on sale for $599 including free shipping. That’s $50 off MSRP and among the lowest prices available for these iPads from any Apple reseller. Read more

Jobs Board

QA Automation Engineer, *Apple* Pay - Apple...
# QA Automation Engineer, Apple Pay Job Number: 113202642 Santa Clara Valley, California, United States Posted: 11-Dec-2017 Weekly Hours: 40.00 **Job Summary** At Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, 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* Retail - Multiple Positions - Apple,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
*Apple* Information Security - Security Data...
# Apple Information Security - Security Data Analyst Job Number: 113119545 Austin, Texas, United States Posted: 10-Nov-2017 Weekly Hours: 40.00 **Job Summary** This Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.