Creating data from multiple rows

[Travis959]

Hey guys, I am attempting to recreate a script in PHP to display results depending on data from a mysql database, but I cannot get it to display right.

 

 

In the image above you can see basically what I'm trying to recreate. I put in red what the name of the fields that are corresponding with what get's displayed.

 

 

In this image you can see what happens with my current code in PHP. The darker blue gets repeated after every line, when it should only repeat when there is a new "field4" value. When the field3 value changes to say "20", the script does not start over and put the light blue bars under the corresponding green bars, as it does on the first image as you can see in this image:

 

 

The other major problem I have is that, if you look at the first image, is that the number between field 10 and field 9 is basically the two fields divided together, which is fine. However if you notice the darker blue bar, you can see that all the number fields get added together, and then the green bar ontop adds on the darker blue bars together. How would I go about adding dynamic fields to variable that I can then add or divide from?  I am completely lost on how to do this part.

 

Here is my code:

 

$searchdatabase2 = "SELECT DISTINCT field3 FROM slssum2 WHERE company = '$companyname' AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear' AND field3 != 0 ORDER BY field3 ASC";
$run2 = mysql_query($searchdatabase2) or die (mysql_error());
$searchdatabase = "SELECT * FROM slssum2 WHERE company = '$companyname' AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear' AND field4 != 0 ORDER BY field4 ASC";
$run = mysql_query($searchdatabase) or die (mysql_error());
$searchdatabase3 = "SELECT field4 FROM slssum2 WHERE company = '$companyname' AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear'  AND field4 != 0 ORDER BY field4 ASC";
$run3 = mysql_query($searchdatabase3) or die (mysql_error());
$row3 = mysql_fetch_assoc($run3);

 

while($row2 = mysql_fetch_assoc($run2))
{ 
$field2_3 = $row2['field3'];
echo '<table width="915" border="0" cellspacing="0" cellpadding="5" align="center"><tr>
   	<td bgcolor="#ccffcc">'; echo $field2_3; echo '</td>
   	<td bgcolor="#ccffcc" colspan="3">TOTAL</td>
   	<td bgcolor="#ccffcc"> </td>
   	<td bgcolor="#ccffcc"> </td>
   	<td bgcolor="#ccffcc"> </td>
</tr>';
	while ($row = mysql_fetch_assoc($run))
	{ 
		$field3 = $row['field3'];
		$field4 = $row['field4'];
		$field5 = $row['field5'];
    		$field6 = $row['field6'];
   			$field9 = $row['field9'];
		$field10 = $row['field10'];
		if($field3 == $field2_3) 
		{
			echo '<tr>
  				<td>'; echo $field2_3; echo '</td>
   				<td bgcolor="#ccccff">'; echo $field3; echo '<br />'; echo $field4; echo'</td>
  				<td bgcolor="#ccccff" colspan="2">TOTAL</td>
  				<td bgcolor="#ccccff"> </td>
  				<td bgcolor="#ccccff"> </td>
  				<td bgcolor="#ccccff"> </td>
  				</tr>';
				foreach ($row3 as $field4)
				{ 
					echo '<tr>
  						<td> </td>
    					<td> </td>
    					<td bgcolor="#e6e6e6">'; echo $field5; echo '</td>
    					<td bgcolor="#e6e6e6">'; echo $field6; echo '</td>
    					<td bgcolor="#e6e6e6" align="right">'; echo $field10; echo '</td>
    					<td bgcolor="#e6e6e6">'; echo $field9/$field10; echo '</td>
    					<td bgcolor="#e6e6e6" align="right">'; echo $field9; echo '</td>
    					</tr>';
				}
		}
	}
  echo '</table><br /><br />';
}
?>

 

[attachment deleted by admin]

[Zane]

It looks like you need to Group by... someting.  I can't tell what that something is because you gave your field name numbers and I'm not up for guessing which is what.

 

You should definitely change your naming structure for one, but seeing as that isn't the problem (I'm assuming) ... I doubt you'll do that.

 

It would help tremendously if you also provided a screenshot of what you see in phpmyadmin.

... either that or a db schema... telling us which fields are which.. which one you want to group by in particular.

 

 

EDIT:

Although, one problem that I DO spot out immediately is that you are creating a new table inside a loop.  Unless you really want as many tables as you have rows, then this is a big no-no.

[Travis959]

The reason the fields are named that, is because I wasn't sure of what the values were in the fields when I created the database. Image is below of phpmyadmin:

 

 

The table part should only get created everytime there is a new field3 value, which should only be a few times at most. At the moment, however, I'm more worried about getting everything working correctly then going back and fixing any performance issues with regards to html.

 

The way the layout works is that, for each unique field3 value, there should be a green bar. Then below that there should be a darkblue bar where every row has the same field3 as the parent green bar and also has a unique field4 value. After that, there should be the light blue rows when they all have the same field4 that match with the parent darkblue row.

 

I hope that makes it clearer.

 

[attachment deleted by admin]

[Zane]

You should not need to create multiple tables.  Mainly because the information within those

s is relevant to one another.

You wouldn't buy a new box of Kleenex every time you wanted to blow your nose would you?

But you would buy a trashcan if you needed to throw one or whatever else away... get me?

 

 

Regardless, you provided the table structure and a detailed description, so it's much easier to see what you're doing wrong.. or need help with.

------------------------------------------

The most typical goal in using SQL is to use as less queries as possible.  Even though you only have three queries, you still have to loop through each one.. breaking in an out to get your

structure right.... and you don't need to field3 over and over unless you just want to

SELECT * FROM slssum2
WHERE company = '$companyname' 
    AND (month BETWEEN '$startmonth' AND '$endmonth')
    AND (year BETWEEN '$startyear' AND '$endyear') 
    AND field4 != 0 
GROUP BY field3, field4

That query should give you everything you need... notice the GROUP BY as opposed to ORDER BY.

This should make the DISTINCT part you use in your first query... uneeded...

But still, I'm no SQL expert.  I could move this to the MySQL if need be, but you also have problems with your table layout, so unless another mod sees a good reason not to, I'll leave your topic here.

 

Run that query in phpmyadmin and show us what you get.

[Travis959]

For whatever reason this only returns 16 results, when there should be 96 results.

 

Running without the GROUP BY returns the normal set of results however.

 

[attachment deleted by admin]

[Zane]

Group by only field3 and see what you get.

 

 

[Travis959]

Only returns 3 results.

 

 

 

[attachment deleted by admin]

[Zane]

Ugh, so off to the mysql boards this goes.

[fenway]

Someone catch me up.

[Zane]

@fenway, he's using three queries to get data from the same table.

 

I suggested a query to encapsulate all three and the OP tells me he was expecting way more results.

I.E, he only get 16 as opposed to 96.

 

My query suggestion involved the use of GROUP BY with two parameters...

When I told him to shorten it to one parameter he claims he gets less results..

Completely confused, I figured I'd move it to this board.

[Travis959]

I don't think this is a MySQL problem because with my queries, I get all my results fine.

 

It's the format in the PHP that is off and not displaying what I want.