Invalid column name, groupname in field list

[brim4brim]

Getting this error message:

Unknown column 'groupName' in 'field list'
File: /home/vhosting/d/vhost0014446/domains/anti-cheaters.com/htdocs/ndh-clan/Sources/TPSubs.php(1748) : eval()'d code
Line: 21

with the following query:

Code Select Expand

$query = $smcFunc['db_query']('', '
SELECT ID_GROUP, groupName, onlineColor, stars
  FROM {db_prefix}membergroups'
);

However I'm just modifying someone elses script and I've never worked with SMF before, usually I program in Java so I'm not sure whether it is my code or the original or changes in SMF that are causing my issues.  I also have no access to the database, just the script.

Here is the original:

Code Select Expand

$query = db_query("SELECT ID_GROUP, groupName, onlineColor, stars
  FROM {$db_prefix}membergroups", __FILE__, __LINE__);

[All Colours Sam]

Hi, welcome to SMF.

If you are trying to update some code to work with SMF 2.0  then you need to consider the names in some tables too, for example, in 2.0 the column name is  group_name, onlineColor becomes  online_color.


here's the complete list for the membergroups table:


id_group    group_name    description    online_color    min_posts    max_messages    stars    group_type    hidden    id_parent    limit_posters

[brim4brim]

Hi, welcome to SMF.

If you are trying to update some code to work with SMF 2.0  then you need to consider the names in some tables too, for example, in 2.0 the column name is  group_name, onlineColor becomes  online_color.


here's the complete list for the membergroups table:


id_group    group_name    description    online_color    min_posts    max_messages    stars    group_type    hidden    id_parent    limit_posters

Thank you that resolved my issue with that part of the code.  Last issue is with the members table.

I'm getting The database value you're trying to insert does not exist: id_rank error.

Any chance I could get those values either or is there a location in the manual where all the database tables and columns are listed?

Code Select Expand

$query2 = $smcFunc['db_query']('', '
     SELECT mem.ID_GROUP, mem.ID_MEMBER, mem.realName, mem.avatar, mem.usertitle,
           mem.emailAddress, mem.hideEmail, mem.lastLogin, mem.location, mem.personalText,
           mem.totalTimeLoggedIn, mem.dateRegistered, mem.ICQ, mem.AIM, mem.YIM, mem.MSN,
           mem.hideEmail, mem.emailAddress, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType
    FROM ({db_prefix}members AS mem)
    LEFT JOIN {db_prefix}attachments AS a ON (a.ID_MEMBER = mem.ID_MEMBER)
    WHERE ID_GROUP = {int:id_rank} ',
    array(
    'id_rank' => $groups[$rank]['id'],
    )
    );

[All Colours Sam]

id_rank is not a default column in the members table, I will assume that it comes from a mod or custom code, so you must add the column manually to the table.


This are all the columns in the members table:

Code Select Expand


id_member member_name date_registered posts id_group lngfile last_login real_name instant_messages unread_messages new_pm buddy_list pm_ignore_list pm_prefs mod_prefs message_labels passwd openid_uri email_address personal_text gender birthdate website_title website_url location icq aim yim msn hide_email show_online time_format signature time_offset avatar pm_email_notify karma_bad karma_good usertitle notify_announcements notify_regularity notify_send_body notify_types member_ip member_ip2 secret_question secret_answer id_theme is_activated validation_code id_msg_last_visit additional_groups smiley_set id_post_group total_time_logged_in password_salt ignore_boards warning passwd_flood pm_receive_from


[brim4brim]

id_rank is not a default column in the members table, I will assume that it comes from a mod or custom code, so you must add the column manually to the table.


This are all the columns in the members table:

Code Select Expand


id_member member_name date_registered posts id_group lngfile last_login real_name instant_messages unread_messages new_pm buddy_list pm_ignore_list pm_prefs mod_prefs message_labels passwd openid_uri email_address personal_text gender birthdate website_title website_url location icq aim yim msn hide_email show_online time_format signature time_offset avatar pm_email_notify karma_bad karma_good usertitle notify_announcements notify_regularity notify_send_body notify_types member_ip member_ip2 secret_question secret_answer id_theme is_activated validation_code id_msg_last_visit additional_groups smiley_set id_post_group total_time_logged_in password_salt ignore_boards warning passwd_flood pm_receive_from


Well it is the id_group column I'm trying to set a value for, I think it must be a problem with my script modification somewhere as the column seems to exist here.  I just called the variable mapping id_rank as that is what it was in the old version I think but I could be wrong.

Anyway I'm trying to map to the id_group column so if you know what is wrong with my script, feel free to post what I've done wrong, thanks.

[brim4brim]

Right just had a bit of time to look at this again.

I thought I'd post the old script and see if it is just me converting it wrong.  Can someone compare the original against mine and see if I did something wrong that has a better understanding of the new query syntax?

Code Select Expand

$rankorder = array(
  'Leading Hero',
  'Super Hero',
  'Guardian',
  'Hero',
  'Hero Recruit',
);

$groups = '';
//Does all the real work here for showing groups.
$query = db_query("SELECT ID_GROUP, groupName, onlineColor, stars
  FROM {$db_prefix}membergroups", __FILE__, __LINE__);
while ($row = mysql_fetch_assoc($query))
{
  $groups[$row['groupName']]  = array(
    'id' => $row['ID_GROUP'],
    'color' => empty($row['onlineColor']) ? '' : $row['onlineColor'],
    'stars' => empty($row['stars']) ? '' : substr($row['stars'],2),
  );
}
mysql_free_result($query);

foreach ($rankorder as $rank)
{
//Now get all the users
  $query2 = db_query("
     SELECT mem.ID_GROUP, mem.ID_MEMBER, mem.realName, mem.avatar, mem.usertitle,
           mem.emailAddress, mem.hideEmail, mem.lastLogin, mem.location, mem.personalText,
           mem.totalTimeLoggedIn, mem.dateRegistered, mem.ICQ, mem.AIM, mem.YIM, mem.MSN,
           mem.hideEmail, mem.emailAddress, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType
    FROM ({$db_prefix}members AS mem)
    LEFT JOIN {$db_prefix}attachments AS a ON (a.ID_MEMBER = mem.ID_MEMBER)
    WHERE ID_GROUP = '" . $groups[$rank]['id'] . "' ", __FILE__, __LINE__);
    //prints HTML and script ends
}

So you can see the bit I changed here and I'm convinced I made an error somewhere as I just changed WHERE ID_GROUP = to the bit above that I wrong with the array and id_rank.  Now it says id_rank doesn't exist but I did just make that up because I didn't think the name mattered.

Sorry if this is confusing but if someone can have a look to see if I'm using the correct syntax here, I'd appreciate it.