Jump to content

imgrooot

Members
 View Profile  See their activity

  • Posts

    383

  • Joined

  • Last visited

  • Days Won

    1

 Content Type 

Posts posted by imgrooot

    Is converting product price to cents the best way to store value in the database?

    in PHP Coding Help

    Posted

    49 minutes ago, Barand said:

    I store prices as decimal EG 

    
+-------------+------------------+------+-----+----------------------+--------------------------------+
| Field       | Type             | Null | Key | Default              | Extra                          |
+-------------+------------------+------+-----+----------------------+--------------------------------+
| prod_id     | int(11)          | NO   | PRI | NULL                 | auto_increment                 |
| description | varchar(50)      | YES  |     | NULL                 |                                |
| price       | decimal(10,2)    | YES  |     | NULL                 |                                |
+-------------+------------------+------+-----+----------------------+--------------------------------+

     

    Query example... 

    
TABLE: product                                     TABLE: cart
+---------+-------------+--------+                 +----+---------+------+
| prod_id | description | price  |                 | id | prod_id | qty  |
+---------+-------------+--------+                 +----+---------+------+
|       1 | Product AZ  |  49.99 |                 |  1 |       1 |    2 |
|       2 | Product B   |  29.99 |                 |  2 |       3 |    5 |
|       3 | Product C   |   9.99 |                 |  3 |       7 |    1 |
|       4 | Product D   |  22.99 |                 |  4 |       6 |    2 |
|       5 | Product E   |  29.99 |                 +----+---------+------+
|       6 | Product F   |  19.99 |
|       7 | Product G   | 129.99 |
|       8 | Product H   |  99.99 |
|       9 | Product I   |  74.99 |
|      10 | Product J   |  69.99 |
+---------+-------------+--------+

SELECT p.prod_id
     , p.description
     , p.price
     , c.qty
     , p.price * c.qty as total
FROM test_product p
         JOIN
     test_cart c USING (prod_id);
     
+---------+-------------+--------+------+--------+
| prod_id | description | price  | qty  | total  |
+---------+-------------+--------+------+--------+
|       1 | Product AZ  |  49.99 |    2 |  99.98 |
|       3 | Product C   |   9.99 |    5 |  49.95 |
|       7 | Product G   | 129.99 |    1 | 129.99 |
|       6 | Product F   |  19.99 |    2 |  39.98 |
+---------+-------------+--------+------+--------+

     

    Wow that's a wonderful example.

    I just have one question. Why use (10,2) instead of say (12,2)? Wouldn't it be safer to use a higher threshold if you're expecting bigger to store bigger numbers? 

    Is converting product price to cents the best way to store value in the database?

    in PHP Coding Help

    Posted

    Say I have two functions that convert from Dollar to Cents and Cents to Dollar. 

    function convertToDollar($value) {
    $amount = $value / 100;
    return $amount;
}

function convertToCents($value) {
    $amount = $value * 100;
    return $amount;
}

    Currently I convert all the Dollar amounts to cents and store the cents in the mysql database column. When I showcase those amounts on a page, I simply convert the Cents back to Dollar.

    Am I doing this correctly or is there a better way to store product pricing in mysql table?

    What's the best way to do this?

    in PHP Coding Help

    Posted

    1 hour ago, requinix said:

    If imgroot had just joined then I would be leery too, but I think after ~280 posts they've earned some trust. Perhaps you could try aiming all of your posts in a more helpful direction and away from the critical and insulting?

    Thanks for backing me up. 

    The 100k emails per day is an extreme example I gave. I just want to make sure that a platform I build is able to accommodate that kind of numbers if ever need be and that I'm using the right service to accomplish it. This is merely sending email notifications to registered users, nothing else.

    What's the best way to do this?

    in PHP Coding Help

    Posted

    7 hours ago, requinix said:

    The best way is to not do it yourself: sending 100k emails per day from a generic hosting company is a reliable way to get your server blacklisted and emails blocked. There are plenty of services that provide APIs.

    Could you please give me an example of a good service that provides the API? 

    I've used https://www.sender.net to send bulk emails before. But this is using an email list. And it doesn't seem like they have an API.

    What's the best way to do this?

    in PHP Coding Help

    Posted

    Say I am using a normal web hosting company like Namecheap. I have a cron job set to send emails to 100,000 users each day, what would be the best way to deliver those emails the fastest? I know there are other platforms that allows you to send mass emails. But those you have to manually input the email list. I want it automated running from the cron job.

    How do I count rows from multiple products?

    in PHP Coding Help

    Posted

    8 hours ago, Barand said:

    A single query will do the job without any looping. 

    
SELECT COUNT(*) as likes
FROM products p
     JOIN product_likes pl USING (product_id)
WHERE p.user_id = ?

     

    So based on what you said, here's the full query. 

    $find_likes = $db->prepare("SELECT COUNT(*) as likes FROM products p
JOIN product_likes pl USING (product_id)
WHERE p.user_id = :user_id");
$find_likes->bindParam(':user_id', $my_user_id);
$find_likes->execute();
$get_likes = $find_likes->fetchColumn();

echo $get_likes;

    Seems to work fine now.

    Thanks.

    How do I count rows from multiple products?

    in PHP Coding Help

    Posted · Edited by imgrooot

    It's late and I'm not thinking straight. I'm posting this question. Hopefully I get a reply in the morning.

    I have two tables.

    TABLE 1 - PRODUCTS

    TABLE 2 - LIKES

    I am a User who has posted these products. I want to find out ALL the Likes I have received for all my products. Here is my code. 

    $find_products = $db->prepare("SELECT product_id FROM products WHERE user_id = :user_id");
$find_products->bindParam(':user_id', $my_user_id);
$find_products->execute();
$result_products = $find_products->fetchAll(PDO::FETCH_ASSOC);
if(count($result_products) > 0) {
  foreach($result_products as $row) {
    $product_id = $row['product_id'];

    $find_likes = $db->prepare("SELECT like_id FROM product_likes WHERE product_id = :product_id");
    $find_likes->bindParam(':product_id', $product_id);
    $find_likes->execute();
    $result_likes = $find_likes->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_likes) > 0) {
      $get_likes  = 0;
      foreach($result_likes as $row) {
        $get_likes++;
      }
    }
  }
}

    The issue with the above code is that It only shows the Likes if I echo inside the foreach loop. And it'll show combined Likes from each of my products. But I want to actually combine ALL the Likes from ALL the products and be able show them as a single number, outside of the foreach loop.

    How do I do that?

    Is there a way to remove the + sign from date diff? And is it possible to convert it to months?

    in PHP Coding Help

    Posted

    So this is a simple code that finds out the difference between two dates and displays it in number of days. 

    $date1=date_create("2013-03-15");
$date2=date_create("2013-12-12");
$diff=date_diff($date1,$date2);
echo $diff->format("%R%a days");

// RESULT
+272 days

     

    My first question. Is it possible to remove the + sign in the result above?

    Second question. Is it possible to show "months" if it's greater than 30 days? And years if the days are greater than 365?

    How would I do this?

    Why isn't it able to find the max value if it's less than 5?

    in PHP Coding Help

    Posted

    5 minutes ago, chhorn said:

    For what data? Use SQLite in-memory to mock a databse.

    Not sure what you mean.

    But here is another version of the code. This seems to work fine. 

    $contest_id = 5;
$category_id = 8;

$find_entries = $db->prepare("SELECT entry_id, user_id, votes FROM contest_entries 
WHERE contest_id = :contest_id AND category_id = :category_id AND e_status = :e_status AND votes > :votes ORDER BY votes DESC LIMIT 1");
$find_entries->bindParam(':contest_id', $contest_id);
$find_entries->bindParam(':category_id', $category_id);
$find_entries->bindValue(':e_status', 0);
$find_entries->bindValue(':votes', 0);
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $user_id   =	$row['user_id'];
    $votes     =	$row['votes'];
  }
  echo $entry_id;
  
} else {
	echo 'nothing';
}

     

    Why isn't it able to find the max value if it's less than 5?

    in PHP Coding Help

    Posted · Edited by imgrooot

    The issue I'm having is that this query won't find a max value record if it has less than 5 votes. It'll only show the results of an entry has 5 or greater votes. Why is that? 

    $contest_id = 5;
$category_id = 8;

$find_entries = $db->prepare("SELECT entry_id, user_id, votes FROM contest_entries 
WHERE contest_id = :contest_id AND category_id = :category_id AND e_status = :e_status AND votes = (SELECT MAX(votes) FROM contest_entries) LIMIT 1");
$find_entries->bindParam(':contest_id', $contest_id);
$find_entries->bindParam(':category_id', $category_id);
$find_entries->bindValue(':e_status', 0);
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $user_id   =	$row['user_id'];
    $votes     =	$row['votes'];
  }
  echo $entry_id;
  
} else {
	echo 'nothing';
}

     

    Quick help. This GROUP BY query not working properly.

    in PHP Coding Help

    Posted

    21 hours ago, Barand said:

    That will give the number of entries in each contest. For the number of contests 

    
SELECT COUNT(DISTINCT contest_id) as contests 
FROM entries 
WHERE user_id = :user_id

    or, for all users 

    
SELECT user_id
     , COUNT(DISTINCT contest_id) as contests 
