Jump to content
Ronel

PHP Mysqli update

Recommended Posts

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;
}
?>

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 :(

 

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites
1 hour ago, Barand said:

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  |     |         |                |
+----------+---------------------------------------+------+-----+---------+----------------+

 

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

Share this post


Link to post
Share on other sites
2 hours ago, Barand said:

/* CONNECT TO DB SERVER HERE */

 

Share this post


Link to post
Share on other sites

Sir this is my dbconfig.php

<?php

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

?>

 

Share this post


Link to post
Share on other sites

Replace my comment with

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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;
}
?>

 

 

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites

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??1486894009_Screenshotfrom2020-02-0709-28-19.thumb.png.cf7e3c043cc23c6e7c0bf1fddad2c0d6.png

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites

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>

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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>

 

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.