1

I'm trying to get the ID of the last record inserted in my phpMyAdmin table, but I only get 0's so far.

I have tried almost everything (for example the solution on this answer: a link or what this page recommend : a link ) but I can't get it working.

My table has a column id that is the primary key and is marked as AUTO_INCREMENT.

I use a function query that make the connection to the database and then execute the queries:

function query($sql) {
    $conn = mysqli_connect ( DB_HOST, DB_USER, DB_PASSWORD, DB_NAME );
    // Check connection
    if (mysqli_connect_errno ()) {
        echo 'connection failed: ' . mysqli_connect_error ();
    }
    // Check if the server is alive
    if (mysqli_ping ( $conn )) {
        // echo 'Connection is ok';
    } else {
        echo 'Error: ' . mysqli_error ( $dbc );
    }

    $sql = mysqli_query ( $conn, $sql );
    $last_id = mysqli_insert_id( $conn );
    $num_rows = mysqli_num_rows ( $sql );
    $result = mysqli_fetch_assoc ( $sql );

    return array (
            "num_rows" => $num_rows,
            "result" => $result,
            "sql" => $sql,
            "last_id" => $last_id
    );
    mysqli_close($conn);
}

Then I have another function from where I called the first one to execute the query:

function reg_shp_add($address_type, $company_name, $country, $state, $city, $zip, $street_name, $street_number, $tel){
    if($address_type=="residential"){
        $my_address_type = "r";
    }else{
        $my_address_type = "c";
    }
        $this->query ( "INSERT INTO `Addresses` (`company_name`, `street_address`, `address_two`, `zip_code`, `city_name`, `state_id`, `country_id`, `phone_number`, `res_comm_add`, `date_entered`) VALUES ('$company_name', '$street_name', '$street_number', '$zip', '$city', '$state', '$country', '$tel', '$my_address_type', CURRENT_TIMESTAMP)" );
        $address_id = $sql ['user_id'];
        $customer_id = $_COOKIE['userId'];
        $this->query ( "INSERT INTO `Cust_address_type` (`cust_id`, ` mail_address_id`, `address_type`) VALUES ('$customer_id', '$address_id', 'Shipping')" );
        return 'Address was saved.';

}

I first tried to retrieve the last id from my reg_shp_add function, but I guess it must be done from the first one.

Can anybody help me please?

Community
  • 1
  • 1
mgrdiez
  • 67
  • 1
  • 10
  • Not sure if it's related, but you're running `$sql = mysqli_query ( $conn, $sql );` which is reassigning $sql to be a results object, and you are then trying to pass back in the array with `"sql" => $sql` - did you not want this to be the actual sql rather than the (empty) results object? – SteJ Jul 29 '15 at 21:43
  • 2
    i dont see you actually retrieving the returned array from `query`. `$x=$this->query(...); print_r($x);` –  Jul 29 '15 at 21:44
  • @Dagon: well spotted! Yes, you are calling $this->query but are not doing anything with the returned value – SteJ Jul 29 '15 at 21:45
  • 1
    side note- connecting on every query may not be the best approach-one connection per page\script is better –  Jul 29 '15 at 21:45
  • @SteJ I have a lot of functions where I get the results from the object $sql, they are working perfectly... The only problem that I have is that I can't get the last inserted ID... I have tried a lot of things in the first function, but I always get a 0... – mgrdiez Jul 29 '15 at 21:55
  • where are you getting the `0`? The code in `reg_shp_add` does not pay attention to the return value from `query`...? – SteJ Jul 29 '15 at 21:58
  • @Dagon thank you very much! I guess it was fatigue... Yes, you are right I wasn't retrieving the returned array... – mgrdiez Jul 29 '15 at 22:51
  • @SteJ, the fact is that I use the number retrieved to fill into another table. As you and Dagon spotted, I wasn't retrieving the information correctly. Thank you very much also! – mgrdiez Jul 29 '15 at 22:54

1 Answers1

0

I test your code and the function mysqli_insert_id work correctly with me ( ["last_id"]=> int(8) ). But the problem is in your second function, on the data retrieval ;

$this->query ( "INSERT INTO `Addresses` (`company_name`, `street_address`, `address_two`, `zip_code`, `city_name`, `state_id`, `country_id`, `phone_number`, `res_comm_add`, `date_entered`) VALUES ('$company_name', '$street_name', '$street_number', '$zip', '$city', '$state', '$country', '$tel', '$my_address_type', CURRENT_TIMESTAMP)" );
$address_id = $sql ['user_id'];

Here the $sql variable is undefined, you miss to store the return value into $sql, like that :

$sql = $this->query ( "INSERT INTO `Addresses` (`company_name`, `street_address`, `address_two`, `zip_code`, `city_name`, `state_id`, `country_id`, `phone_number`, `res_comm_add`, `date_entered`) VALUES ('$company_name', '$street_name', '$street_number', '$zip', '$city', '$state', '$country', '$tel', '$my_address_type', CURRENT_TIMESTAMP)" );
$address_id = $sql ['user_id'];
Jitrixis
  • 74
  • 8