How to do a php form with mysql query if statements?

Question

I just want to check if a value is in a table (key_list) and if it is true/the value is in the table (key_list) delete the value and insert an other value to an other table (player_list) if the value is already in the other table (player_list) then it shouldn't insert the value.

Here goes my code:

<!DOCTYPE html>
<head>
    <lang
    ="en">
    <link href="https://fonts.googleapis.com/css2?family=Poppins&display=swap" rel="stylesheet">
    <link href="https://unpkg.com/aos@2.3.1/dist/aos.css" rel="stylesheet">
    <link href="assets/css/main-css.css" rel="stylesheet">
    <script src="assets/js/sweetalert2.min.js"></script>
    <script srt="assets/js/jquery-3.5.1.js"></script>
    <meta charset="UTF-8">
    <link rel="shortcut icon" href="assets/img/icon.png">
    <title>BuzzGames | BetaKey</title>
</head>
<body>

<form method="POST" action="signup.php">
    <input type="text" name="name" placeholder="dein Minecraft-Name">
    <br>
    <input type="text" name="key" placeholder="dein Betakey">
    <br>
    <input type="submit">
</form>
</body>

<?php
$name = $_POST['name'];
$key = $_POST['key'];

$host = "localhost";
$user = "system";
$password = "";
$db = "buzzgames_betakey";

$cxn = mysqli_connect($host, $user, $password, $db);

$qryPlayerGet = mysqli_query($cxn, "SELECT * FROM player_list WHERE username = ('$name')");
$qryPlayerAdd = mysqli_query($cxn,"INSERT INTO player_list('username') VALUES ('$name')");
$qryKeyGet = mysqli_query($cxn,"SELECT * FROM key_list WHERE betakey = '$key'");
$qryKeyRemove = mysqli_query($cxn,"DELETE * FROM key_list WHERE betakey = '$key'");

if (!mysqli_query($cxn, $qryPlayerGet)) {
    mysqli_query($cxn, $qryPlayerAdd);
}

if (!mysqli_query($cxn, $qryKeyGet)) {
    mysqli_query($cxn, $qryKeyRemove);
}

header("Location: index.php");

?>

**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Sep 01 '20 at 21:11
Tip: Don't define your queries in one place and run them in another. Supply the query string *directly* to the function so there's zero confusion about which query is actually running. — tadman, Sep 01 '20 at 21:12
Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era and should not be used in new code. Additionally the procedural interface has less rigorous error checking and reporting, frustrating debugging efforts. — tadman, Sep 01 '20 at 21:12
You don't need to `SELECT` and then `DELETE`, you can just `DELETE` and check how many rows were removed. If `0` then nothing happened, if `1` then that key got removed. — tadman, Sep 01 '20 at 21:13
Consider whether you really want to 'hard delete' data in this way — Strawberry, Sep 02 '20 at 06:22

score 0 · Answer 1 · answered Sep 01 '20 at 21:21

The syntax of your INSERT statement is wrong. It should be:

 INSERT INTO player_list (`username`) VALUES (?)

Where note the use of the backwards quotation marks for column names. These are technically optional unless the entity name conflicts with a reserved keyword so you can skip them entirely here if you want.

Note that this problem went undetected because you're not checking the return value of these queries, you're just running them and assuming everything was fine.

Tip: A lot of problems can be detected and resolved by enabling exceptions in mysqli so errors resulting from simple mistakes made aren’t easily ignored. Without exceptions you must pay close attention to return values, many of these indicate problems you must resolve or report to the user. Exceptions allow for more sophisticated flow control as they can “bubble up” to other parts of your code where it’s more convenient to handle them.

How to do a php form with mysql query if statements?

1 Answers1