if else and while loops

[ijyoung]

I have a table which has names of club members against certain duties.Each member has a membership no identifiying them.

So table might look like :

id, event, date, ood,safety,ood_id,s_id.

I want to be able to pick out all duties for a given membership no.

query might look like.

$query = "SELECT *,DATE_FORMAT(date, '%a-%d-%m-%Y') AS dr from table where ood_id='$id' or  s_id='$id'" ;
 $result = @mysql_query ($query); // Run the query.
 if (!$result) {
    die("Query error! query text: $query<br />Error: " . mysql_error());
} 

 

Now I have tried two different if conditionals

$name = '';
  $duty = '';

  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    if ($row['ood_id']==$id) {
        $name= $row['ood'];
        $duty='Race Officer';
        
    }

    if ($row['s_id']==$id) {
        $name= $row['safety'];
        $duty='Safety';
        
    }
//table here with $name $duty
}//end of while loop

and other try

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  if ($row['ood_id']==$id) {
  $name= $row['ood'];
  $duty='Race Officer';
  }elseif 
  ($row['s_id']==$id) {
  $name= $row['safety'];
  $duty='Safety';
}else{
  $name=FALSE;
  $duty=FALSE;
	}
//etc

 

Now the first conditional returns the last entry in the row and the second conditional returns the first record in the row.

 

How do I get all results per row?

 

I have been worrying away at this for some time and cannot see how to resolve.

 

Hope you can help

[revraz]

Are you checking to see how many rows are actually returned?

[ijyoung]

not sure what you mean here. As it happens in the test table there would only ever be only one row returned as the folks would only have "duties" on one day in the test.

[revraz]

What does this mean?

 

Now the first conditional returns the last entry in the row and the second conditional returns the first record in the row.

 

How do I get all results per row?

[ijyoung]

Ok, sorry if not making myself clear.

 

I have tried two IF statements as above in two separate attempts to solve this problem.

In the row the fields would be something like.

Date:2008-10-05

Event: Race 5 of 6

OOD: Joe Bloggs

ood_id: 413

Safety:Jean Bloggs

S_id: 413.

 

This is much simplified as there are a number of other columns but this is the essence.

 

Using the ifelse version, Joe Bloggs and Race Officer, Race 5 of 6 2008-10-05 is returned

using the if version Jean Bloggs, Safety, Race 5 of 6 2008-10-05 is returned.

 

I need both to be returned. The search parameter is th $id.

 

Hope I have made myself clearer.

 

Cheers

 

Ian

 

[revraz]

Why does Joe and Jean have the same ID?

 

You are using the same variable twice, $name and $duty, so it will over write itself.

 

Use unique variables for OOD and S

[ijyoung]

They have same $id as they have same membership number - same family.

So you are saying that it is not possible to search for two results from same row?

So how we pick out these two folks then?

[revraz]

What about

 

if ($row['ood_id']==$id) {
        $ood_name= $row['ood'];
        $ood_duty='Race Officer';
        
    }

    if ($row['s_id']==$id) {
        $s_name= $row['safety'];
        $s_duty='Safety';
        
    }

 

[ijyoung]

Should have thought about that.

 

I'll give it a go and let you know how I get on.

 

Thanks

 

Best

Ian

[ijyoung]

Now we are talking. Naturally that works fine.

 

I need to limit the results to those that are actully retunred otherwise blank rows.

 

I had used a function to build the results. Now not delivering!

while ($row = mysql_fetch_array($result)) {
  
    	if ($row['s2_id']==$id) {
        $name3= $row['safety2'];
        $duty3='Safety2';
result-duty();
	//etc
        
    }
}//end of while loop

function result-duty() {
echo "<table>";
    echo "<tr>";
echo "<td>Name: </td>";
echo "<td>".$name."</td>";

echo "</table>";

   
} 

 

The idea is that only those results that meet the conditions would be met. However, as $name is outside the loop it ain't working. As I said had this working before (deleted the test page) and cannot for the life of me see where I am going wrong

[ijyoung]

You probably have noticed two typos in above

function should be result_duty and $name should be $name3.

Neither of these are cause of no name displaying.

[ijyoung]

Got it. Great what happens when you go away and come back to a problem.

Basically have just put a table in each of the conditions as per

    $name2= $row['safety'];
        $duty2='Safety';
	?>
	<table width="40%" align="center" bgcolor="red">
    <tr>	   
	<td><?  echo $row['dr']?></td>
	</tr>
	<tr>
	<td><? echo $row['event']?></td>
	</tr>
	<tr>
	<td>Name: 
	<? echo $name2?></td>
	<tr>

	<td>Duty: 
	<? echo $duty2?></td>
	</tr>
	</table>
	<?
        
        
    }

 

Now there may be a more elegant way of doing this but a least this works.

Thanks folks.