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
38 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 21

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 21

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 21

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 21

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now