What's the proper way to do "SORT BY" selection options?

imgrooot · November 15, 2019

I am trying to replicate a "Sort By" selection exactly like how Freelancer has it here https://www.freelancer.com/search/projects/

Sort by

- Oldest

- Newest

- Lowest Price

- Highest Price

- Lowest Bid

- Highest Bid

So far I was only able to do the sort by oldest and newest. Here is my code. This shows my code for the page to retrieve records and use pagination to split them.

My Tables:

1. Projects (project_id, title, description, min_budget, max_budget, posted_date, expired_date, status)

2. Bids (bid_id, project_id, details, posted_date, b_status)

3. Images (image_id, project_id, user_id, image_path, posted_date)

4. Currency (currency_id, currency_name)

5. Users (user_id, username)

As you can see the setup I have works only for sorting by DESC and ASC, which is used for Oldest and Newest. Now I would like to know how I can do the same thing for lowest price, highest price using the same query. Lowest and Highest Bid have a separate table. i was wondering if that could also be sorted in the same query as well?

<div id="sort-by-wrap">
  <select id="sort-select" onchange="if(this.value != '') document.location = 'explore-projects?p=3&c1=<?php echo $url_c1; ?>&category=<?php echo $url_category; ?>&name=<?php echo $url_name; ?>&sort_by=' + this.value">
    <option value="DESC"<?php if(isset($_GET['sort_by']) && $_GET['sort_by'] == 'DESC') {echo 'selected="selected"';} ?>>Newest</option>
    <option value="ASC"<?php if(isset($_GET['sort_by']) && $_GET['sort_by'] == 'ASC') {echo 'selected="selected"';} ?>>Oldest</option>
  </select>
  <div id="sort-txt">Sort by:</div>
</div>

<?php
$url_category     	= trim(!empty($_GET['category']) ? $_GET['category'] : null);
$url_name     		= trim(!empty($_GET['name']) ? $_GET['name'] : null);
$url_sort_by  		= trim(!empty($_GET['sort_by']) ? $_GET['sort_by'] : null);
$url_c1           	= trim(!empty($_GET['c1']) ? $_GET['c1'] : null);
$url_page_no  		= trim(!empty($_GET['page_no']) ? $_GET['page_no'] : null);
$page_url 		    = basename($_SERVER['REQUEST_URI']);
$get_page_url		= preg_replace('/[?&]page_no=\d+/', '', $page_url);

if(!empty($url_page_no)) {
  $page_no = $url_page_no;
} else {
  $page_no = 1;
}

$total_records_per_page = 10;
$offset 			= ($page_no - 1) * $total_records_per_page;
$previous_page 		= $page_no - 1;
$next_page 			= $page_no + 1;

$count_records = $db->prepare("SELECT COUNT(*) FROM projects WHERE category_id = :category_id AND publish = :publish");
$count_records->bindParam(':category_id', $url_category);
$count_records->bindValue(':publish', 1);
$count_records->execute();
$total_records = $count_records->fetchColumn();

$total_no_of_pages = ceil($total_records / $total_records_per_page);
$second_last       = $total_no_of_pages - 1; // total page minus 1

$find_records = $db->prepare("SELECT images.image_id, currency.currency_name, users.username, users.user_id, projects.* FROM projects
LEFT JOIN images ON projects.project_id = images.project_id
LEFT JOIN currency ON projects.currency_id = currency.currency_id
LEFT JOIN users ON projects.user_id = users.user_id
WHERE projects.category_id = :category_id AND projects.publish = :publish ORDER BY projects.project_id $url_sort_by LIMIT $offset, $total_records_per_page");
$find_records->bindParam(':category_id', $url_category);
$find_records->bindValue(':publish', 1);
$find_records->execute();
$result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    $project_id       =	$row['project_id'];
    $title            =	$row['title'];
    $description      =	$row['description'];
    $min_budget    	  =	$row['min_budget']);
    $max_budget    	  =	$row['max_budget']);
    $posted_date      =	$row['posted_date'];
    $expired_date     =	$row['expired_date'];
    $status           =	$row['status'];

    $image_id    	  =	$row['image_id'];
    $currency_name    =	$row['currency_name'];

    $user_id    	  =	$row['user_id'];
    $username    	  =	$row['username'];
	
    // COUNT TOTAL BIDS FOR EACH PROJECT
    $count_bids = $db->prepare("SELECT COUNT(*) FROM bids WHERE project_id = :project_id");
    $count_bids->bindParam(':project_id', $project_id);
    $count_bids->execute();
    $total_bids = $count_bids->fetchColumn();

    // HTML CODE TO OUTPUT EACH PROJECT RESULT
    include '../snippets/record-box-wrap.php';
  }

} else {

  ?>
  <div id="no-content">
    <span>No projects</span>
  </div>
  <?php

}
?>
<div class="pagination-wrap">
  <div class="inner-pagination">
    <ul>
      <li <?php if($page_no <= 1){ echo "class='disabled'"; } ?>>
        <a <?php if($page_no > 1){ echo "href='$get_page_url&page_no=$previous_page'"; } ?>>Prev</a>
      </li>
      <?php
      if($page_no <= 6)  {

        for($counter = 1; $counter < 6; $counter++) {
          if ($counter == $page_no) {
           echo "<li class='active'><a>$counter</a></li>";
          } else {
             echo "<li><a href='$get_page_url&page_no=$counter'>$counter</a></li>";
          }
        }

      } else {

        for($counter = $total_no_of_pages - 6; $counter <= $total_no_of_pages; $counter++) {
          if($counter == $page_no) {
            echo "<li class='active'><a>$counter</a></li>";
          } else {
             echo "<li><a href='$get_page_url&page_no=$counter'>$counter</a></li>";
          }
        }
      }
      ?>
      <li <?php if($page_no >= $total_no_of_pages){ echo "class='disabled'"; } ?>>
        <a <?php if($page_no < $total_no_of_pages) { echo "href='$get_page_url&page_no=$next_page'"; } ?>>Next</a>
      </li>
      <?php if($page_no < $total_no_of_pages) { echo "<li><a href='$get_page_url&page_no=$total_no_of_pages'>Last ››</a></li>"; } ?>
    </ul>
  </div>
</div>

mac_gyver · November 15, 2019

to do this, you need to produce an array that defines the permitted choices and also contains entries that control which tables/columns to use in the query. you would then dynamically produce the base query by having general-purpose code that makes use of the data in the array. short-answer: you are trying to produce a data driven design, rather than to hard-code logic for every possible choice.

some comments about the existing code -

use domain relative urls, this will eliminate the need for the $page_url value.
trimming input data after you have tested if the data is empty is backwards.
use http_build_query() to produce links in your code from any existing get parameters and any new values. this will eliminate the need for the preg_repalce() statement near the start of the code.
put the 'get method' business logic before the start of your html document.
the base query, with any table, join, where, and having clauses must be the same for both the COUNT(*) query and the data retrieval query. build this part of the query and any bound input parameters, in this part of the query, in dedicated php variables, then use these variables when building and executing the two queries.
put literal, constant values directly into queries, such as the publish = 1 value.
use implicate binding, by supplying an array of the input values to the execute call.
don't copy variables to other variables without a good reason. just use the original variables.

Edited November 15, 2019 by mac_gyver

imgrooot · November 15, 2019

10 hours ago, mac_gyver said:

to do this, you need to produce an array that defines the permitted choices and also contains entries that control which tables/columns to use in the query. you would then dynamically produce the base query by having general-purpose code that makes use of the data in the array. short-answer: you are trying to produce a data driven design, rather than to hard-code logic for every possible choice.

some comments about the existing code -

use domain relative urls, this will eliminate the need for the $page_url value.

trimming input data after you have tested if the data is empty is backwards.

use http_build_query() to produce links in your code from any existing get parameters and any new values. this will eliminate the need for the preg_repalce() statement near the start of the code.

put the 'get method' business logic before the start of your html document.

the base query, with any table, join, where, and having clauses must be the same for both the COUNT(*) query and the data retrieval query. build this part of the query and any bound input parameters, in this part of the query, in dedicated php variables, then use these variables when building and executing the two queries.

put literal, constant values directly into queries, such as the publish = 1 value.

