MySQL PHP Insert data to databases

MySQL PHP Insert data to databases

Soeng Souy

 

MySQL PHP Insert data to databases



Step 1: db.php

What is CRUD

CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for databases. We've already learned how to perform create (i.e. insert), read (i.e. select), update, and delete operations in previous chapters. In this tutorial, we'll create a simple PHP application to perform all these operations on a MySQL database table in one place.

Well, let's start by creating the table which we'll use in all of our examples.

Creating the Database Table

Execute the following SQL query to create a table named employees inside your MySQL database. We will use this table for all of our future operations.

CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(255) NOT NULL,
    office VARCHAR(255) NOT NULL,
    age VARCHAR(255) NOT NULL,
    start_date DATETIME NOT NULL,
    salary INT(10) NOT NULL
);

Step 2: config.php

Creating the Config File

After creating the table, we need to create a PHP script in order to connect to the MySQL database server. Let's create a file named "config.php" and put the following code inside it.

We'll later include this config file in other pages using the PHP require_once() function.

<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '654321');
define('DB_NAME', 'name_db');
 
/* Attempt to connect to MySQL database */
$connection = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
 
// Check connection
if($connection === false)
{
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>

Step 3: index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Data table</title>
    <!-- library css -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.2/css/buttons.bootstrap4.min.css">

    <link rel="stylesheet" href="css/style.css">
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-12">
                <br>
                <h3 class="titulo-tabla">Record Data Table Using PHP</h3>
                <hr>
                
                    <?php
                        // Include config file
                        require_once "config.php";
                        
                        // Attempt select query execution
                        $sql = "SELECT * FROM employees";
                    ?>
                    <?php
                    if($result = mysqli_query($connection, $sql))
                    {
                        if(mysqli_num_rows($result) > 0)
                        {
                    ?>
                           <table id="ejemplo" class="table table-striped table-bordered" style="width:100%">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Name</th>
                                        <th>Position</th>
                                        <th>Office</th>
                                        <th>Age</th>
                                        <th>Start date</th>
                                        <th>Salary</th>
                                        <th>Action</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                        while($row = mysqli_fetch_array($result))
                                        {
                                    ?>
                                    
                                        <tr>
                                            <td><?php echo $row['id'] ;?></td>
                                            <td><?php echo $row['name'] ;?></td>
                                            <td><?php echo $row['position']; ?></td>
                                            <td><?php echo $row['office'] ;?></td>
                                            <td><?php echo $row['age'] ;?></td>
                                            <td><?php echo $row['start_date']; ?></td>
                                            <td>$<?php echo $row['salary'] ;?></td>
                                            <td>
                                                <a href="read.php?id=" <?php echo $row['id'] ;?>><i class="fa fa-eye" aria-hidden="true" style="color:black"></i></a>
                                                <a href="update.php?id=" <?php echo $row['id'] ;?>><i class="fa fa-edit" aria-hidden="true" style="color:#3ca23c;"></i></a>
                                                <a href="delete.php?id=" <?php echo  $row['id']; ?>><i class="fa fa-trash" aria-hidden="true" style="color:red;"></i></a>
                                            </td>
                                        </tr>
                                    <?php
                                    }
                                    ?>
                                </tbody>                          
                            </table>
                        <?php
                            // Free result set
                            mysqli_free_result($result);
                        }
                        else
                        {
                            echo "<p class='lead'><em>No records were found.</em></p>";
                        }
                    }
                    else
                    {
                        echo "ERROR: Could not able to execute $sql. " . mysqli_error($connection);
                    }
 
                    // Close connection
                    mysqli_close($connection);
                    ?>
            </div>
        </div>
    </div>
        <!-- library js -->
        <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/js/bootstrap.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.bootstrap4.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.html5.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.print.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.colVis.min.js"></script>
        <!-- internal script -->
        <script src="js/export.js"></script>

</body>
</html>

Step 4: style.css


:after, :before {
    box-sizing: border-box;
}

a {
    color: #337ab7;
    text-decoration: none;
}
i{
margin-bottom:4px;
}

.btn {
    display: inline-block;
    font-size: 14px;
    font-weight: 400;
    line-height: 1.42857143;
    text-align: center;
    white-space: nowrap;
    vertical-align: middle;
    cursor: pointer;
    user-select: none;
    background-image: none;
    border: 1px solid transparent;
    border-radius: 4px;
}

.btn-app {
    color: white;
    box-shadow: none;
    border-radius: 3px;
    position: relative;
    padding: 10px 15px;
    margin: 0;
    min-width: 60px;
    max-width: 80px;
    text-align: center;
    border: 1px solid #ddd;
    background-color: #f4f4f4;
    font-size: 12px;
    transition: all .2s;
    background-color: steelblue !important;
}

.btn-app > .fa, .btn-app > .glyphicon, .btn-app > .ion {
    font-size: 30px;
    display: block;
}

.btn-app:hover {
    border-color: #aaa;
    transform: scale(1.1);
}

.pdf {
    background-color: #dc2f2f !important;
}

.excel {
    background-color: #3ca23c !important;
}

.csv {
    background-color: #e86c3a !important;
}

.imprimir {
    background-color: #8766b1 !important;
}

.selectTable{
    height:40px;
    float:right;
}

div.dataTables_wrapper div.dataTables_filter {
    text-align: left;
    margin-top: 15px;
}

.btn-secondary {
    color: #fff;
    background-color: #4682b4;
    border-color: #4682b4;
}
.btn-secondary:hover {
    color: #fff;
    background-color: #315f86;
    border-color: #545b62;
}
.titulo-tabla{
    color:#606263;
    text-align:center;
    margin-top:15px;
    margin-bottom:15px;
    font-weight:bold;
}
.inline{
    display:inline-block;
    padding:0;
}

Step 5: js.js

var idioma=
{
    "sProcessing":     "Procesando...",
    "sLengthMenu":     "Mostrar _MENU_ registros",
    "sZeroRecords":    "No se encontraron resultados",
    "sEmptyTable":     "Ningún dato disponible en esta tabla",
    "sInfo":           "Mostrando registros del _START_ al _END_ de un total de _TOTAL_ registros",
    "sInfoEmpty":      "Mostrando registros del 0 al 0 de un total de 0 registros",
    "sInfoFiltered":   "(filtrado de un total de _MAX_ registros)",
    "sInfoPostFix":    "",
    "sSearch":         "Buscar:",
    "sUrl":            "",
    "sInfoThousands":  ",",
    "sLoadingRecords": "Cargando...",
    "oPaginate": {
        "sFirst":    "Primero",
        "sLast":     "Último",
        "sNext":     "Siguiente",
        "sPrevious": "Anterior"
    },
    "oAria": {
        "sSortAscending":  ": Activar para ordenar la columna de manera ascendente",
        "sSortDescending": ": Activar para ordenar la columna de manera descendente"
    },
    "buttons": {
        "copyTitle": 'Informacion copiada',
        "copyKeys": 'Use your keyboard or menu to select the copy command',
        "copySuccess": {
            "_": '%d filas copiadas al portapapeles',
            "1": '1 fila copiada al portapapeles'
        },

        "pageLength": {
        "_": "Mostrar %d filas",
        "-1": "Mostrar Todo"
        }
    }
};

$(document).ready(function() {

var table = $('#ejemplo').DataTable( {

"paging": true,
"lengthChange": true,
"searching": true,
"ordering": true,
"info": true,
"autoWidth": true,
"language": idioma,
"lengthMenu": [[5,10,20, -1],[5,10,50,"Mostrar Todo"]],
dom: 'Bfrt<"col-md-6 inline"i> <"col-md-6 inline"p>',
buttons: {
dom: {
container:{
tag:'div',
className:'flexcontent'
},
buttonLiner: {
tag: null
}
},

buttons: [
        {
            extend:    'copyHtml5',
            text:      '<i class="fa fa-clipboard"></i>Copiar',
            title:'Titulo de tabla copiada',
            titleAttr: 'Copiar',
            className: 'btn btn-app export barras',
            exportOptions: {
                columns: [ 0, 1 ]
            }
        },

        {
            extend:    'pdfHtml5',
            text:      '<i class="fa fa-file-pdf-o"></i>PDF',
            title:'Titulo de tabla en pdf',
            titleAttr: 'PDF',
            className: 'btn btn-app export pdf',
            exportOptions: {
                columns: [ 0, 1 ]
            },
            customize:function(doc) {

                doc.styles.title = {
                    color: '#4c8aa0',
                    fontSize: '30',
                    alignment: 'center'
                }
                doc.styles['td:nth-child(2)'] = { 
                    width: '100px',
                    'max-width': '100px'
                },
                doc.styles.tableHeader = {
                    fillColor:'#4c8aa0',
                    color:'white',
                    alignment:'center'
                },
                doc.content[1].margin = [ 100, 0, 100, 0 ]
            }
        },

        {
            extend:    'excelHtml5',
            text:      '<i class="fa fa-file-excel-o"></i>Excel',
            title:'Titulo de tabla en excel',
            titleAttr: 'Excel',
            className: 'btn btn-app export excel',
            exportOptions: {
                columns: [ 0, 1 ]
            },
        },
        {
            extend:    'csvHtml5',
            text:      '<i class="fa fa-file-text-o"></i>CSV',
            title:'Titulo de tabla en CSV',
            titleAttr: 'CSV',
            className: 'btn btn-app export csv',
            exportOptions: {
                columns: [ 0, 1 ]
            }
        },
        {
            extend:    'print',
            text:      '<i class="fa fa-print"></i>Imprimir',
            title:'Titulo de tabla en impresion',
            titleAttr: 'Imprimir',
            className: 'btn btn-app export imprimir',
            exportOptions: {
                columns: [ 0, 1 ]
            }
        },
        {
            extend:    'pageLength',
            titleAttr: 'Registros a mostrar',
            className: 'selectTable'
        }
    ]
}
});
} );

Step 6: create.php

Creating the Create Page

In this section, we'll build the Create functionality of our CRUD application.

Let's create a file named "create.php" and put the following code inside it. It will generate a web form that can be used to insert records in the employees table.

<?php

// Include config file
require_once "config.php";
 
// Define variables and initialize with empty values
$name     = $position     = $office     = $age     = $start_date     = $salary     = "";
$name_err = $position_err = $office_err = $age_err = $start_date_err = $salary_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST")
{
    // Validate name
    $input_name = trim($_POST["name"]);
    if(empty($input_name))
    {
        $name_err = "Please enter a name.";
    }
    elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/"))))
    {
        $name_err = "Please enter a valid name.";
    }
    else
    {
        $name = $input_name;
    }

    // Validate position
    $input_position = trim($_POST["position"]);
    if(empty($input_position))
    {
        $position_err = "Please enter a position.";
    }
    elseif(!($input_position))
    {
        $position_err = "Please enter a valid position.";
    }
    else
    {
        $position = $input_position;
    }

    // Validate office
    $input_office = trim($_POST["office"]);
    if(empty($input_office))
    {
        $office_err = "Please enter a office.";
    }
    elseif(!($input_office))
    {
        $office_err = "Please enter a valid office.";
    }
    else
    {
        $office = $input_office;
    }

    // Validate age
    $input_age = trim($_POST["age"]);
    if(empty($input_age))
    {
        $age_err = "Please enter the age.";     
    } 
    elseif(!($input_age))
    {
        $age_err = "Please enter a positive integer value.";
    }
    else
    {
        $age = $input_age;
    }

    // Validate date
    $input_start_date = trim($_POST["start_date"]);
    if(empty($input_start_date))
    {
        $start_date_err = "Please enter the start date.";     
    } 
    elseif(!($input_start_date))
    {
        $start_date_err = "Please enter a positive integer value.";
    }
    else
    {
        $start_date = $input_start_date;
    }
    
    // Validate salary
    $input_salary = trim($_POST["salary"]);
    if(empty($input_salary))
    {
        $salary_err = "Please enter the salary amount.";     
    } 
    elseif(!ctype_digit($input_salary))
    {
        $salary_err = "Please enter a positive integer value.";
    }
    else
    {
        $salary = $input_salary;
    }
    
    // Check input errors before inserting in database
    if(empty($name_err) && empty($position_err) && empty($office_err) && empty($age_err) && empty($start_date_err) && empty($salary_err))
    {
        // Prepare an insert statement
        $sql = "INSERT INTO employees (name, position, office, age, start_date, salary) VALUES (?,?,?,?,?,?)";
         
        if($stmt = mysqli_prepare($connection, $sql))
        {
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "ssssss", $name, $position, $age, $office, $start_date, $salary);
            
            // Set parameters
            $name       = $name;
            $position   = $position;
            $office     = $office;
            $age        = $age;
            $start_date = $start_date;
            $salary     = $salary;
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Records created successfully. Redirect to landing page
                header("location: index.php");
                exit();
            }
            else
            {
                echo "Something went wrong. Please try again later.";
            }
        }
         
        // Close statement
        mysqli_stmt_close($stmt);
    }
    
    // Close connection
    mysqli_close($connection);
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:400,700">
    <title>Create Record</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
    
   <!-- add style css -->
   <link rel="stylesheet" href="css/css-create-style.css">

