Is there a simple way to insert and update the DB?
I have been doing it this way for a long time:
$query = mysql_query("UPDATE table SET name='$name', city='$city', phone='$phone' WHERE id='$id'");
Or when retrieving data from the DB, I've been doing this:
$query = mysql_query("SELECT * FROM table WHERE id='$id'");
$info = mysql_fetch_array($query);
$name = $info['name'];
I'm figuring (hoping) there's a way to streamline this process in a way. It just gets redundant after a while for some applications that have lots of database interaction. But maybe this is just the way it is.
Any thing you find yourself doing over and over is a good candidate for being generalized in a function or class. For example, in this case you might create a dbInsert() function and a dbQuery() function, each of which would accept arguments for table name, field names to be inserted or selected, values to be inserted or columns/values to be tested in a select WHERE clause, and so forth. You might have to spend a bit of time up front to make those functions robust enough, but once done, they could be re-used over and over.
Many frameworks come with some sort of DB abstraction layer with similar capabilities; or you could build your own classes (models in a MVC framework?) to handle common tasks you expect to perform in your DB, and so forth. Exactly what approach I would take would be situation-dependent on the size and scope of the app, what framework you're using (if any) and so forth.
In a couple apps I've written in the past, I created an abstract DB table class, then extended it for each specific table -- the main difference being class variables that provide the column names, which is the primary key column, and so forth. You would then still need to write separate functions/queries for special cases, such as complex queries with multiple tables/joins, but the basic stuff can still be generalized.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation