Crazy! Guru Needed! Brain Buster (mine anyway)

phence

Alright.. lets get down to business!

I working on a report log for my fire company, it keeps track of all the calls we get, as well as keeps track of each member at our company and how many calls he makes. Makes it nice at the end of the year for totaling all this data.. Plus it's a great asset when applying for grants! 🙂

You may want to check out my previous post

I'm still trying to figure out the most efficient way to collect the data from these multipul select boxes. I use the following code to create <select> boxes for each one of the members in our Fire Company.

// Get Member Names & info From Database so it can be updated
$query = "SELECT * FROM members ORDER BY lastname";

// Place into niice tables!	
echo "<div align=\"center\"><table width=\"75%\" border=\"1\" bordercolor=\"#FFFFFF\">"; 
    $j = 0; 
    $rownum = 0;     

    $result = mysql_query($query); 
    if (($result = @ mysql_query ($query, $connection))){ 
        while ($row = mysql_fetch_array($result)){ 
            if($j == $rownum) { 
                echo "<tr>"; 
                $rownum += 3; //<------- NUMBER OF RECORDS PER ROW 
            } 
            echo "<td width=\"30%\"><font size=\"2\" face=\"Tahoma\">
                 <select name=\"".$row['id']."\"> 
                 <option>-----------</option> 
                 <option name=\"$row['fire']\" >Fire Scene</option> 
                 <option name=\"$row['drill']\" >Drill Scene</option> 
                 <option name=\"$row['station']\" >Station</option> 
                 </select>
                 " .$row['lastname'] . ", " . $row['firstname'] . "</td>"; 
        		++$j;		
		} 


    if($j == $rownum) { 
        echo "</tr>"; 
    } 
} 
echo "</table></div>";

  ?>

You Can See this in action here CVFC Report Log it's located at the bottom of the page.

Now the Problem lies with the collecting of this data.
There are a few obsticales to surpass. there are three conditions for each member if there is data sent(if a selection is made). Here is the BASE code for that.

	
	// Each Varible into the $fireVars['var'] Format
	foreach($_POST as $varname => $value)
		$fireVars[$varname] = $value;
// For each member check if he made the call
	// and if he did, update the database accordingly
	if ($fireVars[''] == 'fire'){
		// update members database for the scene
		$sql = mysql_query("UPDATE members SET scene = scene + 1") or die(mysql_error());
	}elseif ($fireVars[''] == 'station'){
		// update members database for the station
		$sql = mysql_query("UPDATE members SET station = station + 1") or die(mysql_error());
	}elseif ($fireVars[''] == 'drill'){
		// update members database for the drill
		$sql = mysql_query("UPDATE members SET drills = drills + 1") or die(mysql_error());
	}

I need some type of loop to do this for each member in the database, i can't do this seperatly for each member because new members may join and other members may leave the company. Not to mention how lengthy the code would get.

The user's id from the database is what's being sent to the 'collection' script (script collecting the data), which is nothing more than a 'auto_increment' integer.

So with all that said. What would be an efficient way to make updates to the members database?

How should i construct a loop and send each variable of each member through the loop? i was thinking along the lines of foreach? but i don't know how to construct it properly for what i need it to do

I really do appreciate your help! and Thank-you!

HalfaBee

I think your SQL should also have a WHERE to update the correct member.

Halfabee

foreach( $fireVars as $fireVar )
{
switch( $fireVar )
{
case 'fire':
        $sql = mysql_query("UPDATE members SET scene = scene + 1") or die(mysql_error());
         break;

case  'station':
        // update members database for the station
        $sql = mysql_query("UPDATE members SET station = station + 1") or die(mysql_error());
        break;

case  'drill':
        // update members database for the drill
        $sql = mysql_query("UPDATE members SET drills = drills + 1") or die(mysql_error());
      break;
}
}

phence

thats it? i've never seen that method before, could you explain it a little 🙂 ? thanks!

sorry i've seen case statements but not in php, nor have i seen switch...

what is $fireVar ?

HalfaBee

$fireVar is created from the foreach statement.
It contains the element from the array as it loops thru.

The switch statement allows you to test simple variables for different values, the case is part of the switch statement.

Hope this helps;
HalfaBee

switch( $i )
{
 case 1:
    // do things if value 1
    break; // This is needed to exit the switch 

// also if you have a couple of values that do the same thing

  case 2:
  case 7:
// do stuff for values 2 && 7
    break;

default:
// do stuff for all othe values not tested for.

}

phence

It still doesn't seem to work, i see no change in the database..

Heres what i have:

foreach( $fireVars as $fireVar ) 
	{ 
		switch( $fireVar ) 
		{ 
			case 'fire': 
        	$sql = mysql_query("UPDATE members SET scene = scene + 1") or die(mysql_error()); 
         	break; 

		case  'station': 
    	// update members database for the station
    	$sql = mysql_query("UPDATE members SET station = station + 1") or die(mysql_error()); 
    	break; 

		case  'drill': 
    	// update members database for the drill
    	$sql = mysql_query("UPDATE members SET drills = drills + 1") or die(mysql_error()); 
  		break; 
	} 
}

i understand the case statements, but not the switch?

thank-you

phence

anyone else wanna take a shot? or will this work, and i'm just missing somthing?

to me it after reading up on switch, it looks like i could work, but for some reason it's not.

thanks, all help is appreciated.

HalfaBee

Stick some debug statements in and see what is happening.
echo $fireVar;
in the foreach loop would be a good start.

You should also and a WHERE clause to your SQL
$sql = mysql_query("UPDATE members SET scene = scene + 1")
This will increment every row in the DB.

HalfaBee

phence

ahh.. yes good point with the sql, i forgot about that!

echo $fireVar; // produces no output

echo $fireVars; // produces "array"

thanks for the help. but its still not working.

HalfaBee

Before the foreach put print_r( $fireVars ).
This will show th content of $fireVars and may show the problem.

HalfaBee