Category Archives: mySQL

How-to for mySQL

Setting Up Eclipse PDT with UniServer for PHP Development (Part 2 of 2)

Eclipse PDT, HTML5, Javascript, jQuery, mySQL, PHP

Eclipse PDT is an IDE for the development of PHP projects.

Installing Eclipse PDT is not a big challenge. However, Configuring Eclipse for Windows can be entertaining (to say the least). That is what part 2 of this series covers … installation and configuration for Eclipse PDT for Windows environment.

If you have a WAMP installed on your computer, you are ready to install Eclipse PDT. If not, return to part one of this article series to install a WAMP.

Finding Eclipse PDT

To find Eclipse PDT, head on over to http://www.zend.com/en/company/community/pdt/downloads and click download.

You will be required to register with the site, to join the community of users. However, the download is free.

Installing Eclipse PDT

Installation of Eclipse PDT is not a typical Windows install. The package comes as a simple zip file. You will choose a directory you would like to use as the tool’s home, and unpack the zip file into that directory. When done, go to the directory, find zend-eclipse-php.exe and create a link to this on your desktop. This shortcut will be your access to eclipse PDT.

Configuring Eclipse PDT

Configuring can be a nuisance for Eclipse PDT, but, here we go. Follow along and you should be fine.

In preparation, you need to determine a few facts:

  • What physical directory has been defined as your “localhost” by your WAMP?
  • What sub-directory of your WAMP localhost do you want as your development area? You will put all your projects in sub-directories of the development area.
  • What would you like to name your development area? Eclipse PDT will call this your workspace. Your projects will end up as sub-directories in the workspace

Now you are ready… begin:

  1. Define your workspace – if you have not yet defined a workspace

    • Click “File” in the toolbar.
    • Hover over “Switch Workspace” and click “Other.”
    • On the switch workplace screen, select browse and browse to the location you would like to make into a workplace. You may use the browse tool to create new directories, and select that directory as the directory to switch too.

    Be certain your new workspace is in the path defined as your development server.

  2. Configure Your General Preference

    • Click “Window” in the toolbar, a menu will pop-down.
    • Click Preferences in the drop down menu and a form will open.
    • Configure for PHP
      • Click “PHP” in the left column of drop down, a submenu will appear.
      • Click on “PHP Executables,” a list may appear … it may be blank.
      • If PHP 5 is not already listed, Click on “Add…,” a form will appear.

      • Fill in the Add PHP Executable” form with

        • PHP 5
        • (browse to and select your php.exe file)
        • CGI
        • XDebug

      • Click Debug, a new configurable set will open. Configure as follows:

        • XDebug
        • Default PHP Web Server
        • PHP 5
        • UTF-8
        • UTF-8
        • will already be set correctly, no need for change
        • click if you want to stop on first line for your debug purposes.
      • Click “PHP Servers,” a new configurable set will open. Assure the default PHP Web Server is mapped to http://localhost.” if not, either edit or add that information to the list of managed servers.

  3. Define a New Empty Project

    • Click “File” in the toolbar, the menu will dropdown.
    • Hover over “New,” and click the sub-option “Local PHP Project,” a form will pop up
    • Fill in the project name, the physical address of the directory to be used for the project, select to use either Basic or Zend Framework. The path must be in your workspace, and if you do know what a Zend Framework is … select Basic. Then, click “Next,” another form will appear.
    • On the new form, the host name should be fine, but, adjust the Base Path to include your path from the “localhost,” assuring that the “Project URL” on the page is correctly defined to location your project. Then, click “Next.”
    • On the Library Configuration page, select the usual libraries you use … typically, I select (a) JavaScript Web Project Support, (b) JQuery Library and (c) ExtJS Library. Then, Click Finish. The existing directory will be set up for use with Eclipse PDT.
    • If you would like to bring in files from other directories, do not simply copy them in, Import them or Eclipse will not recognize them.

  4. Create a project from an Existing Directory

    • Copy your files into a directory you would like to define as a new project directory
    • Click “File” in the toolbar, the menu will drop down.
    • Hover over “New,” and click the sub-option “PHP Project from Existing Directory, a form will pop up
    • Fill in the projects and the physical address of the project in a subdirectory of the workspace, a from will pop up. Then, click
      “Next.”
    • The host name should be fine, but, adjust the Base Path to include your path from the “localhost,” assuring that the Project URL on the page is correctly defined to location your project. Then, click “Next.”
    • On the Library Configuration page, select the usual libraries you use … typically, I select (a) JavaScript Web Project Support, (b) JQuery Library and (c) ExtJS Library. Then, Click “Finish.” The existing directory will be set up for use with Eclipse PDT.

  5. Configure Your Properties

    • Click “Project” in the toolbar, a menu will pop-down.
    • Click “Properties” in the drop down menu and a form will open.
    • Configure for PHP debugging
      • Click “PHP Debug” in the left column of drop down, Configurables will change in the right column.

      • Configure as follows:

        • XDebug
        • Default PHP Web Server
        • PHP 5
        • UTF-8
        • UTF-8
        • will already be set correctly, no need for change
        • click if you want to stop on first line for your debug purposes.

