I have a function similar to the following
function add_user_comment($user_id, $comment_id)
{
- some code-
}
both user and comment have a separate tables in the database.
Now, when calling the function if providing wrong values for either $user_id or $comment_id it should return an error message.
What is the proper approach to this:
perform validations that user_id, comment_id exist in their tables and then run the query? this means going to the database multiple times.
run the query without checking and throw an error when it fails because of foreign key constrains.
The proper way to do this is to create a stored procedure that handles your insert for you. In the stored procedure you can create a transaction, check for your values, and then raise an error that will get returned from the DBMS up to your code, which you can then catch and use THAT as your indication that an error occurred. If you are NOT in an error condition, then the Stored Procedure can perform the insert, and you've how handled both tasks in a single operation, using only ONE database turn-around. Stored procedures run on the DBMS, and are generally "compiled" so they run very quickly.
This is the best way, rather than having you CODE execute a query to see if something exists, and have the results fetched from the DB, packaged up, and set down the wire to your code, at which point you have to take action based on what you see, then YOU have to package up the data, and send IT down the wire to the DBMS, to perform the action you wish to perform. That's two database turnarounds, when one will do the trick. It's all about error handling, and how you do it, and what you do in the case of an error.
I should also mention that your Schema has A GREAT DEAL to do with how successful your error handing mechanism will be, overall. if your Schema is wrong, then you might be updating multiple tables, and inserting data, and getting ID's back in order to insert data into other tables, etc... That's just... WRONG... You want to make sure your inserts are also done in a Stored Procedure, so a single call to it will insert the record, any associated records, and possibly return you the new ID of the record you just inserted. Well, that or an error.