SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Add extra peel around query

    Hi,

    My joomla website has a table: jos_rsform_submission_values.
    This table has the following columns: SubmissionValueId, FormId, SubmissionId, FieldName and FieldValue.

    The Fieldname column contains: Region, Name, Adress, Telephone and Email.

    I managed to create a query that puts some data in a nice line. (See below)
    The "problem" is, it shows all the form submissions.

    I would love to have this query a bit adapted. I would like to have shown only the data that belongs to a certain region. So, in the form region 111 is submitted. Can i adapt or expand the query below, so that it only shows the Name, Adress,Email and telephone from region 111 ?

    Can someone help me on this, or at least point me into the right direction ?
    Thanks in advance !

    ==========================================================

    SELECT A.FormId, B.SubmissionId, B.DateSubmitted, B.UserIp,
    (SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
    (X.FieldName = 'Name') AND (X.SubmissionId=B.SubmissionId)) as
    Naam ,
    (SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
    (X.FieldName = 'adress') AND (X.SubmissionId=B.SubmissionId)) as
    Adress ,
    (SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
    (X.FieldName = 'telephone') AND (X.SubmissionId=B.SubmissionId)) as
    Telephone ,
    (SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
    (X.FieldName = 'email') AND (X.SubmissionId=B.SubmissionId)) as Email,


    FROM jos_RSFORM_FORMS A,
    jos_RSFORM_SUBMISSIONS B
    WHERE A.FormId=B.FormId
    AND A.FormId = 2

    ==========================================================

    Mesjoggah !

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT A.FormId
         , B.SubmissionId
         , B.DateSubmitted
         , B.UserIp
         , ( SELECT FieldValue 
               FROM jos_RSFORM_SUBMISSION_VALUES 
              WHERE FieldName = 'Name'
                AND SubmissionId = B.SubmissionId ) as Naam 
         , ( SELECT FieldValue 
               FROM jos_RSFORM_SUBMISSION_VALUES 
              WHERE FieldName = 'adress'
                AND SubmissionId = B.SubmissionId ) as Adress 
         , ( SELECT FieldValue 
               FROM jos_RSFORM_SUBMISSION_VALUES 
              WHERE FieldName = 'telephone'
                AND SubmissionId = B.SubmissionId ) as Telephone 
         , ( SELECT FieldValue 
               FROM jos_RSFORM_SUBMISSION_VALUES 
              WHERE FieldName = 'email'
                AND SubmissionId = B.SubmissionId ) as Email 
      FROM jos_RSFORM_FORMS A
    INNER
      JOIN jos_RSFORM_SUBMISSIONS B
        ON B.FormId = A.FormId
    INNER
      JOIN jos_RSFORM_SUBMISSION_VALUES C
        ON C.SubmissionId = B.SubmissionId 
       AND C.FieldName = 'region'
       AND C.FieldValue = '111'
     WHERE A.FormId = 2
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you !
    From the bottom of my heart !

    Could not figure it out myself for some reason....

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What if i wanted to show the data from a certain region and a certain capacity, located in the same table ?

    So, show data, where "region" is 111 and "capacity" is 20 ?
    Can I add a simple INNER JOIN to it, like:

    INNER
    JOIN jos_RSFORM_SUBMISSION_VALUES C
    ON C.SubmissionId = B.SubmissionId
    AND C.FieldName = 'region'
    AND C.FieldValue = '111'
    INNER
    JOIN jos_RSFORM_SUBMISSION_VALUES D
    ON D.SubmissionId = B.SubmissionId
    AND D.FieldName = 'capacity'
    AND D.FieldValue = '20'

    Thanks in advance,
    Mesjoggah

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mesjoggah View Post
    Can I add a simple INNER JOIN to it, like:
    what happened when you tested it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    Thank you for your reply !!!

    It does not return any errors, but it returns an empty value......

    MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0034 sec )

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that would indicate that there aren't any submissions with the specified characteristics
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    You're right.
    The database value had some spaces in it.
    So the value was not "123" but " 123"

    Thank you very much for your support !

    Mesjoggah

  9. #9
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, right. Here I am.....again.

    Well, The "capacity" field can contain more than one number.
    Users can check on the front end of the website some checkboxes so the data at the capacity field can contain for example:
    >10
    10-20
    20-30 etc.

    I hoped that "LIKE" would be helpfull in this, so i tried:

    SELECT A.FormId
    , B.SubmissionId
    , B.DateSubmitted
    , B.UserIp
    , ( SELECT FieldValue
    FROM jos_RSFORM_SUBMISSION_VALUES
    WHERE FieldName = 'Name'
    AND SubmissionId = B.SubmissionId ) as Name
    , ( SELECT FieldValue
    FROM jos_RSFORM_SUBMISSION_VALUES
    WHERE FieldName = 'adress'
    AND SubmissionId = B.SubmissionId ) as Adress
    , ( SELECT FieldValue
    FROM jos_RSFORM_SUBMISSION_VALUES
    WHERE FieldName = 'telephone'
    AND SubmissionId = B.SubmissionId ) as Telephone
    , ( SELECT FieldValue
    FROM jos_RSFORM_SUBMISSION_VALUES
    WHERE FieldName = 'email'
    AND SubmissionId = B.SubmissionId ) as Email
    FROM jos_RSFORM_FORMS A
    INNER
    JOIN jos_RSFORM_SUBMISSIONS B
    ON B.FormId = A.FormId
    INNER
    JOIN jos_RSFORM_SUBMISSION_VALUES C
    ON C.SubmissionId = B.SubmissionId
    AND C.FieldName = 'region'
    AND C.FieldValue = '152'
    INNER
    JOIN jos_RSFORM_SUBMISSION_VALUES D
    ON D.SubmissionId = B.SubmissionId
    AND D.FieldName = 'capacity'
    AND D.FieldValue LIKE '10-20'
    WHERE A.FormId = 2

    For some reason it does not return any data or any error. And I'm sure that the 10-20 record exists. Here's the actual table:

    SubmissionValueId FormId SubmissionId FieldName FieldValue
    258 2 28 formId 2
    256 2 28 Capacity <10
    10-20
    20-30
    30-40
    40-50
    >50
    255 2 28 Telephone 1234567890
    254 2 28 Email email@email.nl
    251 2 28 Adress Road 1
    250 2 28 Name Mr. Testert nr2
    247 2 28 Region 97

    I'm quite sure you are shaking your head, thinking, how did he miss that....


    Thanks in advance,
    Mesjoggah

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mesjoggah View Post
    I'm quite sure you are shaking your head, thinking, how did he miss that....
    not really

    actually, i am shaking my head, thinking, what kind of crappy cms is this poor guy stuck with...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha, sorry to see you give up.
    Anyway, you helped me a lot.
    Thank you very much for that !

    Greetz,
    A complete mysql nono from The Netherlands.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try AND D.FieldValue LIKE '%10-20%'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That did the trick.
    You're brilliant !

    I promise, i will not bother you again on this one !

    Realized i really need much more knowlegde regarding PHP and MySql, so registered today for a course...

    Thanks,
    Mesjoggah.

    --------------------------------
    Did a bit of google-ing and came to the conclusion that i was lucky enough to deal with the pick of the bunch when it comes to MySql knowlegde. I mean, when you are mentioned on O'Reilly, yep, then you are big !


Bookmarks

Posting Permissions

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