SQL Articles

Stop Accommodating Shortcomings

I came to a very liberating conclusion the other day. I need to stop fucking around with MySQL, or any other RDBMS, and fully embrace a NoSQL server as my primary data store. I’ve been a huge fan of Redis for quite some time but the fact that everything lives […]

How to Setup a LEMP Stack on Ubuntu 14.04 LTS

Last week I covered setting up a LAMP server on Ubuntu 14.04 LTS but for my money, it’s all about LEMP stacks. The “E” actually stands for Nginx (pronouced Engine X) and it’s an altnerative to the Apache web server that is built for speed and has a very low […]

How to Setup a LAMP Stack on Ubuntu 14.04 LTS

The newest long term support release of Ubuntu (Trusty Tahr) is finally here! I just spun up a droplet over on DigitalOcean to walk through setting up a LAMP stack for this post. I also recommend making sure that your system is completely up to date. At the time of […]

Reducing Redis used memory by moving back to MySQL

If you know me then you know I fucking adore Redis. Over the last few years it’s slowly worked it’s way into the majority of the things I have built. Unfortunately, recently I ran into a problem that Redis could easily accomplish but would require more RAM than I have […]

Transactions with PHP Data Objects (PDO)

PHP Data Objects (PDO) is arguably the best database abstraction layer available in PHP. It provides a consistent interface across multiple datasources (MySQL, PostgreSQL, et cetera) and helps boost your site’s security by way of prepared statements. It even supports transactions which we’ll be taking a look at here. Database […]

How to set up a LAMP server on Ubuntu 13.10

Been a while since I’ve covered setting up a brand new LAMP server. This time, I’m going to be using Ubuntu 13.10 and once you are logged into your server you will want to update and upgrade it to the latest software versions: sudo apt-get update && apt-get upgrade Once […]

Using Transactions with PHP Data Objects (PDO)

With PHP Data Objects, transactions are pretty simple. To be able to use a transaction, you simply wrap your queries with a call to beginTransaction() and either use commit() or rollBack() at the end. Rolling back is at your discretion but the easiest implementation would be to wrap the queries […]

How to Use Prepared Statements in PHP

Now that we know how to create a connection to our database using PHP Data Objects, let’s talk a bit about prepared statements. Now I’d like to think that we’ve all made this mistake before, taken user input and used it in an SQL query something like this: $db->execute('SELECT * […]

How to Connect to a Database with PHP Data Objects (PDO)

PHP Data Objects (PDO) provide a consistent interface to different databases including MySQL, PostgreSQL, SQLite, SQL Server and a handful of other systems as well. Aside from database-specific SQL, the only other thing that isn’t consistent is how to connect to a database. Each database type requires a string to […]

MySQL and Redis Command Equivalents

Score another one for HitTail as this particular topic was their suggestion. MySQL (PostgreSQL, SQL Server, SQLite or any other RDBMS I didn’t list) and Redis are completely different beasts in regard to syntax and especially schema design (or lack there of). The following are just some common SQL statements […]

How to Install the SQLite Module on Ubuntu 12.04 LTS

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. The SQLite website also boasts that it is the most widely deployed SQL database engine in the world. As I’ve previously discussed, SQLite has the advantage over MySQL as it can be used as an internal session handler with PHP. […]

SQLite as a PHP Session Handler

Now that I’ve discussed the in-memory data stores (NoSQL if you will) let’s switch gears to a more traditional RDBMS. SQLite by definition is a software library that implement a self-contained server-less, zero-configuration, transactional SQL database engine. It’s more important claim to fame is that it is the most widely […]

PHP Session Handling

Sessions, one of those necessary evils when building websites. They come into play whenever you need to have data available between pages on a site. These scenarios typically arise when you have login restricted areas on a site. Why do I refer to them as evil? Quite a few reasons […]

How to Setup a LEMP Stack on Ubuntu 12.04 LTS

Don’t get me wrong, there’s nothing wrong with a good ol’ LAMP stack (Linux, Apache, MySQL and PHP) but in my experience, Apache doesn’t necessarily scale all that well with PHP. It’s really not Apache’s fault, the fact is that most of the time servers are configured to use the […]

How to Setup a LAMP Stack on Ubuntu 12.04 LTS

It seemed fitting that the first post on PHP Avenue should be about setting up a new server with PHP. This particular post will focus on a traditional LAMP stack (Linux, Apache, MySQL, PHP) on Ubuntu 12.04 LTS. I also intend on writing additional posts covering the setup of a […]

Going Vanilla with MySQL

Over the last few years I’ve gotten down and dirty with MySQL’s config file, my.cnf. I read and re-read the MySQL documentation as well as any blog posts I could find on the subject. I was able to keep my connections low with caching and fought against the InnoDB buffer […]

Drain your InnoDB Buffer Pool by moving large fields out of MySQL

This was supposed to be a “SceneKids.com the first year” post, but nothing has really changed too much since the 8 month post with the exception of expanding out with additional websites on the same platform. Instead, I’m going to discuss a dilemma I ran into with MySQL’s InnoDB data […]

Setting up a secure MySQL server on Rackspace Cloud Servers

Recently I’ve been building out dedicated MySQL servers to combat some of the shortcomings we have encountered with hosting MySQL on Rackspace Cloud Sites (commentary on this will be saved for another post). During my research on what I could do to set up the server as securely as possible […]

Using SQL’s IN syntax to search multiple fields

This may not be something new for anyone, but figured I’d post it anyway. I needed to check for a certain date in multiple fields, instead of listing each one out, I used IN but used the value on the left side, and listed the fields in the parenthesis. Before […]

Issue with SQLyog Job Agent on Ubuntu

No error message, it just says that the sync had started. What sucks about this issue, is that I ran into it with a previous version of SQLyog Job Agent (sja) but was able to troubleshoot it faster because the program would dump an error message. I have been messing […]