use implicate binding, by supplying an array of the input values to the execute call.

don't copy variables to other variables without a good reason. just use the original variables.

Alright so I've made some progress after thinking about what you wrote.

To answer your questions.

1. Not sure what you mean exactly but I am using this method for urls. It seems to work fine for me.

// THIS IS FOR REQUIRE AND INCLUDE FILES
DEFINE("PROJECT_ROOT", dirname(dirname(__FILE__)));
$project_root = PROJECT_ROOT;

// THIS IS FOR SRC AND HREF LINKS
DEFINE("PROJECT_NAME", "/bidder");
$project_name = PROJECT_NAME;

2. I assume you're talking about the url paramters? You're probably right. I'll remove the trim.

3. Wow this is way more efficient. It works beautifully. Check my code below for the change.

4. get method business logic is done before html code. I just showed it before here to separate the php and html code.

5. I don't quite understand what you mean. But check my new query below as how I did the sorts by. If you can improve on it, it would be nice to see your code.

6. I thought putting values directly in the query would be prone to sql injection? Which is why I bind them instead.

7. Not sure you mean exactly. Can you give me an example?

8. Can you show me an example of where I did that?

Having said all that, I have figured out a way to sort by newest, oldest, highest price, lowest price. Now the only thing left is sorting by highest and lowest bids. The thing is the "Bids" is a separate table, so I'm not sure how I include that within this query. But there is a workaround. Basically every time a bid is posted in the Bids table, I add the count number to the projects table. The projects table will have a new column called "bid_count". And from that point on, I simply sort by using the same method I used for highest and lowest price. If there is a better method, let me know.

<?php
$url_category     = trim(!empty($_GET['category']) ? $_GET['category'] : null);
$url_name     	  = trim(!empty($_GET['name']) ? $_GET['name'] : null);
$url_sort_by  	  = trim(!empty($_GET['sort_by']) ? $_GET['sort_by'] : null);
$url_c1           = trim(!empty($_GET['c1']) ? $_GET['c1'] : null);
$url_page_no  	  = trim(!empty($_GET['page_no']) ? $_GET['page_no'] : null);

$link_data = array(
    'p' => $url_p,
    'c1' => $url_c1,
    'category' => $url_category,
    'name' => $url_name,
);
$link_param = http_build_query($link_data);

if(!empty($url_page_no)) {
  $page_no = $url_page_no;
} else {
  $page_no = 1;
}

$total_records_per_page = 3;
$offset 			= ($page_no - 1) * $total_records_per_page;
$previous_page 		= $page_no - 1;
$next_page 			= $page_no + 1;

$count_records = $db->prepare("SELECT COUNT(*) FROM projects WHERE category_id = :category_id AND publish = :publish");
$count_records->bindParam(':category_id', $url_category);
$count_records->bindValue(':publish', 1);
$count_records->execute();
$total_records = $count_records->fetchColumn();

$total_no_of_pages = ceil($total_records / $total_records_per_page);
$second_last       = $total_no_of_pages - 1; // total page minus 1

if($url_sort_by == 'most-recent') {
  $sort_by  = 'desc';
  $order_by = 'project_id';
} else if($url_sort_by == 'oldest') {
  $sort_by = 'asc';
  $order_by = 'project_id';
} else if($url_sort_by == 'highest-price') {
  $sort_by = 'desc';
  $order_by = 'max_budget';
} else if($url_sort_by == 'lowest-price') {
  $sort_by = 'asc';
  $order_by = 'max_budget';
} else {
  $sort_by  = 'desc';
  $order_by = 'project_id';
}

