PHP MySQL Database tutorial

 

 

 

Using PHP with MySQL

e-trainings-PHP-MYSQL-Database-tutorial

In this blog, you are going to learn how PHP communicates with MySQL databases. It is common for every web developer to design a database and develop the code to manage data in the database. Once a database is created, you need to do several operations on it such as creating tables, inserting data and updating and deleting and so on. PHP has its own built-in functions to work on data that is stored in a database. The below code examples explains how to perform each operation using PHP. This tutorial requires knowledge of SQL queries. if no idea on SQL, please visit: 

Learn SQL Queries Part1. 

In this tutorial, we are not using any front-end coding. Using only PHP and Database in order to keep the logic simple and straightforward.

 Table of Contents:

PHP MySQL - CREATE
PHP MySQL - READ
PHP MySQL - INSERT
PHP MySQL - UPDATE
PHP MySQL - DELETE

 

Managing MySQL Database in PHP

To work with MySQL Database, PHP provided the following functions.

  • mysqli_connect()         :  Connects to Server 
  • mysqli_query($SQL)    :  Executes sql-query and returns result 
  • mysqli_close()              :  Closes opened connection


Syntax:
 
$conn = mysqli_connect("servername", "username", "password", "databasename")

CREATE


First create a database and table in phpmyadmin.

Databasename: usersdb

Query for Database :

CREATE database usersdb;

Query for Table :

Create a table register with the given code below, in the database: usersdb.

  
 
