Link to home
Start Free TrialLog in
Avatar of Stiebel Eltron
Stiebel EltronFlag for Thailand

asked on

How to change the sorting type from our web using PHP?

<?php
session_start();

$edition = $_GET['edition'];
$description = $_GET['description'];
$category_id = $_GET['category_id'];
$country_id = $_GET['country_id'];
$city_id = $_GET['city_id'];
$year = $_GET['year'];
$firstdate = $_GET['firstdate'];
$lastdate = $_GET['lastdate'];
$status_id = $_GET['status_id'];
$source = $_GET['source'];
$remark = $_GET['remark'];
$sort_field = $_GET['sort_field'];
$sort_type = $_GET['sort_type'];

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>:::My Sbux Collections:::</title>

<style TYPE="text/css">
<!--
	a
 	{
  		color:#000080;
		font-family:verdana;
		text-decoration:none;
		font-size:12px
	}
	
	
	a:active
 	{
  		color:red;

	}

	a:hover
 	{
  		color:#FF0000;
		text-decoration:none;
		cursor:crosshair;
	}
		
	table
	{
		font-family:verdana;
	}
	
	.td
	{
		font-family:verdana;
		font-size:10px;
		font-weight:lighter;
		text-align:center;
	}

	.td1
	{
		font-family:verdana;
		font-size:10px;
		font-weight:lighter;
		text-align:center;
		color:#FFF;
	}

	.title
	{
		font-family:verdana;
		font-size:12px;
		font-weight:bold;
		text-align:center;
	}
	
	.header
	{
		font-family:Tahoma;
		font-size:17px;
		font-weight:bold;
		text-align:left;
	}

	.b
	{
		font-color:#FF0000;
		font-family:verdana;
		font-size:12px;
	}
	
	body
	{
	background-color:#0c3f00;
	background-size:cover;
	margin-top: 0px;
	}
	
	.searchspan {
		font-family:Tahoma, Geneva, sans-serif;
		font-size:12px;
		text-align:right;
		float:right;
		margin-right:15px;
		margin-top:3px;
	}


-->
</STYLE>

<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
// Set up the image files to be used.
var theImages = new Array() // do not change this
// To add more image files, continue with the
// pattern below, adding to the array.

theImages[0] = 'image/Sbux-header1.jpg'
theImages[1] = 'image/Sbux-header2.jpg'
theImages[2] = 'image/Sbux-header3.jpg'
theImages[3] = 'image/Sbux-header4.jpg'
theImages[4] = 'image/Sbux-header5.jpg'

// do not edit anything below this line

var j = 0
var p = theImages.length;
var preBuffer = new Array()
for (i = 0; i < p; i++){
   preBuffer[i] = new Image()
   preBuffer[i].src = theImages[i]
}
var whichImage = Math.round(Math.random()*(p-1));
function showImage(){
document.write('<img src="'+theImages[whichImage]+'">');
}

//  End -->
</script>

</head>
<body>

