SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HELP update/edit db content with PDO

    Hi I am hoping someone can help me figure out why this script is not wokring .... its been driving me crazy

    This code is to update an existing record in the datbase.

    When I run it I get the following error ... i also echoed the SQL statement being passed (the column names are correct) - in this particular example I was trying to update only the 4 fields listed

    Array
    (
    [0] =>
    [1] =>
    [2] =>
    )

    UPDATE `investigations` SET `site` = ?, `revenue_potential` = ?, `revenue_billed` = ?, `revenue_claimed` = ? WHERE `investigation_id` = ?


    here is my code that is causing the problem ....
    I have also included th ecode that works (which only updates data from one array vs. the two that I need)
    hope someone can point me in the rigth direction

    PHP Code:
    <?php
    //Connect to the database
    $dbh = new PDO('mysql:host=localhost;dbname=testdb''root''');
    //Set the default fetch mode to be an associative array.
    $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,  PDO::FETCH_ASSOC);

    //Define the fields 
    $fields = array(
        
    'site'=>'Site',
        
    'tour'=>'Tour',
        
    'customer'=>'Customer',
        
    'date_started'=>'Date Started',
        
    'date_closed'=>'Date Closed',
        
    'investigated_by'=>'Investigated By'
    );

    $dropfields = array(
        
    'revenue_potential'=>'RP',
        
    'revenue_billed'=>'RB',
        
    'revenue_claimed'=>'RC'
    );



    /* Using $_REQUEST as a shortcut - when we first access
    the page, the contact_id will be in the URL/$_GET array.
    When we submit the form it will then be in $_POST. */
    if(isset($_REQUEST['investigation_id'])){
        
    $fields_str '`investigation_id`, `'.implode(array_merge(array_keys($fields), array_keys($dropfields)), '`, `').'`';
        
    $sql "SELECT {$fields_str} FROM `investigations` WHERE investigation_id = ?";
        
    $stmt $dbh->prepare($sql);
        
    $stmt->execute(array($_REQUEST['investigation_id']));
        
    $values $stmt->fetch();
        if(!
    $values){
            
    $error 'Invalid ID provided';
        }
    }else{
        
    $error 'No ID provided';
    }


    //If the form has been submitted, validate it.
    if(isset($_POST['submit'])){
        
        
    //We require the contact ID be submitted.
        
    if(isset($_POST['investigation_id'])){
            
            
    //Create an array to hold the values we want to update.
            
    $update_values = array();
            
    $update_values2 = array();

            
    //For each of the fields we want, check if the field was posted, and if so compare it to the previous value
            
    foreach($fields AS $field=>$label){
                if(isset(
    $_POST[$field])){
                    
    //trim the whitepace
                    
    $value trim($_POST[$field]);
                    
    //If it's different than what we had stored before, update it.
                    
    if($value != $values[$field]){
                        
    //Add it to the array of values to update
                        
    $update_values[$field] = $value;
                        
    //Add the value to the values array, so it will show up in the form as well.
                        
    $values[$field] = $value;
                    }
                }
            }
            
            
            foreach(
    $dropfields AS $dropfield=>$droplabel){
                if(isset(
    $_POST[$dropfield])){
                    
    //trim the whitepace
                    
    $dropvalue trim($_POST[$dropfield]);
                    
    //If it's different than what we had stored before, update it.
                    
    if($dropvalue != $dropvalues[$dropfield]){
                        
    //Add it to the array of values to update
                        
    $update_values2[$dropfield] = $dropvalue;
                        
    //Add the value to the values array, so it will show up in the form as well.
                        
    $dropvalues[$dropfield] = $dropvalue;
                    }
                }
            }
            
            
            
            
    $errors = array();
            
    /*
            First and Last Names are required. strlen will return the string's length
            Since this time we are UPDATING, we only need to check if the strlen is > 0
            if the field has actually changed. Compare this code carefully to the add code,
            now we are checking if the field was sent AND doesn't have a length.
            */
            
    if(isset($update_values['site']) && !strlen($update_values['site'])){
                
    $errors['site'] = 'Please Enter a Site';
            }
            if(isset(
    $update_values['customer']) && !strlen($update_values['customer'])){
                
    $errors['customer'] = 'Please Enter a Customer';
            }
            
            
    //If there are not and there are fields to update, update the data.
            
    if(!count($errors) && (count($update_values) || count($update_values2))){
                
    $sql "UPDATE `investigations` SET ";
                foreach(
    array_keys($update_values) AS $field){
                    
    $sql .= "`{$field}` = ?, ";
                }
                foreach(
    array_keys($update_values2) AS $dropfield){
                    
    $sql .= "`{$dropfield}` = ?, ";
                }
                
    //Trim the trailing comma.
                
    $sql trim($sql', ');
                
    //Add the WHERE clause
                
    $sql .= ' WHERE `investigation_id` = ?';
                
                
    //Prepare the statement
                
    $stmt $dbh->prepare($sql);
                
                
    /* The values to put in the prepared statement will be the update values, with the contact ID at the end.
                array_values() will pull just the array's values, without the string keys. 
                The positional placeholder requires a numeric key. */
                
    $update_values array_values($update_values);
                
    $update_values[] = $values['investigation_id'];
                
                
    $update_values2 array_values($update_values2);
                
    $update_values2[] = $dropvalues['investigation_id'];
                
                
    $result $stmt->execute($update_values$update_values2);
            }
        }else{
            
    $error 'No ID provided';
        }
    }
    ?>


    <style>
    label{
        display: block;
        margin: 5px 0;
    }

    .error{
        font-weight: bold;
        color: #BB0000;
    }
    </style>

    <?php

    //If there was an error, display it. Otherwise display the form.
    if(isset($error) && $error){
        echo 
    $error;
    }else{
        
    //If the form was submitted and an UPDATE was attempted, display a message.
        
    if(isset($result)){
            if(
    $result){
                echo 
    '<b>Successfully Updated!</b>';
            }else{
                echo 
    '<b>Unable to Update</b>';
                print 
    '<pre>'.print_r($stmt->errorInfo(), true);
                echo 
    $sql;
            }
        }
        
        require_once(
    'menu.php');
        
    ?>
        
        <h1>Update Contact</h1>
        <form method="post" action="edit.php">
            <?php
            
    foreach($fields AS $field=>$label){
                
    //Print the form element for the field.
                
    echo "<label>{$label}:<br>";
                
    //If the field had an error, display it.
                
    if(isset($errors[$field])){
                    echo 
    ' <span class="error">'.$errors[$field].'</span><br>';
                }
                
    //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
                
    echo '<input type="text" name="'.$field.'"';
                if(isset(
    $values[$field])){
                    echo 
    ' value="'.$values[$field].'"';
                }
                echo 
    '/></label>';
            }
            
            
            
            foreach(
    $dropfields AS $dropfield=>$droplabel){
                
    //Print the form element for the field.
                
    echo "<label>{$droplabel}:<br>";

                
    //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
                
    echo '<input type="text" name="'.$dropfield.'"';
                if(isset(
    $dropvalues[$dropfield])){
                    echo 
    ' value="'.$dropvalues[$dropfield].'"';
                }
                echo 
    '/></label>';
            }
            
            
            
            
    //Add the hidden input for the investigation id.
            
    echo '<input type="hidden" name="investigation_id" value="'.$values['investigation_id'].'" />';
            
    ?>
            <input type="submit" name="submit" value="Edit" />
        </form>
    <?php
    }
    ?>


    When I use the same code but only update data from one array it works no problem ...
    This is the code that works (again, not updateding all the fields I need - it does not include data from the $dropfields array.... which is what I need)


    PHP Code:
    <?php
    //Connect to the database
    $dbh = new PDO('mysql:host=localhost;dbname=testdb''root''');
    //Set the default fetch mode to be an associative array.
    $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,  PDO::FETCH_ASSOC);

    //Define the fields for our CRUD application
    $fields = array(
        
    'site'=>'Site',
        
    'tour'=>'Tour',
        
    'customer'=>'Customer',
        
    'date_started'=>'Date Started',
        
    'date_closed'=>'Date Closed',
        
    'investigated_by'=>'Investigated By'
    );

    $dropfields = array(
        
    'revenue_potential'=>'RP',
        
    'revenue_billed'=>'RB',
        
    'revenue_claimed'=>'RC'
    );


    /* Using $_REQUEST as a shortcut - when we first access
    the page, the contact_id will be in the URL/$_GET array.
    When we submit the form it will then be in $_POST. */
    if(isset($_REQUEST['investigation_id'])){
        
    $fields_str '`investigation_id`, `'.implode(array_keys($fields), '`, `').'`';
        
    $sql "SELECT {$fields_str} FROM `investigations` WHERE investigation_id = ?";
        
    $stmt $dbh->prepare($sql);
        
    $stmt->execute(array($_REQUEST['investigation_id']));
        
    $values $stmt->fetch();
        if(!
    $values){
            
    $error 'Invalid ID provided';
        }
    }else{
        
    $error 'No ID provided';
    }


    //If the form has been submitted, validate it.
    if(isset($_POST['submit'])){
        
        
    //We require the contact ID be submitted.
        
    if(isset($_POST['investigation_id'])){
            
            
    //Create an array to hold the values we want to update.
            
    $update_values = array();
            
            
    //For each of the fields we want, check if the field was posted, and if so compare it to the previous value
            
    foreach($fields AS $field=>$label){
                if(isset(
    $_POST[$field])){
                    
    //trim the whitepace
                    
    $value trim($_POST[$field]);
                    
    //If it's different than what we had stored before, update it.
                    
    if($value != $values[$field]){
                        
    //Add it to the array of values to update
                        
    $update_values[$field] = $value;
                        
    //Add the value to the values array, so it will show up in the form as well.
                        
    $values[$field] = $value;
                    }
                }
            }
            
            
    $errors = array();
            
    /*
            First and Last Names are required. strlen will return the string's length
            Since this time we are UPDATING, we only need to check if the strlen is > 0
            if the field has actually changed. Compare this code carefully to the add code,
            now we are checking if the field was sent AND doesn't have a length.
            */
            
    if(isset($update_values['site']) && !strlen($update_values['site'])){
                
    $errors['site'] = 'Please Enter a Site';
            }
            if(isset(
    $update_values['customer']) && !strlen($update_values['customer'])){
                
    $errors['customer'] = 'Please Enter a Customer';
            }
            
            
    //If there are not and there are fields to update, update the data.
            
    if(!count($errors) && count($update_values)){
                
    $sql "UPDATE `investigations` SET ";
                foreach(
    array_keys($update_values) AS $field){
                    
    $sql .= "`{$field}` = ?, ";
                }
                
    //Trim the trailing comma.
                
    $sql trim($sql', ');
                
    //Add the WHERE clause
                
    $sql .= ' WHERE `investigation_id` = ?';
                
                
    //Prepare the statement
                
    $stmt $dbh->prepare($sql);
                
                
    /* The values to put in the prepared statement will be the update values, with the contact ID at the end.
                array_values() will pull just the array's values, without the string keys. 
                The positional placeholder requires a numeric key. */
                
    $update_values array_values($update_values);
                
    $update_values[] = $values['investigation_id'];
                
                
    $result $stmt->execute($update_values);
            }
        }else{
            
    $error 'No ID provided';
        }
    }
    ?>


    <style>
    label{
        display: block;
        margin: 5px 0;
    }

    .error{
        font-weight: bold;
        color: #BB0000;
    }
    </style>

    <?php

    //If there was an error, display it. Otherwise display the form.
    if(isset($error) && $error){
        echo 
    $error;
    }else{
        
    //If the form was submitted and an UPDATE was attempted, display a message.
        
    if(isset($result)){
            if(
    $result){
                echo 
    '<b>Successfully Updated!</b>';
                echo 
    $sql;
            }else{
                echo 
    '<b>Unable to Update</b>';
                print 
    '<pre>'.print_r($stmt->errorInfo(), true);
            }
        }
        
        require_once(
    'menu.php');
        
    ?>
        
        <h1>Update Contact</h1>
        <form method="post" action="edit.php">
            <?php
            
    foreach($fields AS $field=>$label){
                
    //Print the form element for the field.
                
    echo "<label>{$label}:<br>";
                
    //If the field had an error, display it.
                
    if(isset($errors[$field])){
                    echo 
    ' <span class="error">'.$errors[$field].'</span><br>';
                }
                
    //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
                
    echo '<input type="text" name="'.$field.'"';
                if(isset(
    $values[$field])){
                    echo 
    ' value="'.$values[$field].'"';
                }
                echo 
    '/></label>';
            }
            
    //Add the hidden input for the contact id.
            
    echo '<input type="hidden" name="investigation_id" value="'.$values['investigation_id'].'" />';
            
    ?>
            <input type="submit" name="submit" value="Edit" />
        </form>
    <?php
    }
    ?>
    Any suggestions are greatly appritiated
    Thanks

  2. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,438
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Hi,

    The problem is that you're trying to pass two arrays into the execute method, which only takes one argument:
    PHP Code:
    $result $stmt->execute($update_values$update_values2); 
    Merging the arrays first should do the trick:
    PHP Code:
    $result $stmt->executearray_merge($update_values$update_values2) ); 

  3. #3
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    Hi,

    The problem is that you're trying to pass two arrays into the execute method, which only takes one argument:
    PHP Code:
    $result $stmt->execute($update_values$update_values2); 
    Merging the arrays first should do the trick:
    PHP Code:
    $result $stmt->executearray_merge($update_values$update_values2) ); 


    Hi,
    thanks for taking the time to review the code and respond ...

    Now I keep geting SQLSTATE error ... again im just echoing out the SQL statement being run. I can confirm the column names are correct

    Array
    (
    [0] => HY093
    [1] =>
    [2] =>
    )
    UPDATE `investigations` SET `tour` = ?, `revenue_potential` = ? WHERE `investigation_id` = ?



    any thoughts?

  4. #4
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,438
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Just looking through your code some more, I see this line:
    PHP Code:
    if($dropvalue != $dropvalues[$dropfield]){ 
    but the array dropvalues isn't defined anywhere in the code before that.

  5. #5
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, thank you very much ... got it working

    $dropvalues should have been $values

    ... however ever after I changed this I was still getting the same error

    Array
    (
    [0] => HY093
    [1] =>
    [2] =>
    )

    so I ended up commenting ( // ) out this line and now everything works good

    PHP Code:
    $update_values[] = $values['investigation_id']; 
    Thanks again for your help


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •