Setting Up MySQL - Getting Started with PHP & MySQL - PHP, MySQL, JavaScript & HTML5 All-in-One For Dummies (2013)

PHP, MySQL, JavaScript & HTML5 All-in-One For Dummies (2013)

Book I: Getting Started with PHP & MySQL

Chapter 4: Setting Up MySQL

In This Chapter

arrow.png Checking whether MySQL needs to be installed

arrow.png Installing MySQL on Windows, Mac, or Linux

arrow.png Testing MySQL

arrow.png Activating MySQL

arrow.png Troubleshooting the MySQL installation

The MySQL environment includes both the MySQL database software and support programs that you can use to administer your MySQL databases. The MySQL software consists of the MySQL database server, several utility programs that assist in the administration of MySQL databases, and some supporting software that the MySQL server needs (but you don’t need to know about). The heart of MySQL is the MySQL server, which manages the databases. When you interact with a database, you send messages with requests to the database server, which responds by following the instructions in the requests — store data, get data, and so forth.

To use the MySQL databases, you need to use software that can communicate with the MySQL server. When you install MySQL, the mysql client program is automatically installed. The program allows you to administer your MySQL databases.

In this chapter, we tell you what you need to know so that you can get MySQL up and running, and we also include some info on testing the installation as well as doing some troubleshooting if you run into problems.

Checking the MySQL Installation

You might or might not need to install MySQL. MySQL isn’t provided with the Windows operating system, but in many cases on other operating systems, MySQL is already installed. For instance, most recent Linux and Mac distributions automatically install MySQL.

Finding out if MySQL is running or installed

Before installing MySQL, be sure that you actually need to install it. It might already be running on your computer, or it might be installed but not running. Here’s how to check whether MySQL is currently running:

check Windows: If MySQL is running, it will be running as a service. To check this, choose Start⇒Control Panel⇒Administrative Tools⇒Services and scroll down the alphabetical list of services. If MySQL is installed as a service, it appears in the list. If it’s currently running, its status displays Started.

If you found MySQL in the service list, as described, but it isn’t started, you can start it by highlighting MySQL in the service list and clicking Start the Service in the left panel.

check Linux/Unix/Mac: At the command line, type the following:

ps –ax

tip.eps The output should be a list of programs. Some operating systems (usually flavors of Unix) have different options for the ps command. If the preceding doesn't produce a list of programs that are running, type man ps to see which options you need to use.

In the list of programs that appears, look for one called mysqld. If you find it, MySQL is running.

Even if MySQL isn’t currently running, it might be installed but just not started. Here’s how to check to see whether MySQL is installed on your computer:

check Windows: If you didn't find MySQL in the list of current services, look for a MySQL directory or files. You can search by choosing Start⇒Search. The default installation directory is C:\Program Files\MySQL\MySQL Server version number for recent versions or C:\mysql for older versions.

check Linux/Unix/Mac: Type the following:

find / -name "mysql*"

If a directory named mysql is found, then it's likely that MySQL has been installed.

Starting MySQL

If you find MySQL on your computer but did not find it in the list of current services (Windows) or running programs (Linux/Unix/Mac), you need to start it.

To start MySQL on Windows, follow these steps:

1. Open a Command Prompt window.

In Windows 7, choose Start⇒All Programs⇒Accessories⇒Command Prompt. In Windows 8, type command from the Start screen to find the Command Prompt.

2. Change to the folder where MySQL is installed.

For example, type cd C:\Program Files\MySQL\MySQL Server 5.0. Your cursor is now located in the MySQL folder.

3. Change to the bin subfolder by typing cd bin.

Your cursor is now located in the bin subfolder.

4. Start the MySQL Server by typing mysqld --install.

The MySQL server starts as a Windows service. You can check the installation by going to the service list, as described previously, and making sure that MySQL now appears in the service list and its status is Started.

For Linux, chances are that the program will have a script to start it. In some versions of Linux, you can start it by typing:

service mysqld start

In other versions of Linux, you may be able to start it like this:

/etc/init.d/mysqld start

or

/etc/rc.d/init.d/mysqld start

