Categories
MySQL

ODBC MySQL Data connection in Excel

After swatting up on connecting QlikView to a MySQL database, I used the knowledge gained to create a similar set-up, this time with Microsoft Excel. This is how to dynamically connect to and query a MySQL database table directly from MS Excel on a local development machine. I did this using Microsoft Office Professional 2013. Download […]

Categories
PHP

PHP / MySQL: storing database credentials in an .ini file

It is recommended that you do not include database connection details in your scripts, so where exactly do you store them? Luckily, PHP has the handy function parse_ini_file() specifically for this task. From php.net: parse_ini_file ( string $filename [, bool $process_sections = false [, int $scanner_mode = INI_SCANNER_NORMAL ]] ) The $process_sections parameter allows you to […]

Categories
MySQL

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

I got stuck writing a query for Firebird today. And here I was thinking that SQL was a standard — if only the “S” were 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: […]

Categories
MySQL

MySQL TRIGGER

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

Categories
MySQL

MySQL simplicity

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 […]

Categories
MySQL

Restrict running of a MySQL Stored Procedure by time

I am creating an Excel Report that queries a MySQL database using an ODBC connection. The data is the returned result of a Stored Procedure which contains some pretty slow, resource intensive queries on some massive tables belonging to a VICIDIAL installation. I need the data to be as fresh as possible — but not […]

Categories
QlikView

QlikView ODBC Connection

I am in the process of learning about QlikView, an awesome data visualisation application. It has the ability to connect to diverse data sources: anything from a CSV file to a database table. This is how to connect QlikView to a MySQL database using a ODBC on a local dev machine. An ODBC connector works […]

Categories
MySQL

Storing IP addresses in MySQL

I have always stored IP addresses as strings in VARCHAR fields in MySQL. I am currently reading “High Performance MySQL” (O’Reilly) and it mentions the advantages of storing IP addresses as integers in an INT field rather than as a string in a VARCHAR or CHAR field. The MySQL docs specify:  “To store values generated […]

Categories
MySQL

Complex SQL query using SUM and GROUP BY

Got this question as part of a skills evaluation test. It requires a query to calculate summary-like data from tables in an order system type database. There is an `orders` table that consists of an order_no and customer_id fields. Also, an `order_items` table that consists of order_item_id, order_id, product_id, qty, unit_price and total. unit_price is […]