</head>

<body>
    <div class="container">
        <div class="signup-form">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header">
                        <h2>Create Record</h2>
                    </div>
                    <p>Please fill this form and submit to add employee record to the database.</p>
                    <form action="<?= htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
                        <div class="form-group <?= (!empty($name_err)) ? 'has-error' : ''; ?>">
                            <label>Name</label>
                            <input type="text" name="name" class="form-control" value="<?= $name; ?>">
                            <span class="help-block"><?= $name_err;?></span>
                        </div>
                        <div class="form-group <?= (!empty($position_err)) ? 'has-error' : ''; ?>">
                            <label>Position</label>
                            <input type="text" name="position" class="form-control" value="<?= $position; ?>">
                            <span class="help-block"><?= $position_err;?></span>
                        </div>
                        <div class="form-group <?= (!empty($office_err)) ? 'has-error' : ''; ?>">
                            <label>Office</label>
                            <input type="text" name="office" class="form-control" value="<?= $office; ?>">
                            <span class="help-block"><?= $salary_err;?></span>
                        </div>
                        <div class="form-group <?= (!empty($age_err)) ? 'has-error' : ''; ?>">
                            <label>Age</label>
                            <input type="number" name="age" class="form-control" value="<?= $age; ?>">
                            <span class="help-block"><?= $age_err;?></span>
                        </div>
                        <div class="form-group<?= (!empty($start_date_err)) ? 'has-error' : ''; ?>">
                            <label>Start Date</label>
                            <input type="date" name="start_date" class="form-control" value="<?= $start_date; ?>">
                            <span class="help-block"><?= $start_date_err;?></span>
                        </div>
                        <div class="form-group <?= (!empty($salary_err)) ? 'has-error' : ''; ?>">
                            <label>Salary</label>
                            <input type="text" name="salary" class="form-control" value="<?= $salary; ?>">
                            <span class="help-block"><?= $salary_err;?></span>
                        </div>
                        <input type="submit" class="btn btn-primary" value="Submit">
                        <a href="index.php" class="btn btn-default" style="color:red;">Cancel</a>
                    </form>
                </div>
            </div>        
        </div>
    </div>