Refer to your Linux documentation for information on how to start the pre-installed MySQL for your distribution and version.

If MySQL isn't installed on your computer, you need to download it and install it from www.mysql.com. Instructions are provided in the remainder of this chapter.

Obtaining MySQL

MySQL open source software is available in two editions:

check Community Server: A freely downloadable, open source edition of MySQL. Anyone who can meet the requirements of the GPL (GNU Public License) can use the software for free. If you’re using MySQL as a database on a website (the subject of this book), you can use MySQL for free, even if you’re making money with your website.

check Enterprise Server: This is an enterprise-grade set of software and services available for a monthly subscription fee.

technicalstuff.eps MySQL is available with a commercial license for those who prefer it. If a developer wants to use MySQL as part of a new software product and wants to sell the new product, rather than release it for free under the GPL, the developer needs to purchase a commercial license.

After deciding which edition you’d like to use, you can read some general information about what’s available at the MySQL website and then download the appropriate files for your operating system — or an all-in-one kit — as described in the following text. You can also verify that the files you’ve downloaded are secure.

Downloading from the MySQL website

You can obtain MySQL from the official MySQL website at www.mysql.com. MySQL is available in binary files — machine files that are already compiled for specific operating systems. If a binary file is available for your operating system, you should download the binary file. If no binary is available for your operating system, you can download the source code and compile and install MySQL.

To obtain MySQL, go to www.mysql.com, select the edition that's appropriate for your use (such as Community Server), choose your platform, and click the Download link for the version you want.

Obtaining MySQL for Windows

The Windows binary file is available with an installer, which will install, configure, and start MySQL. On the MySQL website download page for the version you want, find the Windows section.

In the Windows section, click the download link beside the file you want to download, typically an MSI installer.

Obtaining MySQL for Linux and Unix

Many Linux distributions come with MySQL already installed — or give you the option to install MySQL when you install Linux. Many Linux systems, such as Fedora, SuSE, and Ubuntu, include built-in utilities that download and install MySQL for you, often the most recent version.

tip.eps If you don’t already have MySQL, in many cases, installing MySQL provided by the Linux distribution is an easier, more efficient choice than downloading and installing MySQL from the MySQL website. If you need to install MySQL, such as if the MySQL on your system is an older version, check your current Linux distribution’s website to see whether it offers an easy way to install a current version of MySQL.

If you can’t get the MySQL you need from your Linux distribution website, you can get it from the MySQL website. The download page provides several files for various Linux distributions.

Obtaining MySQL for Mac

Mac OS X 10.2 and later include MySQL. If you need to install a newer version of MySQL on your machine, the MySQL website provides a DMG file for installation on Mac OS X 10.6 or newer. See the later section, Installing MySQL on Mac from a DMG file, for instructions.

In a few unusual situations, you might not be able to install MySQL from a DMG file, such as if you need more or fewer features than the DMG provides. You can download the source code and compile and install MySQL on your Mac if necessary. Instructions are available at the MySQL website.

Obtaining all-in-one installation kits

You can obtain some kits that install PHP, MySQL, and Apache in one procedure. These kits can greatly simplify the installation process. However, the software provided might not include the features and extensions that you need.

XAMPP is a popular all-in-one installation kit that contains Apache, PHP, and MySQL. It also installs phpMyAdmin, a utility for managing your MySQL databases.

XAMPP has stable versions available for Microsoft Windows. XAMPP is available at www.apachefriends.org/en/xampp.html. Instructions for installing XAMPP are provided in Chapter 5 in this minibook.

Verifying a downloaded file

The MySQL website provides methods to verify the software after you download it, as a security precaution to make sure that the file hasn’t been altered by bad guys. Basically, the same process is used to verify the file for PHP, MySQL, and Apache. You can find instructions for verifying the file in Chapter 3 of this minibook in the section about verifying a downloaded file.

Installing MySQL

Although MySQL runs on many platforms, we describe how to install it on Windows, Linux, Unix, and Mac, which together account for the majority of websites on the Internet. Be sure to read the instructions all the way through before beginning the installation.

