Help with PDO func

[zymesujy]

hi, im trying to make function that take every row from column inv by user id multiply it by 0,015 and then insert this value to zhod with same user id.

i have this but im lost already. if someone can help or simplify it ill be glad.

public function updateZhod($id) {
   $sql = "UPDATE users SET zhod = inv * 0.015 WHERE id = :id AND verified = 1";
    $stmt = $this->conn->prepare($sql);
    $stmt->bindParam(':id', $id);
    $stmt->execute();
}

if (isset($_POST['action']) && $_POST['action'] == 'fetchAllUsersInv') {
    $output = '';
    $data = $admin->fetchAllUsersInv(0);
    if ($data) {
        $output .= '<table class="table table-striped table-bordered text-center">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Inv</th>
				<th>Zhod</th>
                            </tr>
                        </thead>
                        <tbody>';

        foreach ($data as $row) {
            $zhodValue = $row['inv'] * 0.015;

            $output .= '<tr>
                            <td>' . $row['id'] . '</td>
                            <td>' . $row['inv'] . '</td>
                            <td>' . $zhodValue . '</td>
                        </tr>';
        }

        $output .= '</tbody>
                    </table>';

        echo $output;
    } else {
        echo '<h3 class="text-center text-secondary">:( No inv</h3>';
    }
}


if (isset($_POST['action']) && $_POST['action'] == 'updateZhod') {
    $admin->updateZhod();

    echo 'Zhod values updated successfully!';
}

<script>
    $(document).ready(function() {
        $("#zhodLed").click(function() {
            $.ajax({
                url: 'assets/php/admin-action.php',
                type: 'POST',
                data: {
                    action: 'updateZhod'
                },
                dataType: 'json',
                success: function(response) {
                    if (response.success) {
                        $("#invValue").text(response.newValue.toFixed(2) + " ...");
                    } else {
                        alert("Error: Failed to update zhod value.");
                    }
                },
                error: function(jqXHR, textStatus, errorThrown) {
                    console.error("AJAX Error:", textStatus, errorThrown);
                    alert("Error: Unable to make the AJAX request.");
                }
            });
        });
    });
</script>

[marcusparsons]

@zymesujy

public function updateZhod($id) {
   $sql = "UPDATE users SET zhod = inv * 0.015 WHERE id = :id AND verified = 1";
    $stmt = $this->conn->prepare($sql);
    $stmt->bindParam(':id', $id);
    $stmt->execute();
}

Hi @zymesujy, and welcome to the forums!

In reading your goal, I want to clarify a couple things:

1. What DB system are you using for this? i.e. MySQL, T-SQL (Sql Server), Postgres SQL, etc.
2. Is your goal to keep the original records and insert new records with the updated zhod value that is multiplied by 0.015, or is it to just modify all of the existing records to multiply their zhod value by 0.015? That will change the approach to this problem.

And I hope someone else is able to step in if you need help immediately on this. I won’t be back for a few hours, but I want to get some clarifying steps in so that I, or someone else, can help you on the right path.

[zymesujy]

Hi thanks for response.

1. using MySQL
2. my goal is to take value from inv from every user because every user have this value different. Then i have another column called zhod and i want to save here result from inv *0,015. inv always stays the same. i dont know if i described it in the state that native speaker can read it

e.g.
before
id=1, user=joe, inv=10,zhod=
id=2, user=jake, inv=20,zhod=
result after click on btn with that function
id=1, user=joe, inv=10,zhod=0,15
id=2, user=jake, inv=20,zhod=0,30

[marcusparsons]

I noticed that in the updateZhod function you have a parameter where it’s expecting $id to be present, but in the call to that function, you don’t pass in an id.

If you’re wanting to update that entire table, you’ll need to update the filtering/where clause in your update statement, and this will depend on if you still want to only target records where verified = 1

$sql = "UPDATE users SET zhod = inv * 0.015 WHERE verified = 1";

Or if you want to target all records in that table:

$sql = "UPDATE users SET zhod = inv * 0.015";

Either way, it no longer needs the bindParam that is in the updateZhod function since you aren’t using the $id variable.

It sounds like you want to target every record, so this is what I would do:

public function updateZhod() {
    $sql = "UPDATE users SET zhod = inv * 0.015";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
}

[zymesujy]

ok i rewrites the codes. but somehow it work only halfway. when i upload files it will automatically count it. I need to start count after clicking the button. but now whe i click it it cause err 500 and ajax.

dashboard:

<div class="col-xl-4 col-md-6">
                <div class="card bg-light text-black mb-4">
                    <div class="card-header">
					Leden
					<input type="submit" value="Přidat" id="zhodLeden" class="btn btn-primary btn-lg btn-block myBtn button1">
					</div>
                    <div class="card-body">
                        <h1 class="display-4">
                            <?= number_format($count->sumZhod(), 2, ",", "."); ?> ...
                        </h1>
                    </div>
                </div>
            </div>

<script>
$(document).ready(function() {
    $("#zhodLeden").click(function() {
        $.ajax({
            url: 'assets/php/admin-action.php',
            type: 'POST',
            data: {
                action: 'updateZhod'
            },
            dataType: 'json',
            success: function(response) {
                $("#invValue").text(response.newValue.toFixed(2) + " Kč");
            },
            error: function(jqXHR, textStatus, errorThrown) {
                console.error("AJAX Error:", textStatus, errorThrown);
                alert("Error: Failed to update zhod value.");
            }
        });
    });
});
</script>

if (isset($_POST['action']) && $_POST['action'] == 'updateZhod') {
    $admin->updateZhod();

    echo json_encode(['success' => true, 'newValue' => $admin->getSumZhod()]);
}

public function updateZhod() {
    $sql = "UPDATE users SET zhod = inv * 0.015";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
}


public function getSumZhod() {
    return $this->count->sumZhod();
}

somehow it working alone but not when i do it

[marcusparsons]

In between the original post and this update, there were some things added that don’t look quite right to me such as the getSumZhod function. And that function hides another function that you didn’t post called sumZhod.

Please post the code for the sumZhod function and please post any custom functions that it calls as well. My bet is that somewhere in that function, it is going wrong.

[zymesujy]

yea sorry forgout about it im desperate so i tried averything. this was it :

public function getSumZhod() {
    return $this->count->sumZhodn();
}

anyway from your part we was on right path but somehow the whole code dont undestand each other. I probably added something , forgot about it , update it and now im totaly lost and its simple take one from a and place it in b function

[marcusparsons]

I’m asking for the code behind the actual sumZhod function because the getSumZhod function is only calling that function.

[zymesujy]

yea sorry as i told i forgot to dlt this func. cuz as i look i dont have code for this already. i tried and dlted but forgot dlt this one

[stories]

yeah its working for me