How to insert or update a query in mySQL if COUNT = 0

Question

I'm trying to create a registration form to insert or update users credential in a simple mysql table

I've watched almost every question here but I can't figure why my code doesn't update or insert the user in the form

//check if the user is already registered and then override and update his credentials 
$check = "SELECT COUNT(*) FROM theElisa_signUp WHERE Email = '$email'"; 

if (mysqli_query($conn, $check) >= 1)
{
    $update = mysqli_query($conn, "UPDATE theElisa_signUp SET name = '$name', user = '$username', psw = '$password', expire_date = '$expire_date' WHERE email = '$email'");
} elseif (mysqli_query($conn, $check) == 0) {
    //inserire dati sign up dei nuovi registrati
    $query = mysqli_query($conn, "INSERT INTO theElisa_signUp (name, email, telephone, user, psw, role, expire_date) VALUES ('$name', '$email', 'not-set', '$username', '$password', '$role', '$expire_date')");
} else {
    header( "Location: ../admin/index.php" );
}

As you can see, I want to check if the user's email already exists. If exists (in this case, if it's >= 1) the code must UPDATE the values already set in the mysql table. If doesn't exist already (== 0) it will create a new user with the INSERT.

I hope my question is clear and respects the standards.

EDIT: i've tried also the ON DUPLICATE KEY UPDATE but still nothing.

$query=mysqli_query($conn, "INSERT INTO theElisa_signUp (name, email, telephone, user, psw, role, expire_date) VALUES ('$name', '$email', 'not-set', '$username', '$password', '$role', '$expire_date')
ON DUPLICATE KEY UPDATE name = '$name', user = '$username', password = '$password', expire_date = '$expire_date' ");

Really hoping someone can help in this. Unfortunately I'm a simple web designer but for this project they asked me to do this and I have zero code background.

You'll be surprised, but `mysqli_query` does not return your count. — u_mulder, Apr 02 '19 at 08:38
Hi @u_mulder.. I'm super new in mysql and php. Can you explain me more? Sorry — Stefano Zanetti, Apr 02 '19 at 08:40
Take a look at the `INSERT ... ON DUPLICATE KEY UPDATE`. You can do all in one statement. — slaakso, Apr 02 '19 at 08:43
@slaakso - IODKU is like three levels above OPs "capabilities" (not my words). This is a basic "How to use mysqli to read data from DB" - question. — Paul Spiegel, Apr 02 '19 at 09:03
@slaakso I've tried to solve it with `INSERT ... ON DUPLICATE KEY UPDATE` but still nothing. please see my edited answer. thank you — Stefano Zanetti, Apr 02 '19 at 09:16
Take a look at the MySQL documentation on the subject (hint, your syntax has extra brackets in it). Your code is also vunerable to SQL injection, so you should use prepared statements and parameterized queries. Lastly, you should check for error conditions in your code. — slaakso, Apr 02 '19 at 09:24
hi @slaakso I really tried everything in my capabilities... i'm so in trouble in this. — Stefano Zanetti, Apr 02 '19 at 09:55

score 0 · Answer 1 · answered Apr 02 '19 at 08:47

From PHP Manual

/* Select queries return a resultset */
if ($result = mysqli_query($link, "SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", mysqli_num_rows($result));

    /* free result set */
    mysqli_free_result($result);
}

How to insert or update a query in mySQL if COUNT = 0

1 Answers1