Updating database record with PHP/PDO and mySQL

Question

I have a database "Telefoon" with a table "Telefoonnummers". The table has the columns "naam", "number" and "mobiel"

I am trying to make a .php page where I can update a record. I am pretty sure this is right, but somehow nothing changes.

Here you fill in the updated records, and when you click the button it goes to the next page which updates it.

<!DOCTYPE HMTL>
<html>

<head>
    <title>Wijzigen telefoonnummer</title>
</head>

<body>

    <?php

        $record_name = $_GET["naam"];

        $user = "root";
        $pass = "root";

        $dbh = new PDO(
            'mysql:host=localhost; port=8473; dbname=telefoon',
            $user,
            $pass
        );

        $sth = $dbh -> prepare("

            SELECT *
            FROM Telefoonnummers
            WHERE naam = :record_name

        ");

        $sth -> bindValue( ":record_name", $record_name, PDO::PARAM_STR );

        $sth -> execute();

        $printRecord = $sth -> fetchAll(PDO::FETCH_ASSOC);

        /*
        //dit record als array weergeven
        print("<pre>");
        print_r($printRecord);
        print("</pre>");
        */

        //gegevens in variabelen zetten
        $printRecordRecord = $printRecord[0];
        $huidigeNaam = $printRecordRecord["naam"];
        $huidigeNummer = $printRecordRecord["telefoonnummer"];
        $huidigeMobiel = $printRecordRecord["mobiel"];

        //niet meer nodig door bovenstaande
        /*
        foreach( $printRecord AS $printRecordIndex => $printRecordRecord ) {

            $huidigeNaam = $printRecordRecord["naam"];
            $huidigeNummer = $printRecordRecord["telefoonnummer"];
            $huidigeMobiel = $printRecordRecord["mobiel"];

        }
        */

        print("

            <form action='wijzig.php' method='POST'>
                <table>

                    <tr>
                        <td bgcolor='green'><b>Naam</b></td>
                        <td bgcolor='green'><b>Telefoonnummer</b></td>
                        <td bgcolor='green'><b>Mobiel</b></td>
                        <td bgcolor='green'></td>
                    </tr>

                    <tr>
                        <td><input type='text' name='naam' value='$huidigeNaam' disabled='TRUE' /></td>
                        <td><input type='text' name='nummer' value='$huidigeNummer' /></td>
                        <td><input type='text' name='mobiel' value='$huidigeMobiel' /></td>
                        <td><input type='submit' value='Wijzig' /></td>
                    </tr>

                </table>
            </form>

        ");

    ?>

</body>

This is the next page which actually changes (at least that's what I want) the record:

<!DOCTYPE HTML>
<html>

<head>
    <title>Gewijzigd</title>
</head>

<body>

    <?php

        //geupdate gegevens ophalen
        $newNaam = $_POST["naam"];
        $newNumber = $_POST["nummer"];
        $newMobile = $_POST["mobiel"];

        //gegevens updaten als ALLES is ingevuld
        if ( ($newNaam != "") && ($newNumber != "") && ($newMobile != "") ) {

            $user = "root";
            $pass = "root";

            $dhb = new PDO(
                'mysql:host=localhost; port=8473; dbname=telefoon',
                $user,
                $pass
            );

            $sth = $dbh -> prepare("

                UPDATE Telefoonnummers
                SET naam = :naam,
                telefoonnummer = :nummer,
                mobiel = :mobiel
                WHERE naam = :naam

            ");

            $sth -> bindValue( ":naam", $newNaam, PDO::PARAM_STR );
            $sth -> bindValue( ":telefoonnummer", $newNumber, PDO::PARAM_STR );
            $sth -> bindValue( ":mobiel", $newMobile, PDO::PARAM_STR );

            $sth -> execute();

            $sthCheck = $dbh -> prepare("

                SELECT *
                FROM Telefoonnummers
                WHERE naam = :naam

            ");

            $sthCheck -> bindValue( ":naam", $newNaam, PDO::PARAM_STR );

            $sthCheck -> execute();

        }

    ?>

</body>

What is wrong with this?

There's a typo, $dhb != $dbh. Other than that, make sure that it's going into the if line, and wrap all of your PDO code in a try/catch block to check for errors. — aynber
– aynber, Commented Oct 2, 2013 at 20:00
Ah, the typo is removed now! But still, it isn't changing anything in the db. I'm pretty sure it goes into the if() statement. I got the same line in another file, and there it works fine. But that file adds a record instead of changing it. — Jeroen
– Jeroen, Commented Oct 2, 2013 at 21:31
Also, in Coda 2, when I preview the .php files, they all got an error like this: -----Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock) in - on line 21 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] No such file or directory' in -:21 Stack trace: #0 -(21): PDO->__construct('mysql:host=loca...', 'root', 'root') #1 {main} thrown in - on line 21-----, but the last file is the only one who doesn't do that. — Jeroen
– Jeroen, Commented Oct 2, 2013 at 21:38
Okay, maybe you were right, it doesn't enter the if() statement! (Tested by adding some print()'s in the code). But still it doesn't change anything in the database. The connection to the database is right, I just did that the same way as in the other files. And what about the SQL code? The syntax isn't wrong, right? Or is bindValue limited to 1 or something? — Jeroen
– Jeroen, Commented Oct 2, 2013 at 21:46
The syntax looks correct. If it's now going into the if statement, wrap the PDO code inside a try/catch block to see if there's any errors. You can also return the execute statement to a value and dump that to see what it returns. — aynber
– aynber, Commented Oct 3, 2013 at 13:00

Christopher Goehrs · Accepted Answer · 2013-10-15 22:22:47Z

I set up quick test pages using your code. I found two things that weren't working correctly.

The initial form - The "naam" field was disabled in the form. This means that the parameter doesn't make it to the Wijzig.php page. Since "naam" would always be null - and thus equal to "" - it wouldn't make it past your if statement. To get this to work, I reenabled the "naam" field in the first form.
Parameter in the UPDATE statement - In your UPDATE statement, it attempted to access a parameter named ":telefoonnummer" yet in the parameters, ":nummer" was used. This would cause PDO to throw an exception on execution.

Quick Note - I added an echo statement to wijzig.php so that a successful run would produce a visible result of some sort.

With that, I have updated the two files to be like such:

index.php

<head>
<title>Wijzigen telefoonnummer</title>
</head>

<body>

<?php

    $record_name = $_GET["naam"];

    $user = "root";
    $pass = "root";

    $dbh = new PDO(
        'mysql:host=localhost; port=8473; dbname=telefoon',
        $user,
        $pass
    );

    echo $record_name;

    $sth = $dbh -> prepare("

        SELECT *
        FROM Telefoonnummers
        WHERE naam = :record_name

    ");

    $sth -> bindValue( ":record_name", $record_name, PDO::PARAM_STR );

    $sth -> execute();

    $printRecord = $sth -> fetchAll(PDO::FETCH_ASSOC);

    /*
    //dit record als array weergeven
    print("<pre>");
    print_r($printRecord);
    print("</pre>");
    */

    //gegevens in variabelen zetten
    $printRecordRecord = $printRecord[0];
    $huidigeNaam = $printRecordRecord["naam"];
    $huidigeNummer = $printRecordRecord["telefoonnummer"];
    $huidigeMobiel = $printRecordRecord["mobiel"];

    //niet meer nodig door bovenstaande
    /*
    foreach( $printRecord AS $printRecordIndex => $printRecordRecord ) {

        $huidigeNaam = $printRecordRecord["naam"];
        $huidigeNummer = $printRecordRecord["telefoonnummer"];
        $huidigeMobiel = $printRecordRecord["mobiel"];

    }
    */

    print("

        <form action='wijzig.php' method='POST'>
            <table>

                <tr>
                    <td bgcolor='green'><b>Naam</b></td>
                    <td bgcolor='green'><b>Telefoonnummer</b></td>
                    <td bgcolor='green'><b>Mobiel</b></td>
                    <td bgcolor='green'></td>
                </tr>

                <tr>
                    <td><input type='text' name='naam' value='$huidigeNaam'/></td>
                    <td><input type='text' name='nummer' value='$huidigeNummer' /></td>
                    <td><input type='text' name='mobiel' value='$huidigeMobiel' /></td>
                    <td><input type='submit' value='Wijzig' /></td>
                </tr>

            </table>
        </form>

    ");

?>

</body>

wijzig.php

<head>
<title>Gewijzigd</title>
</head>

<body>

<?php

    //geupdate gegevens ophalen
    $newNaam = $_POST["naam"];
    $newNumber = $_POST["nummer"];
    $newMobile = $_POST["mobiel"];

    //gegevens updaten als ALLES is ingevuld
    if ( ($newNaam != "") && ($newNumber != "") && ($newMobile != "") ) {

        $user = "root";
        $pass = "root";

        $dbh = new PDO(
        'mysql:host=localhost; port=8473; dbname=telefoon',
        $user,
        $pass
    );

        $sth = $dbh -> prepare("

            UPDATE Telefoonnummers
            SET naam = :naam,
            telefoonnummer = :nummer,
            mobiel = :mobiel
            WHERE naam = :naam

        ");

        $sth -> bindValue( ":naam", $newNaam, PDO::PARAM_STR );
        $sth -> bindValue( ":nummer", $newNumber, PDO::PARAM_STR );
        $sth -> bindValue( ":mobiel", $newMobile, PDO::PARAM_STR );

        $sth -> execute();

        $sthCheck = $dbh -> prepare("

            SELECT *
            FROM Telefoonnummers
            WHERE naam = :naam

        ");

        $sthCheck -> bindValue( ":naam", $newNaam, PDO::PARAM_STR );

        echo "Number of records changed: ".$sthCheck -> execute();

    }

?>

</body>

When run against an existing record, it produces the following output:

Number of records changed: 1

sidenote: if OP needs that disabled field actually disabled, use the readonly attribute instead. This will allow the field to POST unlike using disabled

Collectives™ on Stack Overflow

Updating database record with PHP/PDO and mySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related