MySQL / Condition per row

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

In that way I can simply select 1000 stories and in PHP pull my sequence and there is not enough video just show stories with image only.


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.