$find_records = $db->prepare("SELECT images.image_id, currency.currency_name, users.username, users.name, users.user_id, projects.* FROM projects
LEFT JOIN images ON projects.project_id = images.project_id
LEFT JOIN currency ON projects.currency_id = currency.currency_id
LEFT JOIN users ON projects.user_id = users.user_id
WHERE projects.category_id = :category_id AND projects.publish = :publish ORDER BY projects.$order_by $sort_by LIMIT $offset, $total_records_per_page");
$find_records->bindParam(':category_id', $url_category);
$find_records->bindValue(':publish', 1);
$find_records->execute();
$result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    $project_id       =	$row['project_id'];
    $title            =	$row['title'];
    $description      =	$row['description'];
    $min_budget    	  =	$row['min_budget']);
    $max_budget    	  =	$row['max_budget']);
    $posted_date      =	$row['posted_date'];
    $expired_date     =	$row['expired_date'];
    $status           =	$row['status'];

    $image_id    	  =	$row['image_id'];
    $currency_name    =	$row['currency_name'];

    $user_id    	  =	$row['user_id'];
    $username    	  =	$row['username'];

    // COUNT TOTAL BIDS FOR EACH PROJECT
    $count_bids = $db->prepare("SELECT COUNT(*) FROM bids WHERE project_id = :project_id");
    $count_bids->bindParam(':project_id', $project_id);
    $count_bids->execute();
    $total_bids = $count_bids->fetchColumn();

    // HTML CODE TO OUTPUT EACH PROJECT RESULT
    include $project_root.'/snippets/record-box-wrap.php';
  }

} else {

  ?>
  <div id="no-content">
    <span>No projects</span>
  </div>
  <?php

}
?>
<div id="sort-by-wrap">
  <select id="sort-select"
    onchange="
    if(this.value == 'most-recent' || this.value == 'oldest' || this.value == 'highest-price' || this.value == 'lowest-price') {
      document.location = '<?php echo $project_name; ?>/projects/explore-projects?<?php echo $link_param; ?>&sort_by=' + this.value
    } else {
      document.location = '<?php echo $project_name; ?>/projects/explore-projects?<?php echo $link_param; ?>&sort_by=' + this.value
    }
    ">
    <option value="most-recent"<?php if(isset($_GET['sort_by']) && $_GET['sort_by'] == 'most-recent') {echo 'selected="selected"';} ?>>Most Recent</option>
    <option value="oldest"<?php if(isset($_GET['sort_by']) && $_GET['sort_by'] == 'oldest') {echo 'selected="selected"';} ?>>Oldest</option>
    <option value="highest-price"<?php if(isset($_GET['sort_by']) && $_GET['sort_by'] == 'highest-price') {echo 'selected="selected"';} ?>>Highest Price</option>
    <option value="lowest-price"<?php if(isset($_GET['sort_by']) && $_GET['sort_by'] == 'lowest-price') {echo 'selected="selected"';} ?>>Lowest Price</option>
  </select>
  <div id="sort-txt">Sort by:</div>
</div>

<div class="pagination-wrap">
  <div class="inner-pagination">
    <ul>
      <li <?php if($page_no <= 1){ echo "class='disabled'"; } ?>>
        <a <?php if($page_no > 1){ echo "href='$project_name/projects/explore-projects?$link_param&page_no=$previous_page'"; } ?>>Prev</a>
      </li>
      <?php
      if($page_no <= 6)  {

        for($counter = 1; $counter < 6; $counter++) {
          if ($counter == $page_no) {
           echo "<li class='active'><a>$counter</a></li>";
          } else {
             echo "<li><a href='$project_name/projects/explore-projects?$link_param&page_no=$counter'>$counter</a></li>";
          }
        }

      } else {

        for($counter = $total_no_of_pages - 6; $counter <= $total_no_of_pages; $counter++) {
          if($counter == $page_no) {
            echo "<li class='active'><a>$counter</a></li>";
          } else {
             echo "<li><a href='$project_name/projects/explore-projects?$link_param&page_no=$counter'>$counter</a></li>";
          }
        }
      }
      ?>
      <li <?php if($page_no >= $total_no_of_pages){ echo "class='disabled'"; } ?>>
        <a <?php if($page_no < $total_no_of_pages) { echo "href='$project_name/projects/explore-projects?$link_param&page_no=$next_page'"; } ?>>Next</a>
      </li>
      <?php if($page_no < $total_no_of_pages) { echo "<li><a href='$project_name/projects/explore-projects?$link_param&page_no=$total_no_of_pages'>Last ››</a></li>"; } ?>
    </ul>
  </div>
</div>

Psycho · November 15, 2019

That's better, but you are hard-coding the list of select options and then use a series of hard-coded if/else statements that are dependent upon those hard coded values. This makes it difficult for maintaining your code. Create one source of truth for your sorting options, then use that source of truth to both create the select options and generate the SQL code for sorting.

Here is a down an dirty example where the sorting properties are in an array of valid values. You could have more complex logic by utilizing a switch statement (I would not use a bunch of if/else statements)

<?php

$sortValues = array(
    'Newest' => 'projects.project_id DESC',
    'Oldest' => 'projects.project_id ASC',
    'Lowest Price' => 'projects.max_budget ASC',
    'Highest Price' => 'projects.max_budget DESCC',
)

//Create select options
$sortByOptions = '';
foreach(array_keys($sortValues) as $sortLabel)
{
    $selected = (isset($_GET['sort_by']) && $_GET['sort_by']==$sortLabel) ? 'selected="selected"' : '';
    $sortByOptions .= "<option value=\"{$sortLabel}\" {$selected}>{$sortLabel}</option><br>\n";
}
//echo $sortByOptions within the <select> field


//Determining the sort logic to use in query
if(isset($_GET['sort_by']) && isset($sortValues[$_GET['sort_by']]))
{
    $sortSql = $sortValues[$_GET['sort_by']];
}
else
{   //Default if no user selection or selectio is invalid
    $sortSql = $sortValues['Newest'];
}
//Use $sortSql in query after the ORDER BY clause

imgrooot · November 15, 2019

35 minutes ago, Psycho said:
That's better, but you are hard-coding the list of select options and then use a series of hard-coded if/else statements that are dependent upon those hard coded values. This makes it difficult for maintaining your code. Create one source of truth for your sorting options, then use that source of truth to both create the select options and generate the SQL code for sorting.

Here is a down an dirty example where the sorting properties are in an array of valid values. You could have more complex logic by utilizing a switch statement (I would not use a bunch of if/else statements)
<?php

$sortValues = array(
    'Newest' => 'projects.project_id DESC',
    'Oldest' => 'projects.project_id ASC',
    'Lowest Price' => 'projects.max_budget ASC',
    'Highest Price' => 'projects.max_budget DESCC',
)

//Create select options
$sortByOptions = '';
foreach(array_keys($sortValues) as $sortLabel)
{
    $selected = (isset($_GET['sort_by']) && $_GET['sort_by']==$sortLabel) ? 'selected="selected"' : '';
    $sortByOptions .= "<option value=\"{$sortLabel}\" {$selected}>{$sortLabel}</option><br>\n";
}
//echo $sortByOptions within the <select> field


//Determining the sort logic to use in query
if(isset($_GET['sort_by']) && isset($sortValues[$_GET['sort_by']]))
{
    $sortSql = $sortValues[$_GET['sort_by']];
}
else
{   //Default if no user selection or selectio is invalid
    $sortSql = $sortValues['Newest'];
}
//Use $sortSql in query after the ORDER BY clause

That is definitely more efficient.

Based on your code, here is the updated sort by code.

<div id="sort-by-wrap">
  <?php
  $sortValues = array(
    'Newest'        => 'projects.project_id DESC',
    'Oldest'        => 'projects.project_id ASC',
    'Lowest Price'  => 'projects.max_budget ASC',
    'Highest Price' => 'projects.max_budget DESC',
  );

  //Create select options
  $sortByOptions = '';
  foreach(array_keys($sortValues) as $sortLabel) {
      $selected = (isset($_GET['sort_by']) && $_GET['sort_by'] == $sortLabel) ? 'selected="selected"' : '';
      $sortByOptions .= "<option value=\"{$sortLabel}\" {$selected}>{$sortLabel}</option><br>\n";
  }
  ?>
  <select id="sort-select"
    onchange="if(this.value != '') { document.location = '<?php echo $project_name; ?>/projects/explore-projects?<?php echo $link_param; ?>&sort_by=' + this.value } ">
    <?php echo $sortByOptions; ?>
  </select>
  <div id="sort-txt">Sort by:</div>
</div>

It works fine. The only slight issue is that in the url paramter field, the sortvalues for lowest and high price are showing up like this "Lowest%20Price". Instead I them to show up like this "lowest-price", "highest-price". Basically lower letters and spaces replaced by dash. How do I do that?