Running the MySQL Setup Wizard on Windows

To set up MySQL on Windows, follow these steps:

1. Double-click the installer (.msi) file that you downloaded.

The opening screen shown in Figure 4-1 is displayed.

9781118213704-fg010401.eps

Figure 4-1: The opening screen of the MySQL Setup Wizard.

2. Click Install MySQL Products.

You see a screen to accept the license agreement. After reading its terms, if you agree, select I Accept the License Terms and click Next.

3. Select Execute.

Updates will be downloaded. On the Choosing a Setup Type tab, select Full, as shown in Figure 4-2.

4. Click Next.

A requirements check may be performed; if so, click Execute. The prerequisites will be installed, if necessary. Click Next as appropriate to install the prerequisites. The Installation Progress screen will be shown.

5. Click Execute.

The installation progress will be shown for each component and then the configuration section will begin.

6. In the Configuration Overview dialog, click Next to begin the configuration process.

9781118213704-fg010402.eps

Figure 4-2: The Choosing a Setup Type screen of the MySQL Setup Wizard.

7. Choose Developer Machine from the MySQL Server Configuration dialog and click Next.

8. On the MySQL Server Configuration dialog, enter the password that you’ll use for root or administrator access and click Next.

9. On the Configuration Overview dialog, click Next to install the samples.

10. When the samples have been installed, click Next.

11. On the Installation Complete dialog, click Finish.

Installing MySQL on Linux from an RPM file

You can install MySQL on Linux using RPM. Although RPM stands for Red Hat Package Manager, RPM is available on many flavors of Linux, not just Red Hat.

tip.eps However, prior to installing the RPM from MySQL you should see if your distribution has MySQL already packaged. Using the packaged version of MySQL is almost always preferable and is almost always easier to both install and maintain later.

To install MySQL on Linux from an RPM file provided on the MySQL website, follow these steps:

1. Change to the directory where you saved the downloaded files.

For instance, type cd /usr/src/mysql.

One file is named MySQL-server-, followed by the version number, followed by .i386.rpm. The second file has the same name with client, instead of server, in the name.

2. Install the RPM by entering this command:

rpm -i listofpackages

For instance, the command might be

rpm -i MySQL-server-5.0.35-0.i386.rpm MySQL-client-5.0.35-0.i386.rpm

This command installs the MySQL packages. It sets the MySQL account and group name that you need and creates the data directory at /var/lib/mysql. It also starts the MySQL server and creates the appropriate entries in /etc/rc.d so that MySQL starts automatically whenever your computer starts.

You need to be using an account that has permissions to successfully run the rpm command, such as a root account.

3. To test that MySQL is running okay, type this:

bin/mysqladmin --version

You should see the version number of your MySQL server.

Installing MySQL on Mac from a DMG file

You can install MySQL using a Mac OS X 10.2 (Jaguar) or later PKG binary package downloaded from the MySQL website at www.mysql.com. If your operating system is earlier than OS X 10.2, you can't use this package; you will need to download a tarball (a file that is a container for many files and subdirectories) and install MySQL from source code, as described in the next section.

1. Create a user and a group named mysql for MySQL to run under.

In most newer Mac versions of OS X, this user and group already exist.

2. Change to the directory where you downloaded MySQL — for instance, /usr/local.

You see a package named mysql-, followed by the version number and the OS number and dmg, such as mysql- 5.0.37-osx10.4-powerpc.dmg. If the downloaded file doesn't have the extension .dmg, change the filename to give it the .dmg extension.

3. Mount the disk image by double-clicking its icon in the Finder.

4. Double-click the package icon to install the MySQL PKG.

The package installer runs and installs the package. It installs MySQL in the directory /usr/local/mysql-, followed by the version number. It also installs a symbolic link, /usr/local/mysql/, pointing to the directory where MySQL is installed. It initializes the database by running the script mysql_install_db, which creates a MySQL account called root.

5. If necessary, change the owner of the mysql directory.

