Xampp Wordpress Database Connection Error - Blog

Xampp WordPress Database Connection Error

By admin 0 Comment March 5, 2019

How to Connect MySQL Database Using PHP – Safe to PHP 7

In order to be able to interact with a database, especially MySQL database, the first thing we need to do is connecting to the database, there are many ways to do it, so this time, we’ll discuss how to connect MySQL Database using PHP.

PHP is growing, its built-in functions are continuously developed and maintained. For some reasons, some of them were removed including functions for connecting to MySQL database.

Note: Functions in this tutorial also works with MariaDB database (The “Duplicate” of MySQL).

I. MySQLi and PDO

Usually, when we want to connect to MySQL Database Using PHP, we use the following functions:

Today, the above method become an old school way.

So, if you still use those functions you should stop it right now, because, PHP 7 no longer support those functions, it only works until PHP 5.6. Otherwise, we should now use MySQLi or PDO.

So, what functions should be used?

Currently, PHP provides two ways to connect PHP to MySQL sever, using MySQLi (MySQL Improvement) and PDO (PHP Data Object).

  • Yes it support procedural programming.
  • If you usually use mysql_xxx function, then this type will suits to you, as MySQLi provide functions similar to mysql extension, we only need to add an i suffix, for example: mysql_connect() become mysli_connect() .

Object Oriented Programming (OOP Way):

  • Yes it support Object Oriented Programming
  • MySQLi only support MySQL database, so if you work with other databases of planning to use other database, you need to change to PDO
  • Doesn’t support named parameters.
  • Support prepare statement (prevent SQL Injection)
  • A bit faster than PHP PDO

PDO

Support procedural way.

  • Doesn’t support procedural way.
  • If you usually use mysql_xxx functions, then it is necessary to learn from scratch to use this extension.

Support OOP Way

  • PHP PDO Support up to 12 database. List of supported database can be found disini
  • Support named parameters, make it easier to bind value to the query
  • Support prepare statement (prevent SQL Injection)
  • A bit slower than MySQLi

II. Connect MySQL Database Using PHP

There are two ways to connect MySQL database using PHP, both by using MySQLi and PDO. we’ll discuss both.

Using MySQLi to connect to MySQL Database

For procedural programming, use mysql_query() function with some parameters: mysqli_connect(‘db host’, ‘db username’, ‘db password’, ‘db name’) , for example:

For OOP way, the format is similar, we only need to add a new keyword: new mysqli (‘db host’, ‘db username’, ‘db password’, ‘db name’) , for example:

Some possible errors that may occur while running the above code:

  • Error: Access denied for user ‘@’ localhost ‘to database’ wordpress’ means that MySQLi can’t login into MySQL database using the provided user name and password.
  • Error: Unknown database ‘wordpress’ means that the login was successful but MySQLi can’t find wordpress database.

More complete example:

To know more about MySQLi function both using OOP and procedural Interface, please visit: PHP: The MySQLi Extension Function Summary – Manual

On that page, there are various functions that we familiar with, such as: mysqli_num_fields() , mysqli_fetch_row() , and mysqli_fetch_array() .

Using PDO to connect to MySQL Database

Unlike MySQLi, to connect PHP to MySQL server using PHP PDO, we have to use try<> and catch<> block.

The point is try<> means that we try to run a PHP script, if an error occurs, than the error will be captured in the catch<> block so, that the output of the error will be isolated in the block.

More complete example:

Attributes in PDO

PDO has many attributes to define various rule, including:

  • PDO :: ATTR_ERRMODE to adjust the displayed error
  • PDO :: ATTR_DEFAULT_FETCH_MODE to set the default fetch mode

Example of using multiple attributes at once:

Example of using individual attribute:

For a complete list of available attributes, please visit: PHP: PDO :: setAtribute – Manual.

III. Closing Connection

If we look at the above scripts, we always close the mysql connection. Should we do this? the answer could be yes and no.

For the yes answer

Yes, for best practice, as in other programming languages, if we not close the connection, it will still open and stored in memory.

For the no answer

PHP is an interpreted language, means that it not directly related to the physical computer memory (RAM), there is an engine which translated the PHP code into a computer language that is Zend Engine, a big company behind PHP

Well, to keep the program to always run well, then at the end of each script execution, the engine will automatically shut down all connections to the database, so we don’t need to do it manually.

Xampp wordpress database connection error

I’m using xampp and navicat to manage mysql on two dev machines (laptop & desktop). The laptop is my main machine, while the desktop acts as a local dev server for the group.

Connecting to mysql via localhost in navicat is working fine on the laptop. Is there a way (other than the http tunneling method) to allow access to the desktop from the laptop via navicat?

Just make sure that your mysql is binding to a socket on 0.0.0.0 and you should be able to connect to that socket. I guess the default is for mysql to bind to 127.0.0.1, which, of course, is hard to reach from the outside.

EDIT: The MySQL configuration file should be in .\xampp\mysql\bin\my.ini . You need to find the bind-address parameter and change it to 0.0.0.0.

Everyone on your network should then be able to connect that MySQL instance. Of course, that may include people who better shouldn’t do that. But as long as there is a password for each mysql user and the instance is not facing the internet, there is not much to worry about.

Accessing XAMPP MySQL database remotely + locally

T his weekend, I was playing around with XAMPP which I am thinking to use for my quest to become familiar with WordPress development. For those who don’t know what the hack is XAMPP, it is cousin of WAMP and it basically provides development environment for PHP based applications (e.g. WordPress). It is basically combo package of essential modules like Apache, MySQL, PHP and few other add-ons (Mercury and Tomcat). Good thing about using packages like XAMPP or WAMP is when we install them they just configure all components to talk with each other so you don’t need to do any kind juggling for setting up each of those individual components before using them . On internet there are many walkthroughs available about to how to setup XAMPP, so I am not going explain that process.

But here in my case, I have a virtual machine where I have installed XAMPP and I wanted to access that MySQL database from my host system. Main reason for that is I never liked phpMyAdmin to manage MySQL instance and MySQL Workbench is by far very flexible and less confusing to use. I already have MySQL Workbench installed in my local system and I wanted to use that application to access database located in my virtual machine. I must say that if it was SQL Server it is very straight forward process but I didn’t expect that it will be really difficult to setup for MySQL ! Because by default use don’t have permission to access MySQL instance remotely and you have to configure server logins to have permissions to access database remotely. And may be I am wrong, but it looks like that users are bound with specific IP so that user has access to server only from that specified IP (or IP range).

To connect to remote instance of MySQL you have to modify existing user or create new user and have that user permission to access instance from some IP (more better could be to have just some IP range). This can be using phpMyAdmin UI. Just go to “Privileges” option and select “edit privileges” for any user you want to use for remote access. And then into “Change Login Information / Copy User” section select “Use Text Field” from dropdown list for HOST settings and add IP (or IP range using wild cards % and _ ) for example in my case I used 192.168.%.% IP range because I was accessing machine on local network.

Once that setting is changed, all I had to do was to allow port 3306 in firewall and I was able to connect to that remote instance of MySQL using MySQL Workbench without any issue.

Now all I have to do is to create user and database for my WordPress setup and have some kind of source control system (I already have Visual SVN server setup for other .NET projects, this should come handy ). I am hoping that after this much of efforts I will be able to design my WP theme for my own blog …

How to Connect MySQL Database Using PHP – Safe to PHP 7

In order to be able to interact with a database, especially MySQL database, the first thing we need to do is connecting to the database, there are many ways to do it, so this time, we’ll discuss how to connect MySQL Database using PHP.

PHP is growing, its built-in functions are continuously developed and maintained. For some reasons, some of them were removed including functions for connecting to MySQL database.

Note: Functions in this tutorial also works with MariaDB database (The “Duplicate” of MySQL).

I. MySQLi and PDO

Usually, when we want to connect to MySQL Database Using PHP, we use the following functions:

Today, the above method become an old school way.

So, if you still use those functions you should stop it right now, because, PHP 7 no longer support those functions, it only works until PHP 5.6. Otherwise, we should now use MySQLi or PDO.

So, what functions should be used?

Currently, PHP provides two ways to connect PHP to MySQL sever, using MySQLi (MySQL Improvement) and PDO (PHP Data Object).

  • Yes it support procedural programming.
  • If you usually use mysql_xxx function, then this type will suits to you, as MySQLi provide functions similar to mysql extension, we only need to add an i suffix, for example: mysql_connect() become mysli_connect() .

Object Oriented Programming (OOP Way):

  • Yes it support Object Oriented Programming
  • MySQLi only support MySQL database, so if you work with other databases of planning to use other database, you need to change to PDO
  • Doesn’t support named parameters.
  • Support prepare statement (prevent SQL Injection)
  • A bit faster than PHP PDO

PDO

Support procedural way.

  • Doesn’t support procedural way.
  • If you usually use mysql_xxx functions, then it is necessary to learn from scratch to use this extension.

Support OOP Way

  • PHP PDO Support up to 12 database. List of supported database can be found disini
  • Support named parameters, make it easier to bind value to the query
  • Support prepare statement (prevent SQL Injection)
  • A bit slower than MySQLi

II. Connect MySQL Database Using PHP

There are two ways to connect MySQL database using PHP, both by using MySQLi and PDO. we’ll discuss both.

Using MySQLi to connect to MySQL Database

For procedural programming, use mysql_query() function with some parameters: mysqli_connect(‘db host’, ‘db username’, ‘db password’, ‘db name’) , for example:

For OOP way, the format is similar, we only need to add a new keyword: new mysqli (‘db host’, ‘db username’, ‘db password’, ‘db name’) , for example:

Some possible errors that may occur while running the above code:

  • Error: Access denied for user ‘@’ localhost ‘to database’ wordpress’ means that MySQLi can’t login into MySQL database using the provided user name and password.
  • Error: Unknown database ‘wordpress’ means that the login was successful but MySQLi can’t find wordpress database.

More complete example:

To know more about MySQLi function both using OOP and procedural Interface, please visit: PHP: The MySQLi Extension Function Summary – Manual

On that page, there are various functions that we familiar with, such as: mysqli_num_fields() , mysqli_fetch_row() , and mysqli_fetch_array() .

Using PDO to connect to MySQL Database

Unlike MySQLi, to connect PHP to MySQL server using PHP PDO, we have to use try<> and catch<> block.

The point is try<> means that we try to run a PHP script, if an error occurs, than the error will be captured in the catch<> block so, that the output of the error will be isolated in the block.

More complete example:

Attributes in PDO

PDO has many attributes to define various rule, including:

  • PDO :: ATTR_ERRMODE to adjust the displayed error
  • PDO :: ATTR_DEFAULT_FETCH_MODE to set the default fetch mode

Example of using multiple attributes at once:

Example of using individual attribute:

For a complete list of available attributes, please visit: PHP: PDO :: setAtribute – Manual.

III. Closing Connection

If we look at the above scripts, we always close the mysql connection. Should we do this? the answer could be yes and no.

For the yes answer

Yes, for best practice, as in other programming languages, if we not close the connection, it will still open and stored in memory.

For the no answer

PHP is an interpreted language, means that it not directly related to the physical computer memory (RAM), there is an engine which translated the PHP code into a computer language that is Zend Engine, a big company behind PHP

Well, to keep the program to always run well, then at the end of each script execution, the engine will automatically shut down all connections to the database, so we don’t need to do it manually.

Xampp wordpress database connection error

I’m using xampp and navicat to manage mysql on two dev machines (laptop & desktop). The laptop is my main machine, while the desktop acts as a local dev server for the group.

Connecting to mysql via localhost in navicat is working fine on the laptop. Is there a way (other than the http tunneling method) to allow access to the desktop from the laptop via navicat?

Just make sure that your mysql is binding to a socket on 0.0.0.0 and you should be able to connect to that socket. I guess the default is for mysql to bind to 127.0.0.1, which, of course, is hard to reach from the outside.

EDIT: The MySQL configuration file should be in .\xampp\mysql\bin\my.ini . You need to find the bind-address parameter and change it to 0.0.0.0.

Everyone on your network should then be able to connect that MySQL instance. Of course, that may include people who better shouldn’t do that. But as long as there is a password for each mysql user and the instance is not facing the internet, there is not much to worry about.

Accessing XAMPP MySQL database remotely + locally

T his weekend, I was playing around with XAMPP which I am thinking to use for my quest to become familiar with WordPress development. For those who don’t know what the hack is XAMPP, it is cousin of WAMP and it basically provides development environment for PHP based applications (e.g. WordPress). It is basically combo package of essential modules like Apache, MySQL, PHP and few other add-ons (Mercury and Tomcat). Good thing about using packages like XAMPP or WAMP is when we install them they just configure all components to talk with each other so you don’t need to do any kind juggling for setting up each of those individual components before using them . On internet there are many walkthroughs available about to how to setup XAMPP, so I am not going explain that process.

But here in my case, I have a virtual machine where I have installed XAMPP and I wanted to access that MySQL database from my host system. Main reason for that is I never liked phpMyAdmin to manage MySQL instance and MySQL Workbench is by far very flexible and less confusing to use. I already have MySQL Workbench installed in my local system and I wanted to use that application to access database located in my virtual machine. I must say that if it was SQL Server it is very straight forward process but I didn’t expect that it will be really difficult to setup for MySQL ! Because by default use don’t have permission to access MySQL instance remotely and you have to configure server logins to have permissions to access database remotely. And may be I am wrong, but it looks like that users are bound with specific IP so that user has access to server only from that specified IP (or IP range).

To connect to remote instance of MySQL you have to modify existing user or create new user and have that user permission to access instance from some IP (more better could be to have just some IP range). This can be using phpMyAdmin UI. Just go to “Privileges” option and select “edit privileges” for any user you want to use for remote access. And then into “Change Login Information / Copy User” section select “Use Text Field” from dropdown list for HOST settings and add IP (or IP range using wild cards % and _ ) for example in my case I used 192.168.%.% IP range because I was accessing machine on local network.

Once that setting is changed, all I had to do was to allow port 3306 in firewall and I was able to connect to that remote instance of MySQL using MySQL Workbench without any issue.

Now all I have to do is to create user and database for my WordPress setup and have some kind of source control system (I already have Visual SVN server setup for other .NET projects, this should come handy ). I am hoping that after this much of efforts I will be able to design my WP theme for my own blog …

Add Comment

Your email address will not be published. Required fields are marked *