PHP PDO: Sell button for individually rows

Question

I wrote some PHP code that shows all 'cars' a user has in his garage. It shows the model, the worth and a sell button, but I can't seem to fix the sell button. The sell button appears in every row in the table together with the model and worth (waarde). I want that if you press the sell button, the right car gets sold and deleted from the database. But right now, it doesn't matter which one you press, it sells all the cars. I think I am just putting the code for the selling of the cars at the wrong place, so can someone help me figure this out?

Code:

<?php
include "notLoggedIn.php";

$username = $_SESSION['username'];

$globalstmt = $pdo->prepare("SELECT id from users where gebruikersnaam = :username");
$globalstmt->execute(['username' => $username]);
$globalrow = $globalstmt->fetch();
$globalid = $globalrow['id'];

$stmt = $pdo->prepare("SELECT auto_id FROM garage WHERE user_id = :globalid");
$stmt->execute(['globalid' => $globalid]);
$rows = $stmt->fetchAll();
if (count($rows) == 0) {
echo "Je hebt nog geen auto's in je garage.<br /><a href='auto_stelen.php'>Druk hier om een auto te proberen stelen!</a>";
} else {
    echo "<table width='300px''>
    <tr>
        <th>Garage</th>
    </tr>
    <tr>
        <td><b>Model</b></td>
        <td><b>Waarde</b></td>
    </tr>
    <form method='post'>
    ";
    foreach ($rows as $row) {
        $autos = $row['auto_id'];
        $stmt = $pdo->prepare("SELECT * FROM autos WHERE id = :autos");
        $stmt->execute(['autos' => $autos]);
        $row = $stmt->fetch();
        echo "
        <tr>
        <td>" . $row['model'] . "</td>
        <td>€" . number_format($row['waarde'], 0, ',', '.') . "</td>
        <td><input type='submit' name='sell' value='Verkopen' /></td>
        </tr>";
    }
    echo "
    </form>
    </table>";
}

if (isset($_POST['sell'])) {
    $stmt = $pdo->prepare("SELECT * FROM garage WHERE user_id = :globalid");
    $stmt->execute(['globalid' => $globalid]);
    $rows = $stmt->fetchAll();
    foreach ($rows as $row) {
        $autos = $row['auto_id'];
        $stmt = $pdo->prepare("SELECT * FROM autos WHERE id = :autos");
        $stmt->execute(['autos' => $autos]);
        $row = $stmt->fetch();
        $waarde = $row['waarde'];
        $stmt = $pdo->prepare("UPDATE users SET cashgeld = cashgeld + :waarde WHERE gebruikersnaam = :username");
        $stmt->execute([
            'waarde' => $waarde,
            'username' => $username
        ]);
        $stmt = $pdo->prepare("DELETE FROM garage WHERE auto_id = :autos");
        $stmt->execute(['autos' => $autos]);
        header("Refresh: 0");
    }
}
?>

The page currently looks like this

**Warning!** You're open to [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection)! Read [how to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to learn how to use prepared statements correctly, using bound parameters. — M. Eriksson, Apr 03 '22 at 14:12
For every button `Verkopen` you have to send the correct values that belong to that sale (=Verkoop). (P.S. original code got lost in edit.... ) — Luuk, Apr 03 '22 at 14:31

score 0 · Answer 1 · 2022-04-03T14:59:50.127

Please, use PDO features. In short, you need to pass the parameter "auto_id". Now, try this way:

<?php

include "notLoggedIn.php";

$username = $_SESSION['username'];

$globalstmt = $pdo->prepare("SELECT id from users where gebruikersnaam = :username");
$globalstmt->execute(['username' => $username]);
$globalrow = $globalstmt->fetch();
$globalid = $globalrow['id'];

$stmt = $pdo->prepare("SELECT auto_id FROM garage WHERE user_id = :user_id");
$stmt->execute(['user_id' => $globalid]);
$rows = $stmt->fetchAll();
if (count($rows) == 0) {
echo "Je hebt nog geen auto's in je garage.<br /><a href='auto_stelen.php'>Druk hier om een auto te proberen stelen!</a>";
} else {
    echo "<table width='300px''>
    <tr>
        <th>Garage</th>
    </tr>
    <tr>
        <td><b>Model</b></td>
        <td><b>Waarde</b></td>
    </tr>
    <form method='post'>
    ";
    foreach ($rows as $row) {
        $autos = $row['auto_id'];
        $stmt = $pdo->prepare("SELECT * FROM autos WHERE id = :id");
        $stmt->execute(['id' => $autos]);
        $row = $stmt->fetch();
        echo "
        <tr>
        <td>" . $row['model'] . "</td>
        <td>€" . number_format($row['waarde'], 0, ',', '.') . "</td>
        <td><input type='submit' name='sell' value='Verkopen' /></td>";
        echo "</tr>";
        echo "<input type='hidden' name='auto_id' value='".$row['id']."' />";
    }
    echo "
    </form>
    </table>";
}

// New Code for sell button
if (isset($_POST['sell']) && isset($_POST['auto_id'])) {

    $stmt = $pdo->prepare("SELECT * FROM `autos` WHERE id = :id");
    $stmt->execute(['id' => $_POST['auto_id']]);
    $row = $stmt->fetch();

    $stmt = $pdo->prepare("UPDATE `users` SET `cashgeld` = `cashgeld` + :waarde WHERE `gebruikersnaam` = :username");
    $stmt->execute([
         'waarde' => $row['waarde'],
         'username' => $username
    ]);

    $stmt = $pdo->prepare("DELETE FROM `garage` WHERE `auto_id` = :auto_id");
    $stmt->execute(["auto_id" => $_POST['auto_id']]);

    header("Refresh: 0");

    }
}
?>

This just sells the newest one – Mauro Apr 03 '22 at 15:06 — Mauro, Apr 03 '22 at 15:06

score 0 · Answer 2 · answered Apr 03 '22 at 15:20

I would create a seperate form for each Auto in this situation. You also need to be able to know which Auto is being sold so the auto_id needs to be part of each form too. Place that into a hidden field so it gets passed when the sell button is pressed

To do this move the form into the <td> that contains the button.

    echo "<table width='300px''>
    <tr>
        <th>Garage</th>
    </tr>
    <tr>
        <td><b>Model</b></td>
        <td><b>Waarde</b></td>
    </tr>";

    foreach ($rows as $row) {
        $autos = $row['auto_id'];
        $stmt = $pdo->prepare("SELECT * FROM autos WHERE id = :autos");
        $stmt->execute(['autos' => $autos]);
        $row = $stmt->fetch();

        echo "
        <tr>
            <td>$row[model]</td>
            <td>€" . number_format($row['waarde'], 0, ',', '.') . "</td>
            <td>
                <form method='post'>
                    <input type='hidden' name='auto_id` value='$row[id]' />
                    <input type='submit' name='sell' value='Verkopen' />
                </form>
            </td>
        </tr>";
    }
    echo '</table>';

PHP PDO: Sell button for individually rows

2 Answers2