PHP PDO update a joined table

Question

I am creating a site that holds music albums. When the user clicks on the album, it will take them to the page showing all the tracks and information about that album. However, on the main album page, it shows information that was gathered in a query by joining a couple tables together. I do not understand how I can update a table's information using the UPDATE statement when I'm dealing with multiple tables. I figured I could run separate queries and execute them one after another. At the very bottom of the code (under // execute the album update query), I am able to execute the $album_statement but then I get an error:

    Fatal error: Uncaught PDOException: SQLSTATE[HY093]: 
    Invalid parameter number: 
    number of bound variables does not match number of tokens

I don't understand this error. I'm very new to PHP and PDO. I thought I needed to bind the parameters but when I tried what Binding multiple values in pdo said to do, I still got the same error in the same spot.

    // execute the album update query
    $album_statement->execute(array(':album_name' => $album_name, 
                                    ':record_label' => $record_label, 
                                    ':release_date' => $release_date, 
                                    ':album_id' => $album_id));
    $genre_statement->execute(array(':genre' => $genre));
    $artist_statement->execute(array(':stage_name' => $stage_name));

This is the code that I have.

    // grabbing the id of the album that was selected to be edited
        $album_id = $_GET['album_id'];
        $sql = 'SELECT albums.album_id, albums.genre_id, albums.album_name, 
                       artists.stage_name, genres.genre, albums.record_label, 
                       albums.release_date 
                FROM Albums 
                  JOIN Artists ON Albums.artist_id = Artists.artist_id
                  JOIN Genres ON Albums.genre_id = Genres.genre_id
                    WHERE album_id = :album_id';
        
        // prepare the query
        $statement = $connection->prepare($sql);
        $statement->execute([':album_id' => $album_id]);
        $album = $statement->fetch(PDO::FETCH_OBJ);
        
        // if all values are set, assign them to variables
        if (isset($_POST['album_name']) && 
            isset($_POST['stage_name']) && 
            isset($_POST['genre']) && 
            isset($_POST['record_label']) && 
            isset($_POST['release_date'])) {
          $album_name = $_POST['album_name'];
          $stage_name = $_POST['stage_name'];
          $genre = $_POST['genre'];
          $record_label = $_POST['record_label'];
          $release_date = $_POST['release_date'];
        
          $album_update = 'UPDATE albums SET album_name=:album_name, record_label=:record_label, release_date=:release_date WHERE album_id=:album_id'; 
          $album_statement = $connection->prepare($album_update);
          $genre_update = 'UPDATE genres SET genre=:genre WHERE albums.genre_id=:album_id';
          $genre_statement = $connection->prepare($genre_update);
          $artist_update = 'UPDATE artists SET stage_name=:stage_name WHERE artist_id=:album_id';
          $artist_statement = $connection->prepare($artist_update);
        
          // execute the album update query
          $album_statement->execute([':album_name' => $album_name, ':record_label' => $record_label, ':release_date' => $release_date, ':album_id' => $album_id]);
          $genre_statement->execute([':genre' => $genre]);
          $artist_statement->execute([':stage_name' => $stage_name]);
        
          header("Location: index.php");
        }

Start debugging by commenting out all _executes_, then uncomment one-by-one to see which one is failing. Also, `UPDATE genres SET genre=:genre WHERE albums.genre_id=:album_id` expects two params, you supplied one. Same with _artist_. — waterloomatt, Mar 13 '22 at 03:19
@waterloomatt, would you mind explaining how you know it expects 2 params and how I can change it only expect one, please? That is the one I am having problems with and I imagine `$artist_update = 'UPDATE artists SET stage_name=:stage_name WHERE artist_id=:album_id';` will give me the same problem. — Queen of the North, Mar 13 '22 at 03:21
@QueenoftheNorth The two parameters are indicated by the placeholder variables `:genre` and `:album_id` in the SQL statement. — kmoser, Mar 13 '22 at 03:23
@waterloomatt FYI, there is no need to "Start debugging by commenting out all executes". The exact line number is already shown in the error message. — Your Common Sense, Mar 13 '22 at 09:10

score 0 · Accepted Answer · answered Mar 13 '22 at 03:26

There are 2 placeholders here, :genre and :album_id

$genre_update = 'UPDATE genres SET genre=:genre WHERE albums.genre_id=:album_id';

But you only supplied one to your execute statement.

$genre_statement->execute([':genre' => $genre]);

You simply need to pass all the parameters.

$genre_statement->execute([':genre' => $genre, ':album_id' => $album_id]);

Same goes for your artist statements.

PHP PDO update a joined table

1 Answers1