Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010

    Unanswered: Trying to format data into rows so I can search it

    I have two tables, the first one has these columns
    Table Name: jos_rsform_submission_values
    Columns: SubmissionValueId, FormId, SubmissionId, FieldName, FieldValue

    Sample Data:
    "101855","15","4284","Are you Human","garm"
    "101851","15","4284","High School Name","Radnor High School"
    "101850","15","4284","Coachs Phone",
    "101849","15","4284","Coachs Name",
    "101846","15","4284","Positions","Center "
    "101845","15","4284","League","Metropolitan Junior Hockey League"
    "101844","15","4284","Current Team","Philadelphia Little Flyers "
    "101841","15","4284","Date of Birth","06/30/1993"
    "101840","15","4284","Email"," m"
    "101838","15","4284","Phone","123 124 1234"
    "101835","15","4284","Address","32 Wentworth Lane"
    "101834","15","4284","Name","John Doe"
    Table Name: jos_rsform_submissions
    Columns: SubmissionId, FormId, DateSubmitted, UserIp, Username, UserId

    Sample Data:
    "4284","15","2010-03-22 07:47:36","",,"0"

    What I want to do is write a query that will put these into a table form thats horizontal instead of vertical right now so instead of these columns:
    SubmissionValueId, FormId, SubmissionId, FieldName, FieldValue

    The columns will be:
    Name, Address, City, State, Phone, Zip, Email, Date of Birth, Weight, Height, Athletic Information, Current Team, League, Positions, Shoots, Statistics, Coach Name, Coachs Phone, Academic Information, High School Name, GPA, SAT, ACT, Submit

    with the database organized that way.. That way I can sort through these and pull out players that I need to email..
    These column names are all in FieldName, and are the same throughout the table.

    Any idea on how to do this?


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    this is a perfect example of how difficult it is to pull meaningful information out of an EAV (entity-attribute-value) scheme

    google that term for more information, you will see that nobody recommends it

    as for achieving what you want, i suggest you pull all the rows you want into php (or whatever your application language is) and do the "horizontalization" there -- it'll be a lot easier than trying to do it with sql | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Quote Originally Posted by meridianblade
    Any idea on how to do this?
    As Rudy mentioned, using EAV doesn't make any sense here but if you want to access the data directly from the table then the easiest way is just to create a function that can pull a given attribute for a given entity ie

    create function get_attribute(
            in_id           int,
            in_param        varchar(50) )
            returns varchar(250)
            **      get value
            declare out_value               varchar(250);
            select  max(FieldValue) into out_value
            from    jos_rsform_submission_values
            where   SubmissionId = in_id
                    and FieldName = in_param;
            return ifnull(out_value,'');
    I didn't pay too much attention to the field names etc but I'm sure you can work these out. Obviously this data would do better in a standard table but I assume the data is being generated by another application. You could then create "normal" queries pulling data from the original table like this :
    select get_Attribute( Submission_Id,'Name'), 
           get_Attribute( Submission_Id,'Email'),
           get_Attribute( Submission_Id,'Coachs Name'),
           get_Attribute( Submission_Id,'Coachs Phone')
    from   jos_rsform_submissions
    where  get_Attribute( Submission_Id,'Shoots') = 'Straight'
           and get_Attribute( Submission_Id,'Weight') >= 200
           and get_Attribute( Submission_Id,'City') = 'Rosemont'

Posting Permissions

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