Query update function not working

Question

I'm using the following function from CS50 2014 and it's working when I SELECT but it's not working when I try to UPDATE. Here's the query and the function:

query(
    "UPDATE users 
         SET pwtxt = ?, pass = ?, dt = ?, reset = ?
         WHERE usr  = ?",
    NULL,
    "compass",
    NULL,
    0,
    1
);

function query(/* $sql [, ... ] */)
{

    // SQL statement
    $sql = func_get_arg(0); 

    // parameters, if any
    $parameters = array_slice(func_get_args(), 1);  

    // try to connect to database
    static $handle;
    if (!isset($handle))
    {
        try
        {
            // connect to database
            $handle = new PDO("mysql:dbname=" . DATABASE . ";host=" . SERVER, USERNAME, PASSWORD);

            // ensure that PDO::prepare returns false when passed invalid SQL
            $handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        }
        catch (Exception $e)
        {
            // trigger (big, orange) error
            trigger_error($e->getMessage(), E_USER_ERROR);
            exit;
        }
    }

    // prepare SQL statement
    $statement = $handle->prepare($sql);
    if ($statement === false)
    {
        // trigger (big, orange) error
        //  trigger_error($handle->errorInfo()[2], E_USER_ERROR);  need to fix array issue
        exit;
    }   

    // execute SQL statement
    $results = $statement->execute($parameters);


    // return result set's rows, if any
    if ($results !== false)
    {       
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }
    else
    {       
        return false;
    }
}

score 0 · Answer 1 · answered May 16 '16 at 19:07

0

it appears you can no longer update with NULLS

answered May 16 '16 at 19:07

DCR

14,737
12
52
115

score -1 · Answer 2 · edited May 23 '17 at 11:59

When your are Updating rows you can't use the fetch methods. That's why it's working only with Select queries.

With PDO, how can I make sure that an UPDATE statement was successful?

You have to use the rowCount method to see how many have been affected by your query. By the way I don't think it's a good idea to try to mix up different queries types in only one method. Return values for a select, an update, an insert or a delete are not the same. You can't sum up these in one same function.

If you want to centralize your database connection, I invite you to create a child class of PDO where you can hard code your DB credentials. Or you can create a global PDO object or better, use a singleton. That way, you will be able to use your PDO instance anywhere without duplicating your DB credentials all around and using PDO native methods for your preparations.

It only uses fetch if results != false. The update works as written if you remove the nulls — DCR, May 16 '16 at 19:18

Query update function not working

2 Answers2