array addition from database
Results 1 to 14 of 14

Thread: array addition from database

  1. #1
    Senior Member
    Join Date
    Dec 2007
    Posts
    178

    array addition from database

    I cannot seem to get the array to add together the way I am looking for so I am here for help.

    Right now it gets information off the database; and every time the in_array instance is true where the "name" is equal to the array variable $preset it will do an addition equation.

    Well my problem is... whenever it will only find the name once and make the addition. What I need it to do is make an addition every time if finds the name in the array rather than once it finds it once it only adds once.


    PHP Code:
    $findManaCost mysql_query("SELECT * FROM affinity");
            
    $cost 0;
            while(
    $spellCost mysql_fetch_array($findManaCost))
            {
                if(
    in_array($spellCost['name'], $preset)){
                
                    if(
    $spellCost['level'] == 1){
                        
    $cost += 1000;
                    }elseif(
    $spellCost['level'] == 2){
                        
    $cost += 2500;
                    }elseif(
    $spellCost['level'] == 3){
                        
    $cost += 7500;
                    }elseif(
    $spellCost['level'] == 4){
                        
    $cost += 20000;
                    }elseif(
    $spellCost['level'] == 5){
                        
    $cost += 50000;
                    }else{
                        
    $cost += 0;
                    }
                
                }
                
            } 

    So what I am trying to say is, once it finds the name it only adds once, but not multiple times for every time it finds the name in $preset.

    I even tried the mysql "IN" statement but that doesn't seem to work very well with mysql_fetch_array()

    What is the best way to go about this. I am having writers block.


    Thanks in advance!
    Want to try a new mmorpg game? www.riseimmortals.com

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,842
    This could be done in the database.
    Code:
    SELECT SUM(
        CASE WHEN level=1 THEN 1000
             WHEN level=2 THEN 2500
             WHEN level=3 THEN 7500
             WHEN level=4 THEN 20000
             WHEN level=5 THEN 50000
             ELSE 0
             END
    ) AS cost FROM affinity WHERE name in (?,?,?,?)
    Using PDO and in particular the example regarding IN clauses in Example 5. (Why are you still using plain old mysql_query (given the notice on that page)?)
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    Senior Member
    Join Date
    Dec 2007
    Posts
    178
    How would I output "AS cost"? Sorry I am a bit confused because I am trying to turn it into a variable in php and trying to execute in php.
    Want to try a new mmorpg game? www.riseimmortals.com

  4. #4
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,764
    You use an array key of 'cost' on the array returned from mysql_fetch_array
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  5. #5
    Senior Member
    Join Date
    Dec 2007
    Posts
    178
    Well when I go to do a mysql_fetch_array I keep getting a warning that it's not supplied argument
    .
    Want to try a new mmorpg game? www.riseimmortals.com

  6. #6
    Senior Member
    Join Date
    Dec 2007
    Posts
    178
    <b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/home/riseofim/public_html/roi/spellsPreset.php</b> on line <b>82</b><br />
    alert('Presets have been distributed. Total cost ');
    PHP Code:
    $presetFinder3000 implode(","$preset);
            
    $cost mysql_query("SELECT SUM(
        CASE WHEN level=1 THEN 1000
             WHEN level=2 THEN 2500
             WHEN level=3 THEN 7500
             WHEN level=4 THEN 20000
             WHEN level=5 THEN 50000
             ELSE 0
             END
    ) FROM affinity WHERE name in ("
    .$presetFinder3000.")");

            while(
    $spellcost mysql_fetch_array($cost)){
                
    $manaCost $spellcost['sum(level)'];
            }
            print(
    "alert('Presets have been distributed. Total cost ".$spellcost."');");
        } 
    Want to try a new mmorpg game? www.riseimmortals.com

  7. #7
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,393
    You've never come across a failing SQL query before? Standard SQL debugging techniques apply here; you should be checking to see if mysql_query() indicates an error has occurred and, if so, output and/or logging the SQL error message (retrievable via mysql_error()) and perhaps the SQL query itself (to aid in debugging).

    Once you fix the query itself, note you've got a couple of issues with that while() loop:
    1. $spellcost will never contain an element at index 'sum(level)' (since there's no such column/alias being SELECT'ed in the query). The "AS" syntax previously shown by Weedpacket allows you to alias items you're SELECT'ing, for example you could alias long complex expressions as a single word ("cost") so they can be more easily accessed in an associative array.
    2. If you only expect at most one result, it doesn't make sense to use a loop construct at all.

  8. #8
    Senior Member
    Join Date
    Dec 2007
    Posts
    178
    Okay this is what I am using now, and getting the error a little better.

    PHP Code:
    $preset implode(","$preset);
            
    $cost mysql_query("SELECT SUM(
        CASE WHEN level=1 THEN 1000
             WHEN level=2 THEN 2500
             WHEN level=3 THEN 7500
             WHEN level=4 THEN 20000
             WHEN level=5 THEN 50000
             ELSE 0
             END
    ) FROM affinity WHERE name in ("
    .$preset.")")or die(mysql_error());

            print(
    "alert('Presets have been distributed. Total cost ".$cost."');");
        } 
    And this is the error
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'V,Intelligence V,Intelligence V,Intelligence V,Intelligence V,Concentration V,Co' at line 9
    Do I not have the required mysql version to run this?
    Want to try a new mmorpg game? www.riseimmortals.com

  9. #9
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,393
    There's nothing wrong with your MySQL version - you just don't have a properly formed query. If you had printed out your query, you'd see that the "WHERE name in ()" section looks like:

    Code:
    WHERE name in (string, string, string, ...)
    which is incorrect, since all strings must be surrounded with (single) quotes in SQL queries.

  10. #10
    Senior Member
    Join Date
    Dec 2007
    Posts
    178
    Okay I fixed that problem. Thanks for letting me use your single quotes. But how do I display cost as a numeric value? Right now it outputs as "Presets have been distributed. Total cost Resource id #11"
    Want to try a new mmorpg game? www.riseimmortals.com

  11. #11
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,842
    You've never fetched a value out of a MySQL result set before?
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  12. #12
    Senior Member
    Join Date
    Dec 2007
    Posts
    178
    Well I hope you can bare with me, I have never used "sum", "in", or "as" in a mysql query before. But this is what I have put together with all of your information and I am getting a returned value of 0 where is should be 450,000 because of the 9 $preset.

    PHP Code:
    $costSQL mysql_query("SELECT SUM(
        CASE `level`
             WHEN '0' THEN '0'
             WHEN '1' THEN '1000'
             WHEN '2' THEN '2500'
             WHEN '3' THEN '7500'
             WHEN '4' THEN '20000'
             WHEN '5' THEN '50000'
             END
    ) AS cost FROM affinity WHERE name IN ('"
    .$preset."')")or die(mysql_error());

            

            while(
    $cost mysql_fetch_assoc($costSQL))
            {
            
                
    $costTotal += $cost['cost'];
            
            }

            print(
    "alert('Presets have been distributed. Total cost ".$costTotal."');"); 
    I can only imagine that it's something with the syntax or I am not outputting it correctly. The values and names appear correct in the database to the code.
    Want to try a new mmorpg game? www.riseimmortals.com

  13. #13
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,842
    I don't want to bare with you. I don't know you well enough. I'm prepared to bear with you, though, long enough to point out that SQL is SQL and as far as PHP is concerned any SQL command is just another string.

    You're only going to get one row back (because the sum is in the query) so the loop is overkill.

    Your single quotes around $preset is going to break things. You want each string to be quoted, not the whole thing. You're searching for records with the name 'foo,bar,baz' and not records with names in the list ('foo', 'bar', 'baz'). No wonder it's not finding any.

    In other words, you're going to have to go through the $preset array and quote all of the strings in it, then implode. This is a main reason why I used the PDO interface instead of the outdated MySQL one: it does the appropriate quoting and escaping for you.

    And while MySQL probably silently casts strings to integers, making it do so is just wasted effort. 0, 7500 and the like don't need to be quoted.
    Last edited by Weedpacket; 10-24-2012 at 03:35 AM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  14. #14
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,764
    Quote Originally Posted by Weedpacket View Post
    In other words, you're going to have to go through the $preset array and quote all of the strings in it, then implode.
    Alternatively you could implode with a separator of "','" and insert the way you are.

    PHP Code:
    $preset implode("','",$preset);
    $costSQL mysql_query("SELECT SUM( 
        CASE `level` 
             WHEN '0' THEN '0' 
             WHEN '1' THEN '1000' 
             WHEN '2' THEN '2500' 
             WHEN '3' THEN '7500' 
             WHEN '4' THEN '20000' 
             WHEN '5' THEN '50000' 
             END 
    ) AS cost FROM affinity WHERE name IN ('"
    .$preset."')")or die(mysql_error()); 
    Although, I do agree 100% that you should not be using the outdated mysql_* extension and should instead move to mysqli or pdo.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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