• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

Merging and displaying two unrelated tables

I have two unrelated tables that I want to display on one report (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 a report that looks like

Animals Qty    Fruits     qty
Dog         2     Oranges   5
Cats        1     Bananas   2
Cows      5

or alternatively

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 and display together where possible.

Is there a neat way of doing this?
0
me_patrick
Asked:
me_patrick
  • 9
  • 6
  • 5
1 Solution
 
icareyCommented:
I have made 2 tables for testing
table name animals with 2 fields id and animal
table name fruit with 2 fields id and fruit

you will need to enter your database connection details.

The formatting is nothing special but does the required display
see snippet below

Ivan
<?php
mysql_connect("localhost","username","password")
	or die (mysql_error());
mysql_select_db("tablemname")
	or die (mysql_error());
 
 
	$sqlanimal = "SELECT COUNT(animal) AS NumberOfAnimals, animal FROM animals GROUP BY animal ORDER BY animal ASC";
	$resultanimal = mysql_query($sqlanimal)
		or die (mysql_error());	
	$num_results_animal = mysql_num_rows($resultanimal);
 
	$sqlfruit = "SELECT COUNT(fruit) AS NumberOfFruit, fruit FROM fruit GROUP BY fruit ORDER BY fruit ASC";
	$resultfruit = mysql_query($sqlfruit)
		or die (mysql_error());	
	$num_results_fruit = mysql_num_rows($resultfruit);
 
?>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">
 
<html>
<head>
<title>twotables</title>
</head>
<body>
 
	<table cellpadding="0" cellspacing="5">
		<th>Animals</th><th>Qty</th><th>Fruits</th><th>Qty</th>
		<tr valign="top">
			<td colspan="2">
			<?php
	
		for ($i=0; $i<$num_results_animal; $i++)
		{
			$myrow1=mysql_fetch_array($resultanimal);
			$animal = $myrow1["animal"];
			$animal_num= stripslashes($myrow1["NumberOfAnimals"]);
			print "<table width=\"100%\">
					<tr>
					<td>$animal</td>
					<td align=\"right\">$animal_num</td>
					</tr>
					</table>";
		}//for
		?>
		</td>
		<td colspan="2">
		<?php
		for ($x=0; $x<$num_results_fruit; $x++)
		{
			$myrow2=mysql_fetch_array($resultfruit);
			$fruit = $myrow2["fruit"];
			$fruit_num = stripslashes($myrow2["NumberOfFruit"]);
			print " <table width=\"100%\" >
					<tr>
						<td>$fruit</td>
						<td align=\"right\">$fruit_num</td>
					</tr>
					</table>";
					
		}//for		
		?>
		</td>
		</tr>
	</table>
 
</body>
</html>

Open in new window

0
 
me_patrickAuthor Commented:
Thanks for that Ivan but I didn't explain my problem properly.

It's not really an HTML issue (the output is not going to screen) it is an array merge issue - basically I want all the data to be written to one array so that I can then spill the merged (composite) array out to my output.

Regards.
Patrick.
0
 
icareyCommented:
Why use an array when this does the required output
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
me_patrickAuthor Commented:
'Cause I'm not outputing the data using HTML - it's going out to a PDF file which is reading the output lines one at a time.
0
 
icareyCommented:
I use FPDF http://www.fpdf.org/

Can also output MySQL data
0
 
me_patrickAuthor Commented:
Yes - that's what I'm using! I can't visualise how both sides of the page are populated by different data without first merging them together into one 'table' that can be output as 'cells' on one line. Am I missing something?
0
 
icareyCommented:
I can look for you but may take a while. they have tutorials and some scripts that may give ideas
0
 
me_patrickAuthor Commented:
I've been through the tutorials and this scenario does not come up. You can format the RHS of the page independantky of the LHS by using the SETXY comand and that's fine if you know what the the data on each side of the page is. The problem I have that I don't know what volume of data I need to fit on each side, e.g. on the RHS I may have half a page bot on the left I may have 3 pages worth and vice versa (or perhaps 3 pages and 4 pages, etc.) .... that's why I was picturing merging it into one table/array and outputing a line for each composite record/element which may have both RHS & LHS data or just one or the other.

Thanks for your interest.

Still not clear on how to create and manipulte an array in this way though.
0
 
me_patrickAuthor Commented:
This answer was provided by cxr (I re-phrased the question a little and asked again) and it works fine. The only thing I was uncertain about was the performance impliocations of using temporary tables rathwer than arrays but maybe there's no difference?
<?php
mysql_connect('localhost','user','pass');
mysql_select_db('test');
 
$rs = mysql_query('select count(*) from fruits');
$fruits_count = mysql_result($rs,0);
$rs = mysql_query('select count(*) from animals');
$animals_count = mysql_result($rs,0);
 
$max = max($fruits_count,$animals_count);
 
mysql_query("create temporary table tmp1 (
    id int not null primary key auto_increment,
    fruit varchar(80),  fruit_qty int,
    animal varchar(80), animal_qty int)");
 
if($max==$fruits_count) { // fruits have most records, or equal
  $other = 'animal';
  mysql_query("insert into tmp1 (fruit,fruit_qty) select fruit,qty from fruits");
}
else { // animals have most records
  $other = 'fruit';
  mysql_query("insert into tmp1 (animal,animal_qty) select animal,qty from animals");
}
 
// add rows from $other table
$i = 0;
$rs = mysql_query("select * from {$other}s");
while($row=mysql_fetch_array($rs)) {
  $i++;
  mysql_query("update tmp1 set $other='{$row[0]}', {$other}_qty={$row[1]} where id=$i");
}
 
// show result
$rs = mysql_query('select * from tmp1');
echo '<table>';
  echo '<tr>'.
       '<th>fruit</th>'.
       '<th>qty</th>'.
       '<th>animal</th>'.
       '<th>qty</th>'.
       '</tr>';
while($row=mysql_fetch_assoc($rs)) {
  echo '<tr>'.
       '<td>'.$row['fruit'].'</td>'.
       '<td>'.$row['fruit_qty'].'</td>'.
       '<td>'.$row['animal'].'</td>'.
       '<td>'.$row['animal_qty'].'</td>'.
       '</tr>';
}
echo '</table>';
?>

Open in new window

0
 
icareyCommented:
Isn't this what I have done based upon your question, why use a temporary table?

This answer still doesn't use and array like you asked?

How does it get to FPDF?

I'm confused.
0
 
Roger BaklundCommented:
The other question is here:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_23922697.html

> performance impliocations of using temporary tables rathwer than arrays but maybe there's no difference?

PHP arrays lives in web server memory, temporary tables are managed by the database server. The db server will handle this in memory when the amount of data is small, and use disk when it is large. There is no limit to how large the dataset can be. The database server software is written to handle this kind of task. If it is done with arrays in php, it will only use web server memory, and crash when the data is too large.
0
 
Roger BaklundCommented:
Now that I know what you are trying to do, I have a better solution for you. You can fetch rows from two queries in parallel:
$rs1 = mysql_query($query1);
$rs2 = mysql_query($query2);
$row1 = mysql_fetch_assoc($rs1);
$row2 = mysql_fetch_assoc($rs2);
while($row1 or $row2) {
  if($row1) {
    // output pdf row from row1
    $row1 = mysql_fetch_assoc($rs1); // fetch next row 
  }
  if($row2) {
    // output pdf row from row1
    $row2 = mysql_fetch_assoc($rs2); // fetch next row 
  }
}

Open in new window

0
 
Roger BaklundCommented:
Like this:
$rs1 = mysql_query(
  "select date, operator, del_value from tmp1_when");
$rs2 = mysql_query(
  "select quantity, product, type, price, total from tmp2_what");
$row1 = mysql_fetch_assoc($rs1);
$row2 = mysql_fetch_assoc($rs2);
while($row1 or $row2) {
  if($row1) {
    $pdf->Cell(30,4,$row1['date'],0);
    $pdf->Cell(50,4,$row1['operator'],0);
    $pdf->Cell(14,4,'£'.$row1['del_value'],0,0,'R');
    $row1 = mysql_fetch_assoc($rs1); // fetch next row 
  }
  if($row2) {
    $pdf->Cell(5,4,$row2['quantity'],0,0,'R');
    $pdf->Cell(50,4,$row2['product'],0);
    $pdf->Cell(8,4,$row2['type'],0);
    $pdf->Cell(15,4,'£'.$row2['price'],0,0,'R');
    $pdf->Cell(15,4,'£'.$row2['total'],0,1,'R');
    $row2 = mysql_fetch_assoc($rs2); // fetch next row 
  }
}

Open in new window

0
 
me_patrickAuthor Commented:
Wow ... interesting - thanks. It will need a further tickle because $row2 data needs to land on the RHS of the page even if there is no $row1 data and as it stands that won't happen - it'll slip to the LHS when $row1 expires but I'll play around with that and give it a go, it sounds good - thanks again ... excellent (again).

Is there any broad rule of thumb about when to use an array and when to use a temporary table or does it just not matter?

Ivan - thank you too for your time and interest. The reason this solution differs from yours and is more appropriate for me at this stage is that allows me to format the LHS/RHS of the same page independantly of one another when outputting to PDF. In your solution it would work fine if I was just reading two tables and outputting each into a separate HTML 'table' that 3 were side by side on the page - I don't believe that is possible when outputing cells in fpdf (but I'm pretty new to this so maybe I've missed something).

Hoefully the code snippet above will make things a little clearer or the reference to the other (related) question may help clarify.

Regards.
Patrick.
0
 
Roger BaklundCommented:
> Is there any broad rule of thumb about when to use an array and when to use a temporary table or does it just not matter?

Depends on the amount of data, and in what context the array/table is to be used.

If there is any chance it will exhaust web server memory limits (usually 8M by default when using php) a temporary table is better.

If the data is to be used in conjuction with other data in the database, a temporary table is better. If you are not using a database at all, an array is better.

An array has the possibility to contain nested arrays and arbitrary objects. This could be difficult to model in the database, but it is easy to do with an array. You would only use a temporary table in such cases if the amount of data is too big for web server memory.
0
 
me_patrickAuthor Commented:
Thanks again for some great advice.
0
 
Roger BaklundCommented:
I suppose I should be happy about getting all the points, but this is not fair to others who actually answered your original question. You should split the points when multiple experts help you.

http://www.experts-exchange.com/help.jsp#hi407
http://www.experts-exchange.com/help.jsp#hi409

In my opinion, icarey should have some of the points for answering the question you actually asked. It's not his fault that your question did not contain all information that was needed to solve your task.
0
 
me_patrickAuthor Commented:
OK - how do I do that?
0
 
me_patrickAuthor Commented:
Sorry - I'm being a bit dim but I can't see how to re-open the question to re-allocate the points?
0
 
Roger BaklundCommented:
Click the Request Attention button, it is located at the right lower side of your original question. Tell them you want to split the points. I think they will just reopen the question, and you can close it again, this time with a point split.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now