Click on button the database status/checkout time should be updated in php

Krishna3 · January 25, 2020, 10:28am

Below is my checkout button code:

<td style="text-align:center; vertical-align:middle;"><span class="btn btn-success btn-xs"><a style="color: white;" href=./update.php?id='.$row->id_order.' target="_blank">Check-out</a></span></td>

After this it goes to action script as below: Here checkout time and status need to be updated. But its not working. It looks variable ‘id_order’ is not passing to second page. Please guide on this.

 ' <?php
     if(isset($_GET['id_order'])) {
        $dbhost = 'localhost:3036';
        $dbuser = 'root';
        $dbpass = 'password';

        $conn = mysql_connect($dbhost, $dbuser, $dbpass);
        $now_date = date('Y-m-d h:i:sa');
        if(! $conn ) {
           die('Could not connect: ' . mysql_error());
        }

        $id_order = $_GET['id_order'];


        $sql = "UPDATE htl_booking_detail SET check_out='$now_date' WHERE id_order='$id_order'";
        mysql_select_db('databasename');
        $retval = mysql_query( $sql, $conn );

        if(! $retval ) {
           die('Could not update data: ' . mysql_error());
        }
        echo "Updated data successfully\n";

        mysql_close($conn);
     }else {
        ?>

        <?php
     }
  ?>'

FatmaNagori · January 25, 2020, 11:18am

you need to set href attribute as following:

<a href=<?php echo "./update.php?id=". $row->id_order .";" ?> target="_blank" >Check-out</a>

since you passed query as id so to get the value instead of using $_GET['id_order'] use $GET['id'] .

Krishna3 · January 25, 2020, 11:49am

I have used the above advised and its not working.

Screenshot as below:
checkout

Krishna3 · January 25, 2020, 11:58am

I have tried with below code:

<a style="color: white;" href="./update.php?id=<?php echo $row->id_order; ?> " target="_blank">Check-out</a>

Now clik on button ‘check-out’ URL is redirecting to http://192.168.1.12/gh/donation/update.php?id=<?php%20echo%20$row->id_order;%20?> and displaying below error:

Warning: mysqli_connect(): (HY000/2002): Connection refused in /var/www/html/gh/donation/update.php on line 14
Could not connect:

Please help on this.

Krishna3 · January 25, 2020, 12:54pm

Update:

I am getting below error now:
Warning : mysqli_select_db() expects exactly 2 parameters, 1 given in /var/www/html/gh/donation/update.php on line 24

Code is as below:

<?php
         if(isset($_GET['id'])) {
            $dbhost = 'localhost';
            $dbuser = 'root';
            $dbpass = 'password';

            $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
            $now_date = date('Y-m-d h:i:sa');
            if(! $conn ) {
               die('Could not connect: ' . mysql_error());
            }

            $id = $_GET['id'];


            $sql = "UPDATE htl_booking_detail SET check_out='$now_date' WHERE id='$id'";
            mysqli_select_db($conn,'db_name') or die("cannot select DB");
            $retval = mysqli_query( $sql, $conn );

            if(! $retval ) {
               die('Could not update data: ' . mysqli_error());
            }
            echo "Updated data successfully\n";

            mysqli_close($conn);
         }else {
            ?>

            <?php
         }
      ?>

Please help me on this.

FatmaNagori · January 25, 2020, 1:11pm

if you want to connect to a database you can simply do that by using following code:

<?php
$host="localhost";
$user="root";
$password="your password";
$db=" your database name here";
$conn=mysqli_connect($host,$user,$password,$db) or die('Could not connect: ' . mysql_error());
/*..................All Your code.....................*/
?>

I want to know the structure of your database if you need more help.

FatmaNagori · January 25, 2020, 2:14pm

if you don’t want to change your code as i suggested in my last post.you can simply change $retval=mysqli_query($sql,$conn); line to $retval=mysqli_query($conn,$sql);
will solve your problem.

Krishna3 · January 27, 2020, 5:07am

Thank you very much @FatmaNagori . It has been solved in my laptop. I am checking same in server setup.

Krishna3 · January 28, 2020, 4:29am

I have updated and now shows as “Updated data successfully”. But no data has been updated in database. What may be the issue. Please help on this.

Latest updated code:

<html>

   <head>
      <title>Update a Record in MySQL Database</title>
   </head>

   <body>
      <?php
         if(isset($_GET['id'])) {
            $dbhost = 'localhost';
            $dbuser = 'root';
            $dbpass = 'password';

            $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
            date_default_timezone_set("Asia/Kolkata");
            $now_date = date('Y-m-d h:i:sa');
            if(! $conn ) {
               die('Could not connect: ' . mysqli_error());
            }

            $id = $_GET['id'];


            $sql = "UPDATE htl_booking_detail SET check_out='$now_date',id_status=3  WHERE id_order='$id'";
            mysqli_select_db($conn,'database') or die("cannot select DB");
            $retval = mysqli_query($conn,$sql);

            if(! $retval ) {
               die('Could not update data: ' . mysqli_error());
            }
            echo "Updated data successfully\n";

            mysqli_close($conn);
         }else {
            ?>

            <?php
         }
      ?>

   </body>
