Run UPDATE query on SELECT query result

Question

I want to run an UPDATE query on a record after selecting it.

$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

$query
   ->select(array('p.*', 'u.id', 'u.name' ,'pr.*'))
   ->from($db->quoteName('#__chichi_photos', 'p'))
   ->join('LEFT', $db->quoteName('#__users', 'u') . ' ON (' . $db->quoteName('p.userID') . ' = ' . $db->quoteName('u.id') . ')')
   ->join('LEFT', $db->quoteName('#__chichi_profile', 'pr') . ' ON (' . $db->quoteName('pr.userID') . ' = ' . $db->quoteName('u.id') . ')')
   ->where($db->quoteName('p.id') .'=' . $photoID .'');

$db->setQuery($query);

$results = $db->loadRow();

I want to run an UPDATE query on a field in #__chichi_photos table that has an id in the result and if possible do it with a single query statement.

Basically what i want to do is update "hits" column on the field after it is selected. Thank You.

Answer 1

You can use $query->clear(); which will clear the data from your first query, allowing you to then use your update query, like so:

$db = JFactory::getDbo();

$query = $db->getQuery(true);

$query
   ->select(array('p.*', 'u.id', 'u.name' ,'pr.*'))
   ->from($db->quoteName('#__chichi_photos', 'p'))
   ->join('LEFT', $db->quoteName('#__users', 'u') . ' ON (' . $db->quoteName('p.userID') . ' = ' . $db->quoteName('u.id') . ')')
   ->join('LEFT', $db->quoteName('#__chichi_profile', 'pr') . ' ON (' . $db->quoteName('pr.userID') . ' = ' . $db->quoteName('u.id') . ')')
   ->where($db->quoteName('p.id') .'=' . $photoID .'');

$db->setQuery($query);

$results = $db->loadRow();


// SECOND PART OF THE QUERY
$query = $db->getQuery(true);
$query->clear();

$fields = array(
    $db->quoteName('hits') . ' = ' . $db->quote($results[0]),
);     
$conditions = array( 
    $db->quoteName('id') . ' = ' . $db->quote($results[1])
);

$query->update($db->quoteName('#__chichi_photos'))
      ->set($fields)
      ->where($conditions);

$db->setQuery($query);

try
{
    $db->execute();
}
catch (RuntimeException $e)
{
    $e->getMessage();
}

You will probably need to change $results[0] and $results[0] that the indexes that your first query outputs.

Hope this helps

Answer 2

Your question is a little unclear on the objective because there is no posted UPDATE coding attempt, but I assume that you mean to "increment" the hit count on the #__chichi_photos table using the $photoID value.

$photoID = 1;                                        // whatever you set it to be
$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->update("#__chichi_photos")
                ->set("hit = hit + 1")               // increment the row's value
                ->where("id = " . (int)$photoID);    // cast as int for security/stability
    echo $query->dump();                             // show yourself the query being run
    $db->setQuery($query);
    $db->execute();
    if ($affrows = $db->getAffectedRows()) {         // check for "logical" success
        echo "Incremented hit count for photo# $photoID";
    } else {                                         // catch "logical" failure
        echo "Failed to increment hit count for photo# $photoID";
    }
} catch (Exception $e) {                             // catch any syntax failures
    echo "Query Syntax Error: " , $e->getMessage();  // never show getMessage() to public
}