FROM entries 
GROUP BY user_id

     

     

    Ah yes this is a better solution. I've tested it and it works. 

    Thanks.

    Quick help. This GROUP BY query not working properly.

    in PHP Coding Help

    Posted

    I have a simply query where I want show how many unique contests are there. So if I have 100 entries in a single contest, then each entry row will have the same contest id in this example.

    So even though there are 100 entries, they all belong to the same contest id. That means the echo of $total_contests should be 1. But instead I am getting the same count as the entry rows, which is 100.  What am I doing wrong? 

    $global_user_id = 1;

$count_contests = $db->prepare("SELECT COUNT(*) FROM entries WHERE user_id = :user_id GROUP BY contest_id");
$count_contests->bindParam(':user_id', $global_user_id);
$count_contests->execute();
$total_contests = $count_contests->fetchColumn();

echo $total_contests;

     

    Is this the best way to generate a Youtube/Vimeo/FB video embed code?

    in PHP Coding Help

    Posted · Edited by imgrooot

    47 minutes ago, requinix said:

    Do you know what those two errors even mean? Do you know what those domains are? Do you see any particular words in those URLs that might give you a clue?

    I just looked those errors up and they have something to do with AdBlocker extension I have installed on the browser. 

    I disabled the AdBlocker extension on that page and reloaded it. Now all the errors are gone. Which is great news.

     

    Is this the best way to generate a Youtube/Vimeo/FB video embed code?

    in PHP Coding Help

    Posted

    I am looking for a code where I can get a video URL from Youtube, Vimeo, Facebook and convert it to an embedded video to be shown on my site. I found a script that does that. It works fine.

    The only errors I get are in the inspect element window in the browser. Errors such as this.  

    Failed to load resource: net::ERR_BLOCKED_BY_CLIENT   googleads.g.doubleclick.net/pagead/id:1 
Failed to load resource: net::ERR_BLOCKED_BY_CLIENT   static.doubleclick.net/instream/ad_status.js:1

     

    This is the function to generate the embedded videos. Do the above errors matter if the videos show up and play fine? 

    function generateVideoEmbedUrl($url){
    //This is a general function for generating an embed link of an FB/Vimeo/Youtube Video.
    $finalUrl = '';
    if(strpos($url, 'facebook.com/') !== false) {
        //it is FB video
        $finalUrl.='https://www.facebook.com/plugins/video.php?href='.rawurlencode($url).'&show_text=1&width=200';
    }else if(strpos($url, 'vimeo.com/') !== false) {
        //it is Vimeo video
        $videoId = explode("vimeo.com/",$url)[1];
        if(strpos($videoId, '&') !== false){
            $videoId = explode("&",$videoId)[0];
        }
        $finalUrl.='https://player.vimeo.com/video/'.$videoId;
    }else if(strpos($url, 'youtube.com/') !== false) {
        //it is Youtube video
        $videoId = explode("v=",$url)[1];
        if(strpos($videoId, '&') !== false){
            $videoId = explode("&",$videoId)[0];
        }
        $finalUrl.='https://www.youtube.com/embed/'.$videoId;
    }else if(strpos($url, 'youtu.be/') !== false){
        //it is Youtube video
        $videoId = explode("youtu.be/",$url)[1];
        if(strpos($videoId, '&') !== false){
            $videoId = explode("&",$videoId)[0];
        }
        $finalUrl.='https://www.youtube.com/embed/'.$videoId;
    }else{
        //Enter valid video URL
    }
    return $finalUrl;
}

     

    Is this query correct? Something going wrong with getting MAX value.

    in MySQL Help

    Posted

    1 hour ago, Barand said:

    No need to prepare/execute a query with no input parameters. Just use 

    
$find_entries = $db->query("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");

     

    So then it would be like this? But If I am binding parameters, then I use the prepare/execute method yes? 

    $find_entries = $db->query("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['votes'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

     

    Is this query correct? Something going wrong with getting MAX value.

    in MySQL Help

    Posted

    7 hours ago, Barand said:

    Perhaps you want to do something like this 

    
SELECT entry_id
     , votes
FROM entries
WHERE votes = ( SELECT MAX(votes) FROM entries );

     

    Perfect! That's exactly it. 

    Here is my updated query with your fix. 

    $find_entries = $db->prepare("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['votes'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

     

    Is this query correct? Something going wrong with getting MAX value.

    in MySQL Help

    Posted

    I have a table where I would like to find the maximum value from a certain column and retrieve a single result. But it doesn't seem to work properly.

    I don't get any errors. The issue is that it's retrieving the entry_id from one row and the votes from another row. But I would like the row with max value to retrieve the same row's entry_id.

    Here is my code. Can you see what's wrong with it? 

    $find_entries = $db->prepare("SELECT entry_id, MAX(votes) as vts FROM entries WHERE votes > :votes");
$find_entries->bindValue(':votes', 0);
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['vts'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

     

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