How to get error details when updating Joomla database? Topic is solved

[shenkwen]

I was trying to programmatically set the first image in introtext as intro image, here is what I did:

Code: Select all

    for ($i=1;$i<2;$i++) {  //Only update 1 article to test
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        $query->select($db->quoteName(array('introtext','images')))
              ->from($db->quoteName('#__content'))
              ->where($db->quoteName('id')." = ". $i);
        $db->setQuery($query);
        $result = $db->loadObjectList();
        $intro = $result[0]->introtext;
        echo '<pre><strong>Initial Value:</strong>';
        var_dump($result[0]->images);
        echo '</pre>';
        $images = json_decode($result[0]->images); 
        if (preg_match('/<img.*?>/', $intro, $img)) { //find the first img tag
            preg_match('/src="(.*?)"/',$img[0],$src); //find image url
            $images->image_intro = $src[1];
            $update = json_encode($images);
            echo '<pre><strong>Value to update:</strong>';
            var_dump($update);
            echo '</pre>';
            $db2 = JFactory::getDbo();
            $updateQuery = $db2->getQuery(true);
            $updateQuery->update($db2->quoteName('#__content'))
                        ->set(array($db2->quoteName('images')."=".$update))
                        ->where($db2->quoteName('id')." = ". $i);
            echo '<pre><strong>SQL:</strong>';
            var_dump(array($db2->quoteName('images')."=".$update));
            echo '</pre>';
            $db2->setQuery($updateQuery);
            try {
                $db2->execute();
            }
            catch (Exception $e){
                echo $e->getMessage();
            }
        } else {
            // echo $i;
        }
        // echo "<br>";
    }

The output is like


The problem is with $update, because if I change it to a simple string, it will go through. But I couldn't find what is wrong with it, can anyone help? Thanks.
By the way, I know my code is awkward, any tips to improve it is greatly appreciated.
And, do I need to use a second JDatabase object in this case? Thanks!

[SharkyKZ]

You can use query object's dump() method to see the generated SQL. This way you can find syntax errors more easily:

Code: Select all

try
{
	$db2->execute();
}
catch (Exception $e)
{
	echo $e->getMessage();
	echo $db2->getQuery()->dump();
}

In this case the issue is that the string you are trying to insert is unquoted. Wrap it with database object's quote() method:

Code: Select all

->set(array($db2->quoteName('images') . ' = ' . $db2->quote($update)))

And, do I need to use a second JDatabase object in this case?

No. You only need to call JFactory::getDbo() once in your code, before the loop.

[shenkwen]

Thanks!
And can I use same $query value to perform the second query(update) in above code?