Is it possible to fetch data from other tables inside a foreach loop after a certain amount of resul-CodePudding

I am looking forward to know if it's possible. What I want is, suppose, I have a table_1 with 10 rows and I have two other tables with 2 rows each. Now, my foreach loop fetching 10 rows from table_1, but after every 2nd rows I want to display one row from table_2 and after 4th row I want to display one row from table_3. How am I going to achieve this?

Please see the attached image.

CodePudding user response：

PHP Approach

Assuming your data looks like this:

$table1 = [
    'Row 1 from table_1',
    'Row 2 from table_1',
    'Row 3 from table_1',
    'Row 4 from table_1',
    'Row 5 from table_1',
    'Row 6 from table_1',
    'Row 7 from table_1',
    'Row 8 from table_1',
    'Row 9 from table_1',
    'Row 10 from table_1',
];

$table2 = [
    'Row 1 from table_2',
    'Row 2 from table_2'
];

$table3 = [
    'Row 1 from table_3',
    'Row 2 from table_3'
];

One way to approach this would be to do a loop and refer to the index number to decide whether to append an array element from one of the other tables based on the modulus operator (i.e. the remainder when dividing).

One way to write such logic would be like so:

$out = [];

foreach($table1 as $k=>$v)
{
    $out[] = $v;
   if( $k % 4 === 1 && count($table2) > 0)
   {
     $out[] = array_shift($table2);
   }
    if( $k % 4 === 3 && count($table3) > 0)
    {
        $out[] = array_shift($table3);
    }
}

print_r($out);

For me the above gives me an output, which I understand to be your desired output:

Array
(
    [0] => Row 1 from table_1
    [1] => Row 2 from table_1
    [2] => Row 1 from table_2
    [3] => Row 3 from table_1
    [4] => Row 4 from table_1
    [5] => Row 1 from table_3
    [6] => Row 5 from table_1
    [7] => Row 6 from table_1
    [8] => Row 2 from table_2
    [9] => Row 7 from table_1
    [10] => Row 8 from table_1
    [11] => Row 2 from table_3
    [12] => Row 9 from table_1
    [13] => Row 10 from table_1
)

Side note: array_shift will destroy the array for table2 and table3. If that's an undesired side effect, might try revising the code a bit such as cloning the array first.

Mysql Approach

Alternatively, you can also solve this at the mysql level. Basically you'd join the table together, either through a union or a join. Then you'd need a way to sort it in a special fashion. A good way to do this is to introduce a new column on the fly which would represent some sort of calculation based on the primary key.

Assuming your tables look like this:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `table1` VALUES (1,'Row 1 from table_1'),(2,'Row 2 from table_1'),
(3,'Row 3 from table_1'),(4,'Row 4 from table_1'),
(5,'Row 5 from table_1'),(6,'Row 6 from table_1'),
(7,'Row 7 from table_1'),(8,'Row 8 from table_1'),
(9,'Row 9 from table_1'),(10,'Row 10 from table_1');


CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `table2` VALUES (1,'Row 1 from table_2'),(2,'Row 2 from table_2');

CREATE TABLE `table3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `table3` VALUES (1,'Row 1 from table_3'),(2,'Row 2 from table_3');

You could use a query like the following:

SELECT data FROM (SELECT (id   floor((id - 1) / 2)) AS order_id, data FROM table1
UNION 
SELECT ( (6 * (id-1))  3) AS order_id, data FROM table2
UNION 
SELECT ( (6 * (id-1))  6) AS order_id, data FROM table3) t4
ORDER BY order_id;

The logic here is basically since the tables have the same sort of structure, we can UNION their results together. Then basically we want table one to have a sorting index like this:
1,2, 4,5, 7,8, 10,11, 13,14.

By doing this, we allow room for every 3rd index to be able to point at something else.
As far as the gaps, we will want 3, 6, 9, and 12 to point at the other two tables. 3 and 9 should point at table two. 6 and 12 should point at table three. Then we just do some math based on the primary key and based on the table to wind up with a unique order_id. Then just sort by the order_id.

Results:

Row 1 from table_1
Row 2 from table_1
Row 1 from table_2
Row 3 from table_1
Row 4 from table_1
Row 1 from table_3
Row 5 from table_1
Row 6 from table_1
Row 2 from table_2
Row 7 from table_1
Row 8 from table_1
Row 2 from table_3
Row 9 from table_1
Row 10 from table_1