0

Question:

Is there a way to get the value of client.client_id into a PHP variable to then use it as the value of phone.client_id?

Context:

I have a PostgreSQL database to which I communicate using PHP.

I have two separate tables in the database:

client (client_id[PK], client_name)

and

phone (phone_id[PK], client_id[FK], phone_number)

Both client_id and phone_id are create as bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 .

Since the ids are GENERATED ALWAYS AS IDENTITY I don't need to to specify them when inserting data into the tables in PHP:

$query = "INSERT INTO public.client (client_name) VALUES('" . $clientName . "')";

Now to finally get to my question:

Whenever I insert a client I also want to insert a phone, the phone table needs to hold a reference to the client to which it belongs, and it does so by having a client_id foreign key.

If I was just creating the id in PHP I could use that value in both INSERT statements and end up with the correct behaviour.

Is there a way to get the value of client.client_id into a PHP variable to then use it as the value of phone.client_id?

Dispensable Joe
  • 74
  • 1
  • 11
  • 1
    See this discussion that your issue will be solved: https://stackoverflow.com/questions/6485778/php-postgres-get-last-insert-id – Erme Schultz Jul 10 '20 at 12:09
  • Thanks both @ErmeSchultz and @MagnusEriksson, that question seems to be exactly what I want to achieve! However, I didn't quite understand where to put `RETURNING client_id` and how to access the value after. Will my `client_id` be stored in the `$query` variable? – Dispensable Joe Jul 10 '20 at 12:15
  • another solution might be to generate a UUID in your php script – imposterSyndrome Jul 10 '20 at 12:50

1 Answers1

0

Thanks to @ErmeSchultz and @MagnusEriksson I was able to find a solution in this comment.

My modified code is now:

// crafting the query so that the variable $query contains the value of the automatically generated client_id
$query = "INSERT INTO public.client (client_name) VALUES('" . $clientName . "') RETURNING client_id";

// getting an array of the elments contained inside $query
$row = pg_fetch_row($query);

// putting the first element inside $row into $client_id
$client_id = $row[0];

The variable $client_id now contains the value of client.client_id!

Dispensable Joe
  • 74
  • 1
  • 11
  • improvement on this is using `$row = pg_fetch_assoc($query);` so that then values can be extracted by doing `$client_id = $row['client_id'];` – Dispensable Joe Jul 15 '20 at 14:11