2

Let me explain what I am trying to do. First, here is a preview of my results so far:

enter image description here

My Query:

SELECT slides.name as slide, filename, time, sort_id 
FROM `slide_sort` 
INNER JOIN slides 
    ON slide_sort.slide_id = slides.id 
WHERE slide_sort.user_id = 1

So each slide is a page that is already created, but each user will be able to specify how long to stay on that page and what the next "slide" will be.

So the HTML looks like this: (dashboard.php)

<meta http-equiv="refresh" content="40;url=did-you-know.php">

Right now it is hard coded in but I want to be able to pull it from the database.

So on the dashboard.php file I could have something at the top like: $currentSlide = "Dashboard"; and that way I could add to the query AND slide = $currentSlide so that way I could see that the time is 40 seconds for that slide, so I could stick that in to the content="$time". But my question is, how can I add another query to get the NEXT sort_id so sort_id + 1 and get that filename, so I would have the following information:

slide - time - next_filename

Dashboard - 40 - did-you-know.php

BUT I also need to see if it's the last sort_id and if it is, then the next slide needs to start over at #1.

Does this make sense?

If anyone could help me get in the right direction I would really appreciate it.

Thanks!

Improve this question

2 Answers 2

Reset to default
1

You can retrieve the NEXT sort id with something like this:

SELECT slides.name as slide, filename, time, sort_id  
FROM `slide_sort` 
INNER JOIN slides 
ON slide_sort.slide_id = slides.id 
WHERE slide_sort.user_id = 1 and slide_sort.sort_id = ($sort_id + 1)

You can retrieve the LAST sort id with something like this:

SELECT sort_id 
FROM `slide_sort` 
INNER JOIN slides 
ON slide_sort.slide_id = slides.id 
WHERE slide_sort.user_id = 1 order by sort_id desc limit 1

You can check if the this is the last slide with something like this:

if ($sort_id == $last_sort_id)

If it is, you can retrieve the FIRST sort id record:

SELECT slides.name as slide, filename, time, sort_id  
FROM `slide_sort` 
INNER JOIN slides 
ON slide_sort.slide_id = slides.id 
WHERE slide_sort.user_id = 1 order by sort_id ASC limit 1
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Next id is often not the current + 1, but the smallest one with id > of current one
$sort_id + 1 assumes that the sequence is always sequential. You should say slide_sort.sort_id > $sort_id and then LIMIT 1. To retrieve the last sort_id, you would use MAX(sort_id), MIN(sort_id) to fetch the first one.
@zerkms: OP seemed to state that we can assume current+1("how can I add another query to get the NEXT sort_id so sort_id + 1").
@ZFA: you should never assume that, it is a bad habit. No one should ever rely on PK sequential order
0

You could limit your query results to only 2 rows, including the current slide. That way, you'll get the slide to redirect to, as well as the next slide. Of course, you still need to iterate or use fixed indices, since you need to be working with multiple rows. And you need to check if you actually are getting the 2 rows to see if there is in fact a next slide still available.

That said, what I recommend is retrieving the complete set of slides with your current query and iterating over it to find what you need. Because...

  1. It's a simple query and mysql likes simple queries. Derived tables and subqueries are akin to slow the query down, and you don't want that. Even if it seems like premature optimization.
  2. The dataset is unlikely to be very large, and O(N) algos are cheap
  3. It has the benefit of you being able to present a navigation within the whole slide set
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.