Jump to content

drop down that changes sql query order


pixeltrace

Recommended Posts

guys,

 

i need help,

 

i have a table that lists all items in my database

and i have a drop down menu that should change the order of the list

via

name

clientID

date

staff

 

my problem now is, i dont know how to do it.

the only thing that i have done so far is the generate data

from the database

 

below is my codes for this page.

<table width="100" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><img src="../images/spacer.gif" width="6" height="10"></td>
    <td><table width="216" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td colspan="3" valign="top" bgcolor="#3261BB"><img src="images/spacer.gif" width="1" height="1"></td>
      </tr>
      <tr>
        <td align="left" bgcolor="#3261BB" width="1"><img src="images/spacer.gif" width="1" height="1"></td>
        <td width="214" valign="top"><table width="794" border="0" cellspacing="2" cellpadding="0">
            <tr>
              <td colspan="6"><table width="616" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="329" class="text6">
				  total record found :					  </td>
                    <td width="287"> </td>
                  </tr>
              </table></td>
            </tr>
            <tr>
              <td colspan="6"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="459"> </td>
                    <td width="158" class="text6">sort by :
                      <select name="select">
                          <option value="1">name</option>
                          <option value="2">client ID</option>
                          <option value="3">reg date</option>
                        </select>
                    </td>
                  </tr>
              </table></td>
            </tr>
            <tr>
              <td colspan="6"> </td>
            </tr>
            <tr>
              <td width="58" align="center" bgcolor="#83C2ED" class="text5">ID</td>
              <td width="215" align="center" valign="top" bgcolor="#83C2ED" class="text5">name</td>
              <td width="91" align="center" bgcolor="#83C2ED" class="text5">joined date </td>
              <td width="200" align="center" bgcolor="#83C2ED" class="text5">rate agreed </td>
              <td width="175" align="center" bgcolor="#83C2ED" class="text5">remark</td>
              <td width="41" bgcolor="#83C2ED"> </td>
            </tr>
            <?
include 'db_connect.php';

$uSql = "SELECT clientid, client_name, day_joined, month_joined, year_joined, client_rate, client_remarks FROM clients ORDER by client_name DESC";
$uResult = mysql_query($uSql, $connection);
if(!$uResult){
    echo 'no data found';
}
else{   	 
while($uRow = mysql_fetch_row($uResult)){		
?>
            <tr>
              <td bgcolor="#FFFFCC" class="text8"> C
                <?= $uRow[0]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[1]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[2]?>
                -
                <?= $uRow[3]?>
                -
                <?= $uRow[4]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[5]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[6]?></td>
              <td align="center" bgcolor="#FFFFCC"><a href="clientedit.php?$clientid=$clientid" class="link1">edit</a></td>
            </tr>
            <?        
}
} 
?>
            <tr>
              <td colspan="6"> </td>
            </tr>
            <tr>
              <td colspan="6"> </td>
            </tr>
        </table></td>
        <td align="right" bgcolor="#3261BB" width="1"><img src="images/spacer.gif" width="1" height="1"></td>
      </tr>
      <tr>
        <td colspan="3" valign="top" bgcolor="#3261BB"><img src="images/spacer.gif" width="1" height="1"></td>
      </tr>
    </table></td>
    <td><img src="../images/spacer.gif" width="6" height="10"></td>
  </tr>
</table>

 

hope you could give me the codes that would do that kind of function.

thanks!

Link to comment
https://forums.phpfreaks.com/topic/39279-drop-down-that-changes-sql-query-order/
Share on other sites

so far this is the code that i have done

and i am not sure if its working since i dont see any changes in my table

need help please!

 

<table width="100" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><img src="../images/spacer.gif" width="6" height="10"></td>
    <td><table width="216" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td colspan="3" valign="top" bgcolor="#3261BB"><img src="images/spacer.gif" width="1" height="1"></td>
      </tr>
      <tr>
        <td align="left" bgcolor="#3261BB" width="1"><img src="images/spacer.gif" width="1" height="1"></td>
        <td width="214" valign="top"><table width="794" border="0" cellspacing="2" cellpadding="0">
            <tr>
              <td colspan="6"><table width="616" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="329" class="text6">
				  total record found :					  </td>
                    <td width="287"> </td>
                  </tr>
              </table></td>
            </tr>
            <tr>
              <td colspan="6"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="459"> </td>
                    <td width="158" class="text6">sort by :
<select name="sortby" onFocus="selectedIndex = 0"
onChange="if( options[selectedIndex].value != '') document.location = options[selectedIndex].value" class="sorteventlist">
                          <option value="clientmngr.php?id=2&type=1">name</option>
                          <option value="clientmngr.php?id=2&type=2">client ID</option>
                          <option value="clientmngr.php?id=2&type=3">reg date</option>
                        </select>
                    </td>
                  </tr>
              </table></td>
            </tr>
            <tr>
              <td colspan="6"> </td>
            </tr>
            <tr>
              <td width="58" align="center" bgcolor="#83C2ED" class="text5">ID</td>
              <td width="215" align="center" valign="top" bgcolor="#83C2ED" class="text5">name</td>
              <td width="91" align="center" bgcolor="#83C2ED" class="text5">joined date </td>
              <td width="200" align="center" bgcolor="#83C2ED" class="text5">rate agreed </td>
              <td width="175" align="center" bgcolor="#83C2ED" class="text5">remark</td>
              <td width="41" bgcolor="#83C2ED"> </td>
            </tr>
							<?
				$type = $_GET['type'];
				 {
				$order = 'client_name';
				  if ($type=='1') {$order = 'client_name';}
				  if ($type=='2') {$order = 'clientid';}
				  if ($type=='3') {$order = 'staff_created';}
				} ?>


            <?
include 'db_connect.php';
$order = '$order';
$uSql = "SELECT clientid, client_name, day_joined, month_joined, year_joined, client_rate, client_remarks FROM clients ORDER by '$order' DESC";
$uResult = mysql_query($uSql, $connection);
if(!$uResult){
    echo 'no data found';
}
else{   	 
while($uRow = mysql_fetch_row($uResult)){		
?>
            <tr>
              <td bgcolor="#FFFFCC" class="text8"> C
                <?= $uRow[0]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[1]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[2]?>
                -
                <?= $uRow[3]?>
                -
                <?= $uRow[4]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[5]?></td>
              <td bgcolor="#FFFFCC" class="text8"> 
                  <?= $uRow[6]?></td>
              <td align="center" bgcolor="#FFFFCC"><a href="clientedit.php?$clientid=$clientid" class="link1">edit</a></td>
            </tr>
            <?        
}
} 
?>
            <tr>
              <td colspan="6"> </td>
            </tr>
            <tr>
              <td colspan="6"> </td>
            </tr>
        </table></td>
        <td align="right" bgcolor="#3261BB" width="1"><img src="images/spacer.gif" width="1" height="1"></td>
      </tr>
      <tr>
        <td colspan="3" valign="top" bgcolor="#3261BB"><img src="images/spacer.gif" width="1" height="1"></td>
      </tr>
    </table></td>
    <td><img src="../images/spacer.gif" width="6" height="10"></td>
  </tr>
</table>

 

thanks!

I guess you know that changing :

 

<td bgcolor="#FFFFCC" class="text8"><?= $uRow[0]?></td>
<td bgcolor="#FFFFCC" class="text8"><?= $uRow[1]?></td>

 

to:

 

<td bgcolor="#FFFFCC" class="text8"><?= $uRow[1]?></td>
<td bgcolor="#FFFFCC" class="text8"><?= $uRow[0]?></td>

 

will swap the order of the columns. You don't need to change the SQL.

 

If I were you I'd make a function for order_1 and one for order_2, then call which ever one you need:

 

function order_1( $uRow )
{
?>
<td bgcolor="#FFFFCC" class="text8"><?= $uRow[0]?></td>
<td bgcolor="#FFFFCC" class="text8"><?= $uRow[1]?></td>
... other 
... rows
... here
<?
}

function order_2( $uRow )
{
?>
<td bgcolor="#FFFFCC" class="text8"><?= $uRow[1]?></td>
<td bgcolor="#FFFFCC" class="text8"><?= $uRow[0]?></td>
... other 
... rows
... here
<?
}
?>

 

Then call which ever row inside the loop:

 

while($uRow = mysql_fetch_row($uResult))
{		
  if( $order == 1 )
    order_1( $uRow );
  else
    order_2( $uRow );
}	

 

There are a lot of different ways of doing this kind of thing  :)

 

monk.e.boy

Archived

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

×
×
  • Create New...

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.