Viewing and modifying data within mysql.

[anyweb]

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)

 

+---------------------------+| 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

 

+------------------+--------------+------+-----+---------+----------------+| 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

+-----------------------------------------------------------+| 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