Category: MySQL

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.


Finding missing records of the right joined table in MySQL

10/16/2014 Aaron Belchamber No Comments

Let’s say you have two database tables that are supposed to have the same record IDs. Perhaps they contain different data but share the same ID — for this example, we call it “user_id”. This query will select only the missing records of the right joined table.

SELECT a.user_id, a.other_detail, b.user_id FROM tablea a LEFT JOIN tableb b ON a.user_id=b.user_id WHERE b.user_id IS NULL

Since the “right” table is joining the “left” table with a left join, any missing records will actually be nullified since it doesn’t exist, so when you select both shared IDs from each table, “a.user_id” will still contain the number. It’s a pretty nifty query that has saved me time iterating through values and looping through with a script. This is a good example to reference in the future.


Copying MySQL databases at the command line is fast and easy

09/12/2014 Aaron Belchamber No Comments

Copying your MySQL databases from the command line, if you have privileges is easy, a lot easier than going into PHP MyAdmin and hoping the “Copy Database” function is available and it doesn’t time out…

Here’s how you can copy databases to a remote server:


mysqldump your_db_name  | ssh host2 "mysql your_db_copy"

Here how you copy MySQL database to a local server:


mysqldump your_db_name | mysql your_db_copy

PHP Developers: MySQLi & PDO forcing errors helpful for debugging queries when accessing private or protected isolated classes

09/02/2014 Aaron Belchamber No Comments

Sometimes you don’t have much control over other people’s code but your code must interact with it.  That’s one of the issues you will encounter when you work in a collaborative coding environment and you’re all developing code the proper way — there’s encapsulation so you know your code is isolated to only what you need to affect and others’ code stays within their boundaries.  You may find that you need more verbosity and details in the errors generated by other classes or libraries, perhaps you need to check the type of MySQL/SQL query errors that are being generated.

It may seem a little old school, this method has been around in PHP for 8 years, but it’s still useful today. Overriding the error handling will help you debug faster and that’s a good thing when you’re in the development stage. This is a handy way to set the error reporting and throw database errors to your PHP error handler instead of sending queries blindly to a class that may not report exceptions and since you might not be able to “catch” those errors, you will have a harder time finding the error logs, if you even have access to debug since the classes you are accessing may sometimes be closed (private or protected) from your code.  This is helpful when you use MYSQLi and need verbose errors:

mysqli_report(MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_STRICT);

MySQL PDO can be set similarly with the “setAttribute()” method, you can find out more at http://php.net/manual/en/pdo.setattribute.php.

 

– Aaron Belchamber
www.AaronBelchamber.com


Mysql PDO standard insert (persist) of data to a table with placeholders to protect from MySQL injection attacks

07/02/2014 Aaron Belchamber No Comments

This is a much more secure way to insert records and prevent MySQL injection.  This assumes that $this-conn is your MySQLi PDO database connection object that you’ve already initialized:



Please note that a lot of the PHP snippets on this site assume you have some level of PHP experience.  I use this site as a repository of many frequent code snippets and things I find I look up a lot as a central way to reference materials.  Feel free to use and share as you see fit!
- Aaron Belchamber