How to update the database after transaction?

I have a Drupal 7 site that has a customized theme installed on it and we have also added some new tables and extended some others. Right now I have set up Ubercart for selling our products based on the taxonomy. When the purchase is complete I need to update a custom table in MySQL so I made a proc to do that.

In MySQL the created a proc that will do the updating of the tables that I need, all I need to pass into the proc is the uid (same as from the users table) and an id of the taxonomy that was selected during purchase.

I have created the following code to make the call but I am not sure what the best way to pass in the uid and tid to the proc? Should I be using rules within Drupal?

<?php
$mysqli = new mysqli("mysite.com", "user", "password", "db1");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("CALL UpdateUserDestList(uID, tID")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>

Solution

You can fire your code after a purchase using Ubercart's hook_uc_checkout_complete(). You will want to define that in a custom module, not from within the theme since this is business logic not display information. From within the hook you will have access to both the order and the user account that placed the order.

When working with Drupal, or any other framework really, you should leverage the built-in tools when you can to benefit from the structures they provide.

// Get the Drupal database connection and change the statement class to PDOStatement.
// Save the current class for cleanup later.
$conn = Database::getConnection();
$saved_class = $conn->getAttribute(PDO::ATTR_STATEMENT_CLASS);
$conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('PDOStatement'));

// Prepare the statement and bind params
$statement = $conn->prepare("Call GetNodeList(?,?)");

$op_status = $statement->bindParam(1, $node_type, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 25);
$op_status = $statement->bindParam(2, $publish_state, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT);

// Execute the statement and reset the connection's statement class to the original.
$exec_result = $statement->execute();
$conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, $saved_class);

// Get your data
while ($row = $statement->fetchColumn(0)) {
  // ...
}

Code taken from: https://drupal.stackexchange.com/questions/32708/how-to-execute-stored-procedures-in-drupal which in turn cites: http://public-action.org/content/stored-procedures-and-drupal-7