array addition from database

[Beffic]

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.

$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!

[Weedpacket]

This could be done in the database.

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

[Beffic]

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.

[Derokorian]

You use an array key of 'cost' on the array returned from mysql_fetch_array

[Beffic]

Well when I go to do a mysql_fetch_array I keep getting a warning that it's not supplied argument
.

[Beffic]

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

$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."');");
    } 

[bradgrafelman]

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.

[Beffic]

Okay this is what I am using now, and getting the error a little better.

$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?

[bradgrafelman]

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:

WHERE name in (string, string, string, ...)

which is incorrect, since all strings must be surrounded with (single) quotes in SQL queries.

[Beffic]

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"

[Weedpacket]

You've never fetched a value out of a MySQL result set before?

[Beffic]

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.

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

[Weedpacket]

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.

[Derokorian]

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.

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