user3889963 user3889963 - 2 months ago 9
MySQL Question

return mysql last insert id as a link variable

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)

Answer

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;