Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Arrow Unanswered: CREATE VIEW with ENUM columns

    Is it possible to create a view with ENUM type columns?

    I would get the ENUM values from foreign key tables.


    Thanks,
    David

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    Is it possible to create a view with ENUM type columns?
    of course

    CREATE VIEW foo AS
    SELECT * FROM daTable WHERE userid=937

    Quote Originally Posted by vivoices View Post
    I would get the ENUM values from foreign key tables.
    could you explain what you mean by this?

    what's a "foreign key table" and what does "get" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    My main purpose is to insert and update new rows to a "html_text_field_texts" table more conveniently in phpMyAdmin.

    I have a "html_text_field_texts" table with "htmlTextId, pageTypeId, pageId, localeId, htmlTextFieldId, htmlText" columns.

    My first attempt was to create a "HTML Texts" view with the following SELECT statement:

    Code:
    SELECT
    		html_text_field_texts.htmlTextId
    	,	page_types.pageTypeName
    	,	pages.pageName
    	,	locales.locale
    	,	html_text_field_names.htmlTextFieldName
    	,	html_text_field_texts.htmlText
    
    FROM
    	html_text_field_texts
    	INNER JOIN
    		page_types
    	ON
    		page_types.pageTypeId = html_text_field_texts.pageTypeId
    
    	INNER JOIN
    		pages
    	ON
    		pages.pageId = html_text_field_texts.pageId
    
    	INNER JOIN
    		locales
    	ON
    		locales.localeId = html_text_field_texts.localeId
    
    	INNER JOIN
    		html_text_field_names
    	ON
    		html_text_field_names.htmlTextFieldId = html_text_field_texts.htmlTextFieldId
    
    WHERE
    	locales.enabled = '1'
    Now when I go to the INSERT form for the "HTML Texts" view in phpMyAdmin, the column types are the same as the selected columns from the original tables, e.g. "pageTypeName" is varchar(30) etc.
    I want the VIEW to have ENUM type columns and set the ENUM values for e. g. the"pageTypeName" column of the VIEW from the UNIQUE values of "page_types.pageTypeName".

    Thanks,
    David

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    I want the VIEW to have ENUM type columns and set the ENUM values for e. g. the"pageTypeName" column of the VIEW from the UNIQUE values of "page_types.pageTypeName".
    you lost me right about there

    i'm sorry, i don't understand what you want

    i would expect the values of page_types.pageTypeName to be strings, like VARCHAR(99) or something, not ENUMs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    56
    Quote Originally Posted by r937 View Post
    i would expect the values of page_types.pageTypeName to be strings, like VARCHAR(99) or something, not ENUMs
    yes, "page_types.pageTypeName" is varchar(30).
    So the INSERT dialog of phpMyAdmin displays it as such, so I have to manually type in a pagetype name, but I want to choose one of the existing values of "page_types.pageTypeName".
    phpMyAdmin would create a dialog, that does exactly that when the column type of the VIEW could be set to ENUM and populated with all values from "page_types.pageTypeName".

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am afraid i still don't understand what you're doing

    maybe because i don't use phpmyadmin

    but the idea of inserting into a view also makes me a bit queasy

    good luck to you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    56
    What kind of problems could arise while inserting into a view?

    phpMyAdmin is not very flexible as a CRUD.
    Maybe HeidiSQL could do what I need i.e. showing the values of a "value" column in a dropdown of a "new/update row dialog" and then enter "valueIndex" (primary key of table "values") into the "valueIndex" column of table "combinationOfValues".
    Unfortunately phpMyAdmin is the only access I have to that specific database.

    I will have to build my own simple CRUD with php/js. I tried DaDaBIK and did not like it.

    Thank you for your time,
    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •