Riccardo990 Riccardo990 - 3 months ago 10
MySQL Question

Unable to edit record from a table PDO PHP

I have a list of record each and everyone may be edited. Whenever the user click edit an input mask appears where new data need to be entered in order to replace the old ones. I use PDO to handle the database connection and I strongly believe the problem is that I can't update the existing table

Here is a link to my older post validation and data add to a db table

The first snippet of code creates a form where the user enters some data.

<?php
error_reporting(-1);
ini_set('display_errors', 'On');
?>
<?php
$servername = "xxxx";
$username = "xxxx";
$password = "xxxxx";
$dbname = "xxxxx";

try {
$dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
<?php
if ($_GET['action'] == 'edit') {
//retrieve the record's information
$sth = $dbh->prepare("
SELECT nome, cognome, indirizzo, civico, citta, prov
FROM tagesroma
WHERE id = ?
");
$sth->execute(array($_GET['id']));
} else {
//set values to blank
$nome = '';
$cognome = '';
$indirizzo = '';
$civico = 0;
$citta = '';
$prov = '';
}
?>
<html>
<head>
<meta charset="UTF-8">
<title><?php echo ucfirst($_GET['action']); ?> Tages</title>
<style type="text/css">
<!--
#error { background-color: #600; border: 1px solid #FF0; color: #FFF;
text-align: center; margin: 10px; padding: 10px; }
-->
</style>
</head>
<body>
<?php
if (isset($_GET['error']) && $_GET['error'] != '') {
echo '<div id="error">' . $_GET['error'] . '</div>';
}
?>
<form action="commit.php?action=<?php echo $_GET['action']; ?>&type=tages"
method="post" accept-charset="UTF-8">
<table>
<tr>
<td>Nome</td>
<td><input type="text" name= "nome" value="<?php echo !empty($_POST['nome']) ? $_POST['nome'] : ''; ?>"></td>
</tr><tr>
<td>Cognome</td>
<td><input type="text" name= "cognome" value="<?php echo !empty($_POST['cognome']) ? $_POST['cognome'] : ''; ?>"></td>
</tr><tr>
<td>Indirizzo</td>
<td><input type="text" name= "indirizzo" value="<?php echo !empty($_POST['indirizzo']) ? $_POST['indirizzo'] : ''; ?>"></td>
</tr><tr>
<td>Civico</td>
<td><input type="text" name= "civico" value="<?php echo !empty($_POST['civico']) ? $_POST['civico'] : ''; ?>"></td>
</tr><tr>
<td>Citta</td>
<td><input type="text" name= "citta" value="<?php echo !empty($_POST['citta']) ? $_POST['citta'] : ''; ?>"></td>
</tr><tr>
<td>Prov</td>
<td><input type="text" name= "prov" value="<?php echo !empty($_POST['prov']) ? $_POST['prov'] : ''; ?>"></td>
</tr><tr>
<td colspan="2" style="text-align: center;">
<?php
if ($_GET['action'] == 'edit') {
echo '<input type="hidden" value="' . $_GET['id'] . '" name="id" />';
}
?>
<input type="submit" name="submit"
value="<?php echo ucfirst($_GET['action']); ?>" />
</td>
</tr>
</table>
</form>
</body>
</html>





The second part of the code validates the entered data and if and only if everything is correct then the new data are updated

<?php
error_reporting(-1);
ini_set('display_errors', 'On');
?>
<?php
$servername = "xxx";
$username = "xxx";
$password = "xxxx";
$dbname = "xxxx";

try {
$dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
$sth = $dbh->prepare("use accessibilita");
?>
<?php
switch ($_GET['action']) {
case 'edit':
switch ($_GET['type']) {
case 'tages':
$error = array();
$nome = isset($_POST['nome']) ?
trim($_POST['nome']) : '';
if (empty($nome)) {
$error[] = urlencode('Si prega di inserire un nome.');
}
$cognome = isset($_POST['cognome']) ?
trim($_POST['cognome']) : '';
if (empty($cognome)) {
$error[] = urlencode('Si prega di inserire un cognome.');
}
$indirizzo = isset($_POST['indirizzo']) ?
trim($_POST['indirizzo']) : '';
if (empty($indirizzo)) {
$error[] = urlencode('Si prega di inserire un indirizzo.');
}
$civico = isset($_POST['civico']) ?
trim($_POST['civico']) : '';
if (empty($civico)) {
$error[] = urlencode('Si prega di inserire un numero civico.');
}
$citta = isset($_POST['citta']) ?
trim($_POST['citta']) : '';
if (empty($citta)) {
$error[] = urlencode('Si prega di inserire una citta valida.');
}
$prov = isset($_POST['prov']) ?
trim($_POST['prov']) : '';
if (empty($prov)) {
$error[] = urlencode('Si prega di inserire una provincia.');
}
if (empty($error)) {
$stmt = $dbh->prepare("UPDATE tagesroma SET nome=?, cognome=?, indirizzo=?, civico=?, citta=?, prov=? WHERE id=1");
$stmt->execute(array($nome, $cognome, $indirizzo, $civico, $citta, $prov));
} else {
header('Location:tages.php?action=edit&id=' . $_GET['id'] .
'&error=' . join($error, urlencode('<br/>')));
}
break;
}
break;
}
?>

<html>
<head>
<title>Commit</title>
<meta charset="UTF-8">
</head>
<body>
<p>Done!</p>
</body>
</html>

Answer

I have re-created your code on my web server and it seems to work as you intended - the only thing I changed was the login info for mysql; whatever I type in your form updates the record with id=1 or generates an error message if data doesn't validate. Obviously I had to create a database matching your expected schema, so I just created all fields except id as VARCHAR(255).

I have a couple of suggestions to try:

  1. Check that your database user has the UPDATE permission set
  2. Check that the database does actually have a record with id=1 (it's feasible you've deleted and replaced the record and the id was changed by auto_increment?)

Hope this helps!

**EDIT: Having spoken to you, please see the following code which does what you want (on my server at least):

tages.php:

<?php
error_reporting(-1);
ini_set('display_errors', 'On');
?>
<?php
$servername = "localhost";
$username = "redacted";
$password = "redacted";
$dbname = "test";

try {
    $dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>
<?php
if ($_GET['action'] == 'edit') {
    //retrieve the record's information 
   $sth = $dbh->prepare("
  SELECT nome, cognome, indirizzo, civico, citta, prov
  FROM   tagesroma
  WHERE  id = ?
");
$sth->execute(array($_GET['id']));
    } else {
    //set values to blank
    $nome = '';
    $cognome = '';
    $indirizzo = '';
    $civico = 0;
    $citta = '';
    $prov = '';
}
?>
$thisrecord=$sth->fetch(PDO::FETCH_ASSOC);

<html>
    <head>
        <meta charset="UTF-8">
        <title><?php echo ucfirst($_GET['action']); ?> Tages</title>
        <style type="text/css">
        <!--
        #error { background-color: #600; border: 1px solid #FF0; color: #FFF;
         text-align: center; margin: 10px; padding: 10px; }
        -->
        </style>
    </head>
    <body>
        <?php
            if (isset($_GET['error']) && $_GET['error'] != '') {
                echo '<div id="error">' . $_GET['error'] . '</div>';
            }
        ?>
    <!-- NOTICE THE ADDITION TO THE END OF THE action= TO PASS THE ID ON TO YOUR SCRIPT -->
        <form action="commit.php?action=<?php echo $_GET['action']; ?>&type=tages&id=<?php echo $_GET["id"];?>"
           method="post" accept-charset="UTF-8">
            <table>
                <tr>
                    <td>Nome</td>
                    <td><input type="text" name= "nome" value="<?php echo !empty($_POST['nome']) ? $_POST['nome'] : $thisrecord["nome"]; ?>"></td>
                </tr><tr>
                    <td>Cognome</td>
                    <td><input type="text" name= "cognome" value="<?php echo !empty($_POST['cognome']) ? $_POST['cognome'] : $thisrecord["cognome"]; ?>"></td>                  
                </tr><tr>
                    <td>Indirizzo</td>
                    <td><input type="text" name= "indirizzo" value="<?php echo !empty($_POST['indirizzo']) ? $_POST['indirizzo'] : $thisrecord["indirizzo"]; ?>"></td>
                </tr><tr>
                    <td>Civico</td>
                    <td><input type="text" name= "civico" value="<?php echo !empty($_POST['civico']) ? $_POST['civico'] : $thisrecord["civico"]; ?>"></td>
                </tr><tr>
                    <td>Citta</td>
                    <td><input type="text" name= "citta" value="<?php echo !empty($_POST['citta']) ? $_POST['citta'] : $thisrecord["citta"]; ?>"></td>
                </tr><tr>
                    <td>Prov</td>
                    <td><input type="text" name= "prov" value="<?php echo !empty($_POST['prov']) ? $_POST['prov'] : $sth["prov"]; ?>"></td>
                </tr><tr>
                    <td colspan="2" style="text-align: center;">
                    <?php
                        if ($_GET['action'] == 'edit') {
                            echo '<input type="hidden" value="' . $_GET['id'] . '" name="id" />'; 
                        }
                    ?>
                    <input type="submit" name="submit"
                    value="<?php echo ucfirst($_GET['action']); ?>" />
                    </td>
                </tr>
            </table>
        </form>
    </body>
</html>

commit.php:

<?php
error_reporting(-1);
ini_set('display_errors', 'On');
?>
<?php
$servername = "localhost";
$username = "redacted";
$password = "redacted";
$dbname = "test";

try {
    $dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
    $sth = $dbh->prepare("use accessibilita");
?>
<?php
 switch ($_GET['action']) {    
case 'edit':
    switch ($_GET['type']) {
    case 'tages':
        $error = array();
        $nome = isset($_POST['nome']) ?
            trim($_POST['nome']) : '';
        if (empty($nome)) {
            $error[] = urlencode('Si prega di inserire un nome.');
        }
        $cognome = isset($_POST['cognome']) ?
            trim($_POST['cognome']) : '';
        if (empty($cognome)) {
            $error[] = urlencode('Si prega di inserire un cognome.');
        }
        $indirizzo = isset($_POST['indirizzo']) ?
            trim($_POST['indirizzo']) : '';
        if (empty($indirizzo)) {
            $error[] = urlencode('Si prega di inserire un indirizzo.');
        }
        $civico = isset($_POST['civico']) ?
            trim($_POST['civico']) : '';
        if (empty($civico)) {
            $error[] = urlencode('Si prega di inserire un numero civico.');
        }
        $citta = isset($_POST['citta']) ?
            trim($_POST['citta']) : '';
        if (empty($citta)) {
            $error[] = urlencode('Si prega di inserire una citta valida.');
        }
        $prov = isset($_POST['prov']) ?
            trim($_POST['prov']) : '';
        if (empty($prov)) {
            $error[] = urlencode('Si prega di inserire una provincia.');
        }
        if (empty($error)) {
          $stmt = $dbh->prepare("UPDATE tagesroma SET nome=?, cognome=?, indirizzo=?, civico=?, citta=?, prov=? WHERE id=?"); // changed to WHERE id=?
          $stmt->execute(array($nome, $cognome, $indirizzo, $civico, $citta, $prov,$_GET["id"])); // added $_GET["id"] to the end        
        } else {
          header('Location:tages.php?action=edit&id=' . $_GET['id'] .
              '&error=' . join($error, urlencode('<br/>')));
        }
        break;
    }
    break;
}
?>

<html>
 <head>
  <title>Commit</title>
  <meta charset="UTF-8">
 </head>
 <body>
  <p>Done!</p>
 </body>
</html>

Take a minute to have a look at the changes -- it turned out your problem was that it always edited the record with id=1 regardless of what record you'd selected for editing; this updated code adds the bits you were missing to pass the id of the "currently being edited" record.

Further edit: At your request I've altered tages.php to show the results of the query you have at the top. You already have $sth either set with the current values or initialised how they should be, and you also had the !empty? there - all you needed to do was put the two together.

Comments