Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts

    How to hide entry when value is 0

    Hiya,

    I have a couple of products in my database and they have different prices depending on the amount. As there is a small calculation (math) that I have to do with these prices, they must be set as 'NUMERIC float' in the database.

    However, some products have 22 prices (max)whereas others have less. The float field cannot be left empty, so it fills up with a 0.

    Now my question... How can I make sure that when there's a '0', that field is ignored/not shown?

    thx!

  • #2
    met
    met is offline
    Regular Coder
    Join Date
    Oct 2009
    Location
    United Kingdom
    Posts
    728
    Thanks
    4
    Thanked 119 Times in 119 Posts
    without seeing any code we can't provide anything accurate

    PHP Code:
    if($r['price']>0) {
     
    // show whatever...anything with a price of 0 will not be processed

    SQL query can be adjusted

    WHERE value > 0

    etc.

  • #3
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Ah okay, sorry. Here's a part of my code (at select query):

    PHP Code:
    <?php
    // previous code
    $finalQuery=mysql_query("SELECT * FROM `Folders` WHERE `size`='{$size}' AND `color`='{$color}';");

        while(
    $item=mysql_fetch_array($finalQuery)){
        
    $base_m=5;
        
    $item['price1'] = round ((($item['price1']+5) *1.85),0); 
                 
    $item['price01'] = $base_m*(ceil(($item['price1'])/$base_m));
                  
    // these 2 rows are repeated 22 times with price2, price02, price3, price03 and so on

    if ($column==1) {echo "<tr>";}
    ?>

           <td width="50%" align="center"><h4><?php echo "{$item['amount1']}<br /> {$item['amount2']}<br /> {$item['amount3']}<br /> {$item['amount4']}<br /> {$item['amount5']}<br /> {$item['amount6']}<br /> {$item['amount7']}<br /> {$item['amount8']}<br /> {$item['amount9']}<br /> {$item['amount10']}<br /> {$item['amount11']}<br /> {$item['amount12']}<br /> {$item['amount13']}<br /> {$item['amount14']}<br /> {$item['amount15']}<br /> {$item['amount16']}<br /> {$item['amount17']}<br /> {$item['amount18']}<br /> {$item['amount19']}<br /> {$item['amount20']}<br /> {$item['amount21']}<br /> {$item['amount22']}<br />" ?></h4></td>
           <td width="50%" align="center"><h4><?php echo "{$item['price01']}<br /> {$item['price02']}<br />{$item['price03']}<br />{$item['price04']}<br />{$item['price05']}<br />{$item['price06']}<br />{$item['price07']}<br />{$item['price08']}<br />{$item['price09']}<br />{$item['price010']}<br />{$item['price011']}<br />{$item['price012']}<br />{$item['price013']}<br />{$item['price014']}<br />{$item['price015']}<br />{$item['price016']}<br />{$item['price017']}<br />{$item['price018']}<br />{$item['price019']}<br />{$item['price020']}<br />{$item['price021']}<br />{$item['price022']}<br />" ?></h4></td>
    // rest of code
    As there are 22 prices, I don't think I can add them all into the select where query....right?

    I'm not too sure how to add in the other part you suggested. Does this part of the code helps?

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    If I understand you correctly, that the db may have price values of 0 and you want to ignore those, you can simply add what he mentioned onto the query:

    PHP Code:
    $finalQuery=mysql_query("SELECT * FROM `Folders` WHERE `size`='{$size}' AND `color`='{$color}' AND `price` > 0;"); 
    Thats the best method IMO... you could also:

    PHP Code:
    while($item=mysql_fetch_array($finalQuery)){ if ($item['price'] > 0) { 

  • #5
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Yes, you understood me correctly. However, I knew about the method to add it in the select query... But since I have 22 prices, I'd have to add that 22 times, same for adding it into the while. Right?

    If so, I think it's best to add it into the while, to make it better to read. Don't you think? You have the '{' two times. I also have one of my own. Do I need to add another } after the whole while?

    Which option would you suggest? (knowing I'd have to add the price1, price2 and so on 22 times) And, won't this mess up with my calculation? Where all of the prices (price1, price2, price 3 and so on) are changed into prices0 (price01, price02, price03 and so on).

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Maybe I didn't understand you correctly...

    You're saying for a single item you might have 22 prices? Or that there are 22 items? I'm not following why you'd need to do 22 queries...

    If your only concern is summation, it doesn't matter as adding 0 doesn't add anything obviously... and what do you mean by "prices0"? There is some detail I'm not following.

    Can you show us how your DB is setup? With real/fake data if possible. I think that would clear it up.

  • #7
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Yeah sure, here's the amount and prices part:

    PHP Code:
    `amount1varchar(20COLLATE utf8_bin NOT NULL DEFAULT '250',
      `
    amount2varchar(20COLLATE utf8_bin NOT NULL DEFAULT '1000',
      `
    amount3varchar(20COLLATE utf8_bin NOT NULL DEFAULT '2500',
      `
    amount4varchar(20COLLATE utf8_bin NOT NULL DEFAULT '5000',
      `
    amount5varchar(20COLLATE utf8_bin NOT NULL DEFAULT '10.000',
      `
    amount6varchar(20COLLATE utf8_bin NOT NULL DEFAULT '15.000',
      `
    amount7varchar(20COLLATE utf8_bin NOT NULL DEFAULT '20.000',
      `
    amount8varchar(20COLLATE utf8_bin NOT NULL DEFAULT '25.000',
      `
    amount9varchar(20COLLATE utf8_bin NOT NULL DEFAULT '30.000',
      `
    amount10varchar(20COLLATE utf8_bin NOT NULL DEFAULT '35.000',
      `
    amount11varchar(20COLLATE utf8_bin NOT NULL DEFAULT '40.000',
      `
    amount12varchar(20COLLATE utf8_bin NOT NULL DEFAULT '45.000',
      `
    amount13varchar(20COLLATE utf8_bin NOT NULL DEFAULT '50.000',
      `
    amount14varchar(20COLLATE utf8_bin NOT NULL DEFAULT '55.000',
      `
    amount15varchar(20COLLATE utf8_bin NOT NULL DEFAULT '60.000',
      `
    amount16varchar(20COLLATE utf8_bin NOT NULL DEFAULT '65.000',
      `
    amount17varchar(20COLLATE utf8_bin NOT NULL DEFAULT '70.000',
      `
    amount18varchar(20COLLATE utf8_bin NOT NULL DEFAULT '75.000',
      `
    amount19varchar(20COLLATE utf8_bin NOT NULL DEFAULT '80.000',
      `
    amount20varchar(20COLLATE utf8_bin NOT NULL DEFAULT '85.000',
      `
    amount21varchar(20COLLATE utf8_bin NOT NULL DEFAULT '90.000',
      `
    amount22varchar(20COLLATE utf8_bin NOT NULL DEFAULT '95.000',
      `
    price1float NOT NULL,
      `
    price2float NOT NULL,
      `
    price3float NOT NULL,
      `
    price4float NOT NULL,
      `
    price5float NOT NULL,
      `
    price6float NOT NULL,
      `
    price7float NOT NULL,
      `
    price8float NOT NULL,
      `
    price9float NOT NULL,
      `
    price10float NOT NULL,
      `
    price11float NOT NULL,
      `
    price12float NOT NULL,
      `
    price13float NOT NULL,
      `
    price14float NOT NULL,
      `
    price15float NOT NULL,
      `
    price16float NOT NULL,
      `
    price17float NOT NULL,
      `
    price18float NOT NULL,
      `
    price19float NOT NULL,
      `
    price20float NOT NULL,
      `
    price21float NOT NULL,
      `
    price22float NOT NULL
    So for amount 1 (which is by default 250) there's a certain price and then we've got 21 more amounts and thus also 21 more prices...

    Here's data for one of my products (for those rows):

    PHP Code:
    VALUES('250''1000''2500''5000''10.000''15.000''20.000''25.000''30.000''35.000''40.000''45.000''50.000''55.000''60.000''65.000''70.000''75.000''80.000''85.000''90.000''95.000''100.000'''''29.3737.0846.0248.8979.13103.03122.47164.16183.6213.49232.94274.63294.07323.96343.41385.1404.54434.43453.88483.77515.01544.9564.34); 
    I hope this clears it out why I need more prices? If not ask and I'll try to explain another way.

    Edit: as for price01, price02, price03 and so on... There's an administration cost to the basic price of 5 dollar (basic price is in database) and a multiplyer of 1,85. So therefore, I needed a math function to get the right price. So I wouldn't have to calculate them myself when adding them into the db.
    Last edited by Bar2aYunie; 06-22-2010 at 10:58 PM.

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    So... you have 47 columns... I think that's the first problem right there. I can't be sure given I don't know all your facts, but I'm fairly certain the database could be setup better then that...

    Regardless, I am totally confused on what you're trying to achieve now. If the issue is summing a 0 in, why should that matter? 0 is the summation identity, adding it won't do anything.

  • #9
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts
    To be honest, I've got 64 columns.... Since there's a lot more data than just the prices or the amounts. But most of the columns are for the amounts and the prices, yaan. It is possible yeah that this isn't the best way to set it up....but as I've got 475 sub-products and 18 main products and all of this data to get into a db, this was the easiest way I could think of.... and it all works...

    Now, lets say that one of my products has 5 different amounts available and thus 5 different prices. The products are folders (like small magazines) and ppl can order them. The prices vary depending on the amount they want.

    So let's list the amounts and prices:
    250 folders = 29.37 dollar
    1000 folders = 37.08 dollar
    2500 folders = 46.02 dollar
    5000 folders = 48.89 dollar
    10.000 folders = 79.13 dollar

    Now, after adding the administration cost of 5 dollar and adding the multiplyer of 1,85 the new prices become:

    250 folders = (29.37 + 5) * 1,85 = 63,58 dollar
    and so on for the other prices.

    Lastly, I stated that every price must be rounded by 5, always up. So 63,58 will become 65. That is the calculation I made.

    As in this example, there are 5 prices, from 5 different columns called, I need to add in the calculation 5 times.

    However... For some products, there aren't just 5 prices, but 22! Now for those products, I need to recall 22 columns. But, for the products with just 5 prices (and amounts) I only want the script to show 5 prices, not all of them with an empty value.

    Since the price columns are set to float, an empty column cannot stay empty and it is automatically filled with 0. However, if there's no amounts after the 5 in our example, there cannot be any prices, especially not if the price is 0 (due to the db setup). So I want those values ignored.

    I hope I made myself a bit more clear and you understand what I'm trying to achieve. Sorry if I'm not good in explaining... (it's a bit too obvious to me probably, haha)

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Yah, if I get you this time, then its not a mySQL issue, its a PHP issue. Use the while example above (while (something) { if (prices != 0) { whatever } })

  • #11
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts
    So it would be like this?

    PHP Code:
    while($item=mysql_fetch_array($finalQuery)){
    if (
    price1 != 0) {
      
    $base_m=5;
      
    $item['price1'] = round ((($item['price1']+5) *1.85),0); 
      
    $item['price01'] = $base_m*(ceil(($item['price1'])/$base_m));
      
    // repeated 22 times
    if ($column==1) {echo "<tr>";}
    }
    ?> 
    And can I say: if (price1 != 0), (price2 != 0), (price3 != 0) and so on... ?
    Or how do I add in the other prices?

    This way, even if there's a calculation for a price where the value is 0, the whole calculation and price is ignored??

  • #12
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    well, it'd be $item['price1'] != 0 but yah... there's no neat efficient way do it I can think of... except maybe an array with a foreach loop:

    PHP Code:
    $priceList = array('price1''price2', ...) //all columns
    foreach ($priceList as $indivPriceCol) {
    if (
    $item[$indivPirceCol] != 0) {
    // do your calculations here
    }

    Its still messy and I feel the best way in the end will be to figure out your DB and redo that... You could have a table for prices, a table for quantities, and do your initial calculations in the query itself.

    Do the amounts every change? If they don't, it shouldn't be in the DB as columns of their own... it just confuses the issue, takes up space, and without them you might be able to clear up the queries.

  • #13
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts
    So that way, could I do this:

    PHP Code:
    $priceList = array('price1''price2', ...) //all columns
    foreach ($priceList as $indivPriceCol) {
    $item['indivPriceCol'] != {
    $item['indivPriceCol'] = round ((($item['indivPriceCol']+5) *1.85),0);  
      
    $item['indivPriceCol'] = $base_m*(ceil(($item['indivPriceCol2'])/$base_m)); 
    }  } 
    Or did I miss what you were trying to say?

  • #14
    New Coder
    Join Date
    Dec 2009
    Posts
    96
    Thanks
    1
    Thanked 2 Times in 2 Posts
    When I use any of your options, there's no data at all.... If I add it into the select query, I get no results (empty page). But if I add it in the 'if', I see everything, though no prices (prices column is empty).

    I also tried adding the indivPriceCol loop, but that also gives me an empty price column....

    Can't I use something like this:

    PHP Code:
    if not Len(CCGetParam("n_onb","")) > 0 then
    Results_AR
    .code_on.visible=false
    ELSE
    Results_AR.code_on.visible=true
    END 
    IF 
    I found that on the internet, but I'm not sure how to use it... thx for helping me!

  • #15
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I have no idea what that code is supposed to do, or if its even PHP. I've never seen PHP syntax like that.

    Did you use the code I wrote AFTER you responded to my last post? I accidentally submitted then had to edit it to fix it. Because if you're using the code you put, its not going to work.


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •