MySQL call that combines two Tables with several identical rows

Question

My Question aims to an specific SQL call that combines two Tables.

I have two tables which I can not combine into one, because one is imported from the two every day from a file. What I need exactly, I show you here:

SQL Table 1 snippet:

SQL Table 2 snippet:

Inside the PHP source code i need this result:

Array(122, 146, 234, 400)  
Array(John, Dave, Sam, Jana)  
Array(SOME, SOME, Array(ONE, TWO, THREE, FOUR, FIVE), SOME)  
Array(Some Text…, More…, Array(More…, More…, More…, More…, More…), More…)  

The rows of "num" must each match with Table 1, together:

Array(male, male, male, female)  
Array(false, true, false, true)  

What i found is this Question: MySQL SELECT AS combine two columns into one

So i can use CONCAT to combine ONE, TWO, THREE, FOUR, FIVE. I can call each table alone and build the result in PHP or call both tables together. What is the better way?

What I don't know is a nice overall concept. Can anybody give me an example please?

EDIT:

I understand the syntax now. This is a nice short solution:

mysql > SELECT DISTINCT num, name, GROUP_CONCAT(DISTINCT prefs ORDER BY prefs SEPARATOR ', ') AS array FROM `table1` AS t1 INNER JOIN `table2` AS t2 ON t1.num = t2.num GROUP BY num ORDER BY num

Answer 1

To get the data you need, you can use a simple SELECT...JOIN query:

select
  T1.num,
  T1,name,
  T2.prefs,
  T2.text,
  T1,gender,
  T1.opt,
from
  Table1 as T1
inner join
  Table2 as T2 on T1.num=T2.num
order by
  T1.num

From here, you could build the arrays by processing each row as you read it from the DB (using PDO with associative fetch)

while ($row=$query_result->fetch(PDO::FETCH_ASSOC))
{
  $a1[]=$row['num'];
  $a2[]=$row['name'];
  $a3[$row['num']][]=$row['prefs']; // Store values in a subarray according to num
  $a4[$row['num']][]=$row['prefs']; // Store values in a subarray according to num
  $a5[]=$row['gender'];
  $a6[]=$row['opt'];
}

Unfortunately, you will end up with multiple entries in arrays 1,2,5,6. To cure this, use array_unique:

$array1=array_unique($a1);
$array2=array_unique($a2);
$array5=array_unique($a5);
$array6=array_unique($a6);

If you don't want to use array_unique, you could use:

while ($row=$query_result->fetch(PDO::FETCH_ASSOC))
{
  $a1[$row['num']]=$row['num']; // Gets round the array_unique
  $a2[$row['num']]=$row['name'];// Gets round the array_unique
  $a3[$row['num']][]=$row['prefs']; // Store values in a subarray according to num
  $a4[$row['num']][]=$row['prefs']; // Store values in a subarray according to num
  $a5[$row['num']]=$row['gender']; // Gets round the array_unique
  $a6[$row['num']]=$row['opt']; // Gets round the array_unique
}

But then you would need to use array_values to extract just the values:

$array1=array_values($a1);
$array2=array_values($a2);
$array5=array_values($a5);
$array6=array_values($a6);

To get the resultant multi-dimensional arrays, you have to do a bit more processing:

$array4=array();
$array5=array();

//Loop through each sub array
foreach ($a4 as $subArray)
{
  //If there is only one element, store that value, otherwise store the sub-array
  $array4[]=(count($subArray)>1) ? array_values($subArray) : array_shift($subArray));
}

//Loop through each sub array
foreach ($a5 as $subArray)
{
  //If there is only one element, store that value, otherwise store the sub-array
  $array5[]=(count($subArray)>1) ? array_values($subArray) : array_shift($subArray));
}

(You could probably get flash by using variable variables but for clarity I went with separate loops)

Once you have the data in the arrays, you can do what you want with it. If you want to build PHP source code from it, use var_export($arrayName,true) and capture that into a variable

None of this code is actually tested but it will, hopefully, point you in the right direction