PHP Mysqli update

Ronel · February 6, 2020

Dear Sir/Madame

I am making a website where user can insert data and wait for the admin to approve/reject the form. Now i am stuck with the update status where an admin can submit with a click pending to approval or reject with comments. I am new to PHP programming. Can somebody help me with the issue. Part 1 is inserting the data and part two is fetching the data but i am unable to solve the status approve/reject and comment at the same time on the view.php? page. Kindly help. Thank you.

<?php


$host="localhost";
$username="root";
$pass="";
$db="ems1";

$conn=mysqli_connect($host,$username,$pass,$db);
if(!$conn){
	die("Database connection error");
}

// insert query for register page
if(isset($_REQUEST['proposal']))
{

$details=$_POST['details'];
$location=$_POST['location'];
$date=$_POST['date'];
$time=$_POST['time'];

$status="Pending";
$comment=$_POST['comment'];
	
	
	 $query="INSERT INTO `proposal` (`details`,`location`,`date`,`time`,`status`,`comment`) VALUES ('$details','$location','$date','$time','$status','$comment')";
	
	$res=mysqli_query($conn,$query);
	if($res){
		$_SESSION['success']="Not Inserted successfully!";
		header('Location:');
	}else{
		echo "Leave not Applied, please try again!";
	}
	}
?>

<div class="col-xs-6 col-xs-push-3 well">
<form class="form-horizontal" method="post" action="" >
<input type="hidden" name="proposal" value="">
  <fieldset>
    <legend>New Proposals  </legend>
	        <!----left box----------->
		<!----right box----------->
	<div class="col-xs-9">
<div class="form-group">
      <label for="inputEmail" class="col-lg-3"><b>Details:</b></label>
      <div class="col-lg-9">
        <input type="text" name="details" class="form-control">
      </div>
    </div>
   <div class="form-group">
      <label for="inputEmail" class="col-lg-3"><b>Location:</b></label>
      <div class="col-lg-9">
        <input type="text" name="location" class="form-control" >
      </div>
    </div>
	<div class="form-group">
      <label for="inputEmail" class="col-lg-3"><b>Date:</b></label>
      <div class="col-lg-9">
        <input type="date" name="date"  class="form-control">
      </div>
    </div>
	<div class="form-group">
      <label for="inputEmail" class="col-lg-3"><b>Time:</b></label>
      <div class="col-lg-9">
        <input type="time" name="time"  class="form-control" >
      </div>
    </div>
	
      <div class="col-lg-9">
          <input type="hidden" name="status" class="form-control" >
      </div>
    </div>
	<div class="form-group">
      <label for="inputEmail" class="col-lg-3"><b></b></label>
      <div class="col-lg-9">
          <input type="hidden" name="comment"  class="form-control">
      </div>
    </div>
	</div>
	
    
    <div class="form-group">
      <div class="col-lg-12">
        <button type="reset" class="btn btn-default">Cancel</button>
        <button type="submit" class="btn btn-primary">Submit</button>
      </div>
    </div>
  </fieldset>
</form>
</div>
</div>

<body>
    
<h2 style="text-align:center; color:orangered;">
DASHBOARD
</h2>
<table>   
<h3>
                    <tr style="background-color:#E4EBC5; color:orangered;">

                    <th>ID</th>
                    <th>Details</th>
                    <th>Location</th>
                    <th>Status</th>
                    <th>Comment</th>
                    

                    </tr>
                    </h3>
</table>
    
  
<?Php
////////////////////////////////////////////
require "dbconfig.php"; // MySQL connection string

$count="SELECT  id,details,location,time,status,comment FROM proposal";

    
if($stmt = $connection->query($count)){


while ($nt = $stmt->fetch_assoc()) {
echo "
  

<body>
<table>

<tr>   
   
  <td><a href=view.php?id=$nt[id]>$nt[id]</a></td>
  <td>$nt[details]</td>
  <td>$nt[location]</td>
  <td>$nt[status]</td>
  <td>$nt[comment]</td>
  

     </tr>
     
      </table>
  </body>
  ";
   
}

}else{
echo $connection->error;
}
?>

