Flat File Databases What are your best practices around creating flat file database structures in PHP? A lot of the more mature PHP flat file frameworks I see out there attempt to implement SQL-like query syntax, which is over the top for my purposes in most cases (I would just use a database at that point). Are there any elegant tricks out there to get good performance and features with the small code overhead one would want by taking on this problem in

How does database indexing work? Given that indexing is so important as your data set increases in size, can someone explain how does indexing works at a database agnostic level? For information on queries to index a field, check out How do I index a database column. Solution Why is it needed? When data is stored on disk based storage devices, it is stored as blocks of data. These blocks are accessed in their entirety, making them the atomic disk access operation. Disk blocks

How do I split a string so I can access item x? Using SQL Server 2005, how do I split a string so I can access item x? For example Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"? Solution: You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project). You can use this simple logic: Declare @products varchar(200) = '1|20|3|343|44|6|8765' Declare @individual varchar(20) =

How to select the nth row in a SQL database table? I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server MySQL PostgreSQL SQLite Oracle I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches: WITH Ordered AS ( SELECT ROW_NUMBER() OVER

SQL Group By with an Order By I have a table of tags and want to get the highest count tags from the list. Sample data looks like this id (1) tag ('night') id (2) tag ('awesome') id (3) tag ('night') using SELECT COUNT(*), `Tag` from `images-tags` GROUP BY `Tag` gets me back the data I'm looking for perfectly. However, I would like to organize it, so that the highest tag counts are first, and limit it to only send me the first

How can I prevent SQL injection in PHP? If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's because the user can input something like value'); DROP TABLE table;--, and the query becomes: INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--') What can be done to prevent this from happening? Answer: Use prepared statements and parameterized queries. These are SQL statements that