Mysql select list of items (repeat until certain amount)

Question

I have the following structure:

Table: products

id, name, sort_order

Let's say I only have 5 products, but I want 20.

How would I loop through to get 20 results?

Additionally, I'll need to start at a specific sort_order. So let's say I have

1,2,3,4,5

and I want to get 20 results and start at 3. It should end up as:

3,4,5,1,2,3,4,5,1,2,3,4,5,1,2,3,4,5,etc.

Any ideas? I'm totally lost.. thank you!

Answer 1

$how_many = 20;
$counter = 0;
$rows = array();

$result = mysql_query("Your query");
while($row == mysql_fetch_row($result)) {
  $rows[] = $row;
}

$expanded = array();
for($i = 0; $counter < $how_many; $counter++) {
  $expanded[] = $rows[$i];
  $i++;
  if($i == count($rows)) {
    $i = 0;
  }
}

And now $expanded is filled with those 5 rows for 4 times.

Edit: and don't forget to adjust $i to your start element. For your example that would mean $i = 2 (third element).

Answer 2

create a table named abc and use this procedure to populate the table

CREATE PROCEDURE `populator`(i INT)
BEGIN
   DECLARE existing int;
   DECLARE counting int;
   DECLARE limitation int default i;
   SELECT COUNT(*) INTO existing FROM asd;
   theLoop: LOOP
     SELECT COUNT(*) INTO counting FROM abc;
     IF (existing + counting) >  i
       THEN SET limitation = i - counting;
     END IF;
     IF counting >= i
       THEN LEAVE theLoop;
       ELSE
         SET @sql = CONCAT("INSERT INTO abc SELECT id from asd LIMIT ", limitation);
         PREPARE s1 FROM @sql;
         EXECUTE s1;
         DEALLOCATE PREPARE s1;
     END IF;
   END LOOP theLoop;
END

remember to rename the tables. i used asd as source table and abc as destination table. if you do the concatenation thing right you can even write a procedure that works with temporary tables.

Answer 3

You can do this in MySQL by cheating:

SELECT
   t1.id
FROM
   products t1
   CROSS JOIN products t2
LIMIT
   2, 20

Like the others, I'm not 100% sure what you want. You want to show the same products over again until 20 times?