Jump to content

imgrooot

Members
  • Posts

    383
  • Joined

  • Last visited

  • Days Won

    1

Posts posted by imgrooot

  1. Alright so someone else was able to point out what I was doing wrong. 

    The issue with was this line. I was uploading the original file, which I assumed was needed as the source.

    if(move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $new_file_path)) {

    The fix is this to change it to this.

    if(rename($new_image, $new_file_path)) {

     

    Here is my full code. It works now.

    if(isset($_FILES['fileToUpload']) AND !empty($_FILES['fileToUpload']["name"])) {
      if(is_uploaded_file($_FILES['fileToUpload']["tmp_name"])) {
    
        $target_dir	   = '../members/images/'.$global_user_id.'/projects/'.$url_project_id.'/';
        $target_file   = $target_dir . basename($_FILES["fileToUpload"]["name"]);
        $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);
    
        $source_file   = $_FILES["fileToUpload"]["tmp_name"];
        $random_name   = generateRandomString(10);
        $new_image     = $random_name . '.' . $imageFileType;
        $resized_image = compress_image($source_file, $new_image, 10);
        $new_file_path = $target_dir . $resized_image;
    
        if(!is_dir($target_dir)){
          mkdir($target_dir, 0775, true);
        }
    
        $uploadOk      = 1;
        // Check if image file is a actual image or fake image
        $check = getimagesize($source_file);
        if($check !== false) {
         //   echo "File is an image - " . $check["mime"] . ".";
            $uploadOk = 1;
        } else {
            $errors[] = 'File is not an image!';
            $uploadOk = 0;
        }
        // Check if file already exists
        if (file_exists($target_file)) {
            $errors[] = 'Sorry, file already exists!';
            $uploadOk = 0;
        }
        // Check file size
        if ($_FILES["fileToUpload"]["size"] > 5000000) {
            $errors[] = 'Sorry, your file size is bigger than 5mb!';
            $uploadOk = 0;
        }
        // Allow certain file formats
        if($imageFileType != "jpg" && $imageFileType != "png" && $imageFileType != "jpeg" && $imageFileType != "gif" && $imageFileType != "JPG" && $imageFileType != "PNG" && $imageFileType != "JPEG" && $imageFileType != "GIF") {
            $errors[] = 'Sorry, only JPG, JPEG, PNG & GIF files are allowed!';
            $uploadOk = 0;
        }
        // Check if $uploadOk is set to 0 by an error
        if($uploadOk == 0) {
            $errors[] = 'Sorry, your file was not uploaded!';
        // if everything is ok, try to upload file
        } else {
    
          if(rename($new_image, $new_file_path)) {
    
            echo 'success';
    
          } else {
            $errors[] = 'Sorry, there was an error uploading your file!';
          }
        }
      } else {
        $errors[] = 'You must upload an image!';
      }
    }

     

  2. 50 minutes ago, requinix said:

    The compression function is not the problem. Anymore. After you moved that imagejpeg line.

    So now let's go back to an earlier question: where do you expect the images to go? Have you been looking in the same directory as the PHP script?

    I expect the images to go to this directory.

    $target_dir = '../members/images/'.$global_user_id.'/projects/'.$url_project_id.'/';

    And they do go to this directory. I see the images being uploaded with the correct name and extension. The only thing not happening is that those images are not being reduced in file size. They are the exact same size as the original image.

    Just to clarify, by compressing an image, I meant reducing the image file size. So if the original image file size is 1.6mb, then I want to reduce/compress that to a lower file size and upload it to the directory. I am assuming that's what that function does, unless I am wrong.

  3. I found another compress function that's a little more sophisticated. It uploads the file successfully. But once again it doesn't compress the image. I'm thinking perhaps something is wrong with my original setup? I really need to know exactly what it is instead of hints. I've been at it for over a day and still no luck.

    function compressImage($source_url, $destination_url, $quality) {
    
        //$quality :: 0 - 100
        $info = getimagesize($source_url);
    
        if($destination_url == NULL || $destination_url == "" )  {
    
          $destination_url = $source_url;
    
          $info = getimagesize($source_url);
    
        } else if($info['mime'] == 'image/jpeg' || $info['mime'] == 'image/jpg') {
    
            $image = imagecreatefromjpeg($source_url);
            //save file
            //ranges from 0 (worst quality, smaller file) to 100 (best quality, biggest file). The default is the default IJG quality value (about 75).
            imagejpeg($image, $destination_url, $quality);
    
            //Free up memory
            imagedestroy($image);
    
        } else if($info['mime'] == 'image/png') {
    
            $image = imagecreatefrompng($source_url);
    
            imageAlphaBlending($image, true);
            imageSaveAlpha($image, true);
    
            /* chang to png quality */
            $png_quality = 9 - round(($quality / 100 ) * 9 );
            imagePng($image, $destination_url, $png_quality);//Compression level: from 0 (no compression) to 9(full compression).
            //Free up memory
            imagedestroy($image);
    
        } else {
            return FALSE;
        }
    
        return $destination_url;
    
    }

     

  4. 27 minutes ago, requinix said:

    Alright. Now look at your compress_image function and tell me if you see anything wrong with it.

    Nevermind, this question is no longer relevant given your answer to the first one.

    Here is the updated function. I moved the imagejpeg line outside the elseif statement. Still the same issue. Image not compressing. 

    function compress_image($source_url, $destination_url, $quality) {
    
      $info = getimagesize($source_url);
    
      if($info['mime'] == 'image/jpeg') {
    
        $image = imagecreatefromjpeg($source_url);
    
      } else if ($info['mime'] == 'image/gif') {
    
        $image = imagecreatefromgif($source_url);
    
      } else if ($info['mime'] == 'image/png') {
    
        $image = imagecreatefrompng($source_url);
    
      } else {}
    
      imagejpeg($image, $destination_url, $quality);
      return $destination_url;
    }

     

  5. 19 minutes ago, requinix said:

    Fair enough. Two questions:

    1. What type of image are you testing with?
    2. Where are you looking to see the compressed image?

    1. For now I am only testing jpeg images. The image I'm testing with is 1.64mb in size.

    2. Not sure I understand this question.

     

    The compression function I am using is based on these two tutorials.  Maybe it'll give you an insight on what I'm trying to do.

    https://makitweb.com/how-to-compress-image-size-while-uploading-with-php/

    https://www.johnpatel.com/compress-images-in-php/ 

  6. Normally I would agree with you but this problem is little more complex than my brain can handle.  I had a good night's rest and still I don't see the solution.

    Let me walk you through to my thoughts. This is a bit different method than my original code.

    // RETRIEVING THE DIRECTORY WHERE THE IMAGES ARE LOCATED
    $target_dir = '../members/images/'.$global_user_id.'/projects/'.$url_project_id.'/';
    $target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
    
    // RETRIEVING THE EXTENSION OF THE IMAGE. E.G. JPG
    $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);
    
    // THE SOURCE IMAGE I AM UPLOAD. E.G. php4AC9.tmp 
    $source_image = $_FILES["fileToUpload"]["tmp_name"];
    
    // INSTEAD OF USING THE ORIGINAL NAME, I AM GENERATING A NEW NAME FOR EACH IMAGE I UPLOAD. e.g. Dbo9PxcZi6
    $random_name = generateRandomString(10);
    
    // THIS WOULD GENERATE A NEW NAME FOR THE IMAGE. E.G. Dbo9PxcZi6.jpg
    $destination_image = $random_name . '.' . $imageFileType;
    
    // THIS COMPRESSES THE IMAGE USING THE SOURCE IMAGE AND THE DESTINATION IMAGE.
    $resized_image = compress_image($source_image, $destination_image, 50);
    
    // NEW FILE PATH FOR THE IMAGE UPLOAD. E.G. ../members/images/7/projects/8/Dbo9PxcZi6.jpg
    $new_file_path = $target_dir . $resized_image;
    
    // CHECKS IF THE IMAGE HAS BEEN UPLOADED TO THE DIRECTORY
    if(move_uploaded_file($source_image, $new_file_path)) {
     // SUCCESS
    }

     

    So now that you know my thought process, could you please point out exactly what I am doing wrong? I don't want to keep beating around the bush.

  7. 2 hours ago, requinix said:

    Step away from your computer for the rest of the day. Or at least stop looking at your code for a while. Come back to it tomorrow.

    Then go through your form processing code line by line and say, out loud to yourself (or maybe to a duck), what it does. Think about where the files are and what you're doing with them each step of the way.

    I have tried many different variations of the code. Some work and some don't. But none of the solutions gets me a compressed image. And I doubt I am going to figure out this particular solution by stepping away from the computer. I have already spent hours on it. Posting on this forum is my last resort. 

  8. I have an image upload script where I am trying to compress an image to a lower file size. The code works. The image uploads to the directory with the correct name as intended. The only thing is that it's not compression the image as it should be. The size of the new image is the same as the old image. 

    Here's my code. Can you spot what i'm doing wrong?

    // FUNCTION FOR COMPRESSION
    function compress_image($source_url, $destination_url, $quality) {
    
      $info = getimagesize($source_url);
    
      if($info['mime'] == 'image/jpeg') {
    
        $image = imagecreatefromjpeg($source_url);
    
      } else if ($info['mime'] == 'image/gif') {
    
        $image = imagecreatefromgif($source_url);
    
      } else if ($info['mime'] == 'image/png') {
    
        $image = imagecreatefrompng($source_url);
        imagejpeg($image, $destination_url, $quality);
    
      }
      return $destination_url;
    }
    
    // FORM SUBMIT CODE
    
    $errors = array();
    $db->beginTransaction();
    
    if(isset($_FILES['fileToUpload']) AND !empty($_FILES['fileToUpload']["name"])) {
      if(is_uploaded_file($_FILES['fileToUpload']["tmp_name"])) {
    	
        // GENERATES A 10 CHARACTER STRING TO BE USED FOR AN IMAGE NAME
        $random_name   =	generateRandomString(10);
    	
    	$global_user_id = 10;
    	$url_project_id = 5;
    
        $target_dir	   = '../members/images/'.$global_user_id.'/projects/'.$url_project_id.'/';
        $target_file   = $target_dir . basename($_FILES["fileToUpload"]["name"]);
        $uploadOk      = 1;
        $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);
    
        if(!is_dir($target_dir)){
          mkdir($target_dir, 0775, true);
        }
    
        // RESIZE IMAGE
        $pathname      = $_FILES["fileToUpload"]["tmp_name"];
        $resized_image = compress_image($pathname, $random_name, 40);
        $new_file_path = $target_dir . $resized_image . '.' . $imageFileType;
    
        // Check if image file is a actual image or fake image
        $check = getimagesize($_FILES["fileToUpload"]["tmp_name"]);
        if($check !== false) {
         //   echo "File is an image - " . $check["mime"] . ".";
            $uploadOk = 1;
        } else {
            $errors[] = 'File is not an image!';
            $uploadOk = 0;
        }
        // Check if file already exists
        if (file_exists($new_file_path)) {
            $errors[] = 'Sorry, file already exists!';
            $uploadOk = 0;
        }
        // Check file size
        if ($_FILES["fileToUpload"]["size"] > 5000000) {
            $errors[] = 'Sorry, your file size is bigger than 5mb!';
            $uploadOk = 0;
        }
        // Allow certain file formats
        if($imageFileType != "jpg" && $imageFileType != "png" && $imageFileType != "jpeg" && $imageFileType != "gif" && $imageFileType != "JPG" && $imageFileType != "PNG" && $imageFileType != "JPEG" && $imageFileType != "GIF") {
            $errors[] = 'Sorry, only JPG, JPEG, PNG & GIF files are allowed!';
            $uploadOk = 0;
        }
        // Check if $uploadOk is set to 0 by an error
        if($uploadOk == 0) {
            $errors[] = 'Sorry, your file was not uploaded!';
        // if everything is ok, try to upload file
        } else {
    
          if(move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $new_file_path)) {
    
            // SUCCESS MESSAGE
    
          } else {
            $errors[] = 'Sorry, there was an error uploading your file!';
          }
        }
      } else {
        $errors[] = 'You must upload an image!';
      }
    }
    
    if(empty($errors)) {
      $db->commit();
    
      header("Location: http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]");
      exit;
    
    } else {
     $db->rollBack();
    }

     

  9. 9 minutes ago, kicken said:

    Because you're not concatenating the function call like I showed, your just embedding it in the string and it doesn't work like that.  The proper way is to end the string and concatenate, like so:

    
    $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);

    As Barand mentioned though, there are other issues with that version of the code.

    
    $where[] = 'MATCH(title) AGAINST(? IN BOOLEAN MODE)';
    $binds[] = $search_query;

     

    Perfect. That works. 

    Thanks.

  10. 29 minutes ago, Barand said:

    The query has inbuilt syntax errors. Your WHERE clause will always begin with "WHERE AND … "

    IMO a cleaner way to include conditions only if there is a value is

    
    $min_price = 10;
    $max_price = 50;
    $featured  = 1;
    $binds = [];
    
    $where = [];
    $whereclause = '';
    
    if ($min_price > 0) {
        $where[] = "min_price >= ?";
        $binds[] = $min_price;
    }
    if ($max_price > 0) {
        $where = "max_price <= ?";
        $binds[] = $max_price;
    }
    if (in_array($featured, [0,1])) {
        $where[] = "featured = ?";
        $binds[] = $featured ;
    }
    
    if ($where) $whereclause = 'WHERE ' . join(' AND ', $where);
    
    $find_records = $db->prepare(" SELECT * 
                                   FROM projects
                                   $whereclause
                                   ");
    $find_records->execute($binds);
    $result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);

     

    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);

     

  11. 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
      }
    }

     

  12. 13 hours ago, kicken said:

    I have a function that I use so that you can still code like old school concatenation, but use modern binding.  It goes a little something like this:

    
    function Parameterize($value, &$binds){
        static $counter = 0;
    
        if (!is_array($binds)){
            $binds = [];
        }
    
        if (is_array($value)){
            if (count($value) == 0){
                return 'null';
            } else {
                $allParams = [];
                foreach ($value as $v){
                    $allParams[] = Parameterize($v, $binds);
                }
    
                return implode(',', $allParams);
            }
        } else {
            if (is_bool($value)){
                $value = (int)$value;
            } else if ($value instanceof \DateTime){
                $value = $value->format('Y-m-d H:i:s');
            }
    
            $param = ':param' . (++$counter);
            $binds[$param] = $value;
    
            return $param;
        }
    }

    You'd then use it like:

    
    $min_price = 10;
    $max_price = 50;
    $featured  = 1;
    $binds = [];
    
    $sql = '
    SELECT * 
    FROM projects 
    WHERE 
        min_price >= '.Parameterize($min_price, $binds).'
        AND max_price <= '.Parameterize($max_price, $binds).'
        AND featured = '.Parameterize($featured, $binds).'
    ';
    
    $find_records = $db->prepare($sql);
    $find_records->execute($binds);
    $result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);

    I find it keeps things easy to understand and helps reduce the code footprint while still keeping things safe.

    Oh wow! This is exact solution what I was looking for. 

  13. 16 minutes ago, Barand said:

    If you do it the second way (no placeholders), there is no point in preparing it; just use $db->query().

    CAVEAT: If $vars originated from an external source ($_GET, $_POST, $_COOKIE etc) then you are injection-prone and, as you are not even escaping the values your queries could fail.

    EG

    
    $username = "O'Reilly";
    
    $res = $db->query("SELECT password FROM user WHERE username = '$username' ")      // fails with syntax error and open to injection

    If in doubt, prepare();

     

    Your bindings do not either, the query does. The array is just a more convenient way of binding.

    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.

  14. 6 minutes ago, ginerjm said:

    It is since the parms are not 'user input' but simply values that your script produced.

    FYI - you can avoid the bind-param calls if you simply create an array of your parms and their values like this:

    
    $parms = array(
    	'min_price' => $min_price,
    	'max_price' => $max_price,
    	'featured' => $featured);
    $find_records-> execute($parms);

    allows for easier maintenance later on and avoids all of the bind-param calls.

    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.

  15. 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);

     

  16. 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?

  17. 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>

     

  18. 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?

  19. 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 -

    1. use domain relative urls, this will eliminate the need for the $page_url value.
    2. trimming input data after you have tested if the data is empty is backwards.
    3. 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.
    4. put the 'get method' business logic before the start of your html document.
    5. 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.
    6. put literal, constant values directly into queries, such as the publish = 1 value.
    7. use implicate binding, by supplying an array of the input values to the execute call.
    8. 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>

     

  20. 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>

     

×
×
  • 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.