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?

Improve this question
3
  • 1
    See this discussion that your issue will be solved: stackoverflow.com/questions/6485778/… Commented Jul 10, 2020 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? Commented Jul 10, 2020 at 12:15
  • another solution might be to generate a UUID in your php script Commented Jul 10, 2020 at 12:50

1 Answer 1

Reset to default
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!

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

improvement on this is using $row = pg_fetch_assoc($query); so that then values can be extracted by doing $client_id = $row['client_id'];

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.