How to update database table?

Omeka Classic Plugins
1

Hi All…how could I update table in omeka?

2

Could you clarify what you need to do?

Omeka’s own tables shouldn’t need any updates that aren’t built into the updates it does itself.

If you are talking about a change to a plugin’s tables for a new version, that’s done with the upgrade hook. You would directly write the SQL and query the database, much like you do in the install hook.

3

Hi Patrick I tried to modified CollectionTree plugin to enable user to rearrange the collection order by drag and drop.
What I did was I add ‘order’ column in the CollectionTree table, and when user would like to arrange the collection, I will re assigned the order of the collection. When retrieve the collection tree it will be “order by” order.

4

I was trying something like this:
$sql = "UPDATE omeka_collection_trees SET order= " . (int)$order . “WHERE id=” . (int)$colTreeId;
get_db()->query($sql);

but it doesn’t works.

5

Assuming this is in the upgrade hook, you need to have it check for the new version, as it is stated in the plugin.ini file. Omeka will compare what it has stored in the database and what’s in plugin.ini, and prompt the super user to upgrade the plugin from the plugins page if there is a difference.

First thing might be to make sure that that hook runs at all, then make sure there are no problems with the sql query itself.

6

Thanks Patrick…it’s the latest version (2.0.2), I didn’t change the version number, I just un installed and reinstalled it. The hook seems to run correctly, hence it did add “order” column in the collectionTree table.

It could be the problem(???), since I only modified the index page for admin(other than the hookInstall), and using jquery to call php function that only update the ‘order’ column. I put my php function in the View/helpers folder.

public function updateCollectionTreeOrder($collectionId=0, $order=0)
{
echo “collection Id : " . $collectionId . " =====order in the list: ======” . $order;

    // Child collection IDs are unique, so only fetch one row.
    if($collectionId > 0 && $order > 0)
    {
    $db = get_db();
    $colTreeTbl = get_db()->getTable('CollectionTree');
    $select = $colTreeTbl->getSelectForFindBy(array('collection_id' => $collectionId));
    $colTreeRow = $colTreeTbl ->fetchObjects($select);
   
    $colTreeId = $colTreeRow[0]['id'];
    
    $sql  = "UPDATE {$db->CollectionTree} SET order=" . $order . " WHERE id=" . $colTreeId;
    echo  "\n" . $sql;   ===> "UPDATE omeka_collection_trees SET order=1 WHERE id=4"
     $db->query($sql); ===> PROBLEMS***????
    }
      
}

**Zend_Db_Statement_Mysqli_Exception
Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘order=1 WHERE id=4’ at line 1

Syntaxly I don’t see anything wrong, did I missed something?

Thanks

7

It sounds like things on the Omeka side are working okay, but there’s a problem in the SQL. I often do debug($sql); just before the query to inspect exactly what the query is in the log file. Make sure that the log level in config.ini will log debug messages.

8

Thanks Patrick will give it a try.

9

For those of you who might facing the same issue, after spending whole day long trying to debug this error, in my case, it turned out a very simple thing:
$sql = “UPDATE {$db->CollectionTree} SET ‘order’ =” . (int)$order . " WHERE ‘id’=" . (int)$colTreeId ;

my problem was I was missing the single quote on the column name.

Thanks