While researching the most performant way to delete a high volume of rows in MySQL, I happened upon some syntax that I had never heard of before. This syntax allows you to run a query on a schedule, without the need of any external code. As a rule of thumb,
[…]
Universally unique identifiers (UUID) are one of my favorite things. They are easy to generate, without collision, and they make it easy to expose an identifier to end users without it being so obviously guessable, like an automatically incrementing integer value. While they are fantastic, the ability to generate one
[…]
Variables. One of the cornerstones of most, if not all, programming languages. They save you time by not having to type the same crap over and over. They can be manipulated for your own gain. They can even be used in your MySQL queries. The declaration syntax for variables in
[…]
By default, new columns are added to the end of a table in MySQL. This works well most of the time, but sometimes when you’re retrofitting a column into a table, you may want it to be adjacent columns closer to the start of the table. The ALTER statement makes
[…]
Coming from MySQL / MariaDB, I was surprised that I couldn’t kill a query on an Amazon’s Relational Database Service or Amazon Aurora with the kill command. Fortunately, the functionality isn’t omitted, it’ just buried under some AWS RDS specific command. Before we go over that, let’s go over how
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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 *
[…]
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
[…]
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
[…]
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.
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]
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
[…]