MainelyDesign.com Blog

Database Tagged Blog Posts

Speeding Up Windows 8 for Web Development (using WAMP)

Posted on 02/25/2013 at 01:33 pm | Viewed 48,189 times | 0 comments

Here is my list of optimizations and changes you can make (and I've had to make) in order to get WAMP (and CakePHP) to run faster on my Windows 8 computer.

Speed Up WAMP Web Development on Windows 8

The short list, in order of importance (stop once you are happy with your localhost web server response time and speed):

  • Get the latest version of WAMP.
    Start by downloading the latest wamp.  I used the 64 bit version.  Download WAMP here.

  • Edit your hosts file.
    Make sure it has the proper entries, and most importantly, remove the IPv6 localhost setting.  Your Windows 8 hosts file should look like this:

    127.0.0.1          localhost
    #    ::1             localhost

  • Disable IP v6
    A great tutorial can be found here.  My advice is to disable IPv6 BOTH ways- via the network adapter and via the regedit hack.

  • Edit Apahce httpd.conf
    These tweaks are supposed to speed up the lstat() system calls when reading files/scanning directories.  They are:

    EnableMMAP On
    EnableSendfile On

  • Edit PHP.ini
    Increase the real path cache size.  This helps when you have lots of files involved.

    ; Determines the size of the realpath cache to be used by PHP. This value should
    ; be increased on systems where PHP opens many files to reflect the quantity of
    ; the file operations performed.
    ; http://php.net/realpath-cache-size
    realpath_cache_size = 24M

  • CakePHP: make sure debug is off.

Forcing A Single Join in CakePHP Pagination

Posted on 10/14/2011 at 09:25 am | Viewed 22,130 times | 0 comments

The devil's in the details... I was trying to make a really simple, dreadfully easy, database join in my CakePHP web application.  I've forced joins in Cake before, using the 'joins' key in the options array for find calls and paginate calls with no issue.  It was late and for the first time I only wanted to use a single join.  I copied the join code from a much more complex web app and pasted it into my new 'joins' conditions.  And then... I got SQL errors. 

Inserting NOW() into MySQL Using CakePHP

Posted on 08/11/2011 at 11:16 pm | Viewed 14,413 times | 0 comments

Great find.  If you want to insert NOW() into your query using CakePHP's save() or saveAll() functions, you can use the following expression:

$this->data['Model']['custom_date_field'] = DboSource::expression('NOW()');

Not sure how it works on MSSQL, but I will be testing that soon.

Cheers,
-Kevin Wentworth

Best PaginateCount for CakePHP - with Group By Support

Posted on 08/07/2011 at 05:03 pm | Viewed 19,546 times | 0 comments

I'm been muddling my way through pagination with multiple joins, and complex filtering.  I finally got it all working (more on that later, maybe....) when I noticed that I couldn't paginate my results.  Everything was working fine until I added a "group" parameter to the find call.  Instead of getting the right count, I got a count of 1!  A quick look at my sql log tables (at the bottom of the page) and I saw that I had actually returned all the mysql records that were needed!  If only there was a way to use them...

Changing CakePHP's Model useTable on the Fly

Posted on 11/02/2010 at 05:12 pm | Viewed 18,178 times | 0 comments

I finally found an answer to/solution to something I've never been able to get around in CakePHP before: dynamically changing/setting the useTable value in a model.  You can't just say $this->CakePHPModel->useTable = 'cakephp_table_name';  Instead you have to use the following function:

Setting CakePHP Model useTable Dynamically

Getting All ACL Permissions in One Lookup (CakePHP 1.3)

Posted on 09/19/2010 at 11:36 am | Viewed 20,709 times | 1 comment

The biggest hurdle I've had to overcome migrating my CakePHP application from 1.2 to the much improved 1.3 branch involves Plugins and the ACL component.  A while back I noticed that my backend was kind of sluggish due to all of the ACL lookups (that happen with each request) so I optimized my ACL database tables.  At that same time, I also discovered an incredible mysql query that would lookup all of the ACL permissions for a particular ARO.  This setup worked great... until I started upgrading to CakePHP 1.3. 

Global Mysql Find and Replace with CakePHP

Posted on 08/31/2010 at 09:03 am | Viewed 14,296 times | 0 comments

I'm moving my Content Management System over to CakePHP 1.3.  I'll post an article about my experiences later.  Today I will focus on one and only one issue with the migration from Cake 1.2 to 1.3: the change of webroot/theme/ to webroot/theme/ to serve static content.  At first I was a little disapointed/scared at the prospects of making this change, but have since come to see the absolute brilliance in this approach.

Difference Between Truncate and Empty (Delete From) in Mysql

Posted on 06/23/2010 at 02:30 pm | Viewed 32,030 times | 1 comment

I use Navicat for administering all of my MySQL databases.  It has a command that I always use- empty table.  I noticed today that all along there has been a command right below it for truncate table.  Hmmm.  I figured this would result in the same outcome and wondered what the difference is between emptying a mysql table and truncating the same table.

Speeding Up Cakephp's ACL Component

Posted on 05/28/2010 at 12:12 pm | Viewed 17,608 times | 1 comment

I came across a posting today that changed the performance of my application tremendously and reinforced a concept I had forgotten about- mysql indices. I didn't realize my app was running slow until I implemented the mysql indexes below- no formal benchmark testing, but I would say speed improved by about 300%.  The improvement was most noticeble when you are building your ACL using the CakePHP suggested method.

Best Free Mysql Zip code Database

Posted on 04/20/2010 at 04:42 pm | Viewed 19,735 times | 0 comments

I'm sure every developer reaches a point when they will need to use a mysql database of zip codes in the United States.  Say you want to make a store locator and show how many miles a customer is from your store, or if you have some custom php logic that uses zip codes, you will inevitably need a mysql database of zipcodes. 

Controlling CakePHP Error Handling When Debug = 0

Posted on 04/19/2010 at 04:27 pm | Viewed 27,517 times | 1 comment

I finally had a client request a piece of functionality that required me to program CakePHP so I could better control the default error handling when the site is in production mode (i.e. debug is set to 0).  By default, CakePHP will throw a 404 page not found header whenever ANY errors occur on a production site with debug equal to zero.  This works great, most of the time.  But what about when you are having database connectivity issues?  Say when there are too many mysql connections to that overloaded shared hosting box?  Modifed CakePHP error handling to the rescue.

Cache Results from Query in CakePHP

Posted on 03/24/2010 at 11:18 am | Viewed 14,908 times | 0 comments

I have a HUGE query that I wanted to cache.  I love CakePHP's caching functionality out of the box, but one thing is missing- you can't cache the results of a query.  Let me clarify.  You can cache the reults of a query, but only for that instance of a page load.  Using $this->cacheQueries only caches the query to memory, which only lasts for the duration of the page load.  If you want to cache query results to the file system, listen to Miles.

Getting a List of Database Tables in CakePHP

Posted on 02/23/2010 at 12:22 pm | Viewed 18,057 times | 1 comment

I ran into an interesting problem today.  What I wanted to do was know if a table didn't exist in my CakePHP application.  What happens- a cakeError is thrown everytime a database table doesn't exist.  This is really annoying...I think it would be beneficial to be able to tell Controller::loadModel() to return false instead of an error message.  That's not the case.  Here's how I got around it (with thanks to Miles Johnson):

Delete Dependent Just Deleted All My Records

Posted on 09/17/2009 at 10:12 am | Viewed 13,063 times | 0 comments

Oh man! I just deleted all my database records using $model->del($id) with dependent=>true in the model association.  That wasn't supposed to happen!  It turns out to be an issue with how I specified my model association.  What's weird is that everything works fine except when I go to do the delete with cascade set to true.

Invalid Date Value Error in MySQL

Posted on 09/09/2009 at 02:26 pm | Viewed 12,310 times | 0 comments

I ran into an interesting issue with valid dates and MySQL on Windows.  I wrote a CakePHP import function that basically moves MySQL data from one DB to the other.  I never thought that I would see an error related to an invalid date, but I did.

What determines if a date value is invalid (read: warning) or unacceptable (read: error)?

Using Between with Date Ranges in Proper DB Date Format

Posted on 07/09/2009 at 11:56 am | Viewed 17,837 times | 0 comments

I need my CakePHP apps to run on both LAMP and Windows IIS using MSSQL Server.  Usually this isn't a problem because CakePHP does such a good job of database abstraction and database independence.  However, I came across a scenario the other day where I needed to select a date range using the BETWEEN SQL command.

Using CakePHP's BETWEEN function for date ranges

Using CakePHP's Set Class to Make Select List Options

Posted on 06/19/2009 at 11:22 am | Viewed 12,891 times | 0 comments

If you haven't become familar with any of the functionality of the CakePHP's Set class, I highly recommend you take a peak.  I don't know too much about it and really only learn about it as I go.  Well, I finally found out how to do something that used to take several lines of code: creating options for a select list that includes the Model.id as the key, but has a concatenated string for the value, like 'Model.name - created by Model.userName'.

Dealing with MySQL Old-Style Passwords

Posted on 05/20/2009 at 04:37 pm | Viewed 12,273 times | 0 comments

I ran into a problem I've seen before- Client does not support authentication protocol requested by server; consider upgrading MySQL client. Last time I came across this MySQL error, it was a simple query to fix the issue (from dev.mysql.com):

UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host = 'some_host' AND User = 'some_user';
FLUSH PRIVILEGES;

Changing Display Options in Model::find('list')

Posted on 05/19/2009 at 10:49 am | Viewed 13,786 times | 0 comments

I wanted to change the displayed field that Cake automatically returns with a Model::find('list').  If you have a database column named title or name (I'm not sure of the full list), CakePHP will automatically return an array index by Model.id => Model.name.  This usually works, but today I'm working with a database table that doesn't use that field- it's a table of uploaded files and uses the columns named 'src' or 'alt' to display information to the user.  I forgot how to do this, so I'm posting it for reference.

Pagination with MSSQL in CakePHP

Posted on 05/07/2009 at 04:36 pm | Viewed 16,461 times | 0 comments

One thing, and there are a few, that I can't stand about having to use PHP and MSSQL Server together is the lack of support for pagination.  With PHP and MySQL- no problem use limit.  MSSQL- can't do it without all these ugly sub-queries.  I have to give it to the CakePHP developers though, for the built-in support for pagination in MSSQL.  Albeit it is flawed, it works.

Transactional Support in CakePHP

Posted on 04/17/2009 at 11:50 am | Viewed 14,339 times | 0 comments

I just found out that CakePHP has transactional support built-in.  It should have been obvious, seeing how the saveAll() function has transaction support built-in too.  My first attempt at using it didn't work.  In my contoller/model I was doing the following:

$this->Model->begin();
if(!$this->Model->save($this->data)) {
	$this->Model->rollback();
} else {
	$this->Model->commit();
}

saveAll() to save multiple records in 1 model

Posted on 04/17/2009 at 09:58 am | Viewed 18,522 times | 0 comments

I ran into another issue on something I thought would be simple: saving multiple records with the saveAll() command.  In case you don't know about it, saveAll() is one of the best functions in CakePHP.  It automatically supports transactions, HABTM saves, and certainly saves a lot of typing for almost every type of save you want to do.

Importing an Excel file into CakePHP

Posted on 04/16/2009 at 02:42 pm | Viewed 33,110 times | 0 comments

I needed to be able to upload an excel file, parse the file, and then add each row as a separate database entry.  I had this working for a CSV file using the php built-in function fgetcsv(), but the client switched to using an excel file (instead of direct PHP access to the in-house database, we decided to have the client upload a CSV/Excel file). 

Full Tag List

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design