SQL statement to select from a drupal webform table - MySQL

[skinhat]

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.

[Captain]

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

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

[skinhat]

 

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

[J.O.]

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

[skinhat]

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.

[Captain]

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!

[skinhat]

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.

[Captain]

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.

[skinhat]

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 :).

[Captain]

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'

[skinhat]

 

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

[Captain]

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.

[skinhat]

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 :)

[Captain]

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]

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"