me_patrick
asked on
Merging 2 unrelated tables into a single array.
I have two unrelated tables that I want to output as one (it's too complicated to explain why but suffice to say I need to do this) so I have:
Table 1:
Animals
Quantity
Table 2:
Fruits
Quantity
No relationship between them but I want to output something that could look like
Animals Qty Fruits qty
Dog 2 Oranges 5
Cats 1 Bananas 2
Cows 5
or
Animals Qty Fruits qty
Dog 2 Oranges 5
Cats 1 Bananas 2
Apples 6
I don't know what's in either table at any one time (variable numbers of records in both) but I want to pair the records up output them together wherever possible.
Is there a neat way of doing this?
I have asked this question before but I didn't frame it quite right so appologies for that.
Table 1:
Animals
Quantity
Table 2:
Fruits
Quantity
No relationship between them but I want to output something that could look like
Animals Qty Fruits qty
Dog 2 Oranges 5
Cats 1 Bananas 2
Cows 5
or
Animals Qty Fruits qty
Dog 2 Oranges 5
Cats 1 Bananas 2
Apples 6
I don't know what's in either table at any one time (variable numbers of records in both) but I want to pair the records up output them together wherever possible.
Is there a neat way of doing this?
I have asked this question before but I didn't frame it quite right so appologies for that.
ASKER
Thanks for the interest but it's not an HTML issue - I'm outputing to a pdf file using another method and the way I'm thinking abou it, it needs both unrelated tables merged into one table that may have more entries one one side or the other.
It looks like you first need a couple of queries to build an artificial relationship between the tables.
example:
example:
set @Ct = 0
______
create temporary table tmpAnimals
select
@Ct := @Ct + 1 as ID,
Animals,
Qty
from
AnimalsTable
____
set @Ct = 0
_____
create temporary table tmpFruits
select
@Ct := @Ct + 1 as ID,
Fruits,
Qty
from
FruitsTable
________
Then finally join them:
select
tmpAnimals.Animals,
tmpAnimals.Qty as AnimalsQty,
tmpFruits.Fruits,
tmpFruits.Qty as FruitsQty
from
tmpAnimals
left join tmpFruits on tmpAnimals.ID = tmpFruits.ID
union
select
tmpAnimals.Animals,
tmpAnimals.Qty as AnimalsQty,
tmpFruits.Fruits,
tmpFruits.Qty as FruitsQty
from
tmpFruits
left join tmpAnimals on tmpAnimals.ID = tmpFruits.ID
where
tmpAnimals.ID is NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, thanks for this it is very helpful but I can't quite get it to work. The situation is slighly more complicated than I outlined above in that basically I am prducing a pdf documant that contains information from one source on the LHS of the page, and information from another source on the RHS - this can run to more than one page.
I have managed to produce a single page looking good based on two temporary files using the methos described above hhowever I then need to merge them into one temproaray table to go on to manage the page throws for multiple page reports.
The first two temporary tables are fine and I can see all of the data is present and correct but when I merge into the third I only see the data from the larger of the two files; i.e. the 'insert' works, but the 'update' does not ...it's probably something stupid but I've been playing with the code for ages and I just can't see it.... see attached snippet.
I have managed to produce a single page looking good based on two temporary files using the methos described above hhowever I then need to merge them into one temproaray table to go on to manage the page throws for multiple page reports.
The first two temporary tables are fine and I can see all of the data is present and correct but when I merge into the third I only see the data from the larger of the two files; i.e. the 'insert' works, but the 'update' does not ...it's probably something stupid but I've been playing with the code for ages and I just can't see it.... see attached snippet.
if ( $num_orders != 0 )
{ $sql = "select count(*) from tmp1_when";$res = mysql_query($sql);
$tmp1_when_cnt = mysql_num_rows($res);
$sql = "select count(*) from tmp2_what";
$res = mysql_query($sql);
$tmp2_what_cnt = mysql_num_rows($res);
$biggest_cnt = max($tmp1_when_cnt,$tmp2_what_cnt);
mysql_query("create temporary table tmp3_all (id int not null primary key auto_increment, date varchar(40),operator varchar(50),del_value varchar(10), quantity int, product varchar(50), type varchar(5), price varchar(10), total varchar(10))");
if ($biggest_cnt == $tmp1_when_cnt) // when table is bigger
{ mysql_query("insert into tmp3_all (date, operator, del_value) select date, operator, del_value from tmp1_when");
$i = 0;
$sql = "select * from tmp2_what";
$res_what = mysql_query($sql);
while ($row = mysql_fetch_array($res_what))
{ $i++;
mysql_query("update tmp3_all set
quantity = ".$row['quantity'].",
product = ".$row['product'].",
type = ".$row['type'].",
price = ".$row['price'].",
total = ".$row['total']."
where id=$i");
}
}
else // what table is bigger
{ mysql_query("insert into tmp3_all (quantity, product, type, price, total) select quantity, product, type, price, total from tmp2_what");
$i = 0;
$sql = "select * from tmp1_when";
$res_when = mysql_query($sql);
while ($row = mysql_fetch_array($res_when))
{ $i++;
mysql_query("update tmp3_all set
date = $row[0],
operator = $row[1],
del_value = $row[2]
where id=$i");
}
}
$sql = "select * from tmp3_all";
$res = mysql_query($sql);
while ($row = mysql_fetch_array($res))
{ $pdf->Cell(30,4,$row['date'],0);
$pdf->Cell(50,4,$row['operator'],0);
$pdf->Cell(14,4,'£'.$row['del_value'],0,0,'R');
// $pdf->Cell(10,4,$row['id'],0);
$pdf->Cell(5,4,$row['quantity'],0,0,'R');
$pdf->Cell(50,4,$row['product'],0);
$pdf->Cell(8,4,$row['type'],0);
$pdf->Cell(15,4,'£'.$row['price'],0,0,'R');
$pdf->Cell(15,4,'£'.$row['total'],0,1,'R');
}
}
ASKER
OK - took a break and came back to it and realised what the answer was (just hadn't written the update out properly (set field = '$value' rather than field = $value). Anyway thank you for your time (and anybody else who took the time to write) ... a big help.
One other small thing - I originally asked about arrays as I assumed that that would be the way to do it - is there no difference in terms of performamce (memory consumption, processing speeds, etc.) between using arrays and temporary tables?
One other small thing - I originally asked about arrays as I assumed that that would be the way to do it - is there no difference in terms of performamce (memory consumption, processing speeds, etc.) between using arrays and temporary tables?
Open in new window