Barand · February 6, 2020

Change the select query to add a where clause so you only select pending records.

On each output row, add Approve and Reject buttons with references to the record's id.

A couple of comments on your code:

Use prepared statements, don't put values directly into the query string.
the "for=" attributes in your labels should contain the unique id of the associated input element.

Ronel · February 6, 2020

27 minutes ago, Barand said:

Change the select query to add a where clause so you only select pending records.

On each output row, add Approve and Reject buttons with references to the record's id.

A couple of comments on your code:

Use prepared statements, don't put values directly into the query string.

the "for=" attributes in your labels should contain the unique id of the associated input element.

sir can you please show me how to apply on the code? I am very much new to this

Barand · February 6, 2020

I had something like this in mind

image.png.d32383ef1351d84f445fbb498e1a1381.png

Example code:

<?php

    /* CONNECT TO DB SERVER HERE */
    
    
        //
        //  Check if data was posted for processing
        //
        if ($_SERVER['REQUEST_METHOD']=='POST') {
            $stmt = $conn->prepare("UPDATE ronel
                                    SET status = ?
                                    WHERE id = ?
                                    ");
            $stmt->bind_param('si', $_POST['status'], $_POST['id']);
            $stmt->execute();
            
            header("Location: #"); // reload the page
            exit;
        }

$pendingRecs = '';
$res = $conn->query("SELECT id
                          , details
                          , location
                          , status
                          , comment
                      FROM ronel
                      WHERE status = 'Pending'
                      ORDER BY date, time    
                    ");
if ($row = $res->fetch_assoc()) {
    do {
        $pendingRecs .= "
            <form method='post'>
                <input type='hidden' name='id' value='{$row['id']}'>
                <tr>
                    <td>{$row['id']}</td>
                    <td>{$row['details']}</td>
                    <td>{$row['location']}</td>
                    <td>{$row['status']}</td>
                    <td>{$row['comment']}</td>
                    <td><button name='status' value='Approved' class='w3-button w3-green'>Approve</button></td>
                    <td><button name='status' value='Rejected' class='w3-button w3-red'>Reject</button></td>
                </tr>
            </form>
            ";
    } while ($row = $res->fetch_assoc());
}
else {
    $pendingRecs = "<tr><td colspan='7'>No Pending records</td></tr>" ;
} 
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    body  { font-family: calibri, sans; font-size: 12pt; }
</style>
</head>
<body>
<header class="w3-container w3-orange w3-padding w3-center">
    <h2>Dashboard</h2>
</header>
<div class="w3-content w3-padding">
    <table class="w3-table w3-bordered">
        <tr style="background-color:#E4EBC5; color:orangered;">
            <th>ID</th>
            <th>Details</th>
            <th>Location</th>
            <th>Status</th>
            <th>Comment</th>
            <th colspan="2">Action</th>
        </tr>
        <?=$pendingRecs?>
    </table>
</div>
</body>
</html>

TABLE: ronel

+----------+---------------------------------------+------+-----+---------+----------------+
| Field    | Type                                  | Null | Key | Default | Extra          |
+----------+---------------------------------------+------+-----+---------+----------------+
| id       | int(11)                               | NO   | PRI |         | auto_increment |
| details  | varchar(45)                           | YES  |     |         |                |
| location | varchar(45)                           | YES  |     |         |                |
| date     | date                                  | YES  |     |         |                |
| time     | time                                  | YES  |     |         |                |
| status   | enum('Pending','Approved','Rejected') | NO   |     | Pending |                |
| comment  | varchar(250)                          | YES  |     |         |                |
+----------+---------------------------------------+------+-----+---------+----------------+

Edited February 6, 2020 by Barand

Ronel · February 6, 2020

1 hour ago, Barand said:

I had something like this in mind

Example code:


<?php

    /* CONNECT TO DB SERVER HERE */
    
    
        //
        //  Check if data was posted for processing
        //
        if ($_SERVER['REQUEST_METHOD']=='POST') {
            $stmt = $conn->prepare("UPDATE ronel
                                    SET status = ?
                                    WHERE id = ?
                                    ");
            $stmt->bind_param('si', $_POST['status'], $_POST['id']);
            $stmt->execute();
            
            header("Location: #"); // reload the page
            exit;
        }

$pendingRecs = '';
$res = $conn->query("SELECT id
                          , details
                          , location
                          , status
                          , comment
                      FROM ronel
                      WHERE status = 'Pending'
                      ORDER BY date, time    
                    ");
if ($row = $res->fetch_assoc()) {
    do {
        $pendingRecs .= "
            <form method='post'>
                <input type='hidden' name='id' value='{$row['id']}'>
                <tr>
                    <td>{$row['id']}</td>
                    <td>{$row['details']}</td>
                    <td>{$row['location']}</td>
                    <td>{$row['status']}</td>
                    <td>{$row['comment']}</td>
                    <td><button name='status' value='Approved' class='w3-button w3-green'>Approve</button></td>
                    <td><button name='status' value='Rejected' class='w3-button w3-red'>Reject</button></td>
                </tr>
            </form>
            ";
    } while ($row = $res->fetch_assoc());
}
else {
    $pendingRecs = "<tr><td colspan='7'>No Pending records</td></tr>" ;
} 
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    body  { font-family: calibri, sans; font-size: 12pt; }
</style>
</head>
<body>
<header class="w3-container w3-orange w3-padding w3-center">
    <h2>Dashboard</h2>
</header>
<div class="w3-content w3-padding">
    <table class="w3-table w3-bordered">
        <tr style="background-color:#E4EBC5; color:orangered;">
            <th>ID</th>
            <th>Details</th>
            <th>Location</th>
            <th>Status</th>
            <th>Comment</th>
            <th colspan="2">Action</th>
        </tr>
        <?=$pendingRecs?>
    </table>
</div>
</body>
</html>

TABLE: ronel


+----------+---------------------------------------+------+-----+---------+----------------+
| Field    | Type                                  | Null | Key | Default | Extra          |
+----------+---------------------------------------+------+-----+---------+----------------+
| id       | int(11)                               | NO   | PRI |         | auto_increment |
| details  | varchar(45)                           | YES  |     |         |                |
| location | varchar(45)                           | YES  |     |         |                |
| date     | date                                  | YES  |     |         |                |
| time     | time                                  | YES  |     |         |                |
| status   | enum('Pending','Approved','Rejected') | NO   |     | Pending |                |
| comment  | varchar(250)                          | YES  |     |         |                |
+----------+---------------------------------------+------+-----+---------+----------------+

Sir this has come!! I cannot seem to fine the solution 😕

<?php

    require "dbconfig.php";
      
        //
        //  Check if data was posted for processing
        //
        if ($_SERVER['REQUEST_METHOD']=='POST') {
            $stmt = $conn->prepare("UPDATE ronel
                                    SET status = ?
                                    WHERE id = ?
                                    ");
            $stmt->bind_param('si', $_POST['status'], $_POST['id']);
            $stmt->execute();
            
            header("Location: #"); // reload the page
            exit;
        }

$pendingRecs = '';
$res = $conn->query("SELECT id
                          , details
                          , location
                          , status
                          , comment
                      FROM ronel
                      WHERE status = 'Pending'
                      ORDER BY date, time    
                    ");
if ($row = $res->fetch_assoc()) {
    do {
        $pendingRecs .= "
            <form method='post'>
                <input type='hidden' name='id' value='{$row['id']}'>
                <tr>
                    <td>{$row['id']}</td>
                    <td>{$row['details']}</td>
                    <td>{$row['location']}</td>
                    <td>{$row['status']}</td>
                    <td>{$row['comment']}</td>
                    <td><button name='status' value='Approved' class='w3-button w3-green'>Approve</button></td>
                    <td><button name='status' value='Rejected' class='w3-button w3-red'>Reject</button></td>
                </tr>
            </form>
            ";
    } while ($row = $res->fetch_assoc());
}
else {
    $pendingRecs = "<tr><td colspan='7'>No Pending records</td></tr>" ;
} 
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    body  { font-family: calibri, sans; font-size: 12pt; }
</style>
</head>
<body>
<header class="w3-container w3-orange w3-padding w3-center">
    <h2>Dashboard</h2>
</header>
<div class="w3-content w3-padding">
    <table class="w3-table w3-bordered">
        <tr style="background-color:#E4EBC5; color:orangered;">
            <th>ID</th>
            <th>Details</th>
            <th>Location</th>
            <th>Status</th>
            <th>Comment</th>
            <th colspan="2">Action</th>
        </tr>
        <?=$pendingRecs?>
    </table>
</div>
</body>
</html>

Notice: Undefined variable: conn in /opt/lampp/htdocs/new1/apply.php on line 21

Fatal error: Uncaught Error: Call to a member function query() on null in /opt/lampp/htdocs/new1/apply.php:21 Stack trace: #0 {main} thrown in /opt/lampp/htdocs/new1/apply.php on line 21

Barand · February 6, 2020

2 hours ago, Barand said:

/* CONNECT TO DB SERVER HERE */

Ronel · February 6, 2020

Sir this is my dbconfig.php

<?php

$connection = mysqli_connect("localhost","root", "","update");

?>

Ronel · February 6, 2020

Barand · February 6, 2020

Replace my comment with

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

Ronel · February 6, 2020

great help from you thank you a ton sir you made my day awesome i have been trying these for days!! i cannot thank you enough .

Ronel · February 6, 2020

Sir what if i want to display the data from the database with a link and when i click on the link shows the whole database on the other page? The whole status comment and details all? can it be possible? please help me out with this !

<body>
   
<h2 style="text-align:center; color:orangered;">
DASHBOARD
</h2>
<table>   
<h3>
                    <tr style="background-color:#E4EBC5; color:orangered;">

                    <th>ID</th>
                    <th>Details</th>
                    <th>Location</th>
                    <th>Status</th>
                    <th>Comment</th>
                    

                    </tr>
                    </h3>
</table>
    
  
<?Php
////////////////////////////////////////////
require "dbconfig.php"; // MySQL connection string

$count="SELECT  id,details,location,status,comment FROM ronel";

    
if($stmt = $connection->query($count)){


while ($nt = $stmt->fetch_assoc()) {
echo "
  

<body>
<table>

<tr>   
   
  <td><a href=view.php?id=$nt[id]>$nt[id]/$nt[department]</a></td>
  <td>$nt[id]</td>
 
  <td>$nt[details]</td>
  <td>$nt[location]</td>
  <td>$nt[status]</td>
  <td>$nt[comment]</td>
  

     </tr>
     
      </table>
  </body>
  ";
   
}

}else{
echo $connection->error;
}
?>

Barand · February 6, 2020

1 hour ago, Ronel said:

what if i want to display the data from the database with a link and when i click on the link shows the whole database on the other page?

The code will be pretty much the same as this. The exceptions will be

No update processing required
No " WHERE status = 'Pending' "
Select all columns (ie date and time also)

Ronel · February 7, 2020

Sir lets say i don't want to show the details,date and time, in the dashboard only status,id,location and comment with a click button on its side to view the full details. Now, when i click the full details button it href me to another page and shows me the full details? How do i that here with the same code or do i need to change anything in order to achieve it??

Ronel · February 7, 2020

29 minutes ago, Ronel said:

Sir what if i want to put the comment section(textbox) near the approve and reject button? please show some more light thanks

Barand · February 7, 2020

Your "view details" link will be

<a href="details.php?id=$id">View full details</a>

so that when details.php loads the id of the selected record will be in GET['id'].

Your query is then

SELECT 
       details
     , location,
     , date
     , time
     , status
     , comment
FROM tablename 
WHERE id = ?

Lay out the results as you want

Edited February 7, 2020 by Barand

Ronel · February 8, 2020

20 hours ago, Barand said:
Your "view details" link will be
<a href="details.php?id=$id">View full details</a>
so that when details.php loads the id of the selected record will be in GET['id'].

Your query is then
SELECT 
       details
     , location,
     , date
     , time
     , status
     , comment
FROM tablename 
WHERE id = ?
Lay out the results as you want

Thanks a lot. Sir it is working now but one more trouble sir and that is i need to put a comment text box with the approve and reject button!!

Barand · February 8, 2020

Then add one, for example

<form method='post'>
<textarea name='comment' cols='50' rows='5'><?=$comment?></textarea>
<button name='status' class='w3-button w3-green' value='Approved'>Approve</button> 
<button name='status' class='w3-button w3-red' value='Rejected'>Reject</button>
</form>

Ronel · February 8, 2020

27 minutes ago, Barand said:

Then add one, for example


<form method='post'>
<textarea name='comment' cols='50' rows='5'><?=$comment?></textarea>
<button name='status' class='w3-button w3-green' value='Approved'>Approve</button> 
<button name='status' class='w3-button w3-red' value='Rejected'>Reject</button>
</form>

Sir it says=

    <?php

        $conn = mysqli_connect("localhost","root", "","update");
      
        //
        //  Check if data was posted for processing
        //
        if ($_SERVER['REQUEST_METHOD']=='POST') {
            $stmt = $conn->prepare("UPDATE ronel
                                    SET status = ?
                                    WHERE id = ?
                                    ");
            $stmt->bind_param('si', $_POST['status'], $_POST['id']);
            $stmt->execute();
            
            header("Location: #"); // reload the page
            exit;
        }

$pendingRecs = '';
$res = $conn->query("SELECT id
                          , details
                          , location
                          , status
                          , comment
                      FROM ronel
                      WHERE status = 'Pending'
                      ORDER BY date, time    
                    ");
if ($row = $res->fetch_assoc()) {
    do {
        $pendingRecs .= "
            <form method='post'>
                <input type='hidden' name='id' value='{$row['id']}'>
                <tr>
                    <td>{$row['id']}</td>
                    <td>{$row['details']}</td>
                    <td>{$row['location']}</td>
                    <td>{$row['status']}</td>
                    <td>{$row['comment']}</td>
                    
                    <td><button name='status' value='Approved' class='w3-button w3-green'>Approve</button></td>
                    <td><button name='status' value='Rejected' class='w3-button w3-red'>Reject</button></td>
                    <textarea name='comment' cols='50' rows='5'><?=$comment?></textarea>
                </tr>
            </form>
            ";
    } while ($row = $res->fetch_assoc());
}
else {
    $pendingRecs = "<tr><td colspan='7'>No Pending records</td></tr>" ;
} 
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    body  { font-family: calibri, sans; font-size: 12pt; }
</style>
</head>
<body>
<header class="w3-container w3-orange w3-padding w3-center">
    <h2>Dashboard</h2>
</header>
<div class="w3-content w3-padding">
    <table class="w3-table w3-bordered">
        <tr style="background-color:#E4EBC5; color:orangered;">
            <th>ID</th>
            <th>Details</th>
            <th>Location</th>
            <th>Status</th>
            <th>Comment</th>
            <th colspan="2">Action</th>
        </tr>
        <?=$pendingRecs?>
    </table>
</div>
</body>
</html>

Notice: Undefined variable: comment in /opt/lampp/htdocs/new1/view.php on line 44

Barand · February 8, 2020

You cannot just blindly copy/paste without thought about context and what it is doing.

Lines 35 to 45 should be

                <tr>
                    <td>{$row['id']}</td>
                    <td>{$row['details']}</td>
                    <td>{$row['location']}</td>
                    <td>{$row['status']}</td>
                    <td><textarea name='comment' cols='50' rows='5'>{$row['comment']}</textarea></td>
                    
                    <td><button name='status' value='Approved' class='w3-button w3-green'>Approve</button></td>
                    <td><button name='status' value='Rejected' class='w3-button w3-red'>Reject</button></td>
                    
                </tr>

Barand · February 8, 2020

Now you have added an input field to edit the comment you need to add the comment column to the database update. At present it only updates status in your above code..

Sign In

PHP Mysqli update

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information