CREATE TABLE `register` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(15) NOT NULL,
  `mobile` varchar(10) NOT NULL,
  `email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

Below example will add new rows to database.

Click here To view how to run the Queries in PhpMyAdmin

Below, code examples explain how to work with MySQL and PHP.  

In the XAMPP server, create a folder and save all the files and run one by one.


Connect to Database in PHP:  

Once the database created you need to create a connection with data database. 

Then you can perform any operations(read, insert,update, delete)on it.

 

 

Here is the code to test connection with the database.


  
connection.php

<?php

$conn = mysqli_connect ("localhost", "root", "", "usersdb");  

 if ($conn) 

{  

     print "<h2>Database Found ";

}  

else 

{   

    print "<h2>Database NOT Found ";  

} 

mysqli_close($conn); 

 ?>

 

 Move to ToC

 

READ

This code example will read the data from the table that you created.

display_rows.php 

<?php
 
$conn = mysqli_connect("localhost", "root", "", "usersdb");
 
if($conn){
 
$SQL = "SELECT * FROM register";
 
$result = mysqli_query($conn, $SQL);
 
while ($db_fields = mysqli_fetch_assoc($result))
{
print $db_fields['username'] . " ";
print $db_fields['password'] . " ";
print $db_fields['mobile'] . " ";
print $db_fields['email'] . "<br>"; 
}
}
else
{
print "<h2>Database NOT Found ";
}
mysqli_close($conn);
 
?>
 

MenuTOP 

INSERT


Below example will add new rows to database.

add_row.php 
 
<?php
 

$username="testuser";
$password ="pass";
$mobile = "9991234999"];
$email ="testuser@example.com";

$conn = mysqli_connect("localhost", "root", "", "usersdb");
 
if($conn)
{
$SQL = "INSERT INTO register(username, password, mobile, email) 
VALUES ('$username','$password','$mobile','$email')";
 
$result = mysqli_query($conn, $SQL);

print "Record added";
}
else
{
print "Database NOT Found ";
}
 
mysqli_close($conn);
 

?> 
 

Move TOP 

 

UPDATE

Below example updates a row in database. 

 
update_row.php 
 
<?php
 
$username = 'testuser';
$oldpassword = 'pass';
$newpassword = 'test123'; 
 
$conn = mysqli_connect("localhost", "root", "", "usersdb");
 
if ($conn) {
 
$SQL = "UPDATE register SET password = '$newpassword' WHERE password=
'$oldpassword' AND username = '$username'";
 
$result = mysqli_query($conn, $SQL);

print "Record updated to the database";
}
else
{
print "Database NOT Found ";
 
mysqli_close($conn);
 
}
 
?>
 

 MoveUP 

DELETE


This code example deletes a row in database. 

First, it checks if the given row is available. If it is there, then it deletes.  

If not found, displays a message.


delete_row.php 
 
 
<?php

$username= 'testuser';
 
$conn = mysqli_connect("localhost", "root", "", "usersdb");
 
if ($conn) {
 
$result = mysqli_query($conn, "SELECT * from register where
username='$username'");
 
$num_rows=mysqli_num_rows($result);
 
if($num_rows==0)
{
die('Could not find User'.mysqli_error());
}
else
{
mysqli_query($conn, "DELETE FROM register WHERE username='$username'") or die
(mysqli_error());
 
echo "<h1>Record Successfully Deleted..!!</h1>";
} 
 
}
else
{
print "Database NOT Found ";
}
 
mysqli_close($conn);
 
?> 
 
 
 
 

 
 
 
 

Comments

Post a Comment

Popular posts from this blog

Using javascript pass form variables to iframe src

Image
   Open PDF in Bootstrap Modal Window   This tutorial shows How to pass a JavaScript/jQuery variable to bootstrap modal view and sets it to iframe  src attribute. It passes value dynamically to src attribute of the iframe. When the user clicks a link, it opens the URL in a popup window. This  code shows the PDF in a Model without download option by hiding the toolbar.  To show PDF toolbar in modal, just add #toolbar=1   in the above script. index.php <!DOCTYPE html> <html lang= "en" > <head> <title>Bootstrap Model with PDF Example - coding-zon</title> <meta charset= "utf-8" > <meta name= "viewport" content= "width=device-width, initial-scale=1" > <link rel= "stylesheet" href= "https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css" > <script src= "https://cdn.jsdelivr.net/npm/jquery@3.6.1/dist/jquery.slim.
Read more

Creating a new PDF by Merging PDF documents using TCPDF

Image
Merging two PDFs in PHP What is PDF? The Portable Document Format. PDF stands for "Portable Document Format". Essentially, the format is used when you need to save files that cannot be modified but still need to be easily shared and printed. PDF is one of the most commonly used file types around. PDFs generally contain shareable data, such as text or photo documents etc., and are accessible to anyone who has a computer or smartphone. Unlike a Word file, which requires Microsoft Word to view or edit, most browsers will open a PDF. How to merge PDFs? You can merge several documents into one. In PHP, you have a library called TCPDF which allows you to combine one or more PDF documents to one single document. In this article, we will create a new PDF by Merging PDF documents using TCPDF. Download the PDFMerger-tcpdf.zip here. Unzip it and copy it to the PHP server. Copy the PDF files into the same folder that you want to combine. Open the file: merge-pdf-example.php Provide
Read more

Import excel file into mysql in PHP

Image
  Upload Excel file and Import  it into MySQL in PHP  Create a table in some database with given code.   Download Library - extract it, rename the folder to library . Create index.php file with given code in a server. Create excelUpload.php with given code. Create db_config.php . Create one Excel file with some sample data as shown in the below image of Excel file. Create ' uploads ' folder in the same directory. Now start server, run the index.php. Upload a .xls file. If Everything is fine, Excel data inserted in the above created MySQL table - tbl_products . PHP Version:   5.5.11 Folder Structure      Query: CREATE TABLE IF NOT EXISTS `tbl_products` ( `id` int ( 11 ) NOT NULL AUTO_INCREMENT, `productname` varchar ( 100 ) DEFAULT NULL , `description` varchar ( 100 ) DEFAULT NULL , PRIMARY KEY (`id`) )     Top index.php <!DOCTYPE html> <html> <head> <title> coding-zon Demo - Upload Excel to MySQL in PHP </title> <lin
Read more