Hello, hopefully i can explain myself i have a table with driver details there are 6 drivers.. im trying to order the drivers on a table by priority i think the best way to do it would be with a loop? but i'm not very familiar with loops yet.. i have been trying something with no success...

this is what i wrote but like i said im not very familiar with loops i didnt succeed

PHP Code:

$sql_driver = "SELECT dr_id,dr_name FROM driver_details ";
$res_driver = mysql_query($sql_driver)or die(mysql_error());

while($row_driver = mysql_fetch_assoc($res_driver)){
    
    $dr_id = $row_driver['dr_id'];
    $driver_name = $row_driver['dr_name'];
    
    $dr_data[$dr_id] = $row_driver['dr_name'];

    foreach($dr_data as $key => $d_name){
        
        echo "dr_id = " . $key . " name = " . $d_name . "<br/>";
    
    }
        

} 


this is the output i need to get
dr_id = 1 name = George Lopez
dr_id = 2 name = Edgar Mungia
dr_id = 3 name = Keneth Ceballos
dr_id = 4 name = Hugo Sanchez
dr_id = 5 name = Nelson Castro
dr_id = 6 name = Driver 6

and after that i need to order them in the table by priority in this case would be the dr_id i'm not sure how to achieve that either :/ can i define the order of the drivers in the while loop and then display it on the table like this?

thanks for your help

PHP Code:


<table>
<tr>
<td id="left" width="100"><?php echo $driver1;?> </td>
</tr>
<tr>
<td id="left" width="100"><?php echo $driver2;?> </td>
</tr>
<tr>
<td id="left" width="100"><?php echo $driver3;?> </td>
</tr>
<tr>
<td id="left" width="100"><?php echo $driver4;?> </td>
</tr>
<tr>
<td id="left" width="100"><?php echo $driver5;?> </td>
</tr>
<tr>
<td id="left" width="100"><?php echo $driver6;?> </td>
</tr>
</table>

Might just be better to do it in the sql query?

SELECT dr_id,dr_name FROM driver_details ORDER BY dr_id DESC

Or

SELECT dr_id,dr_name FROM driver_details ORDER BY dr_id ASC

but what do i do after that?

i need to set

$driver1 = $driver_name where $dr_id = 1;
$driver2 = $driver_name where $dr_id = 2;

and so on... i can do this by performing 1 query per each driver i was just wondering if there was a faster and easier way to do it.. thanks...

PHP Code:


// select driver from DB
$sql_driver1 = "SELECT dr_id,dr_name FROM driver_details WHERE dr_id = '1'";
$res_driver1 = mysql_query($sql_driver1)or die(mysql_error());
$row_driver1 = mysql_fetch_array($res_driver1);

    $driver1= $row_driver1['dr_name'];
    

///
$sql_driver2 = "SELECT dr_id,dr_name FROM driver_details WHERE dr_id = '2'";
$res_driver2 = mysql_query($sql_driver2)or die(mysql_error());
$row_driver2 = mysql_fetch_array($res_driver2);

    $driver2= $row_driver2['dr_name'];
    
/////
$sql_driver3 = "SELECT dr_id,dr_name FROM driver_details WHERE dr_id = '3'";
$res_driver3 = mysql_query($sql_driver3)or die(mysql_error());
$row_driver3 = mysql_fetch_array($res_driver3);

    $driver3= $row_driver3['dr_name'];
    
////
$sql_driver4 = "SELECT dr_id,dr_name FROM driver_details WHERE dr_id = '4'";
$res_driver4 = mysql_query($sql_driver4)or die(mysql_error());
$row_driver4 = mysql_fetch_array($res_driver4);

    $driver4= $row_driver4['dr_name'];
    
/////
$sql_driver5 = "SELECT dr_id,dr_name FROM driver_details WHERE dr_id = '5'";
$res_driver5 = mysql_query($sql_driver5)or die(mysql_error());
$row_driver5 = mysql_fetch_array($res_driver5);

    $driver5= $row_driver5['dr_name'];
    
//