</html>

Krishna3 · January 28, 2020, 4:35am

Changed below code: it fixed my issue. Thank you very much @FatmaNagori .

<td style="text-align:center; vertical-align:middle;"><span class="btn btn-success btn-xs"><a style="color: white;" href=./update.php?id='.$row->id_order.' target="_blank">Check-out</a></span></td>

Krishna3 · January 31, 2020, 7:00am

The solution which you have give is working fine. Now my issue is if I click on button it goes to next page. How can make this is display the result in same page without opening new tab.

FatmaNagori · January 31, 2020, 2:09pm

remove attribute target="_blank" from <a> Element.

Krishna3 · February 1, 2020, 4:25am

I have removed this target="_blank" but still its going to next page and open as tab.

Note: I have checked in incognito window as well after clearing the cache. Still same.

FatmaNagori · February 1, 2020, 5:11am

For me it is going to next page in same window after removing target="_blank" .I am using opera browser and the code I am using is :

<a href=<?php echo "./update.php?id=". $row->id_order ; ?> >Check-out</a>

Krishna3 · February 1, 2020, 5:19am

Thank you. I have checked in the opera and still same for me. I am trying to convert same code in Ajax and will check

FatmaNagori · February 1, 2020, 5:40am

Are you checking the above code on localhost in your computer .

Krishna3 · February 1, 2020, 6:10am

I am checking this in local server as of now

FatmaNagori · February 1, 2020, 6:52am

Can you share all code you are using now.

Krishna3 · February 1, 2020, 7:43am

Please find all the codes which I am using as below:

index.php

<?php
$url=$_SERVER['REQUEST_URI'];
  header("Refresh: 300; URL=$url");
//index.php
include 'crud.php';
$object = new Crud();
$err = "";
?>
<html>
 <head>
  <title>Database records</title>
  <script src="lib/jquery.min.js"></script>
  <link rel="stylesheet" href="lib/bootstrap.min.css" />
  <script src="lib/bootstrap.min.js"></script>
  <style>
   body
   {
    margin:0;
    padding:0;
    background-color:#f1f1f1;
    font:  "Open Sans",Helvetica,Arial,sans-serif;
   }
   .box
   {
    width:1470px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:10px;
    font:   "Open Sans",Helvetica,Arial,sans-serif;
   }
   #search{

     margin-left: 219%;
   }
   #back-to-top {
       position: fixed;
       bottom: 40px;
       right: 40px;
       z-index: 9999;
       width: 28px;
       text-align: center;
       line-height: 30px;
       cursor: pointer;
       border: 0;
       border-radius: 2px;
       text-decoration: none;
       transition: opacity 0.2s ease-out;
       opacity: 0;
   }
   #back-to-top.show {
       opacity: 1;
   }
