Thumbs Element - Error:1054

Q

QSSFabrik

New Member
Installed Thumbs element using Fabrik 3.3.
Created an Element named "thumbstest".
When accessing "view data" get the following error message:

An error has occurred.
1054 Unknown column 'listid' in 'where clause' SQL=SELECT thumb FROM sgj_fabrik_thumbs WHERE listid = 15 AND formid = 15 AND row_id = '1' AND element_id = 229 AND user_id = '655'
 
Check your database in something like phpMyAdmin, look at the structure for the $_fabrik_thumbs table. It should definitely have a listid column, here's the code we have in that element to create the table:

Code: 
        $query = "CREATE TABLE IF NOT EXISTS  `#__{package}_thumbs` (
    `user_id` VARCHAR( 255 ) NOT NULL ,
    `listid` INT( 6 ) NOT NULL ,
    `formid` INT( 6 ) NOT NULL ,
    `row_id` INT( 6 ) NOT NULL ,
    `thumb` VARCHAR( 255 ) NOT NULL,
    `date_created` DATETIME NOT NULL,
    `element_id` INT( 6 ) NOT NULL,
    `special` VARCHAR(30),
    PRIMARY KEY ( `user_id` , `listid` , `formid` , `row_id`, `element_id`, `special` )
);";
        $db->setQuery($query);
        $db->execute();

The only possibilkity I can think of is either that query completely went wrong and no columns got created, or you had an earlier version of that table which for some reason didn't have the listid col.

Anyway, just make sure your thumbs table has the columns as per above. Add any which are missing.

Or, just delete the table, make sure your code is up to date, and display the plugin in the back end again. That should re-run the installation of the table.

-- hugh
 
cheesegrits said:
Check your database in something like phpMyAdmin, look at the structure for the $_fabrik_thumbs table. It should definitely have a listid column, here's the code we have in that element to create the table:

Code: 
        $query = "CREATE TABLE IF NOT EXISTS  `#__{package}_thumbs` (
    `user_id` VARCHAR( 255 ) NOT NULL ,
    `listid` INT( 6 ) NOT NULL ,
    `formid` INT( 6 ) NOT NULL ,
    `row_id` INT( 6 ) NOT NULL ,
    `thumb` VARCHAR( 255 ) NOT NULL,
    `date_created` DATETIME NOT NULL,
    `element_id` INT( 6 ) NOT NULL,
    `special` VARCHAR(30),
    PRIMARY KEY ( `user_id` , `listid` , `formid` , `row_id`, `element_id`, `special` )
);";
        $db->setQuery($query);
        $db->execute();

The only possibilkity I can think of is either that query completely went wrong and no columns got created, or you had an earlier version of that table which for some reason didn't have the listid col.

Anyway, just make sure your thumbs table has the columns as per above. Add any which are missing.

Or, just delete the table, make sure your code is up to date, and display the plugin in the back end again. That should re-run the installation of the table.

-- hugh
Click to expand...
 
In phpMyAdmin, I received the Error message:

"MySQL said:

#1064 - 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 '$query = "CREATE TABLE IF NOT EXISTS `#__{package}_thumbs` (
`user_id` VAR' at line 1 "

I'm wondering if my version of MySQL is incompatible somehow.
 
No, I didn't say run that, I just showed it to you so you could see the structure.

Does that table already exist in your database?

-- hugh
 
I haven't been able to locate $_fabrik_thumbs
in the Fabrik table directory.
Could I have missed an installation procedure?
 
Well, it'll be called sgj_fabrik_thumbs. And I think it must exist, otherwise the error would be about the table not existing, not a column in the table being missing.

-- hugh
 
Found it. Indeed you are correct:

My sgj_fabrik_thumbs table included:
table.id
instead of
list.id

changed it and it is now working.
Thanks hugh!
 
Cool. Yeah, you must have had a much earlier version of Fabrik (like 3.0.x or even 2.x) which created that table.

I'll look at adding some code to check that and fix it if necessary.

-- hugh
 
One other thing to check, can you look at the Primary Key for the table, and make sure it's a composite key (multiple columns in the key) which includes list_id? I'm not sure if MySQL will automatically pick up the change to the column name, when the column is in the PK.

-- hugh
 
cheesegrits said:
Cool. Yeah, you must have had a much earlier version of Fabrik (like 3.0.x or even 2.x) which created that table.

I'll look at adding some code to check that and fix it if necessary.

-- hugh
Click to expand...

My original installation was 3.2.
I updated to 3.3 prior to installing the thumbs element.
 
cheesegrits said:
One other thing to check, can you look at the Primary Key for the table, and make sure it's a composite key (multiple columns in the key) which includes list_id? I'm not sure if MySQL will automatically pick up the change to the column name, when the column is in the PK.

-- hugh
Click to expand...

Will follow up on this.
 
I just tested, and it looks like it does preserve the PK.

I've also added a test in the code to update that field name automatically, so hopefully nobody else will run in to this problem.

-- hugh
 
Yup, that's what I said up ^^ there somewhere. I can't remember exactly when we made that change, but it was quite a while ago.

Anyway, glad it's sorted, and hopefully it'll now autocorrect itself, and nobody else will have this issue.

-- hugh
 
I'm guessing this error message is related to the same issue so am submitting it here.
Tried to connect new list to sgj_fabrik_thumbs but got the following error when I selected view data:

An error has occurred.
500 Fabrik has generated an incorrect query for the list MyList: <br /><br /><pre>Unknown column 'sgj_fabrik_thumbs.' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `sgj_fabrik_thumbs`.`user_id` AS `sgj_fabrik_thumbs___user_id`, `sgj_fabrik_thumbs`.`user_id` AS `sgj_fabrik_thumbs___user_id_raw`, `sgj_fabrik_thumbs`.`listid` AS `sgj_fabrik_thumbs___listid`, `sgj_fabrik_thumbs`.`listid` AS `sgj_fabrik_thumbs___listid_raw`, `sgj_fabrik_thumbs`.`formid` AS `sgj_fabrik_thumbs___formid`, `sgj_fabrik_thumbs`.`formid` AS `sgj_fabrik_thumbs___formid_raw`, `sgj_fabrik_thumbs`.`row_id` AS `sgj_fabrik_thumbs___row_id`, `sgj_fabrik_thumbs`.`row_id` AS `sgj_fabrik_thumbs___row_id_raw`, `sgj_fabrik_thumbs`.`thumb` AS `sgj_fabrik_thumbs___thumb`, `sgj_fabrik_thumbs`.`thumb` AS `sgj_fabrik_thumbs___thumb_raw`, `sgj_fabrik_thumbs`.`date_created` AS `sgj_fabrik_thumbs___date_created`, `sgj_fabrik_thumbs`.`date_created` AS `sgj_fabrik_thumbs___date_created_raw`, `sgj_fabrik_thumbs`.`element_id` AS `sgj_fabrik_thumbs___element_id`, `sgj_fabrik_thumbs`.`element_id` AS `sgj_fabrik_thumbs___element_id_raw`, `sgj_fabrik_thumbs`.`special` AS `sgj_fabrik_thumbs___special`, `sgj_fabrik_thumbs`.`special` AS `sgj_fabrik_thumbs___special_raw`, `sgj_fabrik_thumbs`.`` AS slug , `sgj_fabrik_thumbs`.`` AS `__pk_val` FROM `sgj_fabrik_thumbs` LIMIT 0, 10</pre>
 
Not sure if you'll be able to put a Fabrik list on it, because it uses a composite primary key.

Sent from my HTC One using Tapatalk
 
Thanks for this explanation. Since this is a related question, I thought I would ask it in this thread:

I am interested in accessing the Thumbs Element table data in "sgj_fabrik_thumbs".
The only way I can think of doing this is to download the sgj_fabrik_thumbs table as a .csv file,
then import it back into a new Fabrik list which will create an "id" field.
I plan to then use it for a database join operation.
Wondering if there is a better way to accomplish this.
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 56 (members: 2, guests: 54)
    Back
    Top