Newbie having trouble finding the good loop

[Damas]

Hello,
First of all please excuse my vocabulary, english isn't my mother tong.
I'm having a hard time trying to do this.
I have two tables in a MySQL db, first one is for members datas, second one if the one for french departments (geography).
I need to have a list of every department followed by the list of members that are living in this department (after it will be parsed to a Flash animation in a dynamic text box).
Here is my code:

<?php
//getting every department, sorted by name
$variables="SELECT * FROM membres_regions ORDER BY 'Var_flash'";
$resultat=mysql_query ($variables);

//as long as we have a department
while( $var=mysql_fetch_array ($resultat) ){
 //getting the variables
$flashvar=$var['Var_flash'];
$region=$var['Region'];
//shows each department
echo "$flashvar=";
//get breeders, sorted by pas Affixe
$query_eleveurs = "SELECT * FROM membres_aacas WHERE Region='$region' AND Publication='Oui' ORDER BY Affixe";
       $result_eleveurs = mysql_query($query_eleveurs);
  while( $eleveurs = mysql_fetch_array($result_eleveurs) ){
     
		$popup=$eleveurs['popup'];
		$affixe=$eleveurs['Affixe'];
		$race=$eleveurs['Race'];
		$chaton=$eleveurs['chatons'];

	echo "<a href=\"asfunction:popup,../chatteries/$popup\"><b>$affixe</b></a><br>$race - $chaton<br>";	
	}	
}
mysql_close();
?>

My problem is that I get this kind of list:
&ain=breeder xx breed ii
&aisne=
&allier=
&alpesdehautepr ovence=
&alpesmaritimes =breeder yy breed jj

And I would like to have :
&ain=breeder xx breed ii
&aisne=No breeder !
&allier=No breeder !
&alpesdehautepr ovence=No breeder !
&alpesmaritimes =breeder yy breed jj

I know that my second querry is an obstacle to obtain this result because it shows only "true" results : "WHERE Region='$region ' "
But I cant figure out how to get what I need...
If someone here could help, this would be wonderfull... I really am a beginner so please, be patient !
Thank you very much.

[jonndoe45]

Not sure which fields this applies to as you you have specified the field names in your code - and my french is non-existent, so here goes:

Here is your existing code:

$query_eleveurs = "SELECT * FROM membres_aacas WHERE Region='$region ' AND Publication='Ou i' ORDER BY Affixe";
$result_eleveur s = mysql_query($qu ery_eleveurs);
while( $eleveurs = mysql_fetch_arr ay($result_elev eurs) ){

$popup=$eleveur s['popup'];
$affixe=$eleveu rs['Affixe'];
$race=$eleveurs['Race'];
$chaton=$eleveu rs['chatons'];



You have 2 options.

1. instead of doing (for example) $affixe=$eleveu rs['Affixe'];

do:

if ( empty($eleveurs['Affixe']) ){
$affixe = "no breeder" ;}
else {
$affixe=$eleveu rs['Affixe'];}

Explanation:

if the value of the affixe column is empty then set the affixe variable to 'no breeder' otherwise (else) set it to the value of the affixe column

2. or in the SQL you can do:

$query_eleveurs = "SELECT popup, if(length(affix e)>0,affixe, 'no breeder'), race, chatons FROM membres_aacas WHERE Region='$region ' AND Publication='Ou i' ORDER BY Affixe";


Explanation:

each field is specified in the sql statement

for the affixe column we do a test inside the sql statement

if the length of the affixe column is greater than 0 (i.e it has a value) then return the value of the affixe column, otherwise return the user defined value 'no breeder'

just using the affixe column as an example - not sure which ones you want to set to no breeder.

hope this helps
jonndoe45

[Damas]

Thank you very much JonnDo I finally had what I want using a "if" outside the second "while" :
[php]$popup=$eleveur s['popup'];
$affixe=$eleveu rs['Affixe'];
$reg=$eleveurs['Region'];
$race=$eleveurs['Race'];
$chaton=$eleveu rs['chatons'];

echo "<a href=\"asfuncti on:popup,../chatteries/$popup\"><b>$af fixe</b></a><br>$race - $chaton<br>";
}

if ( $reg <> $region) echo "Il n'y a pas d'&eacute;leveu rs dans votre r&eacute;gion !<br>";
[/php]
I don't really understand why but it gives me exactly the right thing. I will try yours too, I think the right colomn to use is the Region one.
Again, thank you very much !