Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Modify column in phpMyAdmin to have SET Length/Values match a column in another table or pull from a table

Posted on 2016-07-27
7
57 Views
Last Modified: 2016-07-27
TABLE: tbl_a
COLUMN: category
TYPE: SET
LENGTH/VALUES: '1', '2', '3'

TABLE: tbl_b
COLUMN: category
TYPE: SET
LENGTH/VALUES: I want to match the Length/Values of tbl_a, can this by done dynamically so I don't have to re-enter them?

Or better yet, is the following an option?

TABLE: tbl_category
COLUMN: category
RECORDS:
1
2
3

TABLE: tbl_a
COLUMN: category
TYPE: SET
LENGTH/VALUES: Something like: "select category from tbl_category order by category"? So the Length/Values are stored in a separate table which can be reused in multiple places.

Thanks.
0
Comment
Question by:bbdesign
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41731794
Is data type SET something you've picked up from another data base application? I can't find any information on a this data type to use as a reference.

But what you're describing sounds a little like a relational table. The values for a particular column are constrained to the values available in another table. This can also reduce the size of the table when the second table (foreign table) has a key column. The main table with thousands of records contains the much shorter key instead of the word(s) associated with that key.

MySQL does support foreign key constrains when the storage engine is InnoDB. The caveat to using relational tables is that you must join the related table in your queries when using keys.
0
 

Author Comment

by:bbdesign
ID: 41731838
When I click on a table in phpMyAdmin and click Structure, then click Change on any column, one of the Type options is SET. Then, in the Length/Values field you can put in a comma-separated list, such as:

'1', '2', '3'

...then when entering data into that table, you get a drop-down list in phpMyAdmin with only those values available to be selected.

Yes, I believe I want to do what you are describing. I was hoping there was an easy way to do this in phpMyAdmin, when defining the table structure, but I don't see how to do it.
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41731870
So it is. You've taught me something.

As long as the storage engine is InnoDB, when you look at the table structure in phpMyAdmin, you'll see a link for Relation View under the horizontal line below the field definitions. It's just to the right of the Print View button. There you can reference fields in other InnoDB tables.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:bbdesign
ID: 41731947
Yes, I do see Relation View. I clicked it, but I have no idea what I'm doing at that point. It should allow me to do what I described earlier, insert the values from one table's column into the "list of possible values" for another table's column? I.e., the second thing I described in my original question.
0
 
LVL 22

Accepted Solution

by:
Kim Walker earned 500 total points
ID: 41732053
No. It doesn't insert it. It links that column to another column in the same table or another table in the same data base. For example, if you have a table for contact information that includes a prefix column and you wish to limit the values for that column to Mr., Mrs., Ms., you would create a second table called prefixes which has one or two columns and three rows, one for each possible prefix. One column could be labeled name which would contain the prefix name (Mr., Mrs., or Ms.)  The second column would be for a key if you wish to use keys instead. Then in the relation view for the first table, you would select the drop-down beside the row for prefix and select the table and column to link to -- in this case prefixes.name. If you are using keys, you'd select the key column instead.

Now, if you attempt to insert a record in phpMyAdmin, you'll see a drop-down to select your prefix instead of an open text box. If you attempt to manipulate a record via SQL with any other value for a prefix, you'll get an error based on your selection for ON DELETE and ON UPDATE. These are called Referential Actions. You can consult the documentation for a description of each referential action.
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41732059
Correction, if you attempt to manipulate the data in the main table with a prefix value other than those three, MySQL will reject the operation. The referential action refers to any attempt to update or delete a record from the prefixes table which the documentation refers to as the parent table.
0
 

Author Comment

by:bbdesign
ID: 41732082
Awesome, I got it to work. Thank you so much!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
updating table data with inner join 9 45
update joined tables 2 55
sql_mode 1 30
Coldfusion/Mysql page error related to dynamic table creation. 9 32
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question