return mysql last insert id as a link variable

Question

I have the following php pages. I need to return the last inserted id of the INSERT statement to my php page as a link variable. My code is as follows:

room_reservation.php

<?php
    if(isset($_REQUEST["id"])){
        echo $_REQUEST["id"];
    }
?>
<html>
<body>
    <form method="post" action="reception-c.php">
        <input type="text" name="trans_id"/>
        <input type="text" name="start_date"/>
        <input type="text" name="cust_id"/>
        <input type="submit" name="submit" value="submit"/>
    </form>
</body>
</html>

reception-c.php

<?php
    require_once '../model/reception-m.php';

    $trans_id=$_POST["trans_id"];
    $start_date=$_POST["apply_date"];
    $cust_id=$_POST["customerid"];
    $obj=new Reception();
    if($trans_id===""){
        $result=$obj->generateNewTransaction($start_date,$cust_id);
        $trans_id=$result;
    }
    header("Location: ../view/room_reservation.php?id=$trans_id");

?>

reception-m.php

require_once '../../common/model/dbconnection-m.php';

<?php
class Reception{
public function generateNewTransaction($start_date,$cust_id){
    global $db;
    $query="INSERT INTO transaction_log VALUES ('','$start_date',NULL,'pending','$cust_id')";
    $result=$db->add_query($query);
    return $db->insert_id; //Not Working
}
}
?>

dbconnection-m.php

<?php

class Database{
private $connection;

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

public function open_connection(){
    $this->connection=  mysqli_connect('localhost', 'root', '', 'mis');
        if(!$this->connection){
            die("Error, failed to connect to database ".  mysqli_error($this->connection));
        }         
}

public function add_query($query){
    $result=  mysqli_query($this->connection, $query);
    if(!$result){
        die("Error while adding query ".  mysqli_error($this->connection));
    }
    else{
        return $result;
    }
}

public function fetch_array($value){
    return $value->fetch_assoc();
}
}

$db=new Database();

My code works up until the point where in reception-m.php the query gets submitted. Confirmed through phpmyadmin browse of table. However return $db->insert_id; in reception-m .php does not work and I get an empty value returned (not zero).

The table has transaction_id set to AUTO INCREMENT so I don't think that's the issue. I am concerned with my dbconnection-m.php as this is not the one used in explanations of php documentation.

Please help me figure out this issue...

EDIT

1   transaction_id Primary  int(11) AUTO_INCREMENT  
2   start_date  date
3   end_date    date
4   status  enum('pending', 'completed')    
5   customer_id int(11)

Your Class does not have an `insert_id` property!?!?!??!?! **You should be getting an error in the PHP Error log** — RiggsFolly, Sep 18 '16 at 16:47
You are writing classes of your own, but you are using the MYSQLI Procedural calls! **WHY?** — RiggsFolly, Sep 18 '16 at 16:49
You are calling for `insert_id` method in class `Database` but it doesn't exist. — , Sep 18 '16 at 16:52
i followed this post http://stackoverflow.com/questions/19738283/mysqli-last-insert-id... I assumed insert_id can be called on the connection variable. CAn someone point out my mistake?? — user3889963, Sep 18 '16 at 16:54
PS: Its dangerous writing a class wrapper for an existing perfectly usable class, unless you know what you are doing. Its normally a huge overkill and completely unnecessary — RiggsFolly, Sep 18 '16 at 16:55
I see...is it then safe to assume that replacing this class wrapper with the connection establishing code found in http://php.net/manual/en/mysqli.insert-id.php will make $db->insert idl work?? — user3889963, Sep 18 '16 at 16:57
Your mistake: `return $db->insert_id;` should be `return $db->connection->insert_id;` But you will also need to change it to `protected` from `private` — RiggsFolly, Sep 18 '16 at 16:57
You are also using `global` in a class. This is also **bad form** as this totally destroy's any encapsulation — RiggsFolly, Sep 18 '16 at 16:58

Naresh Kumar P · Accepted Answer · 2016-09-18T17:01:48.383

1

Change the following syntax in the files that i will point out since the last inserted id can be get with the help of connection variable and after the query is executed.

reception-m.php

Replace:

return $db->insert_id; //Not Working

with

return $db->$connection->insert_id;

and in this file you have to do the following changes.

dbconnection-m.php

Replace:

class Database{
private $connection;

with

class Database{
protected $connection;

edited Sep 18 '16 at 17:01

answered Sep 18 '16 at 16:58

Naresh Kumar P

4,127
2
16
33

Look closer at [my comment](http://stackoverflow.com/questions/39560169/return-mysql-last-insert-id-as-a-link-variable#comment66431102_39560169) – RiggsFolly Sep 18 '16 at 16:59
answer to the question...but thanks @RiggsFolly, i have some serious issues to address – user3889963 Sep 18 '16 at 17:01
@RiggsFolly. I have made the changes as per the suggestion that you have given bro :) – Naresh Kumar P Sep 18 '16 at 17:02
making both changes gives me the following result: Fatal error: Cannot access protected property Database::$connection in C:\xampp\htdocs\HotelManagementSystem\reception\model\reception-m.php on line 58.......line 58 contains return $db->connection->insert_id; – user3889963 Sep 18 '16 at 17:12
Revert back it as private and check we shall try to crack on – Naresh Kumar P Sep 18 '16 at 17:19
with private I get Fatal error: Cannot access private property Database::$connection in C:\xampp\htdocs\HotelManagementSystem\reception\model\reception-m.php on line 58 – user3889963 Sep 18 '16 at 17:21
or else you try to get the last inserted ID in the add_query itself. If the query is executed you get the last inserted ID or else you return the error message as you have done now. – Naresh Kumar P Sep 18 '16 at 17:24
best to remove the wrapper class and implement a cleaner version...thanks for the help @NareshKumar.P – user3889963 Sep 18 '16 at 17:26

return mysql last insert id as a link variable

1 Answers1