The directory where MySQL is installed (for example, /usr/local/mysql-5.0.37) should be owned by root. The data directory (such as /usr/local/mysql-5.0.37/data) should be owned by the account mysql. Both directories should belong to the group mysql. If the user and group aren't correct, change them with the following commands:

sudo chown -R root /usr/local/mysql-5.0.37

sudo chown -R mysql /usr/local/mysql-5.0.37/data

sudo chown -R root /usr/local/mysql-5.0.37/bin

6. Install the MySQL Startup Item.

To have your server start every time the computer starts, you need to install the MySQL Startup Item, which is included in the installation disk image in a separate installation package. To install the Startup Item, double-click the MySQLStartupItem.pkg icon.

Installing MySQL from source files

Before you decide to install MySQL from source files, check for RPMs or binary files for your operating system. MySQL RPMs and binary files are precompiled, ready-to-install packages for installing MySQL and are convenient and reliable.

You can install MySQL by compiling the source files and installing the compiled programs. This process sounds technical and daunting, but it isn’t. However, read all the way through the following steps before you begin the installation procedure.

To install MySQL from source code, follow these steps:

1. Create a user and group ID for MySQL to run under by using the following commands:

groupadd mysql

useradd -g mysql mysql

The syntax for the commands might differ slightly on different versions of Unix, or they might be called addgroup and adduser.

Note: You must be using an account authorized to add users and groups.

Note: Some recent Linux distributions and Macs have a mysql account already created.

2. Change to the directory where you downloaded the source tarball — for instance, cd-/usr/local.

You see a file named mysql-, followed by the version number and .tar.gz — for instance, mysql-5.0.35.tar.gz. This file is a tarball.

3. Unpack the tarball by typing

gunzip -c filename | tar -xvf –

For example:

gunzip -c mysql-5.0.35.tar.gz | tar -xvf –

You see a new directory named mysql-version — for instance, mysql-5.0.35 — which contains many files and subdirectories. You must be using an account that is allowed to create files in /usr/local.

4. Change to the new directory.

For instance, you might type cd mysql-5.0.35.

5. Type the following:

./configure --prefix=/usr/local/mysql

You see several lines of output. The output will tell you when configure has finished. This might take some time.

6. Type make.

You see many lines of output. The output will tell you when make has finished. make might run for some time.

7. Type make install.

On a Mac, type sudo make install.

make install finishes quickly.

Note: You might need to run this command as root.

8. Type scripts/mysql_install_db.

This command runs a script that initializes your MySQL databases.

9. Make sure that the ownership and group membership of your MySQL directories are correct. Set the ownership with these commands:

chown -R root /usr/local/mysql

chown -R mysql /usr/local/mysql/data

chgrp -R mysql /usr/local/mysql

These commands make root the owner of all the MySQL directories except data and make mysql the owner of data. All MySQL directories belong to group mysql.

10. Start the MySQL server using the following commands:

On a Mac:

cd /usr/local/mysql

sudo ./bin/mysqld_safe

If necessary, enter your password. Press Ctrl+Z, and then type:

bg

Finally, press Ctrl+D or type exit.

On Linux/Unix:

cd /usr/local/mysql

bin/mysqld_safe --user=mysql &

11. Set up your computer so that MySQL starts automatically when your machine starts by copying the file mysql.server from /usr/local/mysql/support-files to the location where your system has its startup files.

Configuring MySQL

MySQL reads a configuration file when it starts up. If you use the defaults or an installer, you probably don't need to add anything to the configuration file. However, if you install MySQL in a nonstandard location or want the databases to be stored somewhere other than the default, you might need to edit the configuration file. The configuration file is named my.ini or my.cnf. It's located in your system directory (such as Windows or Winnt) if you're using Windows and in /etc on Linux, Unix, and Mac. The file contains several sections and commands. The following commands in the mysqld section sometimes need to be changed:

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

#Path to installation directory. All paths are

# usually resolved relative to this.

basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

#Path to the database root

datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

The # at the beginning of the line makes the line into a comment. The basedir line tells the MySQL server where MySQL is installed. The datadir line tells the server where the databases are located. You can change the port number to tell the server to listen for database queries on a different port.

Starting and Stopping the MySQL Server

