CODEWITHSUNDEEP
phpmysqlsubquery
Drew
Drew

Reputation: 6862

MySQL Combining queries into one

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!

Upvotes: 2

Views: 172

Answers (2)

nikc.org
nikc.org

Reputation: 16952

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

Upvotes: 0

ZFA
ZFA

Reputation: 26

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

Upvotes: 1

Related Questions