Help with Select data from database

[jkkenzie]

What am trying from the code below is to get data 'Values_A' and 'Values_C' for each country that falls in one project name. my produces full results from the first country but when it moves to the last two countries, some data are missing(two for each)

 

//Get Countries within project
$result1 = mysql_query("SELECT DISTINCT country FROM tblcalc WHERE projectname='$project' ORDER BY id");
        $count=0;
	while($row1 = mysql_fetch_assoc($result1))
         {         
         $country[]= $row1['country'];  
	 $count++;
         }
//Get Data for each country		 

$count2=0;
While($count2<=0)
     {
        $result2 = mysql_query("SELECT Values_A, Values_C FROM tblcalc WHERE projectname='$project' AND country='$country[$count2]' ORDER BY id");
        while($row2 = mysql_fetch_assoc($result2))
        {  
        $A[] = $row2['Values_A'];
        $C[]= $row2['Values_C'];           
        }
$count2++;

    $result3 = mysql_query("SELECT Values_A, Values_C FROM tblcalc WHERE projectname='$project' AND country='$country[$count2]' ORDER BY id");
        while($row3 = mysql_fetch_assoc($result3))
        {  
        $A1[] = $row3['Values_A'];
        $C1[]= $row3['Values_C'];           
        }
$count2++;

    $result4 = mysql_query("SELECT Values_A, Values_C FROM tblcalc WHERE projectname='$project' AND country='$country[$count2]' ORDER BY id");
        while($row4 = mysql_fetch_assoc($result4))
        {  
        $A2[] = $row4['Values_A'];
        $C2[]= $row4['Values_C'];           
        }
$count2++;

}	

mysql_close($con);

 

Thanks in advance..

 

Jose

[Barand]

Does a project have a single record for each country or several.

 

If several recs for, say, "project X, UK" which of the country records do you want the values from, first, last, sum of all, average of all?

[jkkenzie]

database looks like this:

projectname(can be the same i.e not unique), country(can be the same i.e not unique),Values_A(unique),Values_C(unique).

 

e.g

country                projectname              Values_A              Values_B

KENYA                                      x                                      1                                5

KENYA                                      x                                      3                              4

KENYA                                        y                                    7                                  1

UGANDA                                      x                                          5                          9

UGANDA                                      x                                        4                          6

UGANDA                                  y                                          1                              2

TANZANIA                                  x                                          1                              5

TANZANIA                                    x                                      3                                    1

TANZANIA                                    y                                      5                                  6

 

 

From above: I want to select countries whose project name = x and their Values_A and Values_B .

then from my query as above create an ascending values of Values_C that MUST equal the number of records whose projectname = x(they can be irregular but should start from the lowest and end with the highest value of Values_C )

 

Thats the general idea.

 

Thanks

 

[Barand]

SELECT a.projectname, a.country, a.Values_A, a.Values_C
FROM tblcalc a
WHERE a.projectname = 'x'
ORDER BY a.country, a.values_c 

[pre]

+-------------+----------+----------+----------+

| projectname | country  | Values_A | Values_C |

+-------------+----------+----------+----------+

| x          | KENYA    |        3 |        4 |

| x          | KENYA    |        1 |        5 |

| x          | TANZANIA |        3 |        1 |

| x          | TANZANIA |        1 |        5 |

| x          | UGANDA  |        4 |        6 |

| x          | UGANDA  |        5 |        9 |

+-------------+----------+----------+----------+