#search{
  background-image:url("search.png");

}
.table tr:nth-child(even){background-color: #D4D2D2;}
  </style>
 </head>
 <body>
   <div class="col-md-8">
     <br>
      <span style="text-align:left; margin:10px; margin-top:2px;"><button type="button" class="btn btn-success  "><a style="color: white;  text-decoration:none;" href=index.php >Home</a></button></span>
     <span style="text-align:left; margin:10px; margin-top:2px;"><button type="button" class="btn btn-success  "><a style="color: white;  text-decoration:none;" href=../alert3/>Alert</a></button></span>
     <span style="text-align:left; margin:10px; margin-top:2px;"><button type="button" class="btn btn-success  "><a style="color: white;  text-decoration:none;" href=./updateaddress/>Update Divotee</a></button></span>
     </div>
     <div class="col-md-4">
      <input type="text" name="search" id="search" placeholder="Search by name or employee id" class="form-control"  style="margin:10px;"/>
     </div>
 <br /><br/><br /><br/>

   <div id="user_table" class="table-responsive">

   </div>
    <a href="" id="back-to-top" title="Back to top" class="btn btn-success" style="float:right; text-decoration:none;">&uarr;</a>
  </div>
 </body>
</html>

<script type="text/javascript">
 $(document).ready(function(){
  load_data();
  $('#add').click(function(){
   $('#user_form')[0].reset();
  });
  function load_data(page)
  {
   var action = "Load";
   $.ajax({
    url:"action.php",
    method:"POST",
    data:{action:action, page:page},
    success:function(data)
    {
     $('#user_table').html(data);
    }
   });
  }
  $(document).on('click', '#pagination_link', function(){
   var page = $(this).attr("submission_id");
   load_data(page);
  });
  $('#search').keyup(function(){
   var query = $('#search').val();
   var action = "Search";
   if(query != '')
   {
    $.ajax({
     url:"action.php",
     method:"POST",
     data:{query:query, action:action},
     success:function(data)
     {
      $('#user_table').html(data);
     }
    });
   }
   else
   {
    load_data();
   }
  });

  if ($('#back-to-top').length) {
      var scrollTrigger = 100, // px
          backToTop = function () {
              var scrollTop = $(window).scrollTop();
              if (scrollTop > scrollTrigger) {
                  $('#back-to-top').addClass('show');
              } else {
                  $('#back-to-top').removeClass('show');
              }
          };
      backToTop();
      $(window).on('scroll', function () {
          backToTop();
      });
      $('#back-to-top').on('click', function (e) {
          e.preventDefault();
          $('html,body').animate({
              scrollTop: 0
          }, 700);
      });
  }



});
</script>
<script type="text/javascript">

function selectAll(){
    var blnChecked = document.getElementById("select_all").checked;
    var check_invoices = document.getElementsByClassName("emp_checkbox");
    var intLength = check_invoices.length;
    for(var i = 0; i < intLength; i++) {
        var check_invoice = check_invoices[i];
        check_invoice.checked = blnChecked;
    }
}


</script>

crud.php

<?php
 class Crud
{
 //crud class
 public $connect;
 private $host = 'localhost';
 private $username = 'root';
 private $password = 'password';
 private $database = 'database';

 function __construct()
 {
  $this->database_connect();
 }

 public function database_connect()
 {
  $this->connect = mysqli_connect($this->host, $this->username, $this->password, $this->database);
 }

 public function execute_query($query)
 {
  return mysqli_query($this->connect, $query);
 }

 public function get_data_in_table($query)
 {
  $output = '';
  $result = $this->execute_query($query);
  $output .= '
  <table class="table table-bordered table-striped">
   <tr style="font-family:arial; font-size:13px; font-weight:normal; color:#363A41; background-color:#8ea9db;">
  <!-- <th width="1%"  style="text-align:center; vertical-align:middle; "><input type="checkbox" id="select_all" name="select_all" onClick="selectAll()"></th>-->
 <th width="4%" style="text-align:center; vertical-align:middle; font-family: Arial, Helvetica, sans-serif;">ID</th>
   <th width="10%" style="text-align:center; vertical-align:middle;">Full Name</th>

    <!--<th width="14%" style="text-align:center; vertical-align:middle;">Address</th>-->
<th width="10%" style="text-align:center; vertical-align:middle;">Guest House Name</th>
<th width="10%" style="text-align:center; vertical-align:middle;">Room No</th>
<!--<th width="10%" style="text-align:center; vertical-align:middle;">Date &Time</th>-->
<th width="10%" style="text-align:center; vertical-align:middle;">Check In</th>
<th width="10%" style="text-align:center; vertical-align:middle;">Check Out</th>
<th width="10%" style="text-align:center; vertical-align:middle;">Date To</th>
  <!-- <th width="6%" style="text-align:center; vertical-align:middle;">Alert</th>-->
   <th width="6%" style="text-align:center; vertical-align:middle;">Payment</th>
<th width="6%" style="text-align:center; vertical-align:middle;">Discount</th>

<th width="6%" style="text-align:center; vertical-align:middle;">Checkout</th>
   <th width="6%" style="text-align:center; vertical-align:middle;">Print</th>


   </tr>
  ';
  if(mysqli_num_rows($result) > 0)
  {
   while($row = mysqli_fetch_object($result))
   {
     $time = new DateTime($row->date_add);
     $date = $time->format('d-m-Y');
     $time = $time->format("g"). '.' .$time->format("i"). ' ' .$time->format("A");//this line for to convert into  analog with am or pm
     $check_in = $row->check_in;
     if ($check_in == '0000-00-00 00:00:00') {
       $check_in = '';
     }else{
     $check_in = new DateTime($row->check_in);
       $check_in_date = $check_in->format('d-m-Y');
       $check_in_time = $check_in->format("g"). '.' .$check_in->format("i"). ' ' .$check_in->format("A");
       $check_in= "$check_in_date" . " " . "$check_in_time";
     }

     $check_out= $row->check_out;
    if($check_out == '0000-00-00 00:00:00'){
       $check_out = '';
     }else {
       $check_out = new DateTime($row->check_out);
       $check_out_date = $check_out->format('d-m-Y');
       $check_out_time = $check_out->format("g"). '.' .$check_out->format("i"). ' ' .$check_out->format("A");
       $check_out = "$check_out_date" . " ". "$check_out_time";
     }
     $date_too =  $row->date_too;
     if($date_too == '0000-00-00 00:00:00'){
        $date_too = '';}
        else {
          $date_too =  strtotime($row->date_too);
          $date_too = date('d-m-Y', $date_too);
        }

       /////


     /////
    $output .= '
    <tr >
<tr style="font-family:arial; font-size:13px; font-weight:normal; color:#363A41;">
    <!-- <td style=" text-align:center; vertical-align:middle;"><input type="checkbox" class="emp_checkbox" ></td>-->
     <td style="text-align:center; vertical-align:middle;">'.$row->id_order.'</td>
  <td style="text-align:center; vertical-align:middle; ">'.$row->customer.'</td>
      <!--  <td style="text-align:center; vertical-align:middle;">'.$row->address1.'&nbsp'.$row->address2.'&nbsp'.$row->postcode.'&nbsp'.$row->city.'</td>-->
   <td style=" text-align:center; vertical-align:middle; ">'.$row->hotel_name.'</td>
   <td style="text-align:center; vertical-align:middle; ">'.$row->Room.'</td>
<!--<td style="text-align:center; vertical-align:middle;">'.$date .'&nbsp'.$time .'</td>-->
<td style="text-align:center; vertical-align:middle;">'.$check_in.'</td>
<td style="text-align:center; vertical-align:middle;">'.$check_out.'</td>
<td style="text-align:center; vertical-align:middle;">'.$date_too.'</td>
<!--<td style="text-align:center; vertical-align:middle; "></td>-->
<td style="text-align:center; vertical-align:middle;">'.number_format($row->total_products_wt, 2).'</td>
<td style="text-align:center; vertical-align:middle;">'.number_format($row->total_discount_tax_incl, 2).'</td>

<td style="text-align:center; vertical-align:middle;"><span class="btn btn-success btn-xs"><a style="color: white;" href=./update.php?id='.$row->id_order.' target="_blank">Check-out</a></span></td>
      <td style="text-align:center; vertical-align:middle;"><span class="btn btn-success btn-xs"><a style="color: white;" href=./slip.php?id='.$row->id_order.' target="_blank">Print</a></span></td>

    </tr>
    ';
   }
  }
  else
  {
   $output .= '
    <tr>
     <td colspan="11" align="center">No Data Found</td>
    </tr>
   ';
  }
  $output .= '</table>';
  return $output;
 }

function upload_file($file)
 {
  if(isset($file))
  {
    $extension = explode('.', $file["name"]);
    $new_name = $extension[0] . '.' . $extension[1];
    $destination = './upload/' . $new_name;
    move_uploaded_file($file['tmp_name'], $destination);
    return $new_name;
  }
}

function make_pagination_link($query, $record_per_page)
 {
  $output = '';

  $result = $this->execute_query($query);
  $total_records = mysqli_num_rows($result);
  $total_pages = ceil($total_records/$record_per_page);

  for($i=1; $i<=$total_pages; $i++){
   $output .= '<span class="curPage" class="pagination_link" id="pagination_link" style="cursor:pointer; padding:6px; border:1px solid #ccc;" submission_id="'.$i.'">'.$i.'</span>';
 }
  return $output;
 }
}
?>

update.php (Action Script)

<html>

   <head>
      <title>Update a Record in MySQL Database</title>
   </head>

   <body>
      <?php
         if(isset($_GET['id'])) {
            $dbhost = 'localhost';
            $dbuser = 'root';
            $dbpass = 'password';

            $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
            date_default_timezone_set("Asia/Kolkata");
            $now_date = date('Y-m-d h:i:sa');
            if(! $conn ) {
               die('Could not connect: ' . mysqli_error());
            }

            $id = $_GET['id'];


            $sql = "UPDATE htl_booking_detail SET check_out='$now_date',id_status=3  WHERE id_order='$id'";
            mysqli_select_db($conn,'ghouse_for') or die("cannot select DB");
            $retval = mysqli_query($conn,$sql);

            if(! $retval ) {
               die('Could not update data: ' . mysqli_error());
            }
            echo "Updated data successfully\n";

            mysqli_close($conn);
         }else {
            ?>

            <?php
         }
      ?>

   </body>
</html>

FatmaNagori · February 1, 2020, 10:42am

After trying your code when I remove target="_blank" from <a> element now I am able to checkout in same window.

image before checkout

Image after clicking on checkout button

But I found another problem in your code which is you didn’t pass argument to mysqli_error() (update.php ) .So pass $conn as argument i.e. mysqli_error($conn) .