<table width="900" border="1" cellspacing="0" cellpadding="5" style="border-collapse:collapse" bordercolor="#999999" align="center">
  <tr>
  	<td colspan="13" style="background-size:cover;" width="100%" height="100%" align="center" bgcolor="#FFFFFF">
    	<SCRIPT LANGUAGE="JavaScript">
    	<!-- Begin
		showImage();
		//  End -->
		</script>		
    </td>
  </tr>
  <tr>
  	<td colspan="13" bgcolor="#FFFFFF">
    	<?php
		include('menu.php');
		?>
    </td>
  </tr>
  <tr>
    <td colspan="13" bgcolor="#D6D6D6" class="header">My Sbux Collections<span class="searchspan"><a href="item_search.php?search_mode=2">Search</a></span></td>
  </tr>
  <tr>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=item.id&sort_type=<?php if ($sort_field == 'item.id' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Mug Details</a><?php if ($sort_field == 'item.id') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6">No.</td>
    <td class="title" bgcolor="#D6D6D6">Picture</td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=item.edition&sort_type=<?php if ($sort_field == 'item.edition' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Edition</a><?php if ($sort_field == 'item.edition') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=item.description&sort_type=<?php if ($sort_field == 'item.description' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Description</a><?php if ($sort_field == 'item.description') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=category.category_name&sort_type=<?php if ($sort_field == 'category.category_name' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Category</a><?php if ($sort_field == 'category.category_name') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=country.country_name&sort_type=<?php if ($sort_field == 'country.country_name' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Country</a><?php if ($sort_field == 'country.country_name') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=city.city_name&sort_type=<?php if ($sort_field == 'city.city_name' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">City</a><?php if ($sort_field == 'city.city_name') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=item.year&sort_type=<?php if ($sort_field == 'item.year' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Year</a><?php if ($sort_field == 'item.year') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=item.date_acq&sort_type=<?php if ($sort_field == 'item.date_acq' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Date Acquired</a><?php if ($sort_field == 'item.date_acq') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=status.status_name&sort_type=<?php if ($sort_field == 'status.status_name' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Status</a><?php if ($sort_field == 'status.status_name') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=item.sorce&sort_type=<?php if ($sort_field == 'item.sorce' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Source</a><?php if ($sort_field == 'item.sorce') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
    <td class="title" bgcolor="#D6D6D6"><a href="item_show.php?edition=<?php echo $edition;?>&description=<?php echo $description;?>&category_id=<?php echo $category_id;?>&country_id=<?php echo $country_id;?>&city_id=<?php echo $city_id;?>&year=<?php echo $year;?>&firstdate=<?php echo $firstdate;?>&lastdate=<?php echo $lastdate;?>&status_id=<?php echo $status_id;?>&source=<?php echo $source;?>&remark=<?php echo $remark;?>&sort_field=item.remark&sort_type=<?php if ($sort_field == 'item.remark' && $sort_type == 'ASC') { echo 'DESC'; } else { echo 'ASC'; }?>">Remark</a><?php if ($sort_field == 'item.remark') { ?> <img src="<?php if ($sort_type == 'DESC') { echo "image/up.png"; } else { echo "image/down.png"; } ?>" width="12" height="12" border="0" /><?php } ?></td>
  </tr>
  <?php
  include('connect.php');
  
  if ($sort_field == '') {
	  $sort_field = 'country.country_name';	  
  }
  
  if ($sort_type == '') {
	  $sort_type = 'ASC';
  }
  
  if ($sort_field == 'country.country_name') {
	  $sort_next = 'city.city_name ASC , item.date_acq ASC';
  } else if ($sort_field == 'city.city_name') {
	  $sort_next = 'item.date_acq';
  } else if ($sort_field == 'item.date_acq') {
	  $sort_next = 'country.country_name ASC, city.city_name ASC';
  } else {
	  $sort_next = 'country.country_name ASC, city.city_name ASC, item.date_acq';
  }
  
  if ($edition != '') {
	  $sql_edition = "AND item.edition LIKE '$edition'";
  }
  
  if ($description != '') {
	  $sql_description = "AND item.description LIKE '$description'";
  }
  
  if ($category_id != '') {
	  $sql_category_id = "AND category.category_id = '$category_id'";
  }
  
  if ($country_id != '') {
	  $sql_country_id = "AND country.country_id = '$country_id'";
  }
  
  if ($city_id != '') {
	  $sql_city_id = "AND city.city_id = '$city_id'";
  }
  
  if ($year != '') {
	  $sql_year = "AND item.year = '$year'";
  }
  
  if ($firstdate != '') {
	  if ($lastdate == '') {
		  $sql_date = "AND item.date_acq = '$firstdate'";
	  } else {
		  $sql_date = "AND item.date_acq >= '$firstdate' AND item.date_acq <= '$lastdate'";
	  }
  }
  
  if ($status_id != '') {
	  $sql_status_id = "AND status.status_id = '$status_id'";
  }
  
  if ($source != '') {
	  $sql_source = "AND item.sorce LIKE '$source'";
  }
  
  if ($remark != '') {
	  $sql_remark = "AND item.remark LIKE '$remark'";
  } 
  
  $query = mysql_query("SELECT item.*,category.*,country.*,city.*,status.* 
					   FROM (((item LEFT JOIN category ON item.category_id = category.category_id)
					   LEFT JOIN country ON item.country_id = country.country_id)
					   LEFT JOIN city ON item.city_id = city.city_id)
					   LEFT JOIN status ON item.status_id = status.status_id
					   WHERE status.status_id != '2'
					   $sql_edition
					   $sql_description
					   $sql_category_id
					   $sql_country_id
					   $sql_city_id
					   $sql_year
					   $sql_date
					   $sql_status_id
					   $sql_source
					   $sql_remark
					   ORDER BY $sort_field $sort_type,  
					   $sort_next") or die (" error 253 ");
  $no = 0;
  while ($result = mysql_fetch_array($query)) {
	  $no++;
	  if ($bgcolor == "#FFFFFF") {
		$bgcolor = "#CCCCCC";
	} else {
		$bgcolor = "#FFFFFF";
	}
  ?>
  <tr bgcolor="<?php echo $bgcolor;?>">
    <td class="td"><a href="item_show_detail.php?id=<?php echo $result['id'];?>">Details</a></td>
    <td class="td"><?=$no;?></td>
    <td class="td"><img src="picture/<?=$result["picture"];?>" width="125" height="100"></td>
    <td class="td"><?php echo $result['edition'];?></td>
    <td class="td"><?php echo $result['description'];?></td>
    <td class="td"><?php echo $result['category_name'];?></td>
    <td class="td"><?php echo $result['country_name'];?></td>
    <td class="td"><?php echo $result['city_name'];?></td>
    <td class="td"><?php echo $result['year'];?></td>
    <td class="td"><?php echo $result['date_acq'];?></td>
    <td class="td" <?php if ($result['status_id'] == '1') {  echo "bgcolor=\"#FFFFCC\"";  } else if ($result['status_id'] == '2') { echo "bgcolor=\"#FFCC99\""; } ?>><?php echo $result['status_name'];?></td>
    <td class="td"><?php echo $result['sorce'];?></td>
    <td class="td"><?php echo $result['remark'];?></td>
  </tr>
  <?php
  }
  ?>
  <tr>
  	<td colspan="13" bgcolor="#666666" class="td1">
		STARBUCKS MUGS COLLECTION &copy;2012
    </td>
  </tr>

</table>

</body>
</html>

Open in new window

Dear EE experts,

Kindly check the attached embed experts for the script of our webpage:
http://www.headhuntermanila.com/sbux/item_show.php

We would like to sort the items by CATEGORY then COUNTRY then by CITY.
At this moment, it is sorted by COUNTRY then by CITY only.

Thank you in advance and hope to hear soon...
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
You would need to change the contents of the $sort_field and $sort_type variables, or change the ORDER BY clause on line 264.  Please post the CREATE TABLE statement for the category table and tell us which column you want to sort by.
Avatar of Stiebel Eltron
Stiebel Eltron
Flag of Thailand image

ASKER

Hi! Could u please explain further? "Please post the CREATE TABLE statement for the category table and tell us which column you want to sort by. "

We would like to sort the items by CATEGORY then COUNTRY then by CITY.
At this moment, it is sorted by COUNTRY then by CITY only.

Thank you & hope to hear soon...
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Please run a query to SHOW CREATE TABLE and post the output here.  Then we can see the table definition.  Once we see that we can probably show you how to write the ORDER clause.
Avatar of Stiebel Eltron
Stiebel Eltron
Flag of Thailand image

ASKER

Hello to you again!

Would it be possible to show me how to run a query to show create table?

As I'm new with this database, specially with MySql db...

Actually, I'm just continuing the other changes/additionals that the designer of this website did. The person who creates this web left already so I need to handle it...

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial