Tag: mysql tips

MySQL DATE_FORMAT: Grouping Date & Time value fields into single days made easy

09/24/2015 Aaron Belchamber No Comments

To conform a datetime field to just a date in your results and make it easy to group TIMESTAMP fields in the format “2015-12-25 12:23:23” so you can group any datetime value into actual days for daily reports, use “DATE_FORMAT” below:

DATE_FORMAT(datetime_field, '%Y-%m-%d')

In other words, if you have values from a SELECT statement that are date AND time stamps and you want to group all those values into the DAY, DATE_FORMAT converts the value for you.

So, with these values in the field “created_at”:

2015-03-01 11:11:11
2015-03-01 16:13:132015-03-01 19:01:01
2015-03-03 11:19:23

The following MySQL SELECT statement

SELECT DATE_FORMAT(created_at,'%Y-%m-%d') as created_at_days FROM your_table GROUP BY created_at_days

Will result in the following results:
2015-03-01
2015-03-03


Planning to design or redesign an organization’s database? Get back to basics and refer to MySQL’s reserved keyword list

06/16/2015 Aaron Belchamber No Comments

Oracle’s MySQL is a staple in the open source web environment world.  Being familiar with the reserved words the database uses is just one way to make sure you don’t have to refactor code or special-escape field names in queries later with the menacing back tick: `

Here is the link to MySQL’s reserved keyword’s list.  The MySQL keywords table is very useful to use in order to avoid collisions up front when designing new databases and troubleshooting possible problems of legacy systems.

I design, and redesign a lot of databases for different organizations and companies.  Planning and designing a database will require naming tables and fields logically so a successor can navigate the data and coders can troubleshoot problems without reinventing the wheel — it makes the system’s design an efficient part of the overall system of the organization.  This leads to less time wasted, more stable systems, and more up-time later.  That’s why when it comes to web development and software programming I’m a Business Systems Architect first– then many other job titles second.

– Aaron Belchamber


Accessing your Virtualmin MySQL database from a local dev tool requires addition to “remote hosts” list

06/10/2015 Aaron Belchamber No Comments

Keep your dev servers separate from your workstation

Setting up a development server on your local network separate from the machine you write and crunch code is a great way to keep the machine you rely on to operate as fast as possible.  I never try to install any virtual machine on the same machine I need to write code and design databases on, even a quad core with 8 GBs of RAM will slow down pretty quickly.

Virtualbox, Virtualmin and MySQL database server

So, after installing Virtualbox on another PC, usually the most recently replaced PC that used to be my main computer, I prefer instead of selling it to use it as another dev server on my network.  I prefer to install Virtualmin and use that to help speed up the process of deploying another dev server in minutes.  Obviously, the “M” in L.A.M.P. is short for MySQL, and without a local database to interact with, you are sure to get frustrated with the time lags involved in working with a remote database server.  This is a “dev” environment, after all, so you need every advantage to keep things running fast.  Dev is usually 80% of all the work you put into a new site and system.

Virtualmin allows you to install a new database server, Git repositories, and a myriad of other modules that will help you duplicate your existing host environment.  Of course, you can also open this server to the outside world if you have static IP as well.  Virtualmin allows easy deployment of new servers quickly and reliably.

Failure to connect to MySQL database

Once you set everything up in Virtualmin and can access your site, you will be tempted to go and establish a connection to the site’s database and start devving.  You will likely run into an error message saying your connection isn’t allowed.  This is what happens when I try to connect to the site’s MySQL server directly to its IP through MySQL Workbench:

Failure to connect through MySQL Workbench to local Virtualmin database

You did everything right, you set up your database and user privileges, so what gives?!  Well, Virtualmin can get a little confusing, so go back to your Virtualmin console then click “Edit Database” then go to the “Remote hosts” tab.  Here, you need to put in your IP address, if you’re not DHCP on your local network and that’s where you need to access your database from, it’s best to type in a wildcard of your network, which for me and my local network IPs are in the range from 192.168.1.1 – 192.168.1.255.  By entering the wildcard “192.168.1.%” this will allow any connection within the 192.168.1  IP range to access your database.

Virtualmin - open local IP to connect to your dev server databaseYou won’t have to restart MySQL, just go back to MySQL Workbench or whatever tool you’re using to access your database and see if it works now:

MySQL Workbench Virtualmin Database success screenshotSo, when you think you know where the proper settings are, in Virtualmin, this is something that often gets overlooked.


MySQL: Using sub-selects to filter results of one table that is not contained in another table

03/16/2015 Aaron Belchamber No Comments

In MySQL, if you have two tables with a shared index and you would like to show the results of one table where the index is not within another table, you can use “NOT IN”.

SELECT * FROM table2 WHERE some_field not in 
    (SELECT some_field FROM table1)

Try Googling “MySQL NOT IN” and you get everything but this command, it’s almost as if they need to have an alias for this simple command since these two words are in EVERYTHING and on so many web pages. Perhaps “NOTCONTAINEDIN” would be better, all in one word? Just a suggestion.

In the sub-select, the query inside parenthesis, you can take this a step further and apply conditions to the filter so you can exclude results in “table2” more narrowly than just being in “table1”, perhaps if “table1” had a status of “EXPIRED” in a field, then you could add that criteria to be more selective.


MySQL: Exclude ‘group by’ group if field A in any row has value B with “Natural Join”

12/21/2014 Aaron Belchamber No Comments

Querying databases get a little tricky when you have a stack of records associated with another table, perhaps a master table of accounts, and you’re trying to filter out values that would exclude the results from another group of results.
In this example below, we are getting a list of organization IDs for all countries who DO NOT have any address in the country Uzbekistan. Not picking on you Uzbekistan, just using your country name as an example…

SELECT o.org_id
FROM organization o
NATURAL JOIN addresses a
WHERE NOT EXISTS 

   (SELECT * FROM addresses a1 
    WHERE o.org_id = a1.org_id AND a1.country = 'Uzbekistan')

GROUP BY o.org_id

This kind of filtering gets tricky, unless it’s clearly known the database tables are optimized, joins can bring your server down, so test it on a dev site and be prepared to stop the process on the server. It’s almost always safer to pre-qualify the list then iterate through and sort, though this way is slower, obviously, it’s not prone to slowing down your database performance.

Also, according to MySQL, there are some other basic guidelines to consider when using natural joins:

– The associated tables have one or more pairs of identically named columns with the SAME DATA TYPE.
– Do not use ON clause in a natural join.