How to connect Mysql database to php website
Hey guys, if you are working on php programs in Linux and there’s an assignment related to mysql integration in the php website, this guide is for you ;)
Prerequisites: php and a webserver installed(for example-apache)
So First, let’s take a Problem statement — Create a table once you load your website.
To begin with, make sure you have php installed, and to get the version, run
php -vYou will get the below response
Now we have verified php installation, let us begin with installing mysql server
To install MySQL, open terminal and run
sudo apt-get install mysql-serverEnter your password to begin installation and press enter
To check if mysql is installed, run
sudo mysqlIf the installation is completed successfully, your output will be something like this.
Now to access the database using the php program, we have to pass in username and password as arguments in sql query so let’s create a user and password and grant him privileges to access the database.
To begin with, run the below command to create a new user and password.
sudo mysql -u rootCREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Now we have to grant the permissions for the user to access the database, then we have to make sure those privileges are reflected so flush command is used
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';FLUSH PRIVILEGES;
To access mysql on google cloud
GRANT ALL PRIVILEGES ON `%`.* TO 'newuser'@'localhost';To bind to all addresses, we run
#bind-address = 127.0.0.1Run the above commands, and newuser and password will be created. Now the new user is added, so exit and restart the mysql server
exit
service mysql restartNote: For php version 7+ , we have to use mysqli extension for the proper execution of mysql commands. The MySQLi functions allows you to access MySQL database servers.
To Install mysqli extension, run
Sudo apt Install php-mysqliNow we are all set to execute php programs with mysql integration. Let’s solve our problem statement now.
First, let us create our database in mysql server. Let’s connect to our mysql server. Run
sudo mysqlNow to create a table, we need to create a database first. Let’s say our database name is test, so run command
create database test;Now we have to select our database
use test;
exitNow our database is ready, let’s write up the code To create a table once you load your website.
As mentioned in prerequisite, we will be using apache2 to host, so execute the below command
cd /var/www/html
vim sqltest.phpThe above commands creates a file and opens the vim editor.
Copy paste the below code in the editor
<html>
<head>
<title> creating mysql tables </title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser= 'newuser';
$dbpass = 'password';
$conn = mysqli_connect($dbhost , $dbuser , $dbpass,"test");
if(!$conn){
die('Could not connect:'.mysqli_error($conn));
}
echo 'Connected successfully </br>';
$sql="CREATE TABLE student(".
"USN INT NOT NULL,".
"Name VARCHAR(100) NOT NULL,".
"Marks VARCHAR(40) NOT NULL);";
mysqli_select_db($conn,'test');
$retval=mysqli_query($conn,$sql);if(! $retval){
die('Could not create table:'.mysqli_error($conn));
}
echo "Table created successfully\n";
mysqli_close($conn);
?>
</body>
</html>
In lines number 7 ,8 and 9 we are declaring variables and storing the username password and localhost in it. We then use it in line 10 and pass it as parameters to mysqli_connect command, which will initiate the connection with mysql server. If the connection is not successful, we use if statement and mysqli_error() to display the error. We then write the query to create a table and assign that to a variable called sql. We run the query to select the database test. Then we run the mysqli_query for the creation of a table by passing sql variable and connection variable as parameters.
Now save the file and exit the file and open the browser and open the below site
If everything works right, you will get a Table created successfully message.
If no message is visible when you open the site, then go to the directory where your file is created
cd /var/www/htmlAnd run the below command in terminal
php -S localhost:8000open the browser and open the below site
http://localhost:8000/sqltest.php
Now the successful table creation message will be visible.
To check, run the below command in terminal
sudo mysqlThen, run
use test;
desc student;
Note:
1) Mysql user name is “newuser”, and password is “password”
2) change all MySQL command to mysqli if your php version is 7+ , for ex: mysql_connect becomes mysqli_connect , mysql_error() becomes mysqli_error()
Possible errors which you might get and it’s fixes:
- While executing create command check the type of inverted comma you are using. Don't mismatch between below
` and '2. While executing program if there is any error regarding sql query with format, then while assigning $sql
$sql=”CREATE TABLE student(“.“USN INT NOT NULL,”.“Name VARCHAR(100) NOT NULL,”.“Marks VARCHAR(40) NOT NULL);”;
end the query with a semicolon instead of dot.
3. If you have troubles regarding mysqli commands run the below commands
php -vNote down your php version then
sudo apt-get install phpx.x-mysqli
sudo service apache2 restart
sudo phpenmod mysqli
sudo service apache2 restartIn the above commands replace x.x with your php version so if your php version is 7.4 then command will be sudo apt-get install php7.4-mysqli
4. Every time you want to open your php website, run the below command in the directory where your php program is present.
php -S localhost:8000And, if you get any error like this
Run below command
fuser -k 8000/tcpThen you can execute php -S localhost:8000 it will work. As the process using that port will be killed and you will be allowed to use it.
If you have any queries or stuck at something , comment below.
Did I get something wrong? Mention it in the comments. I would love to improve.
If you learned even a thing or two, clap your hands 👏 as many times as possible to show your support!
