[Resolved] SQL Query problem

[Mr_Mako]

Hi...

I'm working on a Query... and I'm stumped. I can't figure out why I'm getting the results I'm getting.

I have a table where players submit "missions". In that table are the results of crew casualties. What I'm trying to get is the crew replacements per mission.

My code is below :

// Create Crew Reports Query //
$replacements = "SELECT mission_no, loss_pilot, loss_copilot, loss_bomb, loss_nav, loss_eng, loss_radio, loss_ball, loss_lw, loss_rw, loss_tail, rep_pilot, rep_copilot, rep_bomb, rep_nav, rep_eng, rep_radio, rep_ball, rep_lw, rep_rw, rep_tail FROM mission WHERE plane_name = '$plane' AND activate = '1' ORDER BY mission_no ASC";
$replacements_result = @mysql_query($replacements, $connect) or die(mysql_error());

while ($row = mysql_fetch_array($replacements_result)) {
	$mis_num = $row['mission_no'];
	$pilot_loss = $row['loss_pilot'];
	$pilot_rep = $row['rep_pilot'];
	$copilot_loss = $row['loss_copilot'];
	$copilot_rep = $row['rep_copilot'];
	$bomb_loss = $row['loss_bomb'];
	$bomb_rep = $row['rep_bomb'];
	$nav_loss = $row['loss_nav'];
	$nav_rep = $row['rep_nav'];
	$eng_loss = $row['loss_eng'];
	$eng_rep = $row['rep_eng'];
	$radio_loss = $row['loss_radio'];
	$radio_rep = $row['rep_radio'];
	$ball_loss = $row['loss_ball'];
	$ball_rep = $row['rep_ball'];
	$lw_loss = $row['loss_lw'];
	$lw_rep = $row['rep_lw'];
	$rw_loss = $row['loss_rw'];
	$rw_rep = $row['rep_rw'];
	$tail_loss = $row['loss_tail'];
	$tail_rep = $row['rep_tail'];

$crewmember_num_rows = mysql_num_rows($replacements_result);

if($mis_num_check == "0") {
	$display_lost = "
		<p align=left>&nbsp;</p>";
} else if($crewmember_num_rows == "0") {
	$display_lost .= "
		<font face=\"Century Gothic\" size=2 color=#000000><b>Mission #$mis_no:</b> No crew members replaced.</font><br>";
} else if($crewmember_num_rows >= "1") {
	if($pilot_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$pl = "<font size=1>  $pilot_loss replaced by $pilot_rep</font>.";
	}
	if($copilot_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$cl = "<font size=1>  $copilot_loss replaced by $copilot_rep</font>.";
	}
	if($bomb_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$bl = "<font size=1>  $bomb_loss replaced by $bomb_rep</font>.";
	}
	if($nav_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$nl = "<font size=1>  $nav_loss replaced by $nav_rep</font>.";
	}
	if($eng_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$el = "<font size=1>  $eng_loss replaced by $eng_rep</font>.";
	}
	if($radio_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$rl = "<font size=1>  $radio_loss replaced by $radio_rep</font>.";
	}
	if($ball_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$balll = "<font size=1>  $ball_loss replaced by $ball_rep</font>.";
	}
	if($lw_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$lwl = "<font size=1>  $lw_loss replaced by $lw_rep</font>.";
	}
	if($rw_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$rwl = "<font size=1>  $rw_loss replaced by $rw_rep</font>.";
	}
	if($tail_loss == "") {
		$msg1 = "<p align=left>&nbsp;</p>";
	} else {
		$tl = "<font size=1>  $tail_loss replaced by $tail_rep</font>.";
	}

}

$display_lost .= "
	<font face=\"Century Gothic\" size=2 color=#000000><b>Mission #$mis_num:</b>$pl$cl$bl$nl$el$rl$balll$lwl$rwl$tl</font><br>";
}

My results are returning something like this :

Mission # 1: Joe replaced by Frank.
Mission # 2: Fred replaced by Bob. Joe replaced by Frank.
Mission # 3: Larry replaced by Tim. Fred replaced by Bob. Joe replaced by Frank.

The preceeding mission results are always appended to the end of the latest mission. So by say mission 10 I have a paragraph long list of crewmen replaced.

What am I doing wrong? I've fought with this all morning and haven't been able to figure out where my mistake is.

[bastien]

no mission parameter in the sql...

consider adding some sort of query where its limited by date or mission number...

or add a counter to the result set and only run it once

or add the LIMIT statement onto the query to return only one row

[Mr_Mako]

Ok... I've seen part of where the problem lay. My table (the part this code is looking at) looks like this:

mission | loss_pilot | loss_copilot | loss_bomb | loss_nav | loss_eng | loss_radio | loss_ball | loss_lw | loss_rw | loss_tail | rep_pilot | rep_copilot | rep_bomb | rep_nav | rep_eng | rep_radio | rep_ball | rep_lw | rep_rw | rep_tail
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  mis1  |   Joe      |              |           |          |          |            |           |         |         |           |   Frank   |             |          |         |         |           |          |        |        |       

  mis2  |            |     Fred     |           |          |          |            |           |         |         |           |           |     Bob     |          |         |         |           |          |        |        |       

  mis3  |            |              |  Larry    |          |          |            |           |         |         |           |           |             |   Tim    |         |         |           |          |        |        |       

Essentially what I need to do is "flush" the variables each time it's re-written.

Is there a way to accomplish that?

[bastien]

where mission = 'somevalue'

or add a date stamp to the mission parameters to see when the mission was run, then use that to limit the rows returned

select * from missions order by dateField DESC limit 1

[Mr_Mako]

If I do that (which I've tried with your suggestion), I only get a single result, dependant upon ASC or DESC (either Mission 1 or Mission 3).

I need to have the result set list all of the missions as in my example above, not only return a single mission result.

Even though the results are returning single records for each "Mission", the preceeding results are still being displayed because there is nothing new in latest mission fields for those fields.

I need to be able to display all the missions in descending order, but keep only results relevant to the mission in question listed by the relevant mission number.

[bastien]

okay, i get what you want now...you are correct in that you need to remove the old values from the variables....

while ($row = mysql_fetch_array($replacements_result)) { 
    $mis_num = $row['mission_no']; 
    $pilot_loss = $row['loss_pilot']; 
    $pilot_rep = $row['rep_pilot']; 
    $copilot_loss = $row['loss_copilot']; 
    $copilot_rep = $row['rep_copilot']; 
    $bomb_loss = $row['loss_bomb']; 
    $bomb_rep = $row['rep_bomb']; 
    $nav_loss = $row['loss_nav']; 
    $nav_rep = $row['rep_nav']; 
    $eng_loss = $row['loss_eng']; 
    $eng_rep = $row['rep_eng']; 
    $radio_loss = $row['loss_radio']; 
    $radio_rep = $row['rep_radio']; 
    $ball_loss = $row['loss_ball']; 
    $ball_rep = $row['rep_ball']; 
    $lw_loss = $row['loss_lw']; 
    $lw_rep = $row['rep_lw']; 
    $rw_loss = $row['loss_rw']; 
    $rw_rep = $row['rep_rw']; 
    $tail_loss = $row['loss_tail']; 
    $tail_rep = $row['rep_tail']; 

$crewmember_num_rows = mysql_num_rows($replacements_result); 

if($mis_num_check == "0") { 
    $display_lost = " 
        <p align=left>&nbsp;</p>"; 
} else if($crewmember_num_rows == "0") { 
    $display_lost .= " 
        <font face=\"Century Gothic\" size=2 color=#000000><b>Mission #$mis_no:</b> No crew members replaced.</font><br>"; 
} else if($crewmember_num_rows >= "1") { 

//turn off all the previous values for the messages   
$pl = "";
$cl =  "";
$bl = "";
$nl = "";
$el = "";
$rl = "";
$balll = "";
$lwl = "";
$rwl = "";
$tl = "";

//check the recordset values against the 
if($pilot_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $pl = "<font size=1>  $pilot_loss replaced by $pilot_rep</font>."; 
        } 
        if($copilot_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $cl = "<font size=1>  $copilot_loss replaced by $copilot_rep</font>."; 
        } 
        if($bomb_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $bl = "<font size=1>  $bomb_loss replaced by $bomb_rep</font>."; 
        } 
        if($nav_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $nl = "<font size=1>  $nav_loss replaced by $nav_rep</font>."; 
        } 
        if($eng_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $el = "<font size=1>  $eng_loss replaced by $eng_rep</font>."; 
        } 
        if($radio_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $rl = "<font size=1>  $radio_loss replaced by $radio_rep</font>."; 
        } 
        if($ball_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $balll = "<font size=1>  $ball_loss replaced by $ball_rep</font>."; 
        } 
        if($lw_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $lwl = "<font size=1>  $lw_loss replaced by $lw_rep</font>."; 
        } 
        if($rw_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $rwl = "<font size=1>  $rw_loss replaced by $rw_rep</font>."; 
        } 
        if($tail_loss == "") { 
            $msg1 = "<p align=left>&nbsp;</p>"; 
        } else { 
            $tl = "<font size=1>  $tail_loss replaced by $tail_rep</font>."; 
        } 
//move the display lost into the loop       

$display_lost .= " 
        <font face=\"Century Gothic\" size=2 color=#000000><b>Mission #$mis_num:</b>$pl$cl$bl$nl$el$rl$balll$lwl$rwl$tl</font><br>"; 

   } //end while

[Mr_Mako]

Awesome. That did it. I can't believe it was that simple... although I was so caught up with it I probably wasn't able to look at it from an objective perspective.

Thank you very much!