Sorry, me again. Is it possible to help me with being able to sort by clicking on column headers. And the code again:

$query = "SELECT firstname, lastname, pilotid1, pilotid2, COUNT(flight_time) AS flights, SUM(block_time) AS block, SUM(flight_time) AS ftime, MAX(date_of_flight) AS date FROM members, pireps WHERE members.pilotid1 = pireps.pilotid2 GROUP BY pilotid1 ORDER BY flights DESC, block DESC, ftime DESC, date DESC";

$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>First</th> <th>Last</th> <th>Pilot ID</th> <th>Flights</th> <th>Block Time</th> <th>Flight Time</th> <th>Rank</th> <th>Currently In</th> <th>Last Flight</th></tr>";

while($row = mysql_fetch_array( $result )) {

echo "<tr><td align=left>";
echo $row['firstname'];
echo "</td><td align=left>";
echo $row['lastname'];
echo "</td><td align=center>";
echo $row['pilotid1'];
echo "</td><td align=center>";
echo $row['flights'];
echo "</td><td align=center>";
echo sprintf( "%4.1f", $row['block'] );
echo "</td><td align=center>";
echo sprintf( "%4.1f", $row['ftime'] );
echo "</td><td align=center>";
$var1 = '<img src="pics/rankings/fo.gif">';
$var2 = '<img src="pics/rankings/sfo.gif">';
$var3 = '<img src="pics/rankings/c.gif">';
$var4 = '<img src="pics/rankings/sc.gif">';
$var5 = '<img src="pics/rankings/jfo.gif">';
if($row['block']>1299.9 && $row['flights']>519){
echo $var4;
}elseif($row['block']>799.9 && $row['flights']>319){
echo $var3;
}elseif($row['block']>399.9 && $row['flights']>159){
echo $var2;
}elseif($row['block']>99.9 && $row['flights']>39){
echo $var1;
}elseif($row['block']>0){
echo $var5;
}
echo $row['rank'];
echo "</td><td align=center>";
$arrival = mysql_query("Select arr_airport from pireps where pilotid2 = '".$row['pilotid1']."' order by date_of_flight DESC LIMIT 0,1") or die(mysql_error());
while($arrive = mysql_fetch_array($arrival)){
echo $arrive['arr_airport'];
}
echo "</td><td align=center>";
echo $row['date'];
echo "</td></tr>";
}
echo "</table>";
?>

And the solution, thanks to ProfessorPC.....

switch ($_GET['sort']){
case 1:
	$order = 'flights';
	$order2 = 'block';
	$order3 = 'ftime';
	$order4 = 'date';
	break;
case 2:
	$order = 'block';
	$order2 = 'flights';
	$order3 = 'ftime';
	$order4 = 'date';	
	break;
case 3:
	$order = 'ftime';
	$order2 = 'flights';
	$order3 = 'block';
	$order4 = 'date';
	break;
case 4:
	$order = 'date';
	$order2 = 'flights';
	$order3 = 'block';
	$order4 = 'ftime';
	break;
default:
	$order = 'flights';
	$order2 = 'block';
	$order3 = 'ftime';
	$order4 = 'date';
}

$query = "SELECT firstname, lastname, pilotid1, pilotid2, COUNT(flight_time) AS flights, SUM(block_time) AS block, SUM(flight_time) AS ftime, MAX(date_of_flight) AS date FROM members, pireps WHERE members.pilotid1 = pireps.pilotid2 GROUP BY pilotid1 ORDER BY ".$order." DESC, ".$order2." DESC, ".$order3." DESC, ".$order4." DESC"; 

$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>First</th> <th>Last</th> <th>Pilot ID</th> <th><a href='rosterrank.php?sort=1'>Flights</a></th> <th><a href='rosterrank.php?sort=2'>Block Time</a> </th> <th><a href='rosterrank.php?sort=3'>Flight Time</a> </th> <th>Rank</th> <th>Currently In</th> <th>Last Flight</th></tr>";

while($row = mysql_fetch_array( $result )) {

echo "<tr><td align=left>";
echo $row['firstname'];
echo "</td><td align=left>";
echo $row['lastname'];
echo "</td><td align=center>";
echo $row['pilotid1'];
echo "</td><td align=center>";
echo $row['flights'];
echo "</td><td align=center>";
echo sprintf( "%4.1f", $row['block'] );
echo "</td><td align=center>";
echo sprintf( "%4.1f", $row['ftime'] );
echo "</td><td align=center>";
$var1 = '<img src="pics/rankings/fo.gif">';
$var2 = '<img src="pics/rankings/sfo.gif">';
$var3 = '<img src="pics/rankings/c.gif">';
$var4 = '<img src="pics/rankings/sc.gif">';
$var5 = '<img src="pics/rankings/jfo.gif">';
if($row['block']>1299.9 && $row['flights']>519){
echo $var4;
}elseif($row['block']>799.9 && $row['flights']>319){
echo $var3;
}elseif($row['block']>399.9 && $row['flights']>159){
echo $var2;
}elseif($row['block']>99.9 && $row['flights']>39){
echo $var1;
}elseif($row['block']>0){
echo $var5;
}
echo $row['rank'];
echo "</td><td align=center>";
$arrival = mysql_query("Select arr_airport from pireps where pilotid2 = '".$row['pilotid1']."' order by date_of_flight DESC LIMIT 0,1") or die(mysql_error());
while($arrive = mysql_fetch_array($arrival)){
echo $arrive['arr_airport'];
}
echo "</td><td align=center>";
echo $row['date'];
echo "</td></tr>";
}
echo "</table>";
?>

was no problem :)

Professor PC,

I have a sort problem too. I would like to be able to sort on the following headers by city, notice date, start date, and value. Here is function under the model.

function load_default_query() {
      $date_today = date('Y-m-d');
	    	
      $query_sp = "
          select distinct p.tb_p_id,
          case p.tb_p_pubpri when '1' then 'Public' when '2' then 'Private' end, 
          date_format(p.tb_p_date_notice, '%m-%d-%Y') as tb_p_date_notice, 
          date_format(p.tb_p_date_bid_due, '%m-%d-Y') as     tb_p_date_bid_due, 
          s.tb_s_name_full, 
          c.tb_c_name, 
          md5(p.tb_p_id) as md5id,
          p.tb_p_value
          FROM tb_projects p, tb_states s, tb_cities c,tb_project_type_mapping  pt 
          WHERE p.tb_c_id=c.tb_c_id AND c.tb_s_id=s.tb_s_id AND p.tb_p_id=pt.tb_p_id AND  p.tb_p_active='1' AND s.tb_s_active='1' AND c.tb_c_active='1'
       ";
			
      $query_sp .= " ORDER BY tb_p_date_notice DESC ";
			
	    
      return $query_sp;
 }

And here's the view page:

<table width="95%" border="0" class="table-res" cellpadding="0" cellspacing="0">
    <tr>
								<th width="10%" align="center" valign="top">
									<span class="text-13-s">Title</span>
								</th>
								<th width="15%" align="center" valign="top">
									<span class="text-13-s">City</span>
								</th>
								<th width="10%" align="center" valign="top">
									<span class="text-13-s">Notice Date</span>
								</th>
								<th width="10%" align="center" valign="top">
									<span class="text-13-s">Bid Due/Start Date</span>
								</th>
								<th width="10%" align="center" valign="top">
									<span class="text-13-s">Value</span>
								</th>
								<th width="30%" align="center" valign="top">
									<span class="text-13-s">Project Type</span>
								</th>								
     </tr>
		
<?php	
		$count = 0;
		$rows = 0;
		foreach ($fd_search_results as $row) {
								    

                                                $rows++;
			$rowcolor = ($rows%2 == 0)?'#EDEDED':'#ffffff';
								
?>
	<?php
									               if ($count == 1) {
	?>	
									<tr>
									          <td colspan="6" align="center" valign="middle" bgcolor="F2F2F2">
									<?php print anchor('c_landing/signup', img($fd_base.'/'.$fd_images.'/lbl-signup- now-fl.jpg'), array('title' => '')); ?>
									         </td>
									</tr>
								      <?php
									}
	      ?>
									<tr bgcolor=<?php echo $rowcolor;?>>
										<td width="10%" align="center" valign="top">
										<?php echo anchor('c_landing/freesearchprojectdetail/'.$row->md5id, 'PREVIEW', ''); ?>
										</td>
										<td width="15%" align="left" valign="top">
										<?php echo $row->tb_c_name; ?>
										</td>
										<td width="15%" align="center" valign="top">
										<?php echo $row->tb_p_date_notice; ?>
										</td>
										<td width="15%" align="center" valign="top">
										<?php echo $row->tb_p_date_bid_due; ?>
										</td>
										<td width="15%" align="center" valign="top">
										$<?php echo $row->tb_p_value; ?>
										</td>
										<td width="30%" align="left" valign="top" class="text-11">
										<?php echo $fd_search_results_pt[$count]; ?>
										</td>
									</tr>
					
<?php		
							
       $count++;
   }
?>
</table>

you can use the same method as the above solution. using the switch in your function and changing your ORDER BY in your query.
on your display page you can add

<th width="10%" align="center" valign="top"><a href="thispage.php?sort=1">Notice Date</a>

continue doing that with the headers you want sortable. then in your switch just follow the code from the above post. if you have any problems post them and ill take a look but by following the code above for your function and adding the <a href="thispage.php?sort=1-4"> should work.

Professor PC,

I couldn't figure it out. I just keep getting some error messages. I don't know what you're talking about when you mention "><a href="thispage.php?sort=1">Notice Date</a>

I don't know what to put after the a href

The pages on my website after a query for search is made returns a lot of pages

texas[bidding].com

Here is the complete model function for the free search and the changes that I made with your suggestion:

<?php
	class M_free_search extends Model {
	    function __construct() {
	        parent::Model();
	        $this->load->helper('url');
	        $this->load->helper('html');
	    }


#########################
#
#
#
#########################	    
function get_pt($id) {
$str = "";
$query_gpt = "select pt.tb_pt_desc FROM tb_project_type pt, tb_project_type_mapping ptm WHERE pt.tb_pt_id=ptm.tb_pt_id AND ptm.tb_p_id='".mysql_real_escape_string($id)."' ORDER BY pt.tb_pt_desc";
$query_gpt = mysql_query($query_gpt);
if ($arr_gpt = mysql_fetch_array($query_gpt)) {
do {
$str .= $arr_gpt[0].', ';
} while($arr_gpt = mysql_fetch_array($query_gpt));
}
//return $str;
return substr($str, 0, strlen($str)-2);
 }

	    
#########################
#
#
#
#########################	    
function load_default_query() {
$date_today = date('Y-m-d');
	    	
switch ($_GET['sort']){

case 1:	$order = 'tb_c_name';	
$order2 = 'tb_p_date_notice';	
$order3 = 'tb_p_date_bid_due';	
$order4 = 'tb_p_value';	
break;
			
case 2:	
$order = 'tb_p_date_notice';	
$order2 = 'tb_c_name';	
$order3 = 'tb_p_date_bid_due';	
$order4 = 'tb_p_value';		
break;
			
case 3:	
$order = 'tb_p_date_bid_due';	
$order2 = 'tb_c_name';	
$order3 = 'tb_p_date_notice';	
$order4 = 'tb_p_value';	
break;
			
case 4:	
$order = 'tb_p_value';	
$order2 = 'tb_c_name';	
$order3 = 'tb_p_date_notice';	
$order4 = 'tb_p_date_bid_due';	
break;
					
default:	
$order = 'tb_c_name';	
$order2 = 'tb_p_date_notice';	
$order3 = 'tb_p_date_bid_due';	
$order4 = 'tb_p_value';	
					
}
			
$query_sp = "

select distinct p.tb_p_id,
case p.tb_p_pubpri when '1' then 'Public' when '2' then 'Private' end, 
date_format(p.tb_p_date_notice, '%m-%d-%Y') as tb_p_date_notice, 
date_format(p.tb_p_date_bid_due, '%m-%d-%Y') as tb_p_date_bid_due, 
s.tb_s_name_full, 
c.tb_c_name, 
md5(p.tb_p_id) as md5id,
p.tb_p_value
FROM tb_projects p, tb_states s, tb_cities c, tb_project_type_mapping pt 
WHERE p.tb_c_id=c.tb_c_id AND c.tb_s_id=s.tb_s_id AND p.tb_p_id=pt.tb_p_id AND p.tb_p_active='1' AND s.tb_s_active='1' AND c.tb_c_active='1'
	    	
";
			
$query_sp .= " ORDER BY ".$order." DESC, ".$order2." DESC, ".$order3." DESC, ".$order4." DESC"; 
			
	    
return $query_sp;
	    
}
	    
	    
#########################
#
#
#
#########################	    
	   
function compile_query() {
$date_today = date('Y-m-d');	
			
			   	
	    	
//project types
$query_pt = "";
if ($this->input->post('frm_srch_pt')) {
$csv_pt = join(',', $this->input->post('frm_srch_pt'));
$query_pt = " AND pt.tb_pt_id IN (".$csv_pt.") ";
	    	
}

//bid due date
$query_bd = "";	
if (trim($this->input->post('frm_srch_bddl'))) {
if (trim($this->input->post('frm_srch_bdd'))) {
$query_bd = " AND p.tb_p_date_bid_due".mysql_real_escape_string(trim($this->input->post('frm_srch_bddl')))."'".mysql_real_escape_string(trim($this->input->post('frm_srch_bdd')))."' ";
       }
}	    	
	    		    	
//state-city
$query_state = "";
$query_city = "";
if (trim($this->input->post('frm_srch_state'))) {
if (trim($this->input->post('frm_srch_city'))) {
	    			
$query_city = " AND c.tb_c_id='".mysql_real_escape_string(trim($this->input->post('frm_srch_city')))."' ";	    			
}

$query_state = " AND s.tb_s_id='".mysql_real_escape_string(trim($this->input->post('frm_srch_state')))."' ";
}
	    	
//scope
$query_scope = "";
if (trim($this->input->post('frm_srch_scope'))) {
$query_scope = " AND p.tb_p_pubpri='".mysql_real_escape_string(trim($this->input->post('frm_srch_scope')))."' ";
}
	    	
$query_sp = "
select distinct p.tb_p_id,
case p.tb_p_pubpri when '1' then 'Public' when '2' then 'Private' end, 
date_format(p.tb_p_date_notice, '%m-%d-%Y') as tb_p_date_notice, 
date_format(p.tb_p_date_bid_due, '%m-%d-%Y') as tb_p_date_bid_due, 
s.tb_s_name_full, 
c.tb_c_name, 
md5(p.tb_p_id) as md5id,
p.tb_p_value
FROM tb_projects p, tb_states s, tb_cities c, tb_project_type_mapping pt 
WHERE p.tb_c_id=c.tb_c_id AND c.tb_s_id=s.tb_s_id AND p.tb_p_id=pt.tb_p_id AND p.tb_p_active='1' AND s.tb_s_active='1' AND c.tb_c_active='1'
";
			
$query_sp .= $query_state.$query_city.$query_bd.$query_scope.$query_pt;
$query_sp .= " ORDER BY ".$order." DESC, ".$order2." DESC, ".$order3." DESC, ".$order4." DESC"; 	    	
			
return $query_sp;
 }
	    
	    
#########################
#
#
#
#########################	    
 function search_w_pcq($query, $return_num_rows=0) {
$query = $this->db->query($query);
			
if ($return_num_rows) {
return $query->num_rows;
}
else {
       if ($query->num_rows > 0) {
       return $query->result();
       }
       else {
return 0;
       }
}	    	
}
	    
	    
#########################
#
#
#
#########################

 function convertmd5idtoid($md5id) {
	    	
$query_cmi = "select tb_p_id FROM tb_projects WHERE md5(tb_p_id
='".mysql_real_escape_string($md5id)."'";
	    	
$query_cmi = $this->db->query($query_cmi);
      if ($query_cmi->num_rows > 0) {
	return $query_cmi->result();
      }
      else {
	return 0;
            }
     }
	    
#########################
#
#
#
#########################	    
	   
 function get_project_detail($id) {
			
$query_sp = "
	    	
select distinct p.tb_p_id,
p.tb_p_desc,
case p.tb_p_pubpri when '1' then 'Public' when '2' then 'Private' end as tb_p_pubpri, 
date_format(p.tb_p_date_notice, '%m-%d-%Y') as tb_p_date_notice, 
date_format(p.tb_p_date_bid_due, '%m-%d-%Y') as tb_p_date_bid_due, 
s.tb_s_name_full, 
c.tb_c_name, 
md5(p.tb_p_id) as md5id,
p.tb_p_value
FROM tb_projects p, tb_states s, tb_cities c, tb_project_type_mapping pt 
WHERE p.tb_c_id=c.tb_c_id AND c.tb_s_id=s.tb_s_id AND p.tb_p_id=pt.tb_p_id AND p.tb_p_active='1' AND p.tb_p_id='".mysql_real_escape_string($id)."' AND s.tb_s_active='1' AND c.tb_c_active='1'
	    	
";
$query_sp = $this->db->query($query_sp);
if ($query_sp->num_rows == 1) {
      return $query_sp->result();
}
else {
      return 0;
}			 
}
}
?>

Also here is the page view for the page:

<?php 
	$this->load->helper('html');
	$this->load->helper('form');
	
	echo $fd_header;
?>	
<table width="1000" border="0" cellpadding="0" cellspacing="0" class="">
         <tr>
                 <td align="left" valign="top">
<img src="<?php echo $fd_base.'/'.$fd_images.'/';?>spacer.gif" width="1" height="20" alt="" /><br />
                </td>
         </tr>
</table>

<table height="400" width="1000" border="0" cellpadding="0" cellspacing="0" align="center" class="">

         <tr>
                <td width="800" align="center" valign="top">
			
<table width="800" height="400" border="0" cellpadding="0" cellspacing="0" class="">

         <tr>
                 <td width="100%" align="center" valign="top">
<table width="100%" border="0" cellpadding="0" cellspacing="0" class="">
         <tr>
							
                 <td align="center" valign="top">
							
<span class="text-13-blue-s">Texas Construction Projects Search Results</span>
							
<br /><br />
                </td>
        </tr>
         <tr>
							
                 <td align="center" valign="top">
							
<span class="text-9">Sorting by city, dates, and value is available when you become a member.</span>
                 </td>
         </tr>
							
							
         <tr>
								<td align="center" valign="top">
									<br /><br />
									<?php echo $this->data_m['fd_search_results_links'] ;?>
									</td>
        </tr>
														
         <tr>
	<td align="left" valign="top">
							
<img src="<?php echo $fd_base.'/'.$fd_images.'/';?>spacer.gif" width="1" height="20" alt="" /><br />			
								</td>
        </tr>							
</table>					
<table width="95%" border="0" class="table-res" cellpadding="0" cellspacing="0">
         <tr>
								<th width="10%" align="center" valign="top">
	       <span class="text-13-s">Title</span>
                </th>
								<th width="15%" align="center" valign="top">
									<a href="freesearchresults.php?sort=1">City</a>
								</th>
								<th width="10%" align="center" valign="top">
									<a href="freesearchresults.php?sort=2">Notice Date</a>
								</th>
								<th width="10%" align="center" valign="top">
									<a href="freesearchresults.php?sort=3">Bid Due/Start Date</a>
								</th>
								<th width="10%" align="center" valign="top">
									<a href="freesearchresults.php?sort=4">Value</a>
								</th>
								<th width="30%" align="center" valign="top">
									<span class="text-13-s">Project Type</span>
								</th>								
          </tr>
		
<?php	
           $count = 0;
           $rows = 0;
           foreach ($fd_search_results as $row) {
								$rows++;
								$rowcolor = ($rows%2 == 0)?'#EDEDED':'#ffffff';
								
?>
	
<?php
									if ($count == 1) {
?>	
							
       <tr>
									<td colspan="6" align="center" valign="middle" bgcolor="F2F2F2">
									<?php print anchor('c_landing/signup', img($fd_base.'/'.$fd_images.'/lbl-signup-now-fl.jpg'), array('title' => '')); 
                ?>
									</td>
       </tr>
	
<?php
}
?>
							
       <tr bgcolor=<?php echo $rowcolor;?>>
								<td width="10%" align="center" valign="top">
								
<?php echo anchor('c_landing/freesearchprojectdetail/'.$row->md5id, 'PREVIEW', ''); ?>
									</td>
									<td width="15%" align="left" valign="top">
								
<?php echo $row->tb_c_name; ?>
									</td>
									<td width="15%" align="center" valign="top">
									<?php echo $row->tb_p_date_notice; ?>
									</td>
									<td width="15%" align="center" valign="top">
									<?php echo $row->tb_p_date_bid_due; ?>
									</td>
									<td width="15%" align="center" valign="top">
									$<?php echo $row->tb_p_value; ?>
									</td>
									<td width="30%" align="left" valign="top" class="text-11">
									<?php echo $fd_search_results_pt[$count]; ?>
									</td>
							
        </tr>
					
	<?php		
							
                      $count++;
	      }
	?>
</table>
<br />
<table width="800" border="0" cellpadding="0" cellspacing="0" class="">
      <tr>
								<td align="center" valign="top">
<?php echo $this->data_m['fd_search_results_links'] ;?>
								</td>
      </tr>	
								
							
      <tr>
								<td align="left" valign="top">
							
<img src="<?php echo $fd_base.'/'.$fd_images.'/';?>spacer.gif" width="1" height="20" alt="" /><br />
								</td>
     </tr>
</table>
</td>
</tr>
</table>
					
							
</td>
<td width="200" align="center" valign="top">
	<?php print ads(); ?>		
</td>		

</tr>
</table>			
			
<?php
	echo $fd_footer;
?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.