php mysql user register and login page

Hey!

It seems like there are a few issues in your code and setup that might be causing the connection problem with your MySQL database in your DigitalOcean environment.

Make sure that PHP and MySQL are correctly installed on your Droplet and that the PHP mysqli extension is enabled. You can check this by creating a phpinfo.php file in your web directory with the following content:

<?php
phpinfo();
?>

Then navigate to this file in your browser (e.g., http://your-ip-address/phpinfo.php). Look for sections related to MySQL or mysqli to confirm it’s enabled.

You can actually use the 1-Click LEMP from the Marketplace which comes with Nginx, PHP and MySQL installed out of the box:

https://marketplace.digitalocean.com/apps/lemp

Another issue is with how you’re trying to connect to MySQL. The mysqli constructor doesn’t use an HTTP URL for the hostname. Instead, it should be either the hostname (like localhost or an IP address) or a UNIX socket. Since you are likely running your MySQL server on the same droplet, you should use localhost if MySQL is not externally accessible, or the private IP address if it’s configured to allow connections over the network.

Here’s how you should modify your code:

<?php                
$mysqli = new mysqli("localhost", "root", "*****", "Lapidary");

// Check connection
if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
} else {
  echo "Connection successful";
}
?>

You mentioned saving your file in /var/www/. You need to make sure that this directory is actually being served by your web server (Apache, Nginx, etc.). Typically, web servers serve files from a subdirectory like /var/www/html or a similarly configured directory. Please ensure your file is in the correct directory that your web server is configured to serve files from.

Check the file permissions to make sure that your web server has the right to read the file. You can adjust the permissions using the following command:

sudo chmod 644 /var/www/your-file.php

Replace /var/www/your-file.php with the actual path and filename.

Using the root user in your application is not recommended due to security risks. It’s better to create a new MySQL user with limited privileges that can only access the database it needs to.

Here’s how you can create a new user and grant it permissions (run these commands in your MySQL shell):

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON Lapidary.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Replace 'newuser' and 'password' with your desired username and strong password.

If you’re looking for a more straightforward and less management-intensive approach to hosting your web application, you might consider using DigitalOcean’s App Platform:

https://docs.digitalocean.com/products/app-platform/getting-started/sample-apps/php/

The App platform allows you to focus on your application’s development without the need to manage the underlying servers. By simply pushing your code to GitHub, you can seamlessly deploy updates and changes, making the development process more efficient. Additionally, to get more comfortable with using GitHub, you can use resources like this free eBook on Git and GitHub, which provides a comprehensive introduction to version control systems and will help you maximize your workflow on the App Platform. This approach not only simplifies your deployment process but also ensures that your application is scalable and securely hosted, freeing you from the complexities of server administration.

If you need any more help, feel free to ask!

Best,

Bobby