Setting Up Eclipse PDT with UniServer for PHP Development (Part 1 of 2)

Eclipse PDT, HTML5, Javascript, jQuery, mySQL, PHP

General Observations

Not the most convenient or intuitive IDE in the world, Eclipse is still a convenient IDE to use if you are not into spending big money on other developers … but, would like some very useful tools for development. If set up correctly, Eclipse PDT can help develop PHP projects with very useful debug options.

Some of the problems with Eclipse PDT include:

  • Setup can be a pain, it is definitely not load and go
  • Extra effort my be needed if you are not using Zend Server
  • You define your projects, but, deleting projects is a nuisance to do

I should mention, I am not an Eclipse expert. Definitely, it was a massive effort to hunt and peck my way through the installation. This article is to help me remember the process the next time I need to go through the process. It has a bit of rambling side information for those of you that find this articles and are curious why I have elected to do some of the setup that I do to use Eclipse. I hope it helps you too.

Preparation to use Eclipse PDT

To use Eclipse PDT, you must have the ability to run PHP. This implies the availability of a Server environment. Preparation suggests you install an AMP environment. In the case of this article, I have a WAMP environment.

I am developing on a WAMP environment; Windows, Apache, MySql & PHP. To maintain the database, I like using phpMyAdmin. To meet my needs for Apache and mySQL, I have tried using Zend Server.

Zend Server is an interesting, having lots of bells and whistles, but, I find that it is a pain to install phpMyAdmin on Zend Server hosted on Windows. Zend Server has a “simple” button press “deployment” of phpMyAdmin on their Zend Server, but, it fails in Windows. Notes around the internet indicate it works in Unix and Linux but, phpMyAdmin does not deploy conveniently on Windows at this time … true.

Even more inconvenient, uninstall of Zend Server does not do a complete uninstall. Zend Server installs IBM DB2 Server, but, does not remove it in the uninstall. This incomplete uninstall leaves the IBM DB server blocking access to port 3306 for other WAMP package installs. If you want to use port 3306, for the database servers with some other WAMP package like Uniserver, Wampserver, UmAmp … you will need to be aware and uninstall the DB2 server separately. Failure to uninstall IBM DB2 results in the failure of other WAMP packages to install completely. In the future, I may try Zend Server again using a Linux environment, but, not on Windows … until they solve their Windows compatibility problem with phpMyAdmin.

On Windows, I find the Uniform Server (UniServer) extremely convenient to install. I have used UniServer for about 10 years and never have had problems with Uniserver. The installation is quick, simple, and always works. UniServer includes mySQL, PHP and phpMyAdmin and meets my development needs nicely. Load and go in a Windows development environment.

If you are looking to use Eclipse, I assume you have an AMP package installed. If you have a AMP package installed, you can skip to Installing Eclipse PDT. If you are using Windows and need a WAMP package, the following two short sections will help.

Find Uniform Server

Uniform Server can be found at http://www.uniformserver.com/. Go on over to the UniServer site and click Download to retrieve the latest version of UniServer.

Installation of UniServer

To install UniServer:

  • Choose a where you would like to install UniServer. Some would choose the “Windows File” directory, but, I prefer to place it elsewhere. Your development files will be placed in a subdirectory below this and I do not like my development files stored below the Windows/Program Files subdirectory.
  • run the unZip for the uniserver package into the chosen . A will be created, but, standard Windows install with registry is not used by the UniServer package. (The name of the server_subdirectory changes with version, so I will call that )
  • Create a shortcut on your desktop to /UniController.exe. This will be your access to the UniServer control panel.

