How would I UPDATE these table entries with SQL and PHP?

Question

I am working on an Asset Database problem.

I enter assets into a database. Every object is an asset and has variables within the asset table. An object is also a type of asset. In this example the type is server.

Here is the Query to retrieve all necessary data:

SELECT asset.id
,asset.company
,asset.location
,asset.purchaseDate
,asset.purchaseOrder
,asset.value
,asset.type
,asset.notes
,server.manufacturer
,server.model
,server.serialNumber
,server.esc
,server.warranty
,server.user
,server.prevUser
,server.cpu
,server.memory
,server.hardDrive
FROM asset
LEFT JOIN server
    ON server.id = asset.id
WHERE asset.id = '$id'

I then assign all results into single php variables.

How would I write a query/script to update an asset?

Answer 1

Since you have (presumably) a one-to-many relationship between assets and servers, you would need to update them separately:

UPDATE assets SET
    company = '$company',
    location = '$location', ...
WHERE id = $asset_id;

UPDATE servers SET
    manufacturer = '$manufacturer',
    model = '$model', ....
WHERE $id = $server_id

You will have to do the second query n times, where n is number of records of server associated with the particular asset.

Also, I do hope you're careful with those variables - we don't want Bobby Tables here.

UPDATE:

OK, so you have one-to-maybe-one into several tables. In that case why use LEFT JOIN at all? There shouldn't be a possibility a server is not already an asset, right? Anyway, answer, off the top of my head:

UPDATE assets, servers
SET
    assets.company = ...,
    servers.manufacturer = ...
WHERE assets.id = $id
AND servers.id = $id

Answer 2

Since you have a 1-1 connection between your asset table and your server table. It is essentially the same table. If you have control over your database layout, I would rethink it.

If not you will have to do two update calls, one to each table like @Amadan showed you