MySQL – Bradlis7.com

I’ve found that MySQL’s searching implementations don’t quite work for a smaller database. When I try to use fulltext searching on waggonersboots.com, it removes “Wolverine” because 55 of the 85 products in our database are Wolverine. So I think I’m going to have to come up with a custom search.

I’ve searched and searched, and when on a shared server (I don’t think sphinx will do it) there’s really not a lot that I found that’s very helpful. I’m probably going to have to use indexing, instead of trying to use fancy SQL queries. It’s probably going to take around three days of coding, scrapping, and more coding, until I come up with something that is both fast and effective. It probably would have helped had I stuck with a computer science major.

I think I can finish the majority of my tasks on the site within a couple of days, or at least the ones I intend to do before it goes live. If I ever get done, I’m not going to know what to do with myself. Staying busy has the upside of cutting boredom, but it also has a way of getting in the way of homework (not that I do it anyways). I may be spending a lot of time on my Wii.

I’ve been coding lots and lots for many hours, but I can finally see the end for Waggonersboots.com version 2.0. I may have to come up with a new front-end to signify a change, but right now, it’s pretty similar in style. The backend is WAY more extensible though. With logging for errors and data changes, and a pretty good seperation of logic and presentation, I think it’s going to be a lot better. I hope to get some page editing in place for the admin, and better integration with Paypal and Google Checkout.

I can’t quite what I should do with data consistency. If we want to remove a product from the website, I would normally just delete it out of the database, but that creates some inconsistency, such as, if we wanted to look at selling history. I’ll probably continue to delete them, to remove the extra load on the database for indexing, etc, but I’m not sure what the rules are.

At my current job, I do a lot of reading. It’s hard to get used to, because the only jobs I’ve had before, I was expected to be, or at least look, busy. In some ways it’s nice, but it gets to be a little dull. I’ve been reading over MySQL, and even though I haven’t done much with it at work yet, I’ve implemented a lot of it into the WB‘s code. Here’s one that I came up with today:

SELECT name, COUNT(DISTINCT value) FROM variation
GROUP BY name ORDER BY 2

It’s pretty simple to most with SQL experience, but came as a revelation when trying to do different hacks in PHP. This query gets the types of variations (e.g. Size, Color) in order by the fewest distinctions. It’s useful for making a list such as (Colors are always top-level in this case):

Brown
- 32
- 34
- 36
Black
- 34
- 38
- 40

I then take a PHP function and sort it like this, for use in usort():

public function item_cmp(&$a, &$b) {
  $astr = $bstr = '';
  $vartns = Variation::get_variations();
  foreach($vartns as $v) {
    if(isset($a->variations[$v])) { // zero-pad with zeros to three digits
      $astr .= sprintf('%03s',$a->variations[$v]->weight);
    } else $astr .= '999'; // Make null values float to bottom
      if(isset($b->variations[$v])) {
        $bstr .= sprintf('%03s',$b->variations[$v]->weight);
      } else
        $bstr .= '999';
      }
  }
  return strcmp($astr, $bstr); // Compare the two as strings
  // (digits would probably work too, but strcmp was easiest)
}

Rather than hardcoding it, I thought it best to make it smart. I’ve implemented the site in PHP5, and I’m crossing my fingers that the shift from MySQL 5 to MySQL 4.1 won’t cause too many problems.