Jump to content

drop down that changes sql query order

pixeltrace

By pixeltrace
in PHP Coding Help

 Share

Recommended Posts

pixeltrace Advanced Member

pixeltrace

Posted
Posted

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
pixeltrace Advanced Member

pixeltrace

Posted
  • Author
Posted

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!

monk.e.boy Newbie

monk.e.boy

Posted
Posted

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • 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.