$sql_driver6 = "SELECT dr_id,dr_name FROM driver_details WHERE dr_id = '6'";
$res_driver6 = mysql_query($sql_driver6)or die(mysql_error());
$row_driver6 = mysql_fetch_array($res_driver6);

    $driver6= $row_driver6['dr_name']; 


You don't need another loop when you can order them with sql.
This is one way to do it

PHP Code:

$sql_driver = "SELECT dr_id,dr_name FROM driver_details ORDER BY dr_id ASC"; 
$res_driver = mysql_query($sql_driver)or die(mysql_error()); 

echo '<table>';
while($row_driver = mysql_fetch_assoc($res_driver)){ 
    echo '<tr><td id="left" width="100">'.$row_driver['dr_name'].'</td></tr> ';
}
echo '</table>'; 


Originally Posted by miki86

You don't need another loop when you can order them with sql.
This is one way to do it

PHP Code:

$sql_driver = "SELECT dr_id,dr_name FROM driver_details ORDER BY dr_id ASC"; 
$res_driver = mysql_query($sql_driver)or die(mysql_error()); 

echo '<table>';
while($row_driver = mysql_fetch_assoc($res_driver)){ 
    echo '<tr><td id="left" width="100">'.$row_driver['dr_name'].'</td></tr> ';
}
echo '</table>'; 


Thanks miki86

the problem here is that there another row with results from other table in the middle of the driver rows so like this

PHP Code:


echo '<table>';
while($row_driver = mysql_fetch_assoc($res_driver)){ 
    echo '<tr><td id="left" width="100">'.$row_driver['dr_name'].'</td><td>Here goes the results for just driver1</tr> ';
echo '<tr><td id="left" width="100">Task:</td><td>here goes the results from another table for driver 1</td></tr>';
}
echo '</table>'; 


i'll post the code so maybe you can understand me better this code is just for the first two rows for one driver. I had only six drivers and i am repeating this proces 6 times but now i will have about 15 drivers, i was wondering maybe there was a better way with a loop or something.

PHP Code:

echo '<tr>';
if ($rsvdDriver1==true){ 

    echo '<td id="left" width="100">' . $driver1 .'</td>'; 
     for($i = 0; $i < count($ResTimePeriod); $i++) {  
        echo '<td bgcolor="'.$CicketColor_1[$i].'">'. $ResTimePeriod[$i].'</td>'; 
    }  
                        
    }else{  

    echo '<td id="left" width="100">'. $driver1.'</td>'; 
    for($i = 0; $i < count($ResTimePeriod); $i++) {  
     echo '<td bgcolor="'.$GoColor .'">' . $ResTimePeriod[$i]. '</td>'; 
         } 
     
    } 
          
      ////// close table row and new row
    echo '</tr><tr>';

echo '<td id="left" width="100">Task:</td>';

if ($empty > 0) {
                echo '<td colspan="' . $empty . '">&nbsp;</td>';
            }

echo '<td bgcolor="#FF0000" colspan="'.$diff.'" style="font-weight:bold; text-align:center">';
        
            echo '<font color="white">'. $row1['type'] . '</font> ';
            if($type == $row1['type']){echo '<input type="button" value="Add On" onClick="location.href=\'addon_driver.php?id='.$row1['res_id'].'&name='.$name.'&type='.$type.'\'"/>'; } 
            }
             
       echo '<br/> </td>';
echo '</tr> 


so this is just for driver one will i have to repeat this process 15 times?

thanks

You can do like this, try to write the actual code yourself.

select drivers from DB table

<table>
while (driver = fetch row from the DB result) {
    <tr>
    <td>DRIVER</td>
    run a new DB query, to select this drivers' tasks
    while (tasks = fetch row from the second DB result) {
        <td>TASK</td>
    }
    </tr>
}
</table>

You only need one query surely.

use the join in the query it will help show you the output in one table collecting data from 2 or more than 2 tables

then simply use the one while loop for fetching the data and creating the table.

Thanks guys for your help. i have been working on this for a while now.. i cleaned the code tried to organize it. i am close to finish, i just got stock getting the task for the second row it appears to be getting the first value on the same day and not all of them.. maybe it is something on the sql query? ill post a picture and the code please any help would be great

PHP Code:


