1

How do I use SQL transaction with the query function provided by CS50 staff?

I tried:

query("BEGIN TRANSACTION");

and even:

query("START TRANSACTION");

but these didn't work.

1

it appears that there is a (bug?) in query. the function does not work as expected when it comes to transactions.

I have done a little bit of research and found, according to the docs (3rd paragraph, 4th line), that transactions must be initiated with PDO::beginTransaction() and ended with PDO::commit() or PDO::rollback().

I have emailed David with this, but until I hear back from him, you could use this quick and dirty fix:

append these lines to your constants.php

/* goes into constants.php */
define('START_TRANSACTION', 1);
define ('COMMIT', 2);
define('ROLLBACK', 3);

and add these lines to your query function

/* goes into functions.php */
switch ($sql)
{
    case START_TRANSACTION:
        $handle->beginTransaction();
        return;
    case COMMIT:
        $handle->commit();
        return;
    case ROLLBACK:
        $handle->rollback();
        return;
}

so your query function should look like this:

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

    // handle transactions
    switch ($sql)
    {
        case START_TRANSACTION:
            $handle->beginTransaction();
            return;
        case COMMIT:
            $handle->commit();
            return;
        case ROLLBACK:
            $handle->rollback();
            return;
    }

    // prepare SQL statement
    $statement = $handle->prepare($sql);
    if ($statement === false)
    {
        // trigger (big, orange) error
        trigger_error($handle->errorInfo()[2], E_USER_ERROR);
        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;
    }
}

if you then want to start a transaction and commit or rollback, you could do

query(START_TRANSACTION);

// some queries

// and
//query(COMMIT);
// or
//query(ROLLBACK);
0

That's not how the query() function is intended to work. query() gets as argument a string containing a valid SQL command. So its use is something along the following lines:

$cakeIsNiceVar = "true";
$niceCakes = query("SELECT `cakeID` FROM `cupcake_cupboard` WHERE `CakeIsNice` = ?", $cakeIsNiceVar);

(example using the database described in the walk-through video about SQL)

What query() does, is take this string you gave it, replace all the ? with the values following the string and that are coma separated (so if there where two ?s inside the string the first will be replaced with the first value after the string and the second ? with the second value).

But before replacing them, the query function makes sure that the values after the string don't contain any SQL commands themselves. It does what we call "escape them", so that your webpage can't fall victim of an SQL injection attack.

You can check the body of the query() function, located under \pset8\includes\functions.php.


If this answers your question please accept it by clicking the gray check-mark to the left, so that it becomes green. You can also vote it up by pressing the up arrow above the check-mark. And don't forget to keep coding!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .