[RESOLVED] logic error with while loop

[182x]

Hey guys, Just wondering where I went wrong with this while loop as he data is not being sorted properly as all the years are not accounted for?

Thanks.

$getAll = "SELECT year, count(*) FROM test1 GROUP BY year";
		$queryAll = mysql_query($getAll, $link_id) or die(mysql_error());

	$getAll2 = "SELECT year, count(*) FROM test2 GROUP BY year";
	$queryAll2 = mysql_query($getAll2, $link_id) or die(mysql_error());
	$rowAll3[4];

while($rowAll = mysql_fetch_array($queryAll) && $rowAll2 = mysql_fetch_array($queryAll2))
{



	if ($rowAll['year'] == $rowAll2['year'])
	{
	$rowAll3[1] =$rowAll['year'];
	$rowAll3[0] =$rowAll['count(*)']+$rowAll2['count(*)'];
	}

	if ($rowAll['year'] != $rowAll2['year'])
	{
	$rowAll3[1] =$rowAll['year'];
	$rowAll3[0] =$rowAll['count(*)'];
	}

	if ($rowAll2['year'] != $rowAll['year'])
	{
	$rowAll3[1] =$rowAll2['year'];
	$rowAll3[0] =$rowAll2['count(*)'];
	}

[NogDog]

This condition will only evaluate as TRUE if both mysql_fetch_array()'s return a row:

while($rowAll = mysql_fetch_array($queryAll) && $rowAll2 = mysql_fetch_array($queryAll2))
{

If the two queries returned different numbers of rows, then once you finish cycling through the rows of the result with fewer rows, you will get no more from the result with more rows.

There may be a clever way to do this in one query, but I'm not sure if my SQL skills are up to that. Alternatively, you might do something like:

$years = array();
while($rowAll = mysql_fetch_array($queryAll))
{
   $years[$rowAll['year']] = $rowAll['count(*)'];
}
while($rowAll = mysql_fetch_array($queryAll))
{
   if(isset($years[$rowAll2['year']]))
   {
      $years[$rowAll2['year']] += $rowAll2['count(*)'];
   }
   else
   {
      $years[$rowAll2['year']] = $rowAll2['count(*)'];
   }
}
ksort($years);  // sort array by keys (years)

[182x]

Hi, thanks for the quick reply I am having so much trouble with this. I have to have the results in an array like arrayTest[0] and arrayTest[1] as I am putting these values into a chart.

So the idea from the code I posed above was so that at the end of each while iteration the arrays for the chart would be filled as shown below, what you have suggested would work if I could manage to get the index into those two arrays each time where $rowAll3[1] is the year and $rowAll3[0] is the amount. I have tried $years[0] and $years[1] but that does not appear to work.

Have you any idea how it could work in this situation? Thanks again

$amountAll[] = $rowAll3[0];
$yearAll[] = $rowAll3[1];

[182x]

just tried the following but its still not working.. anymore suggestions and thank you

$years = array();
while($rowAll = mysql_fetch_array($queryAll))
{
   $years[0] = $rowAll['year'];
   $years[1] = $rowAll['count(*)'];
}
while($rowAll = mysql_fetch_array($queryAll))
{
   if(isset($years[0]))
   {
      $years[1] =$rowAll2['year']+= $rowAll2['count(*)'];
   }
   else
   {
      $years[1] =$rowAll2['year']= $rowAll2['count(*)'];
   }

   $amountAll[] = $years[1];
$yearAll[] = $years[0];
}

[NogDog]

182x wrote:

... I have to have the results in an array like arrayTest[0] and arrayTest[1]...

I'm not sure I understand why you want two different arrays. If I assume the goal is to list the total count for each year, you can use my suggested code above, then loop through the resulting $years array as:

echo "<table>\n<tr><th>Year</th><th>Count</th></tr>\n";
foreach($years as $year => $count)
{
   echo "<tr><td>$year</td><td>$count</td></tr>\n";
}
echo "</table>\n";

[182x]

Sorry if I wasn't being clear it has to be in two arrays as that is what the graphical package requires in order for the information to be displayed. I have the following code now which nearly works except it does not account for a year that is in one table and not another and the total is wrong for each year. Any ideas on how to make that work with the code that is here?

Thanks again

$years = array();
while($rowAll = mysql_fetch_array($queryAll))
{
   $years[0] = $rowAll['year'];
   $years[1] = $rowAll['count(*)'];
      $amountAll[] = $years[1];
$yearAll[] = $years[0];
}
while($rowAll = mysql_fetch_array($queryAll))
{
   if(isset($years[0]))
   {
      $years[1] =$rowAll2['year']+= $rowAll2['count(*)'];
   }
   else
   {
      $years[1] =$rowAll2['year']= $rowAll2['count(*)'];
   }

   $amountAll[] = $years[1];
$yearAll[] = $years[0];
}

[182x]

Just realised after playing about with your code a little more that its only adding the count amount from one of the query arrays.

This is the code im using so far but the problem is still the same that its only taking one of the queries into account.

$getAll = "SELECT year, count(*) FROM table1 GROUP BY year";
		$queryAll = mysql_query($getAll, $link_id) or die(mysql_error());

	$getAll2 = "SELECT year, count(*) FROM table2 GROUP BY year";
	$queryAll2 = mysql_query($getAll2, $link_id) or die(mysql_error());

$years = array();
while($rowAll = mysql_fetch_array($queryAll))
{
   $years[$rowAll['year']] = $rowAll['count(*)'];
}
while($rowAll = mysql_fetch_array($queryAll))
{
   if(isset($years[$rowAll2['year']]))
   {
      $years[$rowAll2['year']] += $rowAll2['count(*)'];
   }
   else
   {
      $years[$rowAll2['year']] = $rowAll2['count(*)'];
   }


}

   foreach($years as $year => $count)
{
   $amountAll[] = $count;
$yearAll[] = $year;
}

[bradgrafelman]

If I understand you correctly, you're simply trying to add totals for each year that appears in either table, right?

So if a year appears in both tables, there would only be one entry in your final array, and the ammount would be the sum of both tables' data?

[182x]

Yeah sorry its a weird problem this one, is the code that NodDog posted correct if the second while statement uses rowAll2 and queryAll2?

[bradgrafelman]

I would say do this in one query - much, much easier:

$query = 'SELECT year, SUM(count) AS total FROM (
	SELECT year, count(*) AS count FROM table1 GROUP BY year
	UNION
	SELECT year, count(*) AS count FROM table2 GROUP BY year
) t GROUP BY year';

Then it's a simple matter of looping through the result set and building your arrays:

$years = $amounts = array();
while($row = mysql_fetch_assoc($exec)) {
    $years[] = $row['year'];
    $amounts[] = $row['total'];
}

EDIT: My example query uses a subquery, which itself is two queries UNION'ed together. What I'm getting at, is that I'm no SQL guru - there may be a more optimized way to do this, so if you have tons of data to sift through, you might want to ask around in the DB forum.

[182x]

Thanks for the reply, I was just wondering were does the variables $exec and $total come from? Thanks again (sorry im easily confused)

[bradgrafelman]

That's how I normally execute MySQL queries.. forgot to explain fully:

$query = 'SELECT year, SUM(count) AS total FROM ( 
    SELECT year, count(*) AS count FROM table1 GROUP BY year 
    UNION 
    SELECT year, count(*) AS count FROM table2 GROUP BY year 
) t GROUP BY year'; 
$exec = mysql_query($query);

