选择date = NOW（）的行？

Question

I am trying to create a table that logs steps depending on date and the user id. But when I run my code, it happens that I get duplicate rows if a user logs their steps several times a day. I can't have a date with a unique key because that would cause all other users unable to log steps if a any other user has logged steps the same day. So my point is that I want to remove the option of having duplicate rows where user id and date is identical. I have two tables

Table a and table b, and I will refer to them as something.a and something.b

I have a problem with returning a valid row when using $entry = "SELECT * FROM table.a WHERE userid.a = '$user_id.b' AND date=NOW()"

I want to use it as a conditional to decide to either UPDATE or INSERT INTO table.a. I have user_id.b from an previous query which works as it is, so I will leave that as it is for now.

Here is how I query the database:

$entry_result = mysqli_query($conn, $entry);

Which is used here:

if (mysqli_num_rows($entry_result) > 0){
    $conn->query("UPDATE steplogger SET steps='$steps' WHERE userid='$user_id' AND date=NOW()");
} else {
    $conn->query("UPDATE users SET totalsteps = totalsteps + ('$steps') WHERE username = '$user'");
    $conn->query("INSERT INTO steplogger (steps, userid, date) VALUES ('$steps', '$user_id', NOW())");
}

Any thoughts on what I am doing wrong?

PS. When I echo $entry_result I get a mysqli object.

Answer 1

As you said :

I want to remove the option of having duplicate rows where user id and date

The best way is to create an UNIQUE index on user_id and date, this way you won't be able to insert two rows with same user_id and date.

With an UNIQUE index, you can use INSERT...ON DUPLICATE KEY UPDATE that will do what you want : you will insert a new row (new user_id + date) and if a row already exists with the same user_id and date, you will update the row.

Here is the documentation : https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html