php - MySQL call that combines two Tables with several indentical rows -

php - MySQL call that combines two Tables with several indentical rows -

my question aims specific sql phone call combines 2 tables.

i have 2 tables can not combine one, because 1 imported 2 every day file. need exactly, show here:

sql table 1 snippet:

sql table 2 snippet:

inside php source code need 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 table 1, together:

array(male, male, male, female) array(false, true, false, true)

what found question: mysql select combine 2 columns one

so can utilize concat combine one, two, three, four, five. can phone call each table lone , build result in php or phone call both tables together. improve way?

what don't know nice overall concept. can give me illustration please? lot!

edit :

i understand syntax now. nice short solution:

mysql > select distinct num, name, group_concat(distinct prefs order prefs separator ', ') array `table1` t1 inner bring together `table2` t2 on t1.num = t2.num grouping num order num

to info need, can utilize simple select...join query:

select t1.num, t1,name, t2.prefs, t2.text, t1,gender, t1.opt, table1 t1 inner bring together table2 t2 on t1.num=t2.num order t1.num

from here, build arrays processing each row read db (using pdo associative fetch)

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

unfortunately, end multiple entries in arrays 1,2,5,6. cure this, utilize array_unique:

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

if don't want utilize array_unique, use:

while ($row=$query_result->fetch(pdo::fetch_assoc)) { $a1[$row['num']]=$row['num']; // gets round array_unique $a2[$row['num']]=$row['name'];// gets round array_unique $a3[$row['num']][]=$row['prefs']; // store values in subarray according num $a4[$row['num']][]=$row['prefs']; // store values in subarray according num $a5[$row['num']]=$row['gender']; // gets round array_unique $a6[$row['num']]=$row['opt']; // gets round array_unique }

but need utilize array_values extract values:

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

to resultant multi-dimensional arrays, have bit more processing:

$array4=array(); $array5=array(); //loop through each sub array foreach ($a4 $subarray) { //if there 1 element, store value, otherwise store sub-array $array4[]=(count($subarray)>1) ? array_values($subarray) : array_shift($subarray)); } //loop through each sub array foreach ($a5 $subarray) { //if there 1 element, store value, otherwise store sub-array $array5[]=(count($subarray)>1) ? array_values($subarray) : array_shift($subarray)); }

(you flash using variable variables clarity went separate loops)

once have info in arrays, can want it. if want build php source code it, utilize var_export($arrayname,true) , capture variable

none of code tested will, hopefully, point in right direction

php mysql sql group-concat