how to host a php website with mysqli on vps step by step process

This tutorial will walk you through setting up a PHP website with MySQLi database support on your own VPS server. Let's begin!

1. Purchase a VPS

First, you need a VPS server from a provider like DigitalOcean, Vultr, Linode, AWS Lightsail, or any other. After purchasing, you will receive a public IP address and login credentials (usually SSH with root access).

2. Connect to Your VPS

Use SSH to securely access your VPS from your local machine.

ssh root@your_vps_ip

3. Update the Server

Always update the system packages to the latest versions to avoid vulnerabilities and compatibility issues.

apt update && apt upgrade -y

4. Install Apache Web Server

Apache is a reliable and popular web server software used to host websites on Linux servers.

apt install apache2 -y

5. Install PHP

PHP is the programming language that powers your dynamic website. We'll install PHP along with necessary extensions like MySQLi, cURL, and mbstring.

apt install php php-mysqli php-cli php-fpm php-mbstring php-curl php-xml -y

6. Install MySQL Server

MySQL is a powerful relational database server. We'll use it to store your website’s data like users, posts, settings, etc.

apt install mysql-server -y

7. Create a Database and User

Log in to MySQL and create a dedicated database and user for your website for better security and management.

mysql -u root -p

CREATE DATABASE my_database;
CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';
FLUSH PRIVILEGES;

8. Upload Your Website Files

Now transfer your PHP website files from your local machine to the VPS server’s web root directory.

scp -r /path/to/your/local/files root@your_vps_ip:/var/www/html/

9. Set Correct File Permissions

It’s important to set proper permissions so Apache can read the files, but they remain secure.

chown -R www-data:www-data /var/www/html
chmod -R 755 /var/www/html

10. Test Your Website

Open your browser and visit: http://your_vps_ip — you should see your website!

11. Set Up a Domain Name (Virtual Host)

To serve your site on a real domain (like mywebsite.com), configure Apache Virtual Hosts.

nano /etc/apache2/sites-available/yourdomain.com.conf

Add a configuration like this:

    ServerAdmin webmaster@yourdomain.com
    ServerName yourdomain.com
    ServerAlias www.yourdomain.com
    DocumentRoot /var/www/html
    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

12. Secure Your Website with SSL

To enable HTTPS, install Certbot and get a free SSL certificate from Let’s Encrypt:

apt install certbot python3-certbot-apache -y
certbot --apache -d yourdomain.com -d www.yourdomain.com

13. PHP MySQLi Connection Example

Here’s a sample PHP code to connect your website to the MySQL database.

connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Your code here

$conn->close();
?>

14. Troubleshooting

If something goes wrong, check Apache error logs for details:

tail -f /var/log/apache2/error.log

Troubleshooting your PHP website with MySQLi on a VPS, explained in simple terms:

1. Why can't I connect to the MySQL database?

If you're unable to connect, check if MySQL is running on your VPS. You should also verify that your PHP code uses the correct database credentials (username, password, and host). Make sure the database user has permission to connect and that your firewall is not blocking MySQL.

2. Why isn't my PHP form submitting data to the database?

If the form isn’t submitting data, it could be due to incorrect form methods or database connection issues. Ensure your form uses the POST method, and your PHP script correctly accesses the data using $_POST. Also, check for any database connection problems by displaying MySQL errors after attempting to insert data.

3. How do I know if MySQLi is working on my server?

To check if MySQLi is enabled, create a file with phpinfo(); and visit it in your browser. This will show you all PHP settings, including MySQLi support. If MySQLi is missing, you may need to install the MySQLi extension for PHP using a command like sudo apt-get install php-mysqli.

4. Why is my website running slowly on the VPS?

If your website is slow, it could be due to heavy PHP scripts, inefficient database queries, or limited server resources. Check your code for any unnecessary loops or queries. Also, optimize your MySQL database by adding indexes to frequently used fields and ensuring your VPS has enough memory and processing power to handle the load.