Why is my Update command updating all of the fields that have the same ID?

Question

Using the update command, I want to change the type_name for a specific entry in the database, but it is changing the type_name for all the entries with the same type_id.

I need it to only change that individual entry's type_name, and not the type_name that is associated with all the entries with the same type_id.

I have an update query:

$sql = 'UPDATE photos
       LEFT JOIN types 
       ON photos.type_id = types.type_id
       SET photos.photo_title = $_POST['photo_title'], 
           types.type_name = $_POST['type_name']
       WHERE photos.photo_id = 3';

Here's the form I'm using:

<form name="form1" method="post" action="">
    <input name="photo_title" type="text" value=""/>
    <textarea name="type_name"></textarea>
    <input type="submit" name="update" value="Update entry" />
</form>

Here's my database structure:

TABLE photos

• photo_id PRIMARY KEY

• photo_title

• type_id FOREIGN KEY

TABLE types

• type_id PRIMARY KEY

• type_name

Answer 1

$sql = 'UPDATE photos
       LEFT JOIN types 
       ON photos.type_id = types.type_id
       SET photos.photo_title = $_POST['photo_title'], types.type_name = $_POST['type_name']
       WHERE photos.photo_id = 3 LIMIT 1';

On a side note, you shoule be doing

$photo_title = escape_function( $_POST['photo_title'] )
$type_name = escape_function( $_POST['type_name'] )

and wrapping the varialble names in ' ' in your query string.

Answer 2

What is happening is that your join is producing the wrong set of data. You're joining the photos and types on type_id.

Now what you seem to be describing is that the types column may contain multiple rows with the same type___id. What does this mean? It means that your join will produce multiple pairs of (photos,types) for each photo (specifically, for each photo, the join will produce n rows, where n is the number of rows in types having the same type_id as the photo).

As for how to fix this, you should take a look at your database design. You seem to expect a unique row in types for each photo. How is this relationship expressed? That will enable you to get a proper ON clause for your join.

UPDATE

After looking at the table structure, it seems your database is expressing things slightly differently. As it stands you can have multiple photos with the same type (i.e. their typeid in the photos table is the same). Thus it is a bit meaningless to speak of changing the typename of just one such photo. You're merely updating the typename for a particular type, that happens to be the type of the photo whose name you were also updating.

Now what exactly are you trying to achieve, here?

• If you are trying to re categorize a particular photo, then you instead want to either create a new entry in the types table and point your photo to that new record, or find an existing photo with a matching name and point the photo at that record. (I presume you already have such code in your photo insertion logic. This should be similar)
• If you are trying to update the type description for a photo and all other photos with that type, then what you have will work just fine.

Answer 3

I need it to only change that individual entry's type_name, and not the type_name that is associated with all the entries with the same type_id.

This is your fundamental problem. There's only ever going to be one record in the types database per typeid, so when you alter it, it effectively alters it for every photo that references that typeid.

If you need to store a different type_name for every photo, just create a column in the photos table and store it there.

The other way to do this is to create a new record in the types table each time a type_name is edited - possibly doing some checking to see whether any other photos are also using that typeid (if not you can safely update the existing record). But you have to implement code that does this for yourself.

Answer 4

I'm surprised that MySQL allows this, but it looks like you're updating the name in the type table. You're probably looking to update the type_id of a single row in the photos table.

You could do that like this:

UPDATE photos
SET photos.photo_title = $_POST['photo_title'], 
    photos.type_id = (
        select type_id 
        from types 
        where type_name = $_POST['type_name']
    )
WHERE photos.photo_id = 3

Or alternatively:

UPDATE photos
LEFT JOIN types ON types.type_id = $_POST['type_name']
SET photos.photo_title = $_POST['photo_title'], 
    photos.type_id = types.type_id
WHERE photos.photo_id = 3

Before you run this query, you could make sure the type_name exists:

REPLACE INTO types (type_name) VALUES ($_POST['type_name'])