You are now ready to move on to the hard part … installing and configuring Eclipse PDT.

Head on over to the next article to find details of the Eclipse PDT installation and configuration.

Using REGEXP in mySQL WHERE Conditionals

mySQL

SQL logoRecently, I needed a MySQL conditionals that I had not used previously.   I needed to add a condition that included an OR condition on a single field.  While OR is available in the WHERE conditional, it along with the LIKE filter overly complicated my query.   Some OR conditionals become very complicated with the OR conditional.  I found that the MySQL function REGEXP was just what the doctor ordered.   It opened up a wide variety of options, and simplified the query.

Using REGEXP  turned out to be not only easy, but, convenient. We can use regular expressions.  If you are used to using regexp, you will love this feature. For example, assume you have fields called`price` and `view` and you want to find records with the price under $300,000 and a `view` of ‘ocean,’ ‘river,’ or ‘lake,’ you can use regular expressions. You may use the following SQL conditional:

WHERE `price` < 400000 AND `view` REGEXP ‘Ocean|River|Lake’

As with other places you can use regexp, this allows you to say you would like the field `view` to contain the Ocean, River or Lake … regardless of whatever else is in that field.   Therefore, it references the field once and incorporates the concept of LIKE.

Of course, you could request the field to contain:

  • ‘^Ocean$|^River$|^Lake$’ if you wanted one of these words to be the only word in the field.
  • ^Ocean|^River|^Lake’ if you want the first word of the field to be Ocean, River or Lake.
  • [a-z ]+ if you simply want to know that there is some view defined.

OMG, you can create any of the wonderful expressions that can be built with the patterns from the following table …

Try your hand at it, knowing you have the following features to build your regular expression:

Pattern What the pattern matches
^ Beginning of string
$ End of string
. Any single character
[…] Any character listed between the square brackets
[^…] Any character not listed between the square brackets
[a-z] Within[], dash (-) create a range of contiguous characters or numbers. Here we are specifying all characters from a to z and A to Z. MySQL ignores character case when using expressions
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
* Zero or more instances of preceding element
+ One or more instances of preceding element
{n} n instances of preceding element
{m,n} m through n instances of preceding element

In addition to the patterns used in building a filter, you should know that MySQL recognizes the escapes in strings. Therefore you can use \n,\r,\t,\$,\^,\\, etc to embed special characters in the search and to search for ^,$ and \.*

To finish off this memo, you need to know:

  • RLIKE (not to be confused with LIKE) is a synonym for REGEXP in mySQL
  • MySQL examines expressions independent of the character case.
  • Oracle’s REGEXP does not support escape characters found above.
  • You could install LIB_MYSQLUDF_PREGif you wanted an even more powerful expression handling in your mySQL
  • You can learn more about LIB_MYSQLUDF_PREG at http://www.regular-expressions.info/mysql.html

Using the MySQL function DATE_SUB

mySQL

SQL logoRecently, I needed to craft a SQL query that would allow me to determine the records that had been stored in the last 30 days. I found DATE_SUB() was just the thing I needed for the query. Assuming you have a record field containing a date, such as a field `submitted,` if you would like to find all records submitted in the last 30 days, you could use the following SQL feature to the WHERE conditional:

AND `submitted` >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

DATE_SUB() accepts 2 parameters; the date of interest, and a phrase of the form ‘INTERVAL ii xxxxx.’ DATE_SUB() subtracts the specified interval from the from the date of interest. So, if we want to find what has happened in the last 30 days, subtract the INTERVAL 30 DAY from the current date and then ask for everything after that Date in the query.

For completeness, let’s note:

  • CURDATE() returns the current date in the format yyyy-mm-dd.
  • INTERVAL accepts:
    • A number appropriate for the type of interval you choose,
    • The following interval types
      • MICROSECOND
      • SECOND
      • MINUTE
      • HOUR
      • DAY
      • MONTH
      • QUARTER
      • YEAR
      • and other*

*For more available interval types, see http://www.w3schools.com/sql/func_date_sub.asp