imgrooot Posted November 15, 2019 Share Posted 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> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 15, 2019 Share Posted November 15, 2019 (edited) 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 Quote Link to comment Share on other sites More sharing options...
imgrooot Posted November 15, 2019 Author Share Posted 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> Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 15, 2019 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
imgrooot Posted November 15, 2019 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted November 15, 2019 Share Posted 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> Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 15, 2019 Share Posted 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"; Quote Link to comment Share on other sites More sharing options...
imgrooot Posted November 15, 2019 Author Share Posted 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> Quote Link to comment Share on other sites More sharing options...
imgrooot Posted November 16, 2019 Author Share Posted November 16, 2019 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 18, 2019 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.