If you installed MySQL on Windows with the wizards, on Linux with an RPM, or on a Mac with a PKG file, the MySQL server was started during installation and set up so that it starts automatically whenever your computer boots. However, you might sometimes need to stop or start the server. For instance, if you upgrade MySQL, you must shut down the server before starting the upgrade. Instructions for starting and stopping the MySQL server are provided in this section.

If you installed MySQL from source code, you need to start the MySQL server manually and set it up so that it starts automatically when your computer boots. The instructions for starting the server and setting it up to start at boot up are included in the Installing MySQL from source filessection, earlier in this chapter.

Controlling the server on Windows

If you’re using Windows, MySQL runs as a service. (MySQL is installed as a service when you configure it). You can check whether MySQL is installed as a service, as described in the section, Checking the MySQL Installation, earlier in this chapter. Starting and stopping the service is described in the following sections. You can also start and stop the server manually by using commands set up when MySQL is installed.

If you’re using Windows 98/Me, you can start and stop the server from the command line in a Command Prompt window. Starting and stopping the server on Windows is described in the following sections.

Starting or stopping on Windows

To stop or start the MySQL server, do the following:

1. Choose StartControl PanelAdministrative ToolsServices.

A list of all current services appears.

2. Scroll down the alphabetical listing and click the MySQL service you want to stop or start.

Stop and Start links appear to the left of the service name.

3. Click Stop or Start.

If you don’t find the MySQL server in the list, you can set it up as a service using the Configuration Wizard, described earlier in this chapter in the Running the MySQL Setup Wizard on Windows section.

Performing a manual shutdown

Sometimes you might have difficulty shutting down the server. You can shut the server down manually as follows:

1. Open a Command Prompt (perhaps called DOS) window by choosing StartProgramsAccessoriesCommand Prompt.

2. Change to the bin directory in the directory where MySQL is installed.

For instance, you might type cd c:\Program Files\MySQL\MySQL Server 5.0\bin.

3. Type mysqladmin -u root -p shutdown.

In this command, the account is root. The -p means password, so you will be prompted to type a password. If the account you specify doesn't require a password, leave out the -p.

Controlling the MySQL server on Linux and Mac

When MySQL is installed on Linux, Unix, or Mac, a script is sometimes installed that you can use to start and stop the server, with one of the following commands:

mysql.server start

mysql.server stop

mysql_server restart

If those commands don’t work, you can try these commands, which work on newer versions of Red Hat and other distributions of Linux:

service mysqld start

service mysqld stop

service mysqld restart

Finally, some versions of Debian or Ubuntu can also have MySQL started using these commands:

/etc/init.d/mysql stop

/etc/init.d/mysql start

You can also stop the MySQL server with the mysqladmin utility that is installed when MySQL is installed. Change to the bin subdirectory in the directory where MySQL is installed and type

mysqladmin –u root –p shutdown

The -p causes mysqladmin to prompt you for a password. If the account doesn't require a password, don't include -p.

Testing MySQL

You can test whether MySQL is running by entering the following commands at the command line:

1. Change to the directory where MySQL is installed.

For instance, type cd c:\program files\mysql\mysql server 5.0.

Note: In Windows, open a command prompt window to provide a place where you can type the command.

2. Change to the bin subdirectory (cd bin).

3. Type mysqladmin version.

Output providing information on the MySQL version displays on the screen.

You can further test that MySQL is ready to go by connecting to the MySQL server from the mysql client. When MySQL is installed, a simple, text-based program called mysql is also installed. Because this program connects with a server, it’s called a client. This program connects to the MySQL server and exchanges messages with the server. The program is located in the bin subdirectory in the directory where MySQL is installed.

To test that the MySQL server is running and accepting communication, perform the following steps:

1. Start the client.

In Unix and Linux, type the path/filename (for example, /usr/local/mysql/bin/mysql).

In Windows, open a command prompt window and then type the path\filename (for example, c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql).

This command starts the client if you don’t need to use an account name or a password. If you need to enter an account name or a password or both, use the following parameters:

• -u user: user is your MySQL account name.

• -p: This parameter prompts you for the password for your MySQL account.

For instance, if you're in the directory where the mysql client is located, the command might look like this: mysql -u root -p.

Press Enter after typing the command.

2. Enter your password when prompted for it.

The mysql client starts, and you see something similar to this:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 459 to server version: 5.0.15

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

mysql>

If the MySQL server isn’t running correctly, an error message will display instead of the welcome message.

3. Exit the client program by typing quit.

Troubleshooting MySQL

Some of the more common MySQL installation problems and solutions are described in this section.

Displays error message: Access denied

When you attempt to access your MySQL server, an error message similar to the following is displayed:

Access denied for user 'root'@'localhost' (using password: YES)

The error message means that MySQL did not recognize the account name and password. The message gives as much information as possible. In this case, the message shows that access was attempted from localhost using the account name root and using a password. If you accessed using a blank password, the message would show using password: NO. Either MySQL didn't recognize the account name, the account name isn't allowed to access from this host, or the password is incorrect.

Displays error message: Client does not support authentication protocol

MySQL passwords are stored in a table in the mysql database. When MySQL was updated to version 4.1, the password encryption was changed, making the passwords more secure. However, older MySQL clients don’t under-stand the new password encryption, and they display an error similar to the following:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

In particular, using the mysql client with MySQL 4.1 or later sometimes results in this problem. The best solution is to upgrade to PHP 5 and use the mysqli functions. If you can't upgrade for some reason, you need to use a function called OLD_PASSWORD with the SET PASSWORD command to set the password for any accounts that are causing problems. You might use a command similar to the following:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

Setting passwords is described in detail in Book V, Chapter 2.

Displays error message: Can’t connect to . . .

An error message 2003, as shown here, generally means that the MySQL server isn’t running:

(2003): Can't connect to MySQL server on 'localhost'

To correct this problem, start the server as follows:

check Windows: Choose Start⇒Control Panel⇒Administrative Tools⇒Services. Find the MySQL service and click Start.

check Linux/Mac: Type mysql.server start. You might need to be in the directory where the mysql.server script resides.

MySQL error log

MySQL writes messages to a log file when it starts or stops. It also writes a message when an error occurs. If MySQL stops running unexpectedly, you should always look in the error log for clues.

The following are some messages you might find in the error log:

070415 17:17:01 InnoDB: Started; log sequence number 0 189675

070415 18:01:05 InnoDB: Starting shutdown

The error logs are stored in a subdirectory named data in the directory where MySQL is installed. The error log has the .err file extension.

Though many times the errors will tell you exactly what the problem is, if you encounter an error from the log, you can check the MySQL reference manual at https://dev.mysql.com/doc/refman/5.5/en/error-handling.html for further information.

The MySQL Administration Program

MySQL provides a program for managing MySQL databases called MySQL Workbench. This program isn’t required for your MySQL work environment, but it provides features that help you manage your databases. This program runs on Windows, Linux, and the Mac OS but is used primarily on Windows environments.

Activating MySQL Support

The basic PHP software consists of a core set of functionality and optional extensions that provide additional functionality. MySQL support is provided by extensions. In PHP 4, MySQL support is provided by default, but beginning with PHP 5.0, you must activate MySQL support before PHP can interact with MySQL databases. (For more information about connecting MySQL and PHPs, see Book V.)

PHP provides two extensions for MySQL support: the mysql extension and the mysqli (MySQL Improved) extension. Which extension you need to activate depends on which versions of PHP and MySQL you’re using.

check The mysql extension, available with PHP 4, 5, and 6, provides functions for interacting with MySQL version 4.0 and earlier.

check The mysqli extension, added in PHP 5, provides functions for interacting with MySQL version 4.1 and later. You can also use the mysql functions with the later versions of MySQL, but they can’t access some of the new features added in the later versions of MySQL.

Activating MySQL support on Windows

You activate MySQL by configuring extension lines in the php.ini file, after PHP is installed. In addition, you must place the files that the extension needs in a location where PHP can find the files.

To configure PHP for MySQL support, perform the following steps:

1. Open the php.ini file for editing.

2. Find the list of extensions.

3. Find the line for the MySQL extension (mysql or mysqli, as discussed previously) that you want to use, such as

;extension=php_mysqli.dll

4. Remove the semicolon at the beginning of the line.

If a line doesn’t exist for the MySQL extension that you want to use, add the line.

Activating MySQL support on Linux and the Mac OS

MySQL support is activated during PHP installation on Linux and Mac with installation options. The installation options to activate MySQL must be used during Step 4 of the installation (in Chapter 3 of this minibook) to activate MySQL support. MySQL support can’t be added later, after PHP is compiled and installed.

Use one of the following installation options:

--with-mysqli=DIR

--with-mysql=DIR

DIR is the path to the appropriate MySQL directory. When using with-mysqli, use the path to the file named mysql_config. When using with-mysql, use the path to the directory where mysql is installed, such as:

--with-mysql=/user/local/mysql

On Debian and Ubuntu, PHP and MySQL are included with their own package, which is called php5-mysql on Debian.

Checking MySQL support

To check that MySQL is activated, run the test.php script as described in the section Testing PHP, in Chapter 3 of this minibook. The output should include a section showing MySQL settings, as shown in Figure 4-3. If a MySQL section doesn't appear in the output, see the next section,Troubleshooting PHP and MySQL.

9781118213704-fg010403.tif

Figure 4-3: MySQL settings.

Troubleshooting PHP and MySQL

This section looks at some common errors encountered when trying to connect PHP and MySQL to each other, along with some solutions.

Displays error message: Undefined function

You might see an error message complaining of a mysql function, similar to the following:

Fatal error: Call to undefined function mysqli_connect()

This means that MySQL support isn’t activated for the mysqli functions. Either you didn’t activate any MySQL support or you activated the mysql extension, rather than the mysqli function.

Windows

If MySQL support isn't activated, either the extension line in php.ini is not activated or PHP cannot find the necessary files. Here's what you can do about it:

check Remove the semicolon. Check the extension line in php.ini to be sure the semicolon is removed from the beginning of the mysqli extension line.

check Restart or stop and start the web server. If php.ini looks correct, you might have forgotten to restart the web server after making the change. You can also try stopping the web server completely and then starting it, rather than restarting it.

check Check the php.ini file location. You might be editing the wrong php.ini file. Make sure the php.ini file you're editing is in the location where PHP is looking for it, as shown in the output from phpinfo().

check Check your path. Check that the directory where php_mysql.dll and libmysql.dll are located is in your system path. You can check your path in the output from phpinfo(). The Environment section toward the end of the output shows the path. However, the path shown is not the path that's currently in effect unless you restarted the system after changing the path. When you change the path, the new path is displayed, but it doesn't actually become active until you restart the system.

Linux or Mac

If you see the Undefined Function error message on Linux or Mac, you did not activate a mysql extension when you installed PHP. When installing PHP 5 or 6, you must use one of the MySQL options at compile time.

MySQL functions not activated (Windows)

When you look at the output from phpinfo(), you might not see a section for the mysql or mysqli extension if you're having problems with MySQL. However, in your php.ini file, one or both of the extensions are activated. Some possible causes are

check You didn't restart your server after changing your settings in php.ini.

check You're editing the wrong php.ini file. Check the phpinfo() output for the location of the file that PHP is reading the settings from.

check The necessary .dll files are not in a directory that is specified in your system path.

check The MySQL .dll files that PHP is reading are for a different version of PHP. Sometimes when you update PHP, you don't replace the .dll files with the new .dll files.

For instance, suppose you're running PHP 5.0 and the php_mysqli.dll file is located in c:\windows\system32. You upgrade to PHP 6.0. You copy the .dll file from \ext to the main PHP directory and add c:\php to the end of your system path. However, you forget to remove the old.dll file from its current location. When PHP starts, it encounters the old .dll file first, because the system32 directory is first in the system path, and PHP tries to use the old file. Because it can't use the old file, PHP doesn't activate the mysqli extension. This can be extremely confusing, speaking from painful experience.