The UPDATE and INSERT conditionals in syntax are an inconvenience
I read this article today.
http://vinothbabu.com/2010/05/08/update-and-insert-differences-in-syntax-is-an-inconvenience/
I was unable to understand this piece of code written by the author.
list($sets,$cols,$values)=escape_arr($sets);
$insert_sql="INSERT INTO `avatars` ".implode(’,',$cols)."
VALUES(".implode(’,',$values).")";
$update_sql="UPDATE `avatars` SET ".implode(’,',$sets)."
WHERE userid=$userid LIMIT 1″;
and finally, the final part of the article.
a source to share
PHP implode turns an array of column names into a comma separated string. It does the same for values in an INSERT statement.
$ sets starts out as an associative array of column name / value pairs. This statement:
list($sets, $cols, $values) = escape_arr($sets);
reassigns the variable $ sets as a regular array containing strings like "column_name = 'value". It does this with the escape_arr helper function in the article, which returns 3 arrays. Check the list documentation if you don't know what it does.
Then it uses the function again implode
to build one large comma-separated array string $sets
. So efficiently, it builds INSERT and UPDATE statements given an associative array containing the column names as keys as well as their values ....
Was this a question? You can insert some var_dump in your code to keep track of what it does at every step.
Edit: sorry for the messy explanation, but I have to run now :)
a source to share
On the other hand, in MySQL INSERT you can use the UPDATE syntax:
INSERT [INTO] tbl
SET col1 = 'value', col2 = 'value', col3 = 'value', ...
Refer to MySQL INSERT syntax for complete documentation.
a source to share
It doesn't make any sense. He suggests to hard-code your queries, because that way you can avoid thinking of them as objects (WTF?), And then says that hard-coding is bad, seemingly not realizing that this is exactly what he does. Also. WHERE userid=$userid LIMIT 1
is bad coding practice, omitting quotes can lead to SQL injection. And why LIMIT 1? Is it using a unique user ID?
There are many sensible solutions to separate the database layer, such as data access objects, object-relational mapping, active records ...
a source to share