How do i update values to a table that has a foreign key . A commodity table with a foreign key of vendor id from vendor table

Question

insert command for tables with foreign keys

Answer 1

Hi Powell Ragot ,Well, to update the record you only need the specific commodity id, because without that, you risk updating all records.

I am not sure what you mean here by "get the commodity table to fetch vendor name", but if you want to query/fetch the commodities with their respective vendor you can join the tables because they are connected.

SELECT c.id, c.name, v.name
FROM commodity c
INNER JOIN vendor v
ON c.vendor_id = v.id

Assuming foreign key <vendor_id> in commodity table references vendor table's primary key <id>.

P.S. Query untested, but I guess it works ; )

Hth, cmiiw

Answer 2

I suppose there is no special treatment for inserting & updating a referencing table, you insert/update new records as usual, except that for the foreign key, when an input was given an invalid reference the query may fail.

Hth, cmiiw

Answer 3

Powell Ragot Yes you can if you have such field in commodity table, but why save vendor name in commodity while you have vendors table?

Answer 4

Powell Ragot the query example I posted previously showed such a way to fetch fields from different connected tables using JOIN. It was supposed to fetch commodity id, name, and vendor name. Have you tried to run that query?

Answer 5

Ipang okay i need some help here am new to php. i want to update a commodity table with a foreign key of vendor id who uploaded it. How do i get the commodity table to fetch the vendor name using vendor id as a foreign key ?? and display uploaded by maybe ipang in this case

Answer 6

Ipang To simplify this can i insert vendor name into commodity table by fetching it from vendor table with vendor id as the foreign key

Answer 7

Because in my project there will be a section i want to display to the user a commodity including the name of the vendor who uploaded it.