[SOLVED] PHP PDO update a joined table

Issue

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");
        }

Solution

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.

Answered By – waterloomatt

Answer Checked By – Gilberto Lyons (BugsFixing Admin)

Leave a Reply

Your email address will not be published.