I've run across several hacks and add-on that don't properly handle prefixed tablenames. So, for my own sanity and to help others, I'm going to offer these two timesaving tips.
The first is pretty basic. Reference all VB tables like this:
"SELECT * from " . TABLE_PREFIX . "user ..."
I know, it should be common sense, but I have seen this enough the last few days that it bears mentioning.
The second is a more common problem that occurs in programs where the programmer actually thinks he's properly handled prefixed tables, but really hasn't and never tests it on a prefixed table.
Its a problem with more complex SELECTs using JOIN statements. Here how it usually looks:
"SELECT usergroup.usergroupid,usergroup.title, dlm_filequota.* FROM " . TABLE_PREFIX . "usergroup LEFT JOIN dlm_filequota ON (dlm_filequota.usergroupid = usergroup.usergroupid)"
The programmer thinks he's done right by using the TABLE_PREFIX tag, but not really because usergroup.usergroupid is undefined. The proper way to handle this, regardless of whether the VB table is the SELECT FROM table or a JOINed table is like this:
"SELECT usergroup.usergroupid,usergroup.title, dlm_filequota.* FROM " . TABLE_PREFIX . "usergroup AS usergroup LEFT JOIN dlm_filequota ON (dlm_filequota.usergroupid = usergroup.usergroupid)"
I hope this helps everyone.
Good thread, but was in wrong forum ;)
I've run across several hacks and add-on that don't properly handle prefixed tablenames. So, for my own sanity and to help others, I'm going to offer these two timesaving tips.
The first is pretty basic. Reference all VB tables like this:
I know, it should be common sense, but I have seen this enough the last few days that it bears mentioning.
The second is a more common problem that occurs in programs where the programmer actually thinks he's properly handled prefixed tables, but really hasn't and never tests it on a prefixed table.
Its a problem with more complex SELECTs using JOIN statements. Here how it usually looks:
The programmer thinks he's done right by using the TABLE_PREFIX tag, but not really because usergroup.usergroupid is undefined. The proper way to handle this, regardless of whether the VB table is the SELECT FROM table or a JOINed table is like this:
I hope this helps everyone.
I don't get it. Every time you refer to a table you need to do AS? Why? I thought you only did it when it was ambiguous?
Let's say you have the vb3_ prefix for tables. The above query would be..
"SELECT usergroup.usergroupid,usergroup.title, dlm_filequota.* FROM vb3_usergroup LEFT JOIN dlm_filequota ON (dlm_filequota.usergroupid = usergroup.usergroupid)"