05
Sat, Oct
2 New Articles

TechTip: MySQL and PHP Are a Perfect Match, Part I

Web Languages
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Install MySQL on your PC, and learn how to tame it using SQLyog.

 

In my last TechTip, I showed you how to install Apache and PHP locally on your PC. But to make the marriage perfect, PHP really needs a database partner, and the open-source MySQL database server is the perfect match.

 

Normally, the database will be installed on IBM i, Windows, or Unix, but if you want to start using MySQL and PHP, a local installment makes a lot of sense because you don't need to worry about messing things up. Heck, you don't even have to be connected to the Internet.

 

In the second part of this tip, I'll present a program called SQLyog, which is an excellent program to use when working with various MySQL databases.

 

So if you find this appealing, please follow the "local" path down the PHP/MySQL road.

 

You must be sure of one very important thing before we start. In my last TechTip, Figure 14 describes installing the MySQL extension in order to access the database. If you have not done that yet, return to the tip and do it. Otherwise, you will end up tearing your hair out (if you have any left) because you will not get anything to function at the end of this tip.

 

OK. That said, let's fire up the MySQL install rocket and move on.

 

First, we have to download the current stable version of MySQL. At the time of writing, this is Version 5.1. So point your browser to http://dev.mysql.com/downloads/ and click on the link shown in Figure 1.

 

102309Jan1

Figure 1: Download MySQL.  (Click images to enlarge.)

 

Then select the MySQL Community Server, which is the free version.

 

102309Jan2 

Figure 2: Select the MySQL Community Server.   

 

Select Windows (if you are running Windows on your PC).

 

102309Jan3

Figure 3: Select Windows.  

 

Then click the "download" link shown on Figure 4.

 

102309Jan4

Figure 4: Click the "download" link.    

 

Save the file as shown in Figure 5.

 

102309Jan7

Figure 5: Save the file.    

 

When done saving, go to the place where you downloaded the mysql-5.1.39-win32.msi file and double-click it. The welcome picture in Figure 6 will appear. Press Next.

 

102309Jan8

Figure 6: Press Next.     

 

Skip all the ads on the next two pages, and press Next until you see the picture in Figure 7.

 

102309Jan11

Figure 7: Choose the setup type.    

 

Here, you to select how you want your version of the database to act. As this is just a common installation, select "Typical" and press Next. Figure 8 will show a confirmation screen showing you the selections you made.

 

102309Jan12

Figure 8: View your selections.    

 

You are now ready to fire away the rocket. Do so by pressing Next, and watch the install screen while the database is being installed. When you're done, a "Wizard Complete" screen like the one in Figure 9 will be shown.

 

102309Jan14

Figure 9: Your installation is complete.  

 

If you want to register the product, do so, but no matter what you select, make sure the "Configure the MySQL server now" is clicked. Press Next, and on the Configuration Wizard screen, press Next again. You will be presented with the picture in Figure 10.

 

102309Jan16

Figure 10: You have configuration choices.  

 

Because I assume this is a first-time MySQL installation, just select "Standard Configuration" on Figure 10, and press Next.

 

102309Jan23

Figure 11: Select your configuration type. 

 

On this screen, you have to decide how you want MySQL to run on your PC. I have never done anything except running it as a Windows service, so please do that. Then you have to select the name of the service, and you have various alternatives. Personally, I like to know what version I have running by just looking at the service name, so in the drop-down select MySQL501. This also ensures that if you ever want to test another version you can have more than one service installed.  Press Next to view Figure 12.

 

102309Jan24

Figure 12: Select your security settings.  

 

This is a very important screen because here you have to decide your root password. The root password to MySQL is like the QSECOFR to a System i. Because this is just a test on your PC, enter something easy to remember, like "1234." Press Next.

 

102309Jan25

Figure 13: You're ready to execute the configuration.    

 

You have now completed all the tasks involved in configuring the server instance, so press Execute and wait while it is carried out. After a little while, you will see Figure 14.

 

102309Jan27

Figure 14: Your configuration is processing.

 

Success! You have now installed and configured a MySQL Database Server on your PC. Now you are ready to use it.

 

You may wonder how this is going to happen. There are various ways. You can use the MySQL command line interface, you can download the very commonly used phpMyAdmin, or you can use SQLyog, which is MySQL manager running on your Windows desktop.

 

You must be aware of one thing before using MySQL. In the world of non-System I, a database is what we know as a library and a table is what we know as a file. With that said, let's install SQLyog.

 

Point your browser to http://code.google.com/p/sqlyog/downloads/list as shown in Figure 15.

 

102309Janq0

Figure 15: Download sqlyog.  

 

Select the SQLyog814.exe - SQLyog Community Edition - 8.14 and save it somewhere on your PC. Then, execute the SQLyog814.exe file, and you will see the SQLyog welcome screen like the one in Figure 16.

 

102309Janq1

Figure 16: Welcome to the SQLyog community!    

 

Press Next and accept the license terms.

 

102309Janq2

Figure 17: Accept license terms.  

 

Then choose the components you want to install.

 

102309Janq3

Figure 18: Choose your components.    

 

Press Next and confirm the install location.

 

102309Janq4

Figure 19: Confirm the install location      

 

Press Next, and SQLyog will be installed.

 

When it's done, a completion screen will be displayed like the one in Figure 20.

 

You are now asked if you want to run the program. Accept it and press Next.

 

102309Janq6

Figure 20: Run the program.    

 

Now, you will be presented with the screen shown in Figure 21. Here, you have to define the database you want to connect to.

 

102309Janq7

Figure 21: Define the database you want to connect to.     

 

Because we will do a localhost installment, enter the name localhost in the Saved Connections field.

 

In the MySQL host address, enter localhost. If you want to access a database running on another server, this is the place where you would enter either the IP address or the domain name.

 

In the Username enter root. You were asked about the password for the root user profile when you installed MySQL. In the Password field, enter the password you specified during the MySQL installation (e.g., "1234").

 

I expect that you did not change the 3306 port, so just leave that as it is.

 

Press the Save button to save your login profile.

 

If you want to see what version of the MySQL database you are connecting to, press the Test Connection button, and you will see an alert like the one in Figure 22:

 

102309Janq8

Figure 22: See the version you're connecting to.     

 

Press OK to close this screen and return to the Connect to MySQL Host screen. Press Connect.

This will reveal the SQLyog workbench, just like the one in Figure 23.

 

102309Janq9

Figure 23: You're in the workbench!    

 

The screen is made up the following way:

 

A: On the right side, you see all the databases you can work with.

B: On the top left side, you see the Query window, where you can enter SQL statements.

C. On the bottom left side, you see a Result/Messages/Table Data area. This is where your table data will be.

 

In my next tip, I will go into more details about SQLyog. But let's start by defining a database and a small table just to get you working.

 

Right-click in the A area and select Create Database as shown in Figure 24.

 

102309Janq10 

Figure 24: Start creating your database.    

 

The Create Database dialog will be displayed.

 

102309Janq11 

Figure 25: Enter the database name.    

 

Enter mydb for the database name, and leave the other fields as they are. Press Create, and the database will be created.

 

Now we will create a table and fill some data into it.

 

102309Janq12

Figure 26: Start creating a table.      

 

On the Tables tab, right-click and select Create Table.

 

The New Table screen will be displayed. Look at Figure 27 and fill your the fields like the one in the example.

 

102309Janq13

Figure 27: Fill in your fields.   

 

Notice the "id" field, which is defined like an int/Auto Incr field. This is like the relative record number in a System i file, and it will automatically be maintained by the database server. It ensures that all records in the table will have a unique record ID.

 

When done entering the field names and types, press Create Table and you will be prompted for a table name.

 

102309Janq14

Figure 28: Enter your table name.   

 

Enter the name "name" in the field and press OK.

 

An alert will confirm that the table is created. Just press OK, and when asked if you want to create more tables, click No. You will be returned to the workbench.

 

Then expand the table to see the fields (A in Figure 29), and in B, click the Table Data tab. Then fill in some data as shown in the example.

 

102309Janq19

Figure 29: Enter your table name.    

 

Every time you enter new data, the red text shown in the figure will appear. When you move the cursor outside the row, the data will be saved and the red message will disappear. The pitfall is that you may forget to move the cursor outside the row, and therefore the data will not be saved.

 

Note: You do not have to enter anything into the id field. The database server will maintain this field as mentioned before.

 

Success! You have now installed a MySQL Database Server, created a database using SQLyog, and entered data into a table called "name."

 

But before I end this tip, I will provide you with a small PHP script that will read the table just to show you that everything now works.

Making a Small PHP/MySQL Test

In your Apache root-dir (htdocs if you made a standard install), create a document called "readname.php" and open it in your editor of choice (I like Notepad++).

 

Please note that this is just an example. I do not recommend that you write code this way, but for a quick test, this will be OK.

 

Enter the following code:

 

<?php

 

$dbHost = "localhost";

$dbUser = "root";

$dbPass = "1234";

$dbDatabase = "mydb";

 

$conn = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");

mysql_select_db("$dbDatabase", $conn) or die ("Couldn't select the database.");

 

 

      $result=mysql_query("select * from name", $conn);

      while($row = mysql_fetch_array($result))

      {

     

            $name =     $row['name'];

            $age  =     $row['age'];

 

            echo "$name - $age <br>";

     

      }

 

?>

 

Then point your browser to the following URL:

 

http://localhost/readname.php

 

The data shown in Figure 29 will appear.

 

102309Janq20

Figure 29: Your data is revealed!     

 

Congratulations! You just read some MySQL data using a PHP script.

Wrapping It All Up

This completes Part I. If you have followed me so far, great! Now the fun is about to begin.

 

In my next tip, I will use the MySQL Database server in a small PHP/AJAX program, like the one Jeff  Olen wrote about in PHP and AJAX, a Great Pairing.

 

So till next time, stay tuned. I tell you, it will be fun!

 

One final note: I have not had the chance to test this setup on an IBM i, but I have tested it on both UNIX and Windows boxes, so hopefully this will also work with the MySQL database installed on the i. If anybody has the chance to test it in an IBM i environment, please share the results with the rest of us so we know what we're up against.

 

 

 

Jan Jorgensen

Jan Jorgensen is one of the owners of www.reeft.dk, which specializes in mobile and i5 solutions. He works with RPG, HTML, JavaScript, Perl, and PHP. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it.

 

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: