Linux-Noob Forums - Viewing and modifying data within mysql.

this was a real experience, and i'm documenting it so others may also learn. (thanks znx and jy for assistance).

first of all, we will do all the work from the command line, so lets get to our mysql binary directory

in my case that's /usr/local/mysql/bin

once there, lets 'login' to mysql

Code:
./mysql -u root -p

after entering your mysql root password, you should now be able to select the Database you want to work on in this example, the database is called forums

Code:
use forums;

now we are using that database, lets see what is in it (tables)

Code:
show tables;

that produces a list like this one (yours will be different)

Quote:+---------------------------+| Tables_in_forums |

+---------------------------+

| ibf_acp_help |

| ibf_admin_login_logs |

| ibf_admin_logs |

| ibf_admin_permission_keys |

| ibf_admin_permission_rows |

| ibf_admin_sessions |

| ibf_announcements |

| ibf_attachments |

| ibf_attachments_type |

| ibf_badwords |

| ibf_banfilters |

| ibf_bulk_mail |

| ibf_cache_store |

| ibf_cal_calendars |

| ibf_cal_events |

| ibf_components |

| ibf_conf_settings |

| ibf_conf_settings_titles |

| ibf_contacts |

| ibf_converge_local |

| ibf_custom_bbcode |

| ibf_dnames_change |

| ibf_email_logs |

| ibf_emoticons |

| ibf_faq |

| ibf_forum_perms |

| ibf_forum_tracker |

| ibf_forums |

| ibf_groups |

| ibf_languages |

| ibf_login_methods |

| ibf_mail_error_logs |

| ibf_mail_queue |

| ibf_member_extra |

| ibf_members |

| ibf_members_converge |

| ibf_members_partial |

| ibf_message_text |

| ibf_message_topics |

| ibf_moderator_logs |

| ibf_moderators |

| ibf_pfields_content |

| ibf_pfields_data |

| ibf_polls |

| ibf_posts |

| ibf_profile_comments |

| ibf_profile_friends |

| ibf_profile_portal |

| ibf_profile_portal_views |

| ibf_profile_ratings |

| ibf_reg_antispam |

| ibf_rss_export |

| ibf_rss_import |

| ibf_rss_imported |

| ibf_search_results |

| ibf_sessions |

| ibf_skin_macro |

| ibf_skin_sets |

| ibf_skin_template_links |

| ibf_skin_templates |

| ibf_skin_templates_cache |

| ibf_skin_url_mapping |

| ibf_spider_logs |

| ibf_stats |

| ibf_subscription_currency |

| ibf_subscription_extra |

| ibf_subscription_logs |

| ibf_subscription_methods |

| ibf_subscription_trans |

| ibf_subscriptions |

| ibf_task_logs |

| ibf_task_manager |

| ibf_template_diff_changes |

| ibf_template_diff_session |

| ibf_templates_diff_import |

| ibf_titles |

| ibf_topic_markers |

| ibf_topic_mmod |

| ibf_topic_ratings |

| ibf_topic_views |

| ibf_topics |

| ibf_topics_read |

| ibf_tracker |

| ibf_upgrade_history |

| ibf_validating |

| ibf_voters |

| ibf_warn_logs |

+---------------------------+

87 rows in set (0.01 sec)

now, let's say we want to see what's in ibf_conf_settings, to do that we can

Code:
select * from ibf_conf_settings;

great, if there's not much in there, but if it's big and you want to find specific info, then you'll need to understand the structure of the table

Code:
describe ibf_conf_settings;

which will produce a list of colums like this

Quote:+------------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |

+------------------+--------------+------+-----+---------+----------------+

| conf_id | int(10) | | PRI | NULL | auto_increment |

| conf_title | varchar(255) | | | | |

| conf_description | text | | | | |

| conf_group | smallint(3) | | | 0 | |

| conf_type | varchar(255) | | | | |

| conf_key | varchar(255) | | | | |

| conf_value | text | YES | | NULL | |

| conf_default | text | YES | | NULL | |

| conf_extra | text | YES | | NULL | |

| conf_evalphp | text | YES | | NULL | |

| conf_protected | tinyint(1) | | | 0 | |

| conf_position | smallint(3) | | | 0 | |

| conf_start_group | varchar(255) | | | | |

| conf_end_group | tinyint(1) | | | 0 | |

| conf_add_cache | tinyint(1) | | | 1 | |

| conf_help_key | varchar(255) | | | 0 | |

+------------------+--------------+------+-----+---------+----------------+

16 rows in set (0.00 sec)

ok, now you want to search it for a value similar to the word 'poll'

Code:
select * from ibf_conf_settings where conf_title like '%poll%';

the % infront of and behind the word poll allows for different values before and after the word poll

so, you sift through the smaller results you have now, and want to see

in my case, i wanted to manually edit a poll value, and i found it was setting number 249, so to verify that we do this

Code:
select conf_value from ibf_conf_settings where conf_id = 249;

that in turn displays this

Quote:+-----------------------------------------------------------+| conf_value |

+-----------------------------------------------------------+

| https://www.linux-noob.com/forums/index.php?showtopic=2791 |

+-----------------------------------------------------------+

1 row in set (0.00 sec)

so now we know it's the right value, lets change it to a new value (we will change the url)

Code:
update ibf_conf_settings set conf_value="NEW URL HERE" where conf_id = 249;

in the code above, we use 'update' to update the table called ibf_conf_settings and we 'set' a conf_value to a new URL provided that the id=249

so here's the code

Code:
update ibf_conf_settings set conf_value="https://www.linux-noob.com/forums/index.php?showtopic=3201" where conf_id = 249;

that's it, all done.,

the above has actually changed the poll on the front page of linux-noob.com from it's old value to the one listed above

cheers

anyweb