Monday, January 26, 2015

Yii and complex MySQL comparisons for search and sort functions

One of the things I recently ran into was wanting to sort and filter by a calculated result. Say, for example, you wanted to store total capacity of hard drives (GB) on several systems but wanted to sort by percentage of the drive that used.

Well, you're also going to need to know the capacity of those drives, so you'll need that field as well. However, let's suppose you don't want to store the %full / %empty values and would rather calculate those as needed.

At the beginning of our model, we need to add a variable declaration.

public $fill_pct;

In the CDbCriteria section of the sort function within the model, we're going to add the following:

$criteria->compare('(t.used / t.capacity * 100)', $this->fill_pct);

Then, in the CGridview, we can add something like this:

    'name' => 'fill_pct',
    'value' => '@(sprintf(%d", $data->used / $data->capacity * 100))',

Now, we can filter things like >70 or <20 all.="" are="" aren="" as="" at="" but="" close="" find="" full="" getting="" great="" need="" now="" or="" p="" sort="" t="" that="" those="" to="" we="" well.="">
In the CActiveDataProvider (again in the model), we can add the following:

'sort' => array(
    'defaultOrder' => 'id ASC',
    'attributes' => array(
        'fill_pct' => array(
            'asc' => '(t.used / t.capacity)',
            'desc' => '(t.used / t.capacity) DESC'

The default order can be adjusted to whatever you like (perhaps the same as fill_pct desc), and now you can sort by the % used. I didn't have to multiply by 100 here because that doesn't affect the order.

The more I use Yii, the more I enjoy how flexible it is.

Thursday, May 15, 2014

Woocommerce not creating thumbnails

While working on a site that's not my typical day-to-day site, I came across an issue where Woocommerce on WordPress wasn't creating thumbnails. Instead, it was using the large image and just using CSS to resize them.

Normally, I wouldn't have noticed such a thing, but it was hosted over a DSL connection so large images would load visibly slow.

Knowing that the code worked fine on our development environment, it seemed it had to be something with the server. After checking permissions on all the folders where generated images go, I started going through the code to generate images. Even though the "Regenerate Thumbnails" extension said everything worked, I had my doubts.

As it turned out, my doubts were well founded. We needed to install php-gd on the server to make it work. Now, when it is supposed to use a thumbnail, it uses a thumbnail.

Sometimes it's the simple things that make me smile at the end of the day.

Friday, October 11, 2013

New toys...

Unlike some of my prior posts showing expensive hardware that I got to play with, this time it is much more meager. I got a Raspberry Pi.

This time, it really is a toy.

I got one of the starter kits with an 8GB SD pre-loaded with several linux distros to try out and the clear case. If it works out, one more of those and I can put one in each of my sons' rooms. Very basic, yes... but that's exactly why I'd consider it for them. Hard to get in trouble when it doesn't get infected like windows and doesn't have TB of storage.

So far, I'm pretty impressed with what it has to offer in a package smaller than many computer mice. I'm sure I'll be posting more about it soon.

Thursday, August 15, 2013

Yii multiple select dropdownlist with default values

So I was coding a form in Yii, and came across a situation where I had a value of 0111110 (no, yes, yes, yes, yes, yes, no) that I needed to pre-populate values in a multiple select drop down. This didn't seem like it should be that hard, but the Yii documentation lacked a bit there.

The first step was to split out the string.

$optionValues = str_split($model->variable);

Now, we create a blank array to hold our options.

$options = array();

Then, we go through those options and set selected on the ones that need selected.

foreach ($optionValues as $optionKey=>$optionVal) {
     if ($optionVal) {
        $options[$optionKey] = array('selected' => 'selected');

Now, we render the drop down list.

echo $form->dropDownList($model, 'variable', array('0' => 'Zero', '1' => 'One','2'=>'Two','3' => 'Three', '4' => 'Four', '5' => 'Five', '6' => 'Six'), 'options' => $options));

That pre-selects any that are supposed to be selected. Job done. Thanks, Yii.

Thursday, June 20, 2013

Get "Null or Value" on related model in Yii

I have two tables, one with bases and one with transmitters. Bases work without transmitters, but transmitters don't work without bases. This means a HAS_MANY / BELONGS_TO relationship works out well in most cases.

I recently ran into a two-fold problem.

1) I wasn't getting the null values from the related table when filtering on !=7.

2) If I added an OR statement, it ran really... really... really slow.

The second was easily fixed by adding an index containing the joining field and the filtering field, but that still left the OR, which wasn't real efficient.

The solution was coalesce in my search() function.

$criteria->addCondition('coalesce(transmitters.field,0) != 7');

transmitters was the related table I was filtering on, and field was the field. Basically, anything that's null becomes a 0, so it's not a 7 (and not a null), so it shows up.

Nothing too difficult this time, but hopefully it can save someone (probably me in a few weeks) some time digging around for an answer.

Friday, May 10, 2013

MySQL Query Times with Varying Batch Insert Sizes

I'm always curious how different things affect performance, so when I had to import 775 company records with around 30 fields, 26,681 base unit records with almost 90 fields and 30,197 transmitter records with over 100 fields into a MySQL database, it became time to do some testing.

Initially, the process was taking a few seconds, but that was only a subset of the data. Once I got this larger set of data, it was taking 18:49 to import it, which wasn't acceptable. So, time to leave the straight "insert into ___ set ___=___" method that is so much more readable and move to "insert into ___ values ( __ , __ )" style.

The first thing I did in testing was to disable indexes, insert, then enable indexes again. This brought the time down to 9:27, which is a big improvement, but still not where I wanted to be.

Changing from set _=_ to values ( _ , _ ) made no real difference.

Then, I changed to doing multiple records in a single pass, and what a difference that made.

Records At Once Time taken

As you can see, the more records inserted at once, the faster it went. This held up even to the point of doing just 3 inserts. At some point, I'd expect a diminished return as my php script will run out of memory, but on this limited set of data, I didn't hit that point. However, even just increasing to 25 realized most of the performance increase, and even 500 was closing in on the limit of the gains. For memory usage reasons, I'd consider using a smaller number. However, since the plan is to truncate and replace the data, doing the all at once method is probably preferred to have minimal time without data.

Next test will be with a table with around 30 fields and millions of rows. That could provide more useful info.

Happy coding!

Monday, April 29, 2013

No longer in eCommerce

Since I'm no longer in eCommerce, I need a new blog title and about me text. Plus, since I'm developing stuff from scratch, I'm way more likely to write some code snippets on here. Time for some updates.

Suggestions anyone?

Monday, February 18, 2013

A bit of CSS coding humor

/* because ellipsis is too hard to spell */
.dotdotdot {
overflow: hidden;

Monday, December 26, 2011

Sweet new tennis product

As many of you know, I'm a huge tennis fan. I love watching Nadal or Djokovic play at their best, but even more I love to hit the courts myself.

One of the problems with playing myself is improving. I've found video analysis of matches to be helpful, but it's been difficult to get a decent camera angle for recording matches. I can either get the view from behind or from the side, which is great for stroke analysis but lacks the best view for reviewing my shot selection, court positioning and overall strategy.

However, that's all about to change. A new product, debuting next month, is going to make a good camera angle possible for the average tennis player. It's called the VolleyCam, and the first generation was just about having a tripod that would extend to 13' in the air. The second version, which is what I got to demo, is a much simpler design that hangs on a back curtain or fence and allows for easy adjustment of the camera to get the entire court into view.

I recently took it out, along with my Flip Ultra HD, to see just how well it worked. After about 30 seconds of setup, I was ready to go and play. I set both cameras to record and played a match. While the match itself isn't the important part, viewing the results is. I'm including a video I posted on YouTube here to show the differece. I think the results speak for themselves.

I believe every serious tennis player will be interested in purchasing one of these. They'll be available soon at the volley cam website.

Tuesday, November 29, 2011

Back at it

I'm back at looking into ecommerce sites.

The good news? I have found something interesting that ecommerce site owners all over are going to want to see for themselves.

The bad news? They can't see it yet as I'm still developing it.

Give me a few days and I'll start sharing some of the insights that I get as I finish this project. It should be quite revealing when people start utilizing it.

Friday, March 26, 2010

Servers again

Since people seem to enjoy seeing our hardware (server pron), I thought I'd show our current setup again. I'll start at the bottom of our rack.

This is a pic with a few things. First, it's got the white system, which is our memcache box. Just your basic setup (dual dual-core opterons, mirrored 250gig SATA drives, and 32gigs of RAM) for temporary storage of blocks of output. Above it is our Breach WebDefend appliance. I'm not sure the specs (dual quads and 4 gigs of RAM I think), but since it's sold as an appliance it doesn't really matter. Last in this pic are 3 APC power management boxes. Since quite a few of our devices only have one power supply, this allows us to plug them all in to both power sources in case of an outage.

Continuing on up the line, we have one of our oldest servers. We call it www1, although being a www server isn't something it does any more. It basically runs cron jobs on its dual xeon processors with 8gigs of RAM and mirrored 80gig SATA drives. The next machines are a pair of IBM boxes that used to be our database servers with dual dual-core opterons and 8 gigs of RAM with mirrored 73gig SCSI drives, but they've moved to being backup (one to be used as a second DB master and one as only a slave for doing backups.) You can also see our two load balancers, which will probably end up being retired this year in favor of newer models.

Here, you can see our Coradiant TrueSight. Again, it's sold as an appliance so I'm not real sure what the hardware is, but it sits on the network and watches traffic for anomalies in performance and user experience. Also pictured are our SMTP server and our admin server for the backend of our websites. Everything gets done there, then replicated across to the servers shown in the next picture.

These final 3 servers are the workhorses of our sites. Each one has dual six-core opterons with 32gigs of ram and 8 146gig SAS drives. The first two are used as web servers and are configured with Raid 6, the third one is our primary database server and is configured as Raid 10.

As you can see, there are some holes in our setup now. Literally, we've pulled machines out that sat between where things are now creating holes. Not a big deal, though. We'll get around to filling them sooner or later.

For those that have been asking to see the new machines, I hope you're happy. I'll be doing some reviewing of load balancers this year, so I'll share some of my thoughts on what I come across as I evaluate them. There are some neat looking devices and apps out there that I've seen already.

Need to defrag?

Somehow, it seems I need to defrag my system.

I'm not sure I had seen one this bad before. Yay, me.