MySQL Query - Difficult or impossible ?

[Jaramiah]

Hi,

 

I'm struggling big time to write a particular query. I have data in a database table that looks like the following.

 

ID : The table id number, auto-increment

 

Field 1 : Can vary but is 9 for the entries I wish to pull (so only showed those)

 

Field 2 : Field 3 goes from 1 to 6 (id from another table) and every set of 6 is collated by Field 2, hence the first set of Field3 = 1 to 6 are collated by Field2=1, the second set of Field3 = 1 to 6 are collated by Field2=2 etc

 

Fields 4 & 5 : Contain the data I enter.

  - When Field3 is 1 or 2, a date value is being stored and so I store the unix timestamp in Field 5. Hence Field 5 is zero for Field3 = 3 to 6.

  - When Field3 is 3 to 6, Field5 is always zero and Field4 contains either the word Yes or is null.

 

I have tried to display this below :

 

ID --- Field1 --- Field2 ---- Field3 --- Field4 ------- Field5

1 ------- 9 -------- 1 --------- 1 ------- null ------ 123456789

2 ------- 9 -------- 1 --------- 2 ------- null ------ 985689565

3 ------- 9 -------- 1 --------- 3 ------- null ----------- 0

4 ------- 9 -------- 1 --------- 4 ------- Yes ----------- 0

5 ------- 9 -------- 1 --------- 5 ------- null ----------- 0

6 ------- 9 -------- 1 --------- 6 ------- null ----------- 0

 

7 ------- 9 -------- 2 --------- 1 ------- null ------ 457854258

8 ------- 9 -------- 2 --------- 2 ------- null ------ 223654585

9 ------- 9 -------- 2 --------- 3 ------- null ----------- 0

10 ------ 9 -------- 2 --------- 4 ------ Yes ------------ 0

11 ------ 9 -------- 2 --------- 5 ------- null ----------- 0

12 ------ 9 -------- 2 --------- 6 ------- null ----------- 0

 

13 ------ 9 -------- 3 --------- 1 ------- null ----- 252525263

14 ------ 9 -------- 3 --------- 2 ------- null ----- 123655258

15 ------ 9 -------- 3 --------- 3 ------- null ----------- 0

16 ------ 9 -------- 3 --------- 4 ------- null ----------- 0

17 ------ 9 -------- 3 --------- 5 ------- null ----------- 0

18 ------ 9 -------- 3 --------- 6 ------- null ----------- 0

 

19 ------ 9 -------- 4 --------- 1 ------- null ----- 123456789

20 ------ 9 -------- 4 --------- 2 ------- null ----- 123456789 etc etc

 

When I do the query I want back which DISTINCT values of Field2 (e.g. Field2 = 1 and 2 but not 3) contain certain info :

- Where Field3 = 1 and Field5 is between two timestamp values and,

- Where Field3 = 4 and Field4 = Yes

 

In essence I think my problem is due to me trying to search both rows & columns at the same time. I have tried queries within queries but cannot get that to work properly, may be my scripting skills.

 

Assuming I have explained the essence of the problem correctly, hopefully someone will understand my problem and whether its impossible or not ?

 

I'm close to restructuring the database but am dreading the thought !

 

Many thanks.

 

Jeremiah.

[craygo]

I see what you want to display above but what does the data look like in the database.. Are their multiple rows with the same thing?

 

Example

 

1, 9 , 1 , 1 , NULL , 12345

1, 9 , 1 , 1 , Yes , 124556877

 

??

 

Ray

[roopurt18]

Without know which queries you've tried and what they returned, or without an example of the data and the set you'd like returned, it's hard to answer your question.

 

Have you tried anything like:

 

SELECT DISTINCT
  `Field2`
FROM `myTable`
WHERE
  `Field3`=1 AND
  /* You could use BETWEEN here, I didn't fill like */
  /* doing all the casting /shrug */
  `Field5`>=timestamp1 AND
  `Field5`<=timestamp2 AND
  `Field3`=4 AND
  `Field4`='Yes'

[Jaramiah]

Hi,

 

The type of query I would like to type, but I know it doesn't work, would require a few ANDS to come right :

 

SELECT DISTINCT

  `Field2`

