CODEWITHSUNDEEP
phpmysqlwhile-loopinsertnaming-conventions
JakePowell
JakePowell

Reputation: 100

Inserting data into Mysql with PHP while loop

I'm trying to check an email against my database, and if it doesn't already exist, add it to the database. 

$query = "SELECT * FROM users";

$inputQuery = "INSERT INTO users (`email`, 
`password`) VALUES ('$emailInput', 
'$passInput')";

$emailInput = ($_POST['email']);

$passInput = ($_POST['password']);


if ($result = mysqli_query($link, $query)) { 

    while ($row = mysqli_fetch_array($result)) { 
        if ($row['email'] == $emailInput) { 
            echo "We already have that email!";
        } else {
            mysqli_query($link, $inputQuery);
            echo "Hopefully that's been added to the database!";
        }
    }
};

It can detect an existing email, it's just the adding bit...

Currently this seems to add a new empty row for each existing row (doubling the size). I'm trying to understand why it doesn't add the information, and how to escape the loop somehow.

Also for good measure, everyone seems to reuse $query, but this seems odd to me. Is it good practice to individually name queries as I have here?

Please let me know if there's anything else I should add.

Upvotes: 0

Views: 1392

Answers (3)

Siddharth Ramani
Siddharth Ramani

Reputation: 684

Try this :

$emailInput = mysqli_real_escape_string($link, $_POST['email']);
$passInput = mysqli_real_escape_string($link, $_POST['password']);

$qry3=mysqli_query($link,"select * from users where `email`='".$emailInput."'");
$num=mysqli_num_rows($qry3);
if($num==1) {

    echo "Email-Id already exists";

} else {

    $inputQuery = mysqli_query($link,"INSERT INTO users (`email`, `password`) VALUES ('".$emailInput."', '".$passInput."')");
    if ($inputQuery) { 
        echo "Hopefully that's been added to the database!";
    }
}

Upvotes: 0

Sergey Cherednichenko
Sergey Cherednichenko

Reputation: 29

Your code seems to be a bit over-engineered because why not to pass you $_POST['email'] to select query where clause "SELECT * FROM users where email = $emailInput" and then check if it is there already. Also, keep in mind that this is an example only, and you should always check and sanitize user input. From another hand you can do it with MySQL only using INSERT ... ON DUPLICATE KEY UPDATE Syntax. https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html That requires to add unique key for email column.

Upvotes: -1

Chella
Chella

Reputation: 1562

I am not going to talk about the standards but straight, simple answer to your question.

Approach - 1:

INSERT INTO users (`email`,`password`) SELECT '$emailInput', '$passInput' from DUAL WHERE NOT EXISTS (select * from users where `email` = '$emailInput');

Approach - 2: - Create a unique key on email column - use INSERT IGNORE option.

user3783243 comments are worth noting

Upvotes: 2

Related Questions