$years = $amounts = array(); 
while($row = mysql_fetch_assoc($exec)) { 
    $years[] = $row['year']; 
    $amounts[] = $row['total']; 
} 

Not sure where you see a $total variable?

[182x]

sorry I was meaning $row['total'];
Should it be $row['t']; ? As t appears in the query before the last group by or is t for something else?

Thanks again

[bradgrafelman]

The 't' is just an alias for the derived table (which is what you get when you do a subquery like that - it's a pseudo-table, or a virtual table that was created on-the-fly from the result set of the subquery).

The year and total are from the outer-most SELECT statement; the SUM(COUNT) (which adds the totals from both tables if they both contain the same year) is, as you can see, aliased as "total".

[182x]

Ah I see now thank you very much.

Does the following variables just mean create two arrays?

$years = $amounts = array();

[bradgrafelman]

Yup, it's the same as writing:

$years = array();
$amounts = array();

I've gotten into the habit of explicitly initializing a variable as an array before I treat it like one (such as inside the while() loop).

Perhaps it's left over from my C++ coding habits? shrug

[182x]

just tried out the code and for some reason the counts are correct for some of the dates and for others its wrong. Any ideas why this is the case?

[bradgrafelman]

Hm... I tested the query as I posted it, and it seems to work fine for me, with my sample of data.

Any more information on the ones that are wrong? How do you know they are run? What total does it give you? If you do a normal SELECT year, COUNT(*) from the two tables individually, what is the sum?

Does changing "UNION" to "UNION ALL" correct the count(s)?

[182x]

Im not sure what the problem is I adapted NodDogs example and it gives seems to give the correct count.

I am trying to get ur method to work though and for some it gives the correct total, could it be something to do with if one table has a year that the other table does not?

Will try the select year, count(*) individually now to make sure.

[bradgrafelman]

The sample data I used had a mix - years in both tables, years in just table1, and years in just table2. I manually verified the counts, and they were correct. So... without some sample data and more info for your specific situation, I dunno.