Besides the small security increase by using limit (destructive queries can only affect x rows), there are some performance benefits as well. Consider the following queries: select field1, field2 from Product where ProductID = 1; update User set Firstname = ‘jim’ where UserID = 3; MySQL will update the row that matches the ID, but [...]
I’ve been writing an n-tier PHP web application, and I’ve been working on the installer. As part of it, I needed to create stored procedures from PHP. I hit a snag, which was the following: $sql = “delimiter $$ drop procedure if exists spsTableDelete $$ create procedure spsTableDelete(mySession nvarchar(255),myTableID bigint unsigned) begin update sTable set [...]
I have been writing a special web application framework around the n-tier pattern, and in the database layer I ran into a problem with MySQL. Within a stored procedure you can order by a variable but not limit by a variable, which puts a small damper on pagination. There are many threads on the Internet [...]
I spoke to a friend recently who had a nightmare story in osCommerce, where when a new customer signs up, another customers details appeared in their address book. What had happened was the store was copied from another live store, and had the database erased, however entries in the address book table were left. As [...]