To make it simple I have table with columns:
id | title | image | video
where image and video are: name-of-image.jpg/name-of-video.mp4 or NULL.
How can I select first 3 rows only containing image and then 1 row containing video and so on, like:
1 image
2 image
3 image
4 video
5 image
6 image
7 image
8 video
I’m using PHP/MySQL. I need to take max 1000 rows and then just echo rows, my query is:
SELECT * FROM `stories` WHERE (`status`='PUBLISHED') AND (`language_id`='3') ORDER BY `date_published` DESC LIMIT 1000
I need to implement what I wrote above, to return first 3 rows containing image and next 1 containing video
SELECT *
FROM stories
WHERE status = 'PUBLISHED'
AND language_id = 3
AND image IS NOT NULL
UNION
SELECT *
FROM stories
WHERE status = 'PUBLISHED'
AND language_id = 3
AND video IS NOT NULL
ORDER
BY date_published DESC LIMIT 1000
if you’re picking non-null images and videos, the additional WHERE conditions are needed
note this uses UNION instead of UNION ALL so it will eliminate dupes where both the image and the video are not null
after elimination of dupes, the ORDER BY is executed next, so you are sure to have at least 1000 rows you can print
then just loop over the results with php
i hear you php guys are pretty good with arrays, so you should be able to pull out your img-img-img-vid-img-img-img-vid sequence easily