$priority_arr = range(1,20);
    $priority = implode(",", $priority_arr);
    
$sql_driver1 = "SELECT dr_id,dr_name,priority FROM driver_details WHERE priority IN ($priority) ORDER BY priority ASC";
$res_driver1 = mysql_query($sql_driver1)or die(mysql_error());

while($row_driver1 = mysql_fetch_assoc($res_driver1)){
    
    echo '<tr><td id="left" width="100">' . $row_driver1['dr_name'] .'</td>'; 
    
    
    $bg_color = range(1,20);//im not sure if this is necesary
    $sql_rsvpdr="SELECT res_id,res_startTime,res_endTime,type FROM reservation WHERE res_date='$actDate' AND res_driver_id IN ('".$row_driver1['dr_id']."') ";        
        $result_rsvpdr=mysql_query($sql_rsvpdr) or die (mysql_error());                                
        $rec_rsvpdr_count=mysql_num_rows($result_rsvpdr);
        $db_fetch = mysql_fetch_array($result_rsvpdr);
            
        if ($rec_rsvpdr_count>0){
            
            $rsvdDriver=true; 
            }
            else{$rsvdDriver=false;}// end if rec count more than 0     
        
        
        if ($rsvdDriver==true){
            for ($t=0 ;$t<=14 ;$t++){    // this is from 8am to 10pm 15 hours        
            $bg_color[$t]='#DBFDC4'; }

    
    for ($h=0;$h<$rec_rsvpdr_count;$h++){
            $db_startTime = mysql_result($result_rsvpdr,$h,'res_startTime'); 
            $db_endTime = mysql_result($result_rsvpdr,$h,'res_endTime'); 
            $ResTime = $db_startTime.'-'.$db_endTime;

        $start_loop = substr($db_startTime,0,2); 
        $end_loop = substr($db_endTime,0,2); 
                                                
        $arraySt = $start_loop - 8;
        $arrayEnd = $end_loop - 8;
        
            for ($w=$arraySt;$w<$arrayEnd ;$w++){
            $bg_color[$w]='#FF0000'; 
                    }// end for on red background
    }// end for res count and time
    
     for($i = 0; $i <= 14; $i++) {  
        echo '<td bgcolor="'.$bg_color[$i].'">'. $ResTimePeriod[$i].'</td>';//first row ends here 
        }  
}//end if driver == true

    else if($rsvdDriver==false){  

    for($i = 0; $i <=14; $i++) {  
     echo '<td bgcolor="'.$GoColor .'">' . $ResTimePeriod[$i]. '</td>';//first row ends here
         } 
     
    } //end if driver == false

    $last_task = 8;
        
    echo '<tr><td id="left" width="100">Task:</td>';//second row starts here

///***THE CODE WORKS FINE UNTIL THIS POINT********/////////    
    
    $startTime = $db_fetch['res_startTime'];
    $endTime = $db_fetch['res_endTime'];
    $start_sub=substr($startTime,0,2); 
    $end_sub=substr($endTime,0,2);
    $diff = $end_sub - $start_sub;
    
    

    $empty = $start_sub - $last_task; 
        if ($empty > 0) {
            echo '<td colspan="' . $empty . '">&nbsp;</td>';
        }
    $last_task = $end_sub;
    
    $db_type = $db_fetch['type'];
    $db_res_id = $db_fetch['res_id'];
    
    echo $db_type . " db type ";
    
    if($diff == 0){ 
        echo '<td colspan="15"></td>';//im not sure why i have to do this but this works lol
    }
    
    echo '<td bgcolor="#FF0000" colspan="'.$diff.'" style="font-weight:bold; text-align:center">';
        
    echo '<font color="white">'. $db_type . '</font> ';
    if($type == $db_type){echo '<input type="button" value="Add On" onClick="location.href=\'addon_driver.php?id='.$db_res_id.'&name='.$name.'&type='.$type.'\'"/>'; }

    echo '<br/> </td>';


}//close while loop 


EDIT: i tried adding another while loop
while($db_fetch = mysql_fetch_assoc($result_rsvpdr)) right after the second row starts but the loop doesn't seem to be running no idea why