FROM `myTable`

WHERE

  ( 'Field3'=1 AND (Field5>=timestamp1 AND Field5<=timestamp2) )

  AND

  ( 'Field3'=3 AND Field4=Yes )

 

... this number of ANDS doesn't work though, i.e. the brackets pretty much become meaningless.

 

 

In terms of what data I'd like returned, in the example I gave :

- Assume that is what the database table looks like (ID = 1 to 18 say), i.e. that was the total data set in it.

- I would then want returned the rows for Field2 = 1 and 2 because (fictitiously) the timestamps are in the right range and there is a Yes in Field4 (i.e. where Field3 = 4).

- By the time we get to Field2 = 3, Field4 isn't = Yes so its not returned.

 

What I'm really interested in are the Distinct values for Field2. So in essence I would want my query to tell me that Field2 = 1 and Field2 = 2 as the query output.

 

Thanks,

 

J.

[roopurt18]

I'm not sure I understand why you couldn't just do it with either the query I wrote or the one you wrote.  Did you try either of them?  What did they return?  If they didn't return the set you wanted, what should have changed in the result set?

[craygo]

can you give me a phpmyadmin dump of your table and data??

[Jaramiah]

Hi,

 

I attach a dummy database table & query page with the data I originally posted about.

 

Hopefully you can create a dummy database called 'query_test' and the sql file can be used to restore the single table 'mytable', then run the page 'test.php'.

 

In the php file I have done several queries which shoudl be self explanatory and explain the problem.

 

Thanks,

 

J.

 

[attachment deleted by admin]

[craygo]

i got your data and page, but still not sure what you want to get back. Won't be able to do what you want with one query. I assume you want the group with field4 = yes but want to return the first row with the timestamp between the values. Rather than distinct use group by

try this out

$yes = "SELECT field2 FROM mytable WHERE field4 = 'Yes'";
$yres = mysql_query($yes);
while($yr = mysql_fetch_assoc($yres)){
$array[] = $yr['field2'];
}
$field2 = implode("', '", $array);
$query3="
SELECT * FROM mytable
WHERE
field1='9' AND field5 >= '1199181600' AND field5 <= '1230717600' AND
field2 IN ('$field2')
GROUP BY field1, field2";

 

Ray

[sasa]

Hi,

 

The type of query I would like to type, but I know it doesn't work, would require a few ANDS to come right :

 

SELECT DISTINCT

  `Field2`

FROM `myTable`

WHERE

  ( 'Field3'=1 AND (Field5>=timestamp1 AND Field5<=timestamp2) )

  AND //change to OR

  ( 'Field3'=3 AND Field4=Yes )

 

... this number of ANDS doesn't work though, i.e. the brackets pretty much become meaningless.

 

change some AND to OR

[Jaramiah]

Thanks for the help so far ... I now get the feeling, in answer to my question, its difficult, not impossible !

 

I have tried the code Craygo suggested and, once I'd worked out what he was doing, was quite impressed ... my scripting knowledge certainly took a leap forward when I realised how you had structured the query.

 

This evening (well, evening here in South Africa) I have adapted you suggested code into my site and managed to use it to replicate what I was already achieving through a clumsier approach. I have 2 more levels of variable to include in this query, so far we have integrated a text one and a date one, I have 2 more text ones to go ... I will plough through when time allows (usually for an hour after the kids are in bed !) and feedback in a day or so.

 

Many, many thanks for the support so far, you have surpassed all expectations :-)

 

Regards,

 

J.

[Jaramiah]

Just an update :

 

With the approach suggested by Craygo I have managed to improve my page loading time from 15 seconds to 5 seconds and, so far I have only included 2 out of the 4 variable sets into my query. When all 4 are in I am hoping for total happiness :-) This has made the site much less frustrating to use, light at the end of the tunnel indeed !

 

Thanks all,

 

J.

 

[Jaramiah]

Feedback : Problem solved using the suggested approach by Ray above. Many thanks.

 

Just a week ago my page loaded in 38 seconds, by Monday it was down to 15, by Wednesday 5 and now Friday, 3.5 seconds ... my site is definitely back to being a pleasure to use.

 

Thanks all for the help !

 

Regards,

 

J.