1

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.

  • Can you please clarify your question? – mickmackusa Jan 27 at 10:38
2

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

  • 2
    I don't think you need to clear the query when you do $query = $db->getQuery(true);, as parameter true means to return a new query – Marko D Oct 25 '15 at 13:09
  • @MarkoD - You may be right. I always thought that when using multiple queries, it had to be cleared with $query->clear(); – Lodder Oct 25 '15 at 15:48
0

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
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.