Jump to content

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

 

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

 

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

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

 

 

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

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

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>

 

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

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>

 

Link to post
Share on other sites
This thread is more than a year old.

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.