Before I began developing a RESTful API I used a query similar to this:
$query = "INSERT INTO availability (user_id, date, status) " . "VALUES ('".$id."', '".$date."', '".$status."') " . "ON DUPLICATE KEY " . "UPDATE status='".$status."'";
Yes, I know it's subject to SQL injection. Anyway, I'm having trouble deciding if this should be a POST or PUT request since it can insert or update. I got to thinking: maybe it's better to have both POST and PUT methods in the API and then the client determines which one to call.
Is this usually how RESTful APIs handle this scenario?
It might help to first look at the difference between POST and PUT.
The main difference is that PUT is intended for idempotent operations. That is, operations that can be performed multiple times but will behave as if they were performed once. POST has no such assumption. The significance of idempotence is that the client or infrastructure can repeat the request if it doesn't seem to have been acknowledged. With a POST request, this may cause unintended consequences. This is why your browser warns you if you refresh a page that was produced via a POST request.
The operation in your question is idempotent, so PUT can and should be used. A POST request would be appropriate for operations that are not idempotent like operations that are more like RPC calls or, more in the spirit of REST and HTTP, creating new records. For example, if you had an auto-increment ID field and each INSERT created a new record, you'd use POST because such an operation is not idempotent: executing it twice will create two new records.