Professional Web Applications Themes

SQL statement to select from a drupal webform table - MySQL

In the following drupal table 'webform_submitted_data' the date 12/8/2007 is specified via 3 records ie: 2 1176718828 0 12 2 1176718828 1 8 2 1176718828 2 2007 I need to be able to select a sid that value matches a certain date. In the above example the sid value would be '2' if I wanted to select with date '12/8/2007'. I was wondering if there was a mysql (4.1.21- standard) SQL statement I could use select all the distinct sid values that match a certain date. The following is an example of a 'webform_submitted_data' table: sid cid no data 2 ...

  1. #1

    Default SQL statement to select from a drupal webform table

    In the following drupal table 'webform_submitted_data' the date
    12/8/2007 is specified via 3 records ie:

    2 1176718828 0 12
    2 1176718828 1 8
    2 1176718828 2 2007

    I need to be able to select a sid that value matches a certain date.
    In the above example the sid value would be '2' if I wanted to select
    with date '12/8/2007'. I was wondering if there was a mysql (4.1.21-
    standard) SQL statement I could use select all the distinct sid values
    that match a certain date. The following is an example of a
    'webform_submitted_data' table:

    sid cid no data
    2 1176015743 0 com
    2 1176015500 0 alison
    2 1176015691 0 money
    2 1176718828 0 12
    2 1176718828 1 18
    2 1176718828 2 2007
    2 1176851105 0 1
    2 1176851105 1 8
    2 1176851105 2 2008
    2 1177590862 0 Cheapest flights please 7 adt 6 chd
    3 1176015743 0 ijwuishwemnqashiu
    3 1176015500 0 james
    3 1176015691 0 hogh
    3 1176718828 0 4
    3 1176718828 1 5
    3 1176718828 2 2008
    3 1176851105 0 4
    3 1176851105 1 26
    3 1176851105 2 2008
    3 1177590862 0 Cheapest easter seats please
    4 1176015743 0 jasnbuighekjnasiuh
    4 1176015500 0 david
    4 1176015691 0 wings
    4 1176718828 0 12
    4 1176718828 1 8
    4 1176718828 2 2007
    4 1176851105 0 12

    This is a drupal table and I would prefer not modify the structure of
    it.

    skinhat Guest

  2. #2

    Default Re: SQL statement to select from a drupal webform table

    On 1 May, 10:36, skinhat <com> wrote: 

    SELECT
    DISTINCT `sid`
    WHERE LEFT(FROM_UNIXTIME(`cid`),10) = '2007-12-08';

    Captain Guest

  3. #3

    Default Re: SQL statement to select from a drupal webform table

     

    This wouldnt work because the 'cid' field doesnt hold the date. Its
    just a unique identifier.

    skinhat Guest

  4. #4

    Default Re: SQL statement to select from a drupal webform table

    skinhat wrote: 
    >
    > This wouldnt work because the 'cid' field doesnt hold the date. Its
    > just a unique identifier.
    >[/ref]

    Yes, the cid column does hold the date, it's just in a unixtime format and the
    FROM_UNIXTIME() function would then convert it to a human readable format

    see the mysql doentation:
    http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime

    FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

    Returns a representation of the unix_timestamp argument as a value in
    'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the
    function is used in a string or numeric context. The value is expressed in
    the current time zone. unix_timestamp is an internal timestamp value such as
    is produced by the UNIX_TIMESTAMP() function.

    And then using the ten most left characters in the string would give you just
    the YYYY-MM-DD

    +------------------------------------+
    | LEFT(FROM_UNIXTIME(1176015743),10) |
    +------------------------------------+
    | 2007-04-08 |
    +------------------------------------+


    --

    //Aho
    J.O. Guest

  5. #5

    Default Re: SQL statement to select from a drupal webform table

    On May 1, 8:49 pm, "J.O. Aho" <net> wrote: [/ref]

    >
    > Yes, the cid column does hold the date, it's just in a unixtime format and the
    > FROM_UNIXTIME() function would then convert it to a human readable format
    >[/ref]

    Notice in the table these records:


    2 1176718828 0 12
    2 1176718828 1 18
    2 1176718828 2 2007

    3 1176718828 0 4
    3 1176718828 1 5
    3 1176718828 2 2008

    Both have the same CID yet represent different dates. The first is
    12/18/2007 and the second is 4/5/2008.



    skinhat Guest

  6. #6

    Default Re: SQL statement to select from a drupal webform table

    On 1 May, 12:03, skinhat <com> wrote: [/ref]
    > [/ref]

    >
    > Notice in the table these records:
    >
    > 2 1176718828 0 12
    > 2 1176718828 1 18
    > 2 1176718828 2 2007
    >
    > 3 1176718828 0 4
    > 3 1176718828 1 5
    > 3 1176718828 2 2008
    >
    > Both have the same CID yet represent different dates. The first is
    > 12/18/2007 and the second is 4/5/2008.[/ref]

    If they both have the same cid value than it is hardly a "unique"
    identifier!

    Captain Guest

  7. #7

    Default Re: SQL statement to select from a drupal webform table

    On May 1, 9:06 pm, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > If they both have the same cid value than it is hardly a "unique"
    > identifier![/ref]

    The CID is unique to a certain date field. 1176718828 represents a
    date where no=0 is the day, no=1 month and no=2 is the year. A SID is
    equivalent to a record. This is the way drupal do it (www.drupal.org)
    for its webform module for dates.

    skinhat Guest

  8. #8

    Default Re: SQL statement to select from a drupal webform table

    On 1 May, 12:10, skinhat <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > The CID is unique to a certain date field. 1176718828 represents a
    > date where no=0 is the day, no=1 month and no=2 is the year. A SID is
    > equivalent to a record. This is the way drupal do it (www.drupal.org)
    > for its webform module for dates.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    No you are wrong. Unique means it is the only one of it's kind. This
    is not at all unique. It seems to be some sort of field identifier
    (that just happens to look like a unix timestamp. Date fields seem to
    be identified by a cid of either 1176718828 or 1176851105, whilst
    email addresses are identified by a cid of 1176015743, first names by
    1176015500, last names by 1176015691, request info by 1177590862 and
    so on. It is my guess that the cid of 1176718828 represents one type
    of date (e.g. creation date) whilst 1176851105 may represent last
    update date (note that these cannot be the correct descriptions as all
    the dates are in the future.

    I will craft you a self join query that looks at the 1176718828 type
    dates, but you need to identify which of the dates it is that are
    relevant to you.

    Captain Guest

  9. #9

    Default Re: SQL statement to select from a drupal webform table

    On May 1, 9:31 pm, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > No you are wrong. Unique means it is the only one of it's kind. This
    > is not at all unique. It seems to be some sort of field identifier
    > (that just happens to look like a unix timestamp. Date fields seem to
    > be identified by a cid of either 1176718828 or 1176851105, whilst
    > email addresses are identified by a cid of 1176015743, first names by
    > 1176015500, last names by 1176015691, request info by 1177590862 and
    > so on. It is my guess that the cid of 1176718828 represents one type
    > of date (e.g. creation date) whilst 1176851105 may represent last
    > update date (note that these cannot be the correct descriptions as all
    > the dates are in the future.
    >
    > I will craft you a self join query that looks at the 1176718828 type
    > dates, but you need to identify which of the dates it is that are
    > relevant to you.[/ref]

    Thanks. 1176851105 is the other date Im interested in but of course I
    can just replace 1176718828 with it.

    I consider myself good at SQL and spent quite a long time trying to
    get my head around it so if you can do it I'll be in awe at your
    skills :).

    skinhat Guest

  10. #10

    Default Re: SQL statement to select from a drupal webform table

    On 1 May, 12:59, skinhat <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Thanks. 1176851105 is the other date Im interested in but of course I
    > can just replace 1176718828 with it.
    >
    > I consider myself good at SQL and spent quite a long time trying to
    > get my head around it so if you can do it I'll be in awe at your
    > skills :).- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Shucks! Fancy being in awe of little-ole me!

    SELECT
    DISTINCT `w1`.`sid`
    FROM `webf` `w1`
    JOIN `webf` `w2` ON `w2`.`cid` = '1176718828'
    AND `w1`.`sid` = `w2`.`sid`
    AND `w2`.`no` = '2'
    JOIN `webf` `w3` ON `w3`.`cid` = '1176718828'
    AND `w1`.`sid` = `w3`.`sid`
    AND `w3`.`no` = '0'
    JOIN `webf` `w4` ON `w4`.`cid` = '1176718828'
    AND `w1`.`sid` = `w4`.`sid`
    AND `w4`.`no` = '1'
    WHERE `w2`.`data` = '2007'
    AND `w3`.`data` = '12'
    AND `w4`.`data` = '8'

    Captain Guest

  11. #11

    Default Re: SQL statement to select from a drupal webform table

     

    Excellent. Thanks. I don't really get it but it works.

    skinhat Guest

  12. #12

    Default Re: SQL statement to select from a drupal webform table

    On 1 May, 14:09, skinhat <com> wrote: 

    >
    > Excellent. Thanks. I don't really get it but it works.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    An opportunity to improve your "good" SQL skills then ;-)
    This is really a quite standard use of the JOIN syntax.

    Captain Guest

  13. #13

    Default Re: SQL statement to select from a drupal webform table

    On May 1, 11:15 pm, Captain Paralytic <com> wrote: [/ref]
    > [/ref]


    >
    > An opportunity to improve your "good" SQL skills then ;-)
    > This is really a quite standard use of the JOIN syntax.[/ref]

    My mother tells me I'm good at SQL :)

    skinhat Guest

  14. #14

    Default Re: SQL statement to select from a drupal webform table

    On 1 May, 14:41, skinhat <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > My mother tells me I'm good at SQL :)- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Ahh mothers, you've got to love 'em.

    Captain Guest

  15. #15

    Default Re: SQL statement to select from a drupal webform table

    On 1 May, 15:11, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Ahh mothers, you've got to love 'em.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    BTW, when I said it was a standard use of the JOIN syntax, I mean't
    that it was not as convoluted as the ubiqutious "Strawberry Query"

    Captain Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. Replies: 1
    Last Post: September 15th, 05:46 PM
  3. Replies: 0
    Last Post: July 24th, 01:01 PM
  4. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  5. Replies: 2
    Last Post: July 2nd, 05:33 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139