Condition per row

Discussion in 'MySQL' started by Kuna, Oct 11, 2015.

0
  1. #1
    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
     
    Last edited: Oct 11, 2015
    Kuna, Oct 11, 2015 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    I think you'll have to do these separately. How many total rows are we talking about? (I'm thinking you could just fetch all in both categories if it's not too many rows, and parse it in your server-language)
     
    PoPSiCLe, Oct 11, 2015 IP
  3. Kuna

    Kuna Well-Known Member

    Messages:
    426
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    105
    #3
    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
    Code (markup):
    I need to implement what I wrote in my 1st post, to return first 3 rows containing image and next 1 containing video
     
    Last edited: Oct 11, 2015
    Kuna, Oct 11, 2015 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    Can you not handle this in the application? It's irrational and slow to do this in the database but if you have to do it, I think you should be able to with a stored procedure using a loop and a variable that acts as a counter.
     
    jestep, Oct 14, 2015 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #5
    2 queries
    1 to select the images
    1 to select the videos
    then
    
$vidCounter = 0;
$vidMax = count($video);
foreach($images as $k => $row){
//echo out the row
if ($k %3 == 2 && $k < $vidMax){
//echo out $video[$counter] as a table row
$vidCounter++;
}
}
    PHP:
     
    sarahk, Oct 14, 2015 IP
  6. Kuna

    Kuna Well-Known Member

    Messages:
    426
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    105
    #6
    Thanks. I'll do it in PHP, not database
     
    Kuna, Oct 21, 2015 IP
  7. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #7
    Stick with a single query though and use logic to display either the video or the image.
     
    jestep, Oct 22, 2015 IP
  8. Kuna

    Kuna Well-Known Member

    Messages:
    426
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    105
    #8
    I got it, thank you. Since situation is specific and we need some custom implementation and logic I'll have to combine PHP and different MySQL queries
     
    Kuna, Oct 25, 2015 IP