Jump to content

PHP Mysqli update

Ronel

By Ronel
in PHP Coding Help

 Share

Recommended Posts

Ronel Regular Member

Ronel

Posted
Posted

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574050
Share on other sites
Ronel Regular Member

Ronel

Posted
  • Author
Posted
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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574052
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574053
Share on other sites
Ronel Regular Member

Ronel

Posted
  • Author
Posted
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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574056
Share on other sites
Ronel Regular Member

Ronel

Posted
  • Author
Posted

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574063
Share on other sites
Barand Prolific Member

Barand

Posted
Posted
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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574067
Share on other sites
Ronel Regular Member

Ronel

Posted
  • Author
Posted

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574069
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574072
Share on other sites
Ronel Regular Member

Ronel

Posted
  • Author
Posted
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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574087
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574088
Share on other sites
Ronel Regular Member

Ronel

Posted
  • Author
Posted
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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574089
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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 comment
https://forums.phpfreaks.com/topic/309987-php-mysqli-update/#findComment-1574090
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
 Share
Go to topic listing
×
×
  • 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.