Jump to content

Archived

This topic is now archived and is closed to further replies.

jntcomputers

Adding values from query

Recommended Posts

I am trying to tak emultiple entries and add values from the same user into one value. I have this code:



[code]
$sql_select = "SELECT user_id, start_time, end_time FROM default_ScheduleEvents WHERE (school_id = $this_school)";
    $recordSet_select = $conn->Execute($sql_select);
    if ($recordSet_select === false)
        {
        //log_error($sql);
mysql_error();
        }
    $num_records = $recordSet_select->RecordCount();

    if ($num_records > 0) {
    $hours_array = array();
    while (!$recordSet_select->EOF)
        {
        $this_pilot_id = make_db_unsafe ($recordSet_select->fields[user_id]);
        $st_time = strtotime(make_db_unsafe ($recordSet_select->fields[start_time]));
        $en_time = strtotime(make_db_unsafe ($recordSet_select->fields[end_time]));
        $this_date = DATE("M-d-Y",$st_time);
        $flight = ($en_time - $st_time)/3600;
        //$flight_time = DATE("g:i",strtotime($flight));

        
        $sql2 = "SELECT field_name, field_value FROM default_UserDBElements WHERE (user_id = $this_pilot_id)";
        $recordSet2 = $conn->Execute($sql2);
            if ($recordSet2 === false)
        {
        //log_error($sql);
mysql_error();
        }

        while (!$recordSet2->EOF)
        {
        if ($recordSet2->fields[field_name] == "name"){ $pilot_name = $recordSet2->fields[field_value]; };
        $recordSet2->MoveNext();
        } // end while
        
    if ($pilot_name != "") {
    if(isset($hours_array[$pilot_name])){
        //...add the current hour value to the
        //existing value..
        $hours_array[$pilot_name] += $flight;
    } else {
        //if the name isn't there, then add it and
        //give it the value of the hours
        $hours_array[$pilot_name] = $flight;
        
    }        
    echo "<tr><td class=\"dispatch_flight\">$this_date</td><td class=\"dispatch_flight\">$pilot_name</td><td class=\"dispatch_flight\">$hours_array[$pilot_name]</td></tr>";
} // end if pilot != ""
    $recordSet_select->MoveNext();
    } // end while
    } // end if number > 0
echo "</tr></table>";
?>

[/code]



But it outputs
User Name : Hours
USER 1 : 1.5
USER 1 : 7.5


When the table values are 1.5 & 6. So it is adding them, but I can't figure out how to show the user name only once.

Share this post


Link to post
Share on other sites
OK, where do we start?

1) You can use a query that is a little bit more complex to get all the information into 1 query. I am having a really hard time figuring out your data structure because of all your code crap, so I can't help you there.

2) If you don't want to make the MySQL query more complex, then take this section:
[code]
echo "<tr><td class=\"dispatch_flight\">$this_date</td><td class=\"dispatch_flight\">$pilot_name</td><td class=\"dispatch_flight\">$hours_array[$pilot_name]</td></tr>";
[/code]

And put it at the end into a foreach loop.
[code]
foreach ($hours_array AS $pilotname=>$hours) {
     echo "<tr><td class=\"dispatch_flight\">$pilotname</td><td>$hours</td></tr>";
}
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.