Logic error

[182x]

hey guys, trying to make the following code work but there is a problem with the while just wondering how to fix that so that the year will be stored just once and with the total amount of times it appears in each table?

 

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(*)'];
	}

$amountAll[] = $rowAll3[0];
$yearAll[] = $rowAll3[1];
printf("<pre>%s</pre>\n", print_r($rowAll3, 1)); 

 

[lightningstrike]

not sure if I'm thinking of the right thing but perhaps your thinking of SELECT DISTINCT

[182x]

That won't work as I am dealing with data from both tables and need the total of all the year amount but only keep one of the actual year values for example one table could have 2007, 50, 2007, 50 and the other table could have 2007, 10, 2007, 10. So i need just 2007, 120.

[MemphiS]

Not sure if i understand you fully...

 

But if you just wish to count one column in the table you do as below:

 

SELECT COUNT(column) FROM table

[182x]

Sorry maybe I am not being clear I have to be able to count the number of time the years occur from 2 different and unrelated tables and store this information in an array but I only want the actual year value to be stored once along the number of times that year occurs.

[lightningstrike]

Try joining the queries perhaps.

SELECT DISTINCT t1.year,t2.year,count(*) FROM test1 AS t1,test2 AS t2 GROUP BY t1.year,t2.year

[182x]

Just tried it but it didn't procude any result when inserting it into the array.

[lightningstrike]

$query = "SELECT DISTINCT t1.year,t2.year,count(*) FROM test1 AS t1,test2 AS t2 GROUP BY t1.year,t2.year";
$res = mysql_query($query);
while($row = mysql_fetch_array($res)){
print_r($res);
}

 

prehaps try debugging with this by viewing the results of the query.

[182x]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

the above error occurs with the mysql_fetch_array($res) in the code.