How to update rows with same ID but different values in PHP MySQL
979
My table namedgenresis something like this:
tmdb_id genres_name
5 Action
5 Adventure
5 Science Fiction
6 Crime
6 Comedy
Now, I want to update this table, but the problem is, my code updates table like this:
tmdb_id genres_name
5 Action
5 Action
5 Action
6 Crime
6 Crime
It updates the same value in all rows with the same id. I want different values.
Note: I cannot use Primary Key (auto increment) for some reasons.
Here is my full code:
This is how I am receiving data from a page:
$response = file_get_contents("https://api.themoviedb.org/3/movie/550?api_key=xxxxxx");
if ($response != FALSE) {
$response = json_decode($response, true);
}
And here is an example of what is inside the page:
{
"genres": [
{
"name": "Action"
},
{
"name": "Adventure"
},
{
"name": "Science Fiction"
}
]
}
Here is how I Insert the data
$stmt = $conn->prepare("UPDATE genres SET genres_name = :genres_name WHERE tmdb_id = :tmdb_id");
$stmt->bindParam(':tmdb_id', $tmdb_id,PDO::PARAM_INT);
$stmt->bindParam(':genres_name', $genres_name,PDO::PARAM_INT);
if (isset($response["genres"]) && is_array($response["genres"]))
{
foreach ($response["genres"] as $genreObject)
{
$genres_name = $genreObject["name"];
$stmt->execute();
}
}
Original Result:
This is the data it update insideGenres table
tmdb_id genres_name
5 Action
5 Action
5 Action
tmdb_id genres_name
5 Action
5 Adventure
5 Science Fiction
Answer
Solution:
To update any particular row, you need to have key column(s). As you already mentioned that you can't use primary key (auto increment). But, you can have composite key ? where combination of column acts as unique row.
So, you could add
genres_namecolumn in where clause.