MySQL | Tropical Web Works

MLS Listings can be displayed on a page by using a simple shortcode.

I’ve been digging deeply into WordPress over the past few years, learning how to integrate custom programming and database functions into the WordPress framework. The biggest challenge for me was figuring out how to do complex things like allowing visitors to search the MLS database of real estate for sale. This involves first displaying a search form, then having that form submission result in a search of the MLS database for properties matching the criteria in the form, then displaying the search results (with pagination), with each listing containing a link to a details page for that listing. And then the details page has to pull the listing’s details from the MLS database and display all the details. And all the photos.

And to make it more complicated, my real estate clients also need to be able to display MLS listings on pages based on pre-defined searches — for example, all the single-family homes for sale in Punta Gorda that are on the water and have a pool, or all the condos in Port Charlotte under $200,000. My real estate clients are Realtors, not website programmers, so that part had to be easy for them. I’ve learned that making complex things easy can sometimes be harder than doing the complex thing in the first place.

It turned out that once I got my head around how WordPress’s shortcodes work, it wasn’t that hard. Tedious, yes, but not that difficult.

Here’s what a Realtor might put on a page to show single-family waterfront homes in Punta Gorda with a pool:

[showlistings city="Punta Gorda" proptype="SFH" waterfront="y" pool="y"]

Simple enough for almost anyone. Behind the scenes, it’s a bit more complex. When WordPress sees that shortcode in a page or post, it triggers a whole pile of code that searches the MLS database for properties meeting those search criteria and displays them on the page. And triggering that code isn’t particularly difficult.

First, I register the shortcode with WordPress in the functions.php file — identifying the name of the shortcode (in this case, “showlistings”) and the php function that will be triggered by that shortcode. It looks like this:

add_shortcode( 'showlistings', 'getMLSListings' );

Then I add the php function that finds out what search criteria were used in the shortcode, searches the database for properties matching those critera, and displays matching listings on the page. That function looks like this:
function getMLSListings($atts) { (Hundreds of lines of code here to read the attributes of the shortcode, search the database, and display the listings) }

First the function checks the attributes ($atts) of the shortcode to find out that the shortcode says “Punta Gorda” for city, “SFH” (which stands for single-family homes) for property type, “y” for waterfront, and “y” for pool. Then using those criteria, it builds the database query, connects to the database, runs the query, gets the links for the photos, and outputs the whole shebang to the WordPress page.

From my pre-WordPress days, I already had code written to search the MLS database and display the listings. It needed changes to make it display nicely within WordPress and to bring it up to date by making it mobile-responsive, but that wasn’t too terribly difficult.

Also, since I keep the MLS listings in a whole separate database from the WordPress site’s database, I had to learn how to access a different database from within WordPress. Prior to that project, I had never previously had to access two different databases within one site. But I have found over the years that I can learn just about anything from Google University. I love the internet, I love Google, and I love all the people out there who write all those helpful tutorials and informational articles that help me find out what I need to know.

I’ve recently had to set my sights on javascript and learning to integrate custom javascript and jQuery within WordPress. That’s a whole ‘nother kettle of fish. I’ve never been a javascript master, and it’s taken me a lot of effort to figure out how to do various things with javascript & jQuery. But that’s a topic for another day.

This is the result of a [showlistings] shortcode:

Poor hosting companies waste time and cost money.

I needed to set up a MySQL database for a client recently. The client hosts their website with a large and well-known hosting company, which advertises MySQL available with all hosting packages. It should have taken just a few minutes to create the database and assign the db username and password, and then I could get on with the backend programming for the site. Several days and several billable hours later, I was just getting started.

First, I logged into the client’s hosting control panel and looked for the “MySQL” or “Database” option. No problem, that was easy enough to find. There was a link to “Activate MySQL” for the account. I thought that a little odd — most of the time, hosting accounts that have MySQL available simply have it available, no “activation” required. But okay…. I clicked the “Activate MySQL” link, then received a message that MySQL wasn’t available and the account would have to be upgraded to a package that included MySQL. I e-mail my client, explain the situation, and tell them they need to upgrade their hosting account. When the client calls the hosting company to upgrade, it turns out that this host’s current packages all include MySQL, but older legacy accounts never had MySQL added to the available options.

Some days later, the client has upgraded their account to one of the newer hosting packages, which does include MySQL. I log back in to the control panel, activate MySQL, make note of the host, username, and password to use for database connections, then look for a link to phpMyAdmin to access the database. No phpMyAdmin anywhere, but the knowledgebase included instructions for downloading and installing phpMyAdmin. Wow, even the low-end $4/month hosting accounts at GoDaddy include phpMyAdmin already installed and ready to use.

I wouldn’t have to waste time installing phpMyAdmin if I could access the MySQL server through a secure shell, so I checked the account’s features for connecting via telnet or ssh. I didn’t find any information on that, so I tried a few random-but-likely ssh connections, but no go. Apparently the account has no telnet or ssh ability. So off I go to download phpMyAdmin.

I downloaded phpMyAdmin, uploaded it to the server, configured the connection settings, and tried to load up phpMyAdmin. I got a variety of “access denied” messages, all pretty worthless.

I’ve only installed phpMyAdmin myself a couple of times, and I wasn’t positive I had it configured correctly, so I decided to just use the connection string and try a database connection in a test page on the client’s site. I still got “access denied” errors, but this time the error message was more useful: The hostname for the database in the error messages was not the hostname specified in my db connection string. I double-checked and triple-checked the hostname specified in the hosting control panel and made sure I had it correct in the connection string, but I kept getting this same error. So I finally decided I needed to contact the host for technical support.

I hate calling tech support on the phone, because it invariably involves being put on hold for anywhere from 30 minutes to 2 hours, so I looked for a tech support contact form or e-mail link. No e-mail link, but I did find a form. Unfortunately, the form required that I enter the last 4 digits of the credit card number associated with the account. This is a client’s account, not my own, and I don’t happen to have the client’s credit card number, so I couldn’t use the form — even though I’m logged into the account’s control panel using the username and password associated with the account.

Looks like I have to resort to the telephone after all. I dial the tech support number and I get put on hold for about 45 minutes. I put my phone on speaker and tried to get some other work done while waiting, but every couple of minutes a robotic voice would issue from the speaker telling me how important my call was — repeatedly interrupting my concentration and making it virtually impossible to get any work done while waiting on hold. This is exactly why I hate calling tech support.

Finally, after 45 minutes of this, I get a live person who takes down the necessary information, puts me on hold while he checks into the situation, and then tells me that the database was activated on the wrong server and he would fix that but it would take 6 hours for the correction to “take.” I don’t get this at all — if he fixed it, why is there a six-hour delay? Nevermind, I was glad that the problem was identified, so I thanked him and hung up.

By now it’s well past my normal working hours, and the database is supposed to take 6 hours to be fixed, so I closed up shop for the day. The next morning, my connection script is working, and phpMyAdmin is working, and I’m finally able to start work on the client’s database.

Fortunately for me, I charge this client on an hourly basis, and you better believe they were billed for every minute I spent attempting to get their database set up and working, including the 45 minutes I spent on hold listening to that irritating robotic voice telling me how important my call was. If they had been with a better hosting company, they would have saved about 4 billable hours on their last invoice.

Using WordPress Shortcodes To Make Complex Things Easy

Bad Hosting Costs $$ and Wastes Time

Facebook

Get Started Today

Tropical Web Works