Categories

Transaction using PDO in PHP / MySQL

PHP logo

I am trying to leave the basic mysql PHP behind in favour of msqli. I needed to add a record to a table with AUTO_INCREMENT on, and then add a record to a second table using the AUTO_INCREMENT id from the insert on the first table. To do this safely, I needed a [...]

Find record with the nth highest value in a specific field

MySQL logo

As part of a skills test for an interview, I was given a basic table of records, in this case salary information, and asked to write a query to return the nth highest salary. I have never had to solve this particular problem before as such, but I kicked myself when I found a simple [...]

stripslashes() and magic_quotes

PHP logo

I had some shady characters hanging around my database at work yesterday…

So here’s the scenario:

I grab a heap of data in the form of comma delimited strings from and Interbase database on another server and then insert them as records in various MySQL tables on my reporting server. From there I do all kinds of [...]

MySQL sub-query

MySQL logo

I have blogged before about simplicity in code – how the simplest solutions are usually the best, and I normally put it down to [...]

MySQL simplicity

MySQL logo

I love the fact that the best solutions are almost always the simplest!

I spend a lot of time sanitising data. I use Excel a lot of the time to perform most of the work – but I am slowly starting to use more and more SQL in my MySQL databases to do the work.

Today after [...]

MySQL TRIGGER

MySQL logo

I battled for ages to find the correct syntax to create a trigger in MySQL — the examples I could find were all to complex and I could not get any of them to work. Here is a quick little example that you should find really easy to [...]

PHP pagination: LIMIT in MySQL vs. FIRST / SKIP in FireBird

Firebird logo

I got stuck writing a query for Firebird today. And here I was thinking that SQL was a standard — if only the “S” was for “Standard”…

I was busy writing scripts at work to paginate results in our BI portals. In MySQL I would usually write something like this to return the first 20 records:

SELECT * FROM [...]

CSV to MySQL conversion script

PHP logo

A handy script to quickly generate MySQL INSERT statements from CSV data. This script will create SQL INSERT statements from your pasted CSV data. Enter the name of the table (which you have already created), and then paste the data into the main input [...]