Find all values associated with certain value in a MySQL database

Question

I'm creating a signup form and am onto the confirmation email part. I want to find all values associated with one other value in a database. Ex. I get the "key" that is in the URL, then want to find all the values associated with it. In my database there are 4 columns: STR (the key), USERNAME, PASSWORD, and EMAIL. If I get STR I want to get the username, password, and email that are in the same row as the key and then insert it into another table in the same database.

verify.php:

<?php
    $username = $_GET['username'];
    $password = $_GET['password'];
    $email = $_GET['email'];
    $servername = "localhost";
    $user = 'usernamelol';
    $pass = 'passwordlol';
    $dbname = 'vibemcform';
    $str = $_GET['str'];
    $conn = new mysqli($servername, $user, $pass, $dbname);
    /* The variable query gets the "key" from the dont database. I want to compare that value with the other values associated with it. Ex. the variables in the same row as the key. */
    $query = mysqli_query($conn, "SELECT * FROM `dont` WHERE STR='".$key."'");


    /* Below is my attempt. Feel free to change whatever you want. */
    $sql = "SELECT USERNAME, PASSWORD, EMAIL FROM dont";
    $result = $conn->query($sql);
    if (!$query) {
        die('Error: ' . mysqli_error($con));
    }
    if (mysqli_num_rows($query) > 0) {
        if ($result -> num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $sqltwo = "INSERT INTO data (USERNAME, PASSWORD, EMAIL) VALUES ($row["USERNAME"], $row["PASSWORD"], $row["EMAIL"])";
            }
        }
    }


    echo 'Successfully verified your email!'; exit;

?>

Answer 1

Why not simpy use the insert ... select syntax?

insert into data(username, password, email) 
select username, password, email from dont where str = :key

You can run this query right ahead, and then check how many rows were affected:

• If no row was affected, then it means that the select did not bring a row back: so the :key was not found in the database

• If a row was affected, then the key was found and the executed row was inserted

Note that you should use parametrized queries so your code is safe from SQL injection (and more efficient as well); recommended reading How can I prevent SQL injection in PHP??