How to Add Column in MySQL Database using PHP

March 21, 2021
PHP
How to Sum Column in MySQL using PHP

Tutorial: How to Add Column in MySQL Database using PHP with Source Code

About How to Add Column in MySQL Database using PHP

In this tutorial, I’m going to show you How to Add Column in MySQL Database using PHP. I’ve also included in this tutorial the use of GROUP BY in MySQL query and the two mysqli methods which I have included in the comments. This tutorial does not include a good design but will give you an idea on the topic.

Creating a Database

First, we’re going to create our database.

1. Open phpMyAdmin.

2. Click databases, create a database and name it as “sum”.

3. After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.

CREATE TABLE `product` (
  `productid` INT(11) NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(30) NOT NULL,
PRIMARY KEY(`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `sales` (
  `salesid` INT(11) NOT NULL AUTO_INCREMENT,
  `productid` INT(11) NOT NULL,
  `sales_qty` DOUBLE NOT NULL,
PRIMARY KEY(`salesid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

How to Add Column in MySQL Database using PHP

How to Add Column in MySQL Database using PHP

INSERT INTO `product` (`product_name`) VALUES
('Apple'),
('Orange'),
('Strawberry');

Creating a Connection

Next step is to create a database connection and save it as “conn.php“. This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

<?php
 
//MySQLi Procedural
//$conn = mysqli_connect("localhost","root","","sum");
//if (!$conn) {
//	die("Connection failed: " . mysqli_connect_error());
//}
 
//MySQLi OOP
$conn = new mysqli("localhost","root","","sum");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
?>

Creating a Table and a Form

Next is to create out table and our add form. In this case, we will create a sample sales table and add sale form. We name this as “index.php”.

<?php include('conn.php'); session_start(); ?>
<!DOCTYPE html>
<html>
<head>
<title>How to Sum Column in MySQL using PHP/MySQLi</title>
</head>
<body>
    <h3>Sales Table</h3>
    <table border="1">
        <th>Product Name</th>
        <th>Quantity</th>
    <?php
        $total_qty=0;
 
        //MySQLi Procedural
        //$query=mysqli_query($conn,"select * from sales left join product on product.productid=sales.productid order by product.product_name asc");
        //while($row=mysqli_fetch_array($query)){
        /*	?>
                <tr>
                    <td><?php echo $row['product_name']; ?></td>
                    <td><?php echo $row['sales_qty']; ?></td>
                </tr>
 
            <?php */
        //	$total_qty += $row['sales_qty'];
        //}
 
        //MySQLi OOP
        $query=$conn->query("select * from sales left join product on product.productid=sales.productid order by product.product_name asc");
        while($row=$query->fetch_array()) {
            ?>
                <tr>
                    <td><?php echo $row['product_name']; ?></td>
                    <td><?php echo $row['sales_qty']; ?></td>
                </tr>
            <?php 
            $total_qty += $row['sales_qty'];
        }
    ?>
    <tr>
        <td>TOTAL QTY:</td>
        <td><?php echo $total_qty; ?></td>
 
    </tr>
    </table>
    <div style="position:relative; left: 300px; top: -300px;">
    <h3>Group By Product</h3>
    <ul>
    <?php 
        //MySQLi Procedural
        //$a=mysqli_query($conn,"select *, sum(sales_qty) as total_sales from sales left join product on product.productid=sales.productid group by sales.productid");
        //while($arow=mysqli_fetch_array($a)){
        /*	?>
            <li>Total <?php echo $arow['product_name'] ?>: <?php echo $arow['total_sales']; ?></li>
            <?php */
        //}
 
        //MySQLi OOP
        $a=$conn->query("select *, sum(sales_qty) as total_sales from sales left join product on product.productid=sales.productid group by sales.productid");
        while($arow=$a->fetch_array()){
            ?>
            <li>Total <?php echo $arow['product_name'] ?>: <?php echo $arow['total_sales']; ?></li>
            <?php 
        }
    ?>
 
    </ul>
    <h3>Insert New Sales</h3>
    <form method="POST" action="add_sale.php">
        <select name="sales_product">
            <option value="0">Select Product</option>
            <?php
                $p=$conn->query("select * from product");
                while($prow=$p->fetch_array()){
                    ?>
                    <option value="<?php echo $prow['productid']; ?>"><?php echo $prow['product_name']; ?></option>
                    <?php
                }
 
                //$p=mysqli_query($conn,"select * from product");
                //while($prow=mysqli_fetch_array($p)){
                /*	?>
                    <option value="<?php echo $prow['productid']; ?>"><?php echo $prow['product_name']; ?></option>
                    <?php */
                //}
            ?>
        </select>
        Qty: <input type="text" name="sales_qty" required>
        <input type="submit" value="ADD">
    </form>
    <span>
        <?php
            if (isset($_SESSION['msg'])){
                echo $_SESSION['msg'];
                unset ($_SESSION['msg']);
            }
        ?>
    </span>
    </div>
</body>
</html>

Creating a Add Code

Lastly, we create our add sale code. We name this code as “add_sale.php”.

<?php
 
    include('conn.php');
    session_start();
    $product=$_POST['sales_product'];
    $qty=$_POST['sales_qty'];
        if ($product==0){
            $_SESSION['msg']="Please select a product";
            header('location:index.php');
        }
        else{
            //MySQLi Procedural
            //mysqli_query($conn,"insert into sales (productid,sales_qty) values ('$product','$qty')");
 
            //MySQLi OOP
            $conn->query("insert into sales (productid,sales_qty) values ('$product','$qty')");
            header('location:index.php');
        }
?>

Happy Coding! How to Add Column in MySQL Database using PHP.


Related Tutorials: Auto Sum Total In SQLite using PHP, CRUD Operation in XML File using PHP with Modal, Count Total Rows in Table Using PHP/MySQL, Export MySQL Database into CSV File using PHP/MySQLi

Download Here

Leave a Reply

Your email address will not be published. Required fields are marked *