Getting around sub-queries

[tidrion]

Ok, here is the situation. We are on mysql 4.0.17 and are waiting to update to 4.1 until it is out of alpha. What I have is a biweekly magazine that could run any number of stories an issue. We are currently displaying the last 3 issues. They had limited to 3 stories per issue online but are starting to move away from that. I obviously cannot perform a sub-query on this version of mysql so I was wondering if my "work around" is a sane one.

What I've done is set up two queries, the first pulls the max issue number where the date is now or greater. I then used php to calculate back 3 issues to get my "array" Using those value I run the second query and pull each stories title, author and abstract for those 3 issues. My question is, is there a better way or will this work.

Ignore the design of this please...

http://www.cappers.com

I haven't gotten to that stage, I have only recently taken over.

 

[r937]

instead of using php to "calculate" (how would you do this, anyway?) the last 3 issue ids, use

select issueid, issuedate
from yourtable
order by issuedate desc
limit 3

then use those 3 values to drive the 2nd query

this is quite reasonably efficient

rudy

http://r937.com/

SQL Consulting

 

[tidrion]

The reason I didn't do that was because that gets me 3 numbers, I only need two, I need the first and the last for my where clause. In php I simply did this

$maxnum = result from max query
$minnum = $maxnum - 3

Then I used the $maxnum and $minnum to fill my where clause in the query.

select article, title, author from stories where issuedate <= $maxnum and issuedate >= $minnum order by issuedate desc

 

[tidrion]

Now I know that I can always jump to the third, but that would mean having to change the value in 2 places if I ever needed to go back further. I would have to change my limit clause and my result in the second query.

 

[r937]

okay, you know your app better than i do

i just know that calculating something outside the database is making an assumption

and when the assumption centers around dates, it is really risky

besides, it's a biweekly issue, so you can't just subtract 2 days (as your example shows), you have to subtract 28 days for 2 issues

and if you're subtracting issue numbers, as long as your issue numbers are sequential, with no gaps, you're fine

me, i'd rather write a generic query that was guaranteed to work no matter which dates the issues were issued on and whether or not there was any hiccup in the issue numbers

but hey, it's your code, go right ahead, do it your way







rudy

http://r937.com/

SQL Consulting

 

[tidrion]

The issue numbers are relative to the web site. It is asigned when I get it to post. So they will always go in order. Each has an issue number in order so the math is always constant. I apreciate the feedback, but my main concern was the 2 seperate queries and not as much how the 2 where generated.

I'm sorry I wasn't more specific