Variable for LIMIT

Anon

Any clues how to handle a variable within an SQL limit clause?

Here's the deal. I have a db that has dates stored in them, currently I have my limit set to 7...for 7 days in a week. I want all 7 days to appear TOGETHER on one page.

I have NEXT and PREV links for each week which works find.

However, I can have more than one row for a particular day. Hence if I have 2 separate rows for Sunday, then Saturday will be left out of the results.

My guess is I need to somehow determine how many rows I will have for a week (7 days) and then set that value within the LIMIT?

Any up for the challenge...?

Appreciate any feedback at all!!!

Thanks in advance!

Anon

I think it'd be better if you just write a query that will select everything WHERE the date field is within 7 days of what you want. So you'd have like

SELECT * FROM tbl_name WHERE date_field < $date AND date_field > ($date+7)

Obviously, that's simplified. You can't just add 7 to a $date, but you get the idea.

This way, you don't need to worry about how many rows there are.

Diego

Anon

I went this direction, but the problem is I am using NEXT PREV links to traverse to the next page (week).

I need to know how many rows will be displayed so I can limit that amount on the SQL LIMIT clause. If I don't use this LIMIT (or at least I'm not sure how!) my NEXT PREV links will work....

Anon

But you said you want all 7 days to appear together...If you go my way, your Next/Prev links will mean "Next week" and "Previous week", instead of "Next 7 rows" and "Prev 7 rows"...Why do you want to limit the number of rows? If you limit them, then the 7 days won't be together.

Diego

Anon

GREAT! Ok, I think I understand where you going. I'll give it a shot and post my findings...

Thanks much!!

Anon

Diego,

Struggling with this one...BIG TIME!!

I still am having trouble trying to figure out how to pass the LIMIT value needed for my links.

I have everything working, but cannot get a week to show up together since there are more than one rows per day....

Can you provide some further input as to how to accomplish my NEXT PREV links having the proper values.....

Below is my SQL:
/===================================================/
/==Query and fetch array with the limit and offset==/
/===================================================/

$query = "select log2.rundate, DATE_FORMAT(log2.rundate, '%W, %M %d, %Y') as fdate, log.starttime, log.distance, log.runtime, log.runpace, log.course, log.shoes, log.workout_type, log.comments, log.weight, log.avghr from
log2 LEFT JOIN log ON log2.rundate = log.rundate
order by rundate, dayofweek";

And the limit I am using:
/==========================================/
/==Append the limit and offset to the sql==/
/==========================================/
$query.=" limit $thisOffset, $recordLimit";

Now this $recordLimit is used elsewhere to determine # of pages in total and when to display Next Previous links.

For an example of my troubles you can see:
http://www.ndorfnz.com/weeklogdate.php?thisOffset=2142&lineIndex=2142

Even though I have 2 runs for Sunday, I want to be able to still display through Saturday...

Greatly appreciate any input or guidance you can provide!!!

Thanks.