Retterdesdialogs Retterdesdialogs - 2 months ago 6
MySQL Question

Mysql Table field value as index in php array for update statement

i don't get it..., i have a table

resource
with
name
,
amount
and
level


So i created an array where i want to store the amount of resources added to each resource by level.

For example, i have ResourceA in my database with an amount of 100 and with a level 1. Because of level 1 (resourceA array index) i want to add 2 points to the amount of 100.

This is working:
WHEN 'ResourceA' THEN amount + $resourceA[1]


This is not working:
WHEN 'ResourceA' THEN amount + $resourceA[level]


$resourceA = array(0, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024);
$resourceB = array(0, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024);
$resourceC = array(0, 1, 2, 4, 8, 16, 32, 64, 128, 256, 512);

try {
$conn = new PDO("mysql:host=$servername;port=3307;dbname=$dbname",$username, $password);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "UPDATE resource SET amount = CASE name
WHEN 'ResourceA' THEN amount + $resourceA[level]
WHEN 'ResourceB' THEN amount + $resourceB[level]
WHEN 'ResourceC' THEN amount + $resourceC[level]
END";

$conn->exec($sql);
echo "Updated successfully";
}
catch(PDOException $e)
{
echo $e->getMessage();
}

$conn = null;


Can someone help me, to use the table field
level
as index of the resource-array?

Update: I understand why this can't working because of your explanations. Thanks for that.

I thought it would work because

WHEN 'ResourceA' THEN amount + $resourceA[1]


is working and also
amount = amount + 2
is working, then the hole string is interpreted like
WHEN 'ResourceA' THEN amount + $resourceA[1]


Maybe there is a trick with prepared binding, that i can use level as a number in the statement.

Thanks

Answer

To answer your question literally: of course, that's just impossible.

Because SQL won't run your PHP code. Not to mention that in the resulting SQL code there will be no PHP at all - you can check it yourself by simply echoing $sql variable out.
Likewise there is no level field value from a database while you are assembling an SQL query in your PHP script. PHP and SQL are two buildings standing opposite. You can send a letter from one to another, but you cannot write this letter in one house using a pen in another.

Luckily, for your case you don't need PHP arrays at all. Just update the fields using simple math

UPDATE resource SET amount = CASE name 
           WHEN 'ResourceA' THEN amount + pow(2, level)
           WHEN 'ResourceB' THEN amount + pow(2, level)
           WHEN 'ResourceC' THEN amount + pow(2, level) / 2
           END

In general you just have to run a separate query for each name/level combination. It's not a big deal even without prepared statements, but with them it can be done a bit cleaner

$update = [
    'resourceA' = array(0, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024),
    'resourceB' = array(0, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024),
    'resourceC' = array(0, 1, 2, 4, 8, 16, 32, 64, 128, 256, 512),
];
$sql = "UPDATE resource SET amount = ? WHERE name = ? AND level = ?";
$stmt = $pdo->prepare($sql);
foreach ($update as $name => $row)
{
    foreach ($row as $level => $value)
    {
        $stmt->execute([$value, $name, $level]);
    }
}