Jump to content

imgrooot

Members
  • Content Count

    300
  • Joined

  • Last visited

  • Days Won

    1

imgrooot last won the day on May 16 2018

imgrooot had the most liked content!

Community Reputation

1 Neutral

About imgrooot

  • Rank
    Advanced Member
  1. Ah yes that was it. I should've seen that. One more thing. If I want to use this method in my search query, how would you add this code to the above method? WHERE MATCH(title) AGAINST('$search_query' IN BOOLEAN MODE)
  2. I am getting this error. Warning: join(): Invalid arguments passed in On this line $whereclause = 'WHERE ' .join('AND', $where); Also I assume if ($where) is a mistake on your part on this line? if ($where) $whereclause = 'WHERE ' . join(' AND ', $where);
  3. Actually let me post my whole code. I am trying something different so you may find a more efficient method to doing it. So with this code below, I get that "Notice: Array to string conversion" error on the select query. $min_price = 10; $max_price = 50; $featured = 1; $binds = []; $param_1 = "AND projects.min_price >= '.Parameterized($url_min_price, $binds).'"; $param_2 = "AND projects.max_price <= '.Parameterized($url_max_price, $binds).'"; $param_3 = "AND projects.featured = '.Parameterized($url_featured, $binds).'"; if($min_price > 0) { $param_min_price = $param_1; } if($max_price > 0) { $param_max_price = $param_2; } if($featured == 0 OR $featured == 1) { $param_featured = $param_3; } $find_records = $db->prepare("SELECT * FROM projects WHERE $param_min_price $param_max_price $param_featured"); $find_records->execute($binds); $result_records = $find_records->fetchAll(PDO::FETCH_ASSOC); if(count($result_records) > 0) { foreach($result_records as $row) { // OUTPUT RESULTS } }
  4. So I tried your method and it seems to give me an error in the query where Parameterize is set. Notice: Array to string conversion
  5. Ah I see. So based on your array, this is how my new query would look like. $parms = array( 'min_price' => $min_price, 'max_price' => $max_price, 'featured' => $featured); $find_records = $db->prepare("SELECT * FROM projects WHERE min_price >= :min_price AND max_price <= :max_price AND featured = :featured"); $find_records->execute($parms); $result_records = $find_records->fetchAll(PDO::FETCH_ASSOC); Not exactly the solution I was looking for but I suppose it's a bit better than before.
  6. I see. But if you see my min and max price, i use equals to and less/more than operators to compare. But your array does not show that.
  7. Normally this is how I do my query. I am binding min, max and featured parameters like this. $min_price = 10; $max_price = 50; $featured = 1; $find_records = $db->prepare("SELECT * FROM projects WHERE min_price >= :min_price AND max_price <= :max_price AND featured = :featured"); $find_records->bindParam(':min_price', $min_price); $find_records->bindParam(':max_price', $max_price); $find_records->bindParam(':featured', $featured); $find_records->execute(); $result_records = $find_records->fetchAll(PDO::FETCH_ASSOC); Now I was wondering if it's ok to do the same thing but like this? I am adding the parameters directly in the query. Would it be prone to SQL injection? $min_price = 10; $max_price = 50; $featured = 1; $find_records = $db->prepare("SELECT * FROM projects WHERE min_price >= $min_price AND max_price <= $max_price AND featured = $featured"); $find_records->execute(); $result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
  8. 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?
  9. 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>
  10. 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?
  11. 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>
×
×
  • 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.