MYSQL RAND() will not generate correct listing

Question

I have a problem getting the right results out of my select. Table wp_rdp_category_images has 3 columns: id (unique) - category_name - category_image.

In the select below I need as result max 4 x category_name and from each category_name, 1 category_image.

Can someone help me correct this select? Would be very nice. Thanks in advance.

Here the php code:

function show_game_category_images_func() {
    $content = '';
    $i = 1;
    $record = $GLOBALS['wpdb']->get_results('
    SELECT * FROM wp_rdp_category_images
    ORDER BY `category_name`, RAND()
    LIMIT 4 
    ');

    if($record){
        foreach($record as $key => $rec){                   
            // if($key == 'picture' && $rec != null && $rec != '')     
            $rec = '<img src="'.$upload_url.$record->category_image.'" width="150" height="150">';

            if ($i == 1) {
                echo '<span><img src="'.$upload_url.$rec->category_image.'" ></span>';  // even
            }
            if ($i == 2) {
                echo '<span><img src="'.$upload_url.$rec->category_image.'" ></span><br>';  // even
            }
            else {
                echo '<span><img src="'.$upload_url.$rec->category_image.'" ></span>';   // odd
            }
            $i++;
        }
    }   
    return $content;
}
add_shortcode('show_game_category_images', 'show_game_category_images_func');

My SQL:

SELECT * FROM `wp_rdp_category_images`    
ORDER BY `category_name`, RAND() LIMIT 4

Here is a test table:

CREATE TABLE IF NOT EXISTS `wp_rdp_category_images` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(32) NOT NULL,
  `category_image` varchar(64) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

INSERT INTO `wp_rdp_category_images` (`id`, `category_name`, `category_image`) VALUES
(1, 'natuur', 'natuur1.jpg'),
(2, 'natuur', 'natuur4.jpg'),
(4, 'aarde', 'aarde3.jpg'),
(5, 'sport', 'sport2.jpg'),
(6, 'aarde', 'aarde3.jpg'),
(7, 'auto', 'autogrijs.jpg'),
(8, 'auto', 'autowit.jpg'),
(9, 'auto', 'autoblack.jpg'),
(10, 'auto', 'autoroodzwart.jpg'),
(11, 'aarde', 'aarde1.jpg'),
(12, 'aarde', 'aarde2.jpg'),
(13, 'sport', 'sport4.jpg'),
(14, 'sport', 'sport3.jpg'),
(15, 'sport', 'sport1.jpg'),
(16, 'natuur', 'natuur2.jpg'),
(17, 'natuur', 'natuur3.jpg'),
(18, 'people', 'people1.jpg'),
(19, 'people', 'people2.jpg'),
(20, 'people', 'people3.jpg'),
(21, 'people', 'people4.jpg'); 

Answer 1

You could use this query:

select     category_name,
           (select   category_image
            from     wp_rdp_category_images
            where    category_name = main.category_name
            order by rand()
            limit    1) category_image
from       wp_rdp_category_images main
group by   category_name
order by   rand()
limit      4

SQL Fiddle

If you need all columns in the result set, then use this variant:

select     main.*
from       (
            select     category_name,
                       (select   id
                        from     wp_rdp_category_images
                        where    category_name = main.category_name
                        order by rand()
                        limit    1) id
             from       wp_rdp_category_images main
            ) sub
inner join  wp_rdp_category_images main
        on  main.id = sub.id
group by    main.category_name
order by   rand()
limit      4

SQL Fiddle

PHP Code

Your PHP has an issue so that the first row is repeated. Note that when $i is 1 you will execute both the part in the first if, but also the part in the else of the second if.

You need to put an else before the second if:

if ($i == 1) {
    echo '<span><img src="'.$upload_url.$rec->category_image.'" ></span>';  // even
} else if ($i == 2) { // !!!! ADDED "else" here!
    echo '<span><img src="'.$upload_url.$rec->category_image.'" ></span><br>';  // even
}
else {
    echo '<span><img src="'.$upload_url.$rec->category_image.'" ></span>';   // odd
}

But your comments seem to suggest that you want to make two columns. In that case it is better to write it like this:

echo '<span><img src="'.$upload_url.$rec->category_image.'" ></span>';  // all
if ($i % 2 == 0) echo '<br>';  // even only