Author: Aaron Belchamber

Senior Cloud Software Engineer and 25+ years experienced video production, video editing and 3D animation services for a variety of global clients including local video production here in Jacksonville, Florida.

Symfony Doctrine’s version of MySQL’s “IN” — as in “values contained in”

09/04/2014 Aaron Belchamber No Comments

MySQL’s “IN” query filter is very useful. Basically, if you have a list of record ID numbers, you can query them all neatly like so:

$query="SELECT field_name,field_name FROM table_name WHERE id IN (3,7,11,13,17)"

The containing comma-delimited list is a simple and neat query that can be assembled in your code quickly.

Duplicating MySQL’s “IN( )” in Doctrine is just as easy, you just have to give the “findBy” method an array based on the pattern:

array(field(s)=>array(id_val,id_val,id_val…) )

Here’s a basic example to help illustrate:

$resultsArr= $em->getRepository('repositoryName')->findBy(array('id' => array(1, 2, 3)));

I thought it was too easy, thanks to Stack Overflow for showing me the way!


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: Dealing with time and date/time database values set inside fields that are defined as text, strings, or mediumtext

08/29/2014 Aaron Belchamber No Comments

It’s happened to all of us at some point. You take over a project and some knucklehead who designed the database was too lazy to optimize it. This usually means you will end up with fields named “DateBought” and though it’s supposed to always hold a date and/or datetime value, it’s defined as some VARCHAR 255 character length field. To make matters worse, sometimes the dates are normalized so some dates are entered “2014-05-01” and other times perhaps “05/01/2014”. What do you do? First, fire the person who designed this database, or redesignate them to the graphic design position where maybe they’re better at designing layouts that won’t ruin your business systems and wreak havoc on your reporting.

Here’s a list of handy MySQL time conversions and functions I use a lot: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Until then, if you need to rescue… I mean extract the data, you have to convert the text values of the funky fields into time values that are quantifiable. “STR_TO_DATE” is usually the answer to this kind of problem:

STR_TO_DATE(transdate, '%Y-%m-%d' ) >= '2014-05-01'

Changing MySQL “INSERT INTO” into “INSERT IGNORE INTO” can solve a lot of headaches when batch importing a lot of records

08/28/2014 Aaron Belchamber No Comments

Inserting a batch of records into MySQL will stop on the first instance the record already exists with a matching PRIMARY KEY. If you’re at risk of this happening, open the .sql file and use your text editor to search for “INSERT INTO” and replace it with “INSERT IGNORE INTO” and this will skip any existing records with the same primary key value without stopping the batch process.

INSERT IGNORE INTO

Simple, yes? But so many people seem to search for this solution. I was aware of it but seldom use it so I had to look it up, that’s why I put it in the MySQL section here on http://tools.belchamber.us so it’s easy to find and be reminded of this solution.


Grabbing Symfony TWIG template output as a string to use in string-based HTML bodies such as email templates, etc.

08/27/2014 Aaron Belchamber No Comments

Amazing, but this solution is so simple and it is no where on Stack Overflow. There’s a lot more documentation about rendering templates from a string but not TO a string, something very helpful if you want to use templating to build html output for things like email templates.

$output=$this->render('view.html.twig', array(
  'form' => $form->createView(),
  'info' => $info,
  ‘otherVals’ => $otherVals,
  ‘someArray’=> $someArray
));

var_dump($output->getContent()); exit;