so some of you asked me to post more database and php stuff on here, so here is the first official database post. i’ll start off simple for now and then we’ll work our way up to more complicated stuff such as mysql injections and security, spoofing, rainbow tables and breaking security and even touch on how your syntax can be more secure if it was clean and tidy.
one mysql tip that is worth pointing out is that you should insert or update your value in one query. the idea is that you can update a record if it already exists. if it doesn’t exist, the record will be inserted instead. this is especially handy where APIs in the web 2.0 world return a value after a short delay.
here is the brute force approach to this problem. what is happening is that the database is being checked to see if the record exists and acts accordingly.
$sql = 'SELECT something FROM somewhere WHERE something = 123'; $rs = $db->query($sql); if(mysql_num_rows($rs) == 1) { // update sql } else { // insert sql }
while this gets the job done, it would be nicer if we could work it all into one query. to accomplish this, we can use the “on duplicate key” keyword.
INSERT INTO somewhere(something) VALUES(123, 'Lorem Ipsum', 20) ON DUPLICATE KEY UPDATE Status = 'Done'
if the transaction exists, this query will just change the status from the old value to your new “done” value. otherwise, it will insert a new record. we can remove the hardcoded variables in the example and replace them with dynamic ones returned from the API, should you come across a project that requires that.
Related posts: