Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Merging and displaying two unrelated tables

Posted on 2008-11-20
20
Medium Priority
?
366 Views
Last Modified: 2013-12-13
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
Comment
Question by:me_patrick
  • 9
  • 6
  • 5
20 Comments
 
LVL 3

Expert Comment

by:icarey
ID: 23002848
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
 

Author Comment

by:me_patrick
ID: 23003366
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
 
LVL 3

Expert Comment

by:icarey
ID: 23010044
Why use an array when this does the required output
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:me_patrick
ID: 23011053
'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
 
LVL 3

Expert Comment

by:icarey
ID: 23011110
I use FPDF http://www.fpdf.org/

Can also output MySQL data
0
 

Author Comment

by:me_patrick
ID: 23011190
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
 
LVL 3

Expert Comment

by:icarey
ID: 23011513
I can look for you but may take a while. they have tutorials and some scripts that may give ideas
0
 

Author Comment

by:me_patrick
ID: 23011601
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
 

Author Comment

by:me_patrick
ID: 23019577
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
 
LVL 3

Expert Comment

by:icarey
ID: 23019653
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23021136
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23021176
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23021181
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
 

Author Comment

by:me_patrick
ID: 23021283
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 23021324
> 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
 

Author Closing Comment

by:me_patrick
ID: 31518624
Thanks again for some great advice.
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23021516
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
 

Author Comment

by:me_patrick
ID: 23024087
OK - how do I do that?
0
 

Author Comment

by:me_patrick
ID: 23024115
Sorry - I'm being a bit dim but I can't see how to re-open the question to re-allocate the points?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23024535
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month20 days, 21 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question