[SOLVED] odbc query syntax problem.

[deiffert]

Hello,

I'm a total rookie at the data query thing.

 

I'm actually querying a filemaker database via odbc, but I doubt that matters.

 

Here's the query I have that isn't working:

 

$retailerquery = "select DISTINCT Retailer from MainTable where \"Product\" = '$ProductId' and

((\"Week of the Year\" = $week01 and \"Year\" = $week01year) or (\"Week of the Year\" = $week02 and \"Year\" = $week02year) or (\"Week of the Year\" = $week03 and \"Year\" = $week03year) or (\"Week of the Year\" = $week04 and \"Year\" = $week04year) or (\"Week of the Year\" = $week05 and \"Year\" = $week05year) or (\"Week of the Year\" = $week06 and \"Year\" = $week06year) or (\"Week of the Year\" = $week07 and \"Year\" = $week07year) or (\"Week of the Year\" = $week8 and \"Year\" = $week08year) or (\"Week of the Year\" = $week09 and \"Year\" = $week09year) or (\"Week of the Year\" = $week10 and \"Year\" = $week10year) or (\"Week of the Year\" = $week11 and \"Year\" = $week11year) or (\"Week of the Year\" = $week12 and \"Year\" = $week12year))";

 

Nasty I know. The first part works just fine, and the second part of the query (the really repetitive part) I'm just trying to do this basically:

 

select Retailer where the 'week of the year' and 'year' equal any of these 12 week/year combinations that I have.

 

It can match ANY of the 12, that's where all those 'or's come in. I'm sure my syntax is wrong. I'm an old C++ programmer so I always try to do things how I used to do them.

 

Any ideas?

 

Thanks,

- David

[Barand]

I don't know Filemaker syntax, but the logic looks OK.

 

I can give a couple of tips for the the next table you design

 

1 ) Don't have column names with spaces then you don't have to wrap them in "...", [...] or `...` depending on the flavour of your DBMS

 

2 ) Read up on normalization.

 

Have you checked for error messages?

[deiffert]

Hi,

Thanks so much for the response. I'll definitely do what you said.

 

The problem is that it's kicking out an error:

 

Warning: odbc_exec() [function.odbc-exec]: SQL error: [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL, SQL state S1000 in SQLExecDirect in C:\Documents and Settings\Deiffert\My Documents\Active\Slosh\fmps\chart.php on line 126

 

Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource in C:\Documents and Settings\Deiffert\My Documents\Active\Slosh\fmps\chart.php on line 130

[Barand]

Not the most informative error message.

 

What does it look like if you

 

echo $retailerquery ;

[deiffert]

It's outputting:

 

select DISTINCT Retailer from MainTable where "Product" = 'bbb' and (("Week of the Year" = 47 and "Year" = 2007) or ("Week of the Year" = 48 and "Year" = 2007) or ("Week of the Year" = 49 and "Year" = 2007) or ("Week of the Year" = 50 and "Year" = 2007) or ("Week of the Year" = 51 and "Year" = 2007) or ("Week of the Year" = 52 and "Year" = 2007) or ("Week of the Year" = 1 and "Year" = 2008) or ("Week of the Year" = and "Year" = 2008) or ("Week of the Year" = 3 and "Year" = 2008) or ("Week of the Year" = 4 and "Year" = 2008) or ("Week of the Year" = 5 and "Year" = 2008) or ("Week of the Year" = 6 and "Year" = 2008))

 

as the contents of that variable.

Thanks again for the help!

[Barand]

or ("Week of the Year" = and "Year" = 2008)

 

2 is missing

[deiffert]

Oh my god! It works!

I was ready to blow my brains out! (figuratively speaking)

Stupid oversight. I left out one stupid character.

 

Thank you so so much.

 

SOLVED!

[Barand]

I take back what I said about normalizing, I thought you were holding a whole series of weeks and years in a single record.

 

Also it would simplify to

 

$q = "select DISTINCT Retailer from MainTable 
        where \"Product\" = 'bbb' 
        and ((\"year\" = '2007' and \"Week of the Year\" between 47 and 52)
        or (\"year\" = '2008' and \"Week of the Year\" between 1 and 6))";