While using update command, I wish to alter the type_name for any specific entry within the database, but it's altering the type_name for the records with similar type_id.

I want it to simply change that each entry's type_name, and never the type_name that's connected with the records with similar type_id.

I've 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 shape I am 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" />

Here's my database structure:

TABLE photos

  • photo_id PRIMARY KEY

  • photo_title

  • type_id FOREIGN KEY

TABLE types

  • type_id PRIMARY KEY

  • type_title

What's happening is your join is creating the incorrect group of data. You are joining the photos and kinds on type_id.

Ok now what you appear to become explaining would be that the types column could have multiple rows with similar type___id. Exactly what does this suggest? This means that the join will produce multiple pairs of (photos,types) for every photo (particularly, for every photo, the join will produce n rows, where n is the amount of rows in types getting exactly the same type_id because the photo).

For how you can fix this, you need to have a look at the database design. You appear to anticipate a distinctive row in types for every photo. How's this relationship expressed? Which will allow you to get an effective ON clause for the join.


After searching while dining structure, it appears your database is indicating things slightly in a different way. Because it stands you could have multiple photos with similar type (i.e. their typeid within the photos table is identical). As a result it is a little meaningless to talk of altering the typename of a single such photo. You are basically upgrading the typename for the type, that unexpectedly happens to be the kind of the photo whose title you had been also upgrading.

Now exactly what are you attempting to achieve, here?

  • If you're attempting to re classify a specific photo, then you definitely rather wish to either produce a new entry within the types table and point your photo to that particular new record, or locate an existing photo having a matching title and point the photo at this record. (I presume you have such code inside your photo insertion logic. This ought to be similar)
  • If you're attempting to update the kind description for any photo and all sorts of other photos with this type, then that which you have works all right.

I am surprised that MySQL enables this, however it appears like you are upgrading the title within the type table. You are most likely searching to update the kind_id of merely one row within the photos table.

You could do this that they 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 else:

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

Prior to running this question, you can make certain the kindtitle is available:

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