</body>
</html>

Step 7: css/css-create-style.css.php

body {
    background-image: linear-gradient(315deg, #bdd4e7 0%, #8693ab 74%);
	font-family: 'Roboto', sans-serif;
}
.help-block {
    color:red;
}
.form-control {
	font-size: 15px;
}
.form-control, .form-control:focus, .input-group-text {
	border-color: #e1e1e1;
}
.form-control, .btn {        
	border-radius: 3px;
}
.signup-form {
	width: 100%;
	margin: 0 auto;
	padding: 30px 0;		
}
.signup-form form {
	color: #999;
	border-radius: 3px;
	margin-bottom: 15px;
	background: #fff;
	box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3);
	padding: 30px;
}
.signup-form h2 {
	color: #333;
	font-weight: bold;
	margin-top: 0;
}
.signup-form hr {
	margin: 0 -30px 20px;
}
.signup-form .form-group {
	margin-bottom: 20px;
}
.signup-form label {
	font-weight: normal;
	font-size: 15px;
}
.signup-form .form-control {
	min-height: 38px;
	box-shadow: none !important;
}	
.signup-form .input-group-addon {
	max-width: 42px;
	text-align: center;
}	
.signup-form .btn, .signup-form .btn:active {        
	font-size: 16px;
	font-weight: bold;
	background: #19aa8d !important;
	border: none;
	min-width: 140px;
}
.signup-form .btn:hover, .signup-form .btn:focus {
	background: #179b81 !important;
}
.signup-form a {
	color: #fff;	
	text-decoration: underline;
}
.signup-form a:hover {
	text-decoration: none;
}
.signup-form form a {
	color: #19aa8d;
	text-decoration: none;
}	
.signup-form form a:hover {
	text-decoration: underline;
}
.signup-form .fa {
	font-size: 21px;
}
.signup-form .fa-paper-plane {
	font-size: 18px;
}
.signup-form .fa-check {
	color: #fff;
	left: 17px;
	top: 18px;
	font-size: 7px;
	position: absolute;
}



Reactions
Soeng Souy

Posted by Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

3 Comments

  1. UnknownNovember 30, 2020 at 10:46 AM

    Thanks brother We need videos with explanations, so it will be the best channel

    ReplyDelete
  2. UnknownNovember 30, 2020 at 10:47 AM

    We need videos with explanations, so it will be the best channel

    ReplyDelete
  3. UnknownNovember 30, 2020 at 10:48 AM

    We need videos with explanations, so it will be the best channel

    ReplyDelete

CAN FEEDBACK

Emoji
(y)
:)
:(
hihi
:-)
:D
=D
:-d
;(
;-(
@-)
:P
:o
:>)
(o)
:p
(p)
:-s
(m)
8-)
:-t
:-b
b-(
:-#
=p~
x-)
(k)

close