Barand · November 15, 2019

An alternative is store the data in an array and sort the array. Pagination can be achieved using array_slice().

Example...

<?php
/*  TEST DATA
                CREATE TABLE `employee` (
                  `empid` int(11) NOT NULL AUTO_INCREMENT,
                  `fname` varchar(50) DEFAULT NULL,
                  `lname` varchar(50) DEFAULT NULL,
                  `dob` date DEFAULT NULL,
                  PRIMARY KEY (`empid`)
                )

                INSERT INTO `employee` VALUES 
                    (1,'Peter','Smith','1985-01-26'),
                    (2,'Paul','Hartley','1973-12-02'),
                    (3,'Mary','Baker','1980-04-11'),
                    (4,'Jane','Doe','1990-11-28');
*/

// GET DATA (assumes PDO connection)
$res = $db->query("SELECT empid as `Employee ID`
                        , fname as `First Name`
                        , lname as `Last Name`
                        , dob   as `Date of Birth`
                   FROM employee     
                   ");
$data = $columns = [];
$row = $res->fetch();
$columns = array_keys($row);
do {
    $data[] = $row;
} while ($row = $res->fetch());

// SORT THE DATA
$sortby = $_GET['sortby'] ?? 'Employee ID';
$desc = $_GET['desc'] ?? 0;
$chk = $desc==1 ? 'Checked' : '';

usort($data, function($a, $b) use ($sortby, $desc) {
                 if ($desc) 
                    return $b[$sortby] <=> $a[$sortby];
                 else
                    return $a[$sortby] <=> $b[$sortby];
             });
             
// TABLE HEADINGS
$theads = '<tr style="background-color: #EEE; font-weight: 600"><td>' . join('</td><td>', $columns) . "</td></tr>\n";

// TABLE DATA
$tdata = '';
foreach ($data as $d) {
    $tdata .= "<tr><td>" . join('</td><td>', $d) . "</td></tr>\n";
}
 
// SORT OPTIONS
function sortOptions($columns, $current)
{
    $opts = '';
    foreach ($columns as $c) {
        $sel = $c==$current ? 'selected' : '';
        $opts .= "<option $sel>$c</option>\n";
    }
    return $opts;
}

?>
<html>
<head>
<title>Sample data sort</title>
</head>
<body>
<form>
<fieldset>
    Sort by <select name='sortby' onclick="this.form.submit()">
            <?=sortoptions($columns, $sortby)?>
            </select>
    DESC <input type="checkbox" name="desc" value="1" <?=$chk?> onclick="this.form.submit()">
</fieldset>
</form>
    <table style="width:80%; margin: 30px auto; font-family: sans-serif;">
        <?=$theads?>
        <?=$tdata?>
    </table>
</body>
</html>

Psycho · November 15, 2019

1 hour ago, imgrooot said:

It works fine. The only slight issue is that in the url paramter field, the sortvalues for lowest and high price are showing up like this "Lowest%20Price". Instead I them to show up like this "lowest-price", "highest-price". Basically lower letters and spaces replaced by dash. How do I do that?

There are many ways. How I would do it would depend on factors that would require me knowing more about how the application is used, which I'm not going to invest time into. So, I'll give one solution. Change the array to be multi-dimensional such as shown below. If you haven't already caught on, the data for this array which defines the sorting parameters (key, label, fields and direction [ASC/DESC]) could be moved to the DB.

$sortValues = array(
    'newest' => array(
        'label' => 'Newest',
        'sortSql' => 'projects.date DESC'
    ),
    'oldest' => array(
        'label' => 'Oldest',
        'sortSql' => 'projects.date ASC'
    ),
    'lowest-price' => array(
        'label' => 'Low to High',
        'sortSql' => 'projects.price ASC'
    ),
    'highest-price' => array(
        'label' => 'High to Low',
        'sortSql' => 'projects.price DESCC'
    )
);

//Create select options
$sortByOptions = '';
foreach($sortValues as $sortKey => $sortData)
{
    $selected = (isset($_GET['sort_by']) && $_GET['sort_by']==$sortKey) ? 'selected="selected"' : '';
    $sortByOptions .= "<option value=\"{$sortKey}\" {$selected}>{$sortData['label']}</option><br>\n";
}
//echo $sortByOptions within the <select> field


//Determining the sort logic to use in query
if(isset($_GET['sort_by']) && isset($sortValues[$_GET['sort_by']]))
{
    $sortSql = $sortValues[$_GET['sort_by']]['sortSql'];
}
else
{   //Default if no user selection or selectio is invalid
    $sortSql = $sortValues['newest']['sortSql'];
}
//Use $sortSql in query after the ORDER BY clause

$query = "SELECT images.image_id, currency.currency_name, users.username, users.user_id, projects.*
          FROM projects
          LEFT JOIN images ON projects.project_id = images.project_id
          LEFT JOIN currency ON projects.currency_id = currency.currency_id
          LEFT JOIN users ON projects.user_id = users.user_id
          WHERE projects.category_id = :category_id
            AND projects.publish = :publish
          ORDER BY {$sortSql}
          LIMIT $offset, $total_records_per_page";

imgrooot · November 15, 2019

2 minutes ago, Psycho said:

There are many ways. How I would do it would depend on factors that would require me knowing more about how the application is used, which I'm not going to invest time into. So, I'll give one solution. Change the array to be multi-dimensional such as shown below. If you haven't already caught on, the data for this array which defines the sorting parameters (key, label, fields and direction [ASC/DESC]) could be moved to the DB.


$sortValues = array(
    'newest' => array(
        'label' => 'Newest',
        'sortSql' => 'projects.date DESC'
    ),
    'oldest' => array(
        'label' => 'Oldest',
        'sortSql' => 'projects.date ASC'
    ),
    'lowest-price' => array(
        'label' => 'Low to High',
        'sortSql' => 'projects.price ASC'
    ),
    'highest-price' => array(
        'label' => 'High to Low',
        'sortSql' => 'projects.price DESCC'
    )
);

//Create select options
$sortByOptions = '';
foreach($sortValues as $sortKey => $sortData)
{
    $selected = (isset($_GET['sort_by']) && $_GET['sort_by']==$sortKey) ? 'selected="selected"' : '';
    $sortByOptions .= "<option value=\"{$sortKey}\" {$selected}>{$sortData['label']}</option><br>\n";
}
//echo $sortByOptions within the <select> field


//Determining the sort logic to use in query
if(isset($_GET['sort_by']) && isset($sortValues[$_GET['sort_by']]))
{
    $sortSql = $sortValues[$_GET['sort_by']]['sortSql'];
}
else
{   //Default if no user selection or selectio is invalid
    $sortSql = $sortValues['newest']['sortSql'];
}
//Use $sortSql in query after the ORDER BY clause

$query = "SELECT images.image_id, currency.currency_name, users.username, users.user_id, projects.*
          FROM projects
          LEFT JOIN images ON projects.project_id = images.project_id
          LEFT JOIN currency ON projects.currency_id = currency.currency_id
          LEFT JOIN users ON projects.user_id = users.user_id
          WHERE projects.category_id = :category_id
            AND projects.publish = :publish
          ORDER BY {$sortSql}
          LIMIT $offset, $total_records_per_page";

I'll try your solution. But here's one I came up with before your post. Let me know what you think.

<?php 
function encode_slug($slug) {

  $slug   =   htmlspecialchars_decode($slug);

  $lettersNumbersSpacesHypens = '/[^\-\s\pN\pL]+/u';
  $spacesDuplicateHypens = '/[\-\s]+/';

  $slug = preg_replace($lettersNumbersSpacesHypens, '-', mb_strtolower($slug, 'UTF-8'));
  $slug = preg_replace($spacesDuplicateHypens, ' ', $slug);
  $slug = ucwords($slug);

  return $slug;
}
?>
<div id="sort-by-wrap">
  <?php
  $sortValues = array(
    'newest'        => 'projects.project_id DESC',
    'oldest'        => 'projects.project_id ASC',
    'lowest-price'  => 'projects.max_budget ASC',
    'highest-price' => 'projects.max_budget DESC',
  );
  //Create select options
  $sortByOptions = '';
  foreach(array_keys($sortValues) as $sortLabel) {
      $sortLabel_slug = encode_slug($sortLabel);
      $selected = (isset($_GET['sort_by']) && $_GET['sort_by'] == $sortLabel) ? 'selected="selected"' : '';
      $sortByOptions .= "<option value=\"{$sortLabel}\" {$selected}>{$sortLabel_slug}</option><br>\n";
  }
  ?>
  <select id="sort-select"
    onchange="if(this.value != '') { document.location = '<?php echo $project_name; ?>/projects/explore-projects?<?php echo $link_param; ?>&sort_by=' + this.value } ">
    <?php echo $sortByOptions; ?>
  </select>
  <div id="sort-txt">Sort by:</div>
</div>

imgrooot · November 16, 2019

42 minutes ago, Psycho said:

There are many ways. How I would do it would depend on factors that would require me knowing more about how the application is used, which I'm not going to invest time into. So, I'll give one solution. Change the array to be multi-dimensional such as shown below. If you haven't already caught on, the data for this array which defines the sorting parameters (key, label, fields and direction [ASC/DESC]) could be moved to the DB.


$sortValues = array(
    'newest' => array(
        'label' => 'Newest',
        'sortSql' => 'projects.date DESC'
    ),
    'oldest' => array(
        'label' => 'Oldest',
        'sortSql' => 'projects.date ASC'
    ),
    'lowest-price' => array(
        'label' => 'Low to High',
        'sortSql' => 'projects.price ASC'
    ),
    'highest-price' => array(
        'label' => 'High to Low',
        'sortSql' => 'projects.price DESCC'
    )
);

//Create select options
$sortByOptions = '';
foreach($sortValues as $sortKey => $sortData)
{
    $selected = (isset($_GET['sort_by']) && $_GET['sort_by']==$sortKey) ? 'selected="selected"' : '';
    $sortByOptions .= "<option value=\"{$sortKey}\" {$selected}>{$sortData['label']}</option><br>\n";
}
//echo $sortByOptions within the <select> field


//Determining the sort logic to use in query
if(isset($_GET['sort_by']) && isset($sortValues[$_GET['sort_by']]))
{
    $sortSql = $sortValues[$_GET['sort_by']]['sortSql'];
}
else
{   //Default if no user selection or selectio is invalid
    $sortSql = $sortValues['newest']['sortSql'];
}
//Use $sortSql in query after the ORDER BY clause

$query = "SELECT images.image_id, currency.currency_name, users.username, users.user_id, projects.*
          FROM projects
          LEFT JOIN images ON projects.project_id = images.project_id
          LEFT JOIN currency ON projects.currency_id = currency.currency_id
          LEFT JOIN users ON projects.user_id = users.user_id
          WHERE projects.category_id = :category_id
            AND projects.publish = :publish
          ORDER BY {$sortSql}
          LIMIT $offset, $total_records_per_page";

So I've tried your code above but it's giving me an error.

Warning: Illegal string offset 'label'

So there must be something wrong with accessing array label value inside the foreach loop. Perhaps you know what it is?

Edited November 16, 2019 by imgrooot

Psycho · November 18, 2019

On 11/15/2019 at 5:28 PM, imgrooot said:

I'll try your solution. But here's one I came up with before your post. Let me know what you think.

I think you are greatly over-complicating the building of the Label for the select options. If you need different data for the value and the label, the approach I provided gives you more flexibility to explicitly set those two values.

On 11/15/2019 at 6:05 PM, imgrooot said:
So I've tried your code above but it's giving me an error.
Warning: Illegal string offset 'label'
So there must be something wrong with accessing array label value inside the foreach loop. Perhaps you know what it is?

As per my signature:

I do not always test the code I provide, so there may be some syntax errors

In any case, when I run the code I provided it works without error (assuming values are given for $offset & $total_records_per_page, which were assumed set somewhere in your code). I'm guessing you modified the code I provided or didn't fully implement it. Based on the error, I would say you updated the foreach() code, but did not update the array with the new structure.

Sign In

What's the proper way to do "SORT BY" selection options?

Recommended Posts

imgrooot

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

imgrooot

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

imgrooot

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

imgrooot

Link to comment

Share on other sites

imgrooot

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information