Find total rows by day created with PHP and MySQL

I was recently asked by a client to produce a report of content posted by day. My first instinct was to query the database for all rows of the particular content type and handle the counting in PHP.

First Idea

  1. Query the database for all rows of content type X
  2. Create an array in PHP of days
  3. Loop through all rows
  4. Use PHP to figure out the day the row was created
  5. Increment the array element for that day

This seemed pretty inefficient though and felt like something some SQL could handle far better.

SQL Implementation

MySQL offers the WEEKDAY function that returns an integer showing the day of the week for the date it is passed. With “0” being Monday, “1” being Tuesday etc.

The next slight problem I had is my dates were stored as timestamps and weekday requires a date in the YYYY-MM-DD format, you can also optionally pass in hours, minutes and seconds.

MySQL functions to the rescue again. This time we need FROM_UNIXTIME function. This function takes a Unix timestamp and returns it in the correct date format for WEEKDAY.

I then did a loop from 0 to 6 and stored the results in an array for output.

The loop with query in ended up as follows. I am also using the MySQL COUNT function to just return the number of results as that is all I am interested in here.

$contentCount = [];

for ($dayCounter = 0; $dayCounter <= 6; $dayCounter++) {

$contentCountSql = "
 SELECT COUNT(c.id) AS contentCount
 FROM content c
 WHERE c.type = 'x'
 AND weekday(FROM_UNIXTIME(created)) = :dayCounter;";

$query = $PDO->prepare($contentCountSql);

$query->bindParam(':dayCounter', $dayCounter, PDO::PARAM_INT);

$query->execute();

$result = $query->fetchObject();

$contentCount[$dayCounter] = $result->contentCount;

}

$PDO is a PDO connection to the database.

We loop through and get the number of rows, using COUNT, for each day and content type X.

This stored in an array with the key equal to the MySQL day and the value being the count.

So Monday would look like

0 => 763

Tuesday would look like

1 => 542

The Power of Knowledge Sharing

The web is constantly developing and growing at a fast pace. I often wonder how beginners even know where to start and marvel at their ability to not be daunted by the myriad of options available to today’s developers.

I started my web developing career when there was a clear distinction between HTML, CSS and JavaScript. I remember using table based layout but quickly processed to floats for layout and some simple JavaScript for things like rollovers.

With all the new technology and techniques that are coming thick and fast it’s easy to feel like you are getting left behind. Being able to quickly learn new things quickly is a massive gain to you skills and this is where knowledge sharing can really come into it’s own.

Working with such a great team at Headscape means there is always lots of ideas being bandied around and everyone is vey keen to stay on top of their area. Now we are firmly using Drupal 8 for projects we decided we should try and make knowledge sharing more formal.

Documentation

We have created a site specifically to detail how to do Drupal 8 specific tasks. It’s becoming a great reference point and as people refer to pages other have created they can improve and contribute to them. This has the dual benefits of improving the documentation while allowing team members to quickly get up to speed with a task.

The way we have approached this is to document everything in a simple concise manner, imagining we were referring a junior developer to the documentation to help them learn. So it’s entirely possible that in the future this documentation can help other, not just the present team.

Presentations

When someone in the team feels they have something sufficient to share with everyone we do short presentations. Again this has dual purposes, quickly share knowledge with others and spark conversations. It often leads us off on tangents, relative ones though. This has helped clear up outstanding questions on numerous subjects.

Motivation

Being in a productive environment where everyone is enthusiastic to produce good work really rubs off on the whole team. Watching a colleague present on work makes me want to do the same and help those around me.

I appreciate I’m lucky to be surrounded with the team that I am and a lot of people in our industry are freelancers and work alone. If you are one of those people I’d strongly encourage you to find a community or forum where you can find others to share and learn with.

Headless / Decoupled Drupal 8 First Go

The installation of a minimal Drupal setup is very quick and easy. From there I enabled a few modules and a theme to help me create some data.

  • Modules enabled
  • Field UI
  • Image
  • Views
  • View UI
  • RESTful Web Services

I also installed the Devel module to help with dummy content population of my content type Article.

A common function of a website is to list content in an order, newest items or most relevant, and paginate this list. In Drupal this is commonly done in Views.

I set up my view as follows

  • REST Export
  • URL of /api/articles
  • Display 10 items
  • Show fields

This is great, nice and easy and gives the JSON I want.

Now the questions began.

  • If I can set a URL for this how are the URLs structured for individual nodes
  • If my public facing site is example.com then do I need to access my CMS on a subdomain such as cms.example.com
  • How do I know the URL to paginate the Views output
  • Can it supply a next and previous URL, how about total number of page

There is a great discussion about React in Drupal core on the Lullabot podcast. At 48:58 they talk about the Drupal APIs and specifically Views. The current status is there are some in place but using Views is a workaround at the moment.

As I keep going hopefully some of these questions will be answered, and I’m sure there will be moe posts and more questions.

Testing SSL certificates without a domain name

Recently at Headscape we were setting up a new server ready to launch a redesign of a site. Whilst testing we were accessing the site using the server’s IP address which works fine for checking everything is working, apart from when we needed to install the SSL certificate.

The SSL certificate is based on a domain name so going to an IP address causes the SSL certificate to warn of an insecure connection. Not very helpful when you’re checking to see if everything has been setup correctly.

This is the solution we came up with.

Say our server is on an IP address of 123.456.78.9 and the domain name is reallysecure.com

Adding an entry to the hosts file of

123.456.78.9 reallysecure.com

will cause the browser to go to the new server when we request reallysecure.com

The server will see that the incoming request is for the domain name that matches the SSL certificate and, providing everything has been installed correctly, we will be accessing the site over https.

Drupal Commerce, TeamCity and Git Submodules

At Headscape we use TeamCity as our Continuous Integration server and I recently came across an error when trying to build Drupal Commerce Kickstart.

The main parts of the error message were

Failed to build patch for build... due to error: 'build patch' command failed.

stderr: The repository  has a submodule in the commit '77b28273d1ebb08fd03ee254bbb20b6b476d16ec' at a path 'profiles/commerce_kickstart/libraries/jquery.bxslider', but has no .gitmodules configuration at the root directory

To fix this I needed a list of the submodules which I found using a command given in this Stack Overflow thread

$ git ls-files --stage | grep 160000

This listed the submodules as

160000 49a59494c0769c67a7ed2afe35f096e6d6fc956d 0 profiles/commerce_kickstart/libraries/jquery.bxslider
160000 9b8da2340b87e8dbbb5e7e2563a2ca4bdf09a1b4 0 profiles/commerce_kickstart/libraries/jquery_expander
160000 bd879003043b4a93b78cbd4a582b6e0650900bcb 0 profiles/commerce_kickstart/libraries/jquery_ui_spinner
160000 538237c7c5e95736fc376f4efc3e40f5b98eecc5 0 profiles/commerce_kickstart/libraries/selectnav.js

The next step is to find the remote URL for each of them. After moving into the directory

$ cd profiles/commerce_kickstart/libraries/jquery.bxslider

I then used $ git remote show origin

Which returns Fetch URL: https://github.com/stevenwanderski/bxslider-4.git

Each of these needs adding to the .gitmodules file as a submodule so we end up with the .gitmodules file looking like this

[submodule "profiles/commerce_kickstart/libraries/jquery.bxslider"]
path = profiles/commerce_kickstart/libraries/jquery.bxslider
url = https://github.com/stevenwanderski/bxslider-4.git

[submodule “profiles/commerce_kickstart/libraries/jquery_expander”]
path = profiles/commerce_kickstart/libraries/jquery_expander
url = https://github.com/kswedberg/jquery-expander.git

[submodule “profiles/commerce_kickstart/libraries/jquery_ui_spinner”]
path = profiles/commerce_kickstart/libraries/jquery_ui_spinner
url = https://github.com/btburnett3/jquery.ui.spinner.git

[submodule “profiles/commerce_kickstart/libraries/selectnav.js”]
path = profiles/commerce_kickstart/libraries/selectnav.js
url = https://github.com/lukaszfiszer/selectnav.js.git

Once this file has been committed TeamCity was able to complete the build.

Codeception Database Helper

I’ve been using Codeception for some acceptance testing recently. The project has a large database and using the standard Codeception database module that rebuilds the entire DB from an SQL dump was too slow.

All I needed to do was run a few queries to put some tables into a known state. I made a helper module that will run all .sql files in a directory.

I’ve put the DbHelper on Github and hopefully it will be useful for others.

Guesting on Modules Unraveled Podcast

At Headscape we were recently contacted by Brian Lewis from Modules Unraveled Podcast to see if we would like to appear on the show to discuss our use of Drupal.

It was really great to have a chat with Brian and there were a few questions sent in by Twitter users who were watching the live stream.

You can listen to episode 90 on the Modules Unraveled website.

Moving on from the MBA to Headscape

I have been working for The Marine Biological Association for close to five years now but the time has come to move on. I’m very proud to say I will be working for Headscape where I’m sure I will learn lots and continue to progress as a developer.

In the five years since I started the web and my skills have progressed exponentially. It’s been fun and nostalgic to talk about the early days and see how far the sites and applications have come since I began working with the MBA.

As with all good work places it’s not only the work but the people who make it special and I made some excellent friendships that I’m sure will continue.

I’m certain that this will continue to be the case in my new endeavors with Headscape.

Shop Talk Answer

I recently discovered Shop Talk, a live web design and development podcast by Chris Coyier and Dave Rupert. So I’ve been listening to the old episodes and in episode 9 with Ethan Marcotte. Andy Howells from the UK asked a question ,which is 15:38 into the episode.

He asked about an eCommerce site that could have twenty four additional images of a product and rightly expressed concerns about page load speed on mobile devices over slow network connections.

As I was listening I had the idea that this could quite easily be solved by just having one main image on the product page and then a link to view more images of the product. This could then be enhanced to load the additional images in using AJAX. The user clicks a link that pulls in the extra images and then displays them on the current page.

Another step could be to load an additional six or twelve images then the customer could continue loading more images until they have seen them all. Waiting for twenty four images to load could still be very slow even if the user has consciously chosen to perform this action.

The Downsides?

From an SEO perspective the reduction of images on the page means a reduction of alt tags with the product name. I feel this is probably a very small hit but if anyone knows different please let me know. The product name should be appearing in the body copy as a description and title etc which should provide the majority of your “SEO juice”.

On larger screens only seeing one image could reduce the visual impact when a user first sees the page. This could be overcome by triggering the AJAX request to fire when the page loads and a media query over a certain size is present. Using Modernizr this would look something like

if(Modernizr.mq('all and (min-width: 768px)')) {
    loadExtraImages('/extra-images.php?product_id=1234');
}

Here is where the issues gets a little grey as we are assuming that a user on a larger screen has a better internet connection when it might actually be the case that someone has a large screen and only a dial up connection but a small screen device is connected to a high speed WiFi connection. If anyone knows of a good way to test bandwidth to replace the above media query test above please leave a comment.

I’m off to Future of Web Apps 2012

This year I’m lucky enough to be attending Future of Web Apps London 2012. I’ve been to Future of Web Design before and found it hugely inspirational so I have expectations for it’s more technical sibling.

I’m looking forward to

Jeffrey Zeldman – Obviously a major influence in the web world and I’ve heard he is an amazing speaker.

Bruce Lawson – I’ve seen Bruce speak at FOWD and he is very engaging as a speaker and I admire his honesty and openness on standards.

Lorna Jane Mitchell – Not only am I interested in her API talk I also get to attend the API workshop with her. APIs are something I’ve been looking at and working with a lot more recently so the chance to pick the brains of someone as accomplished as Lorna is a fantastic opportunity.

Andrew Appleton – I’ve also been looking a lot more into the many JavaScript MV* frameworks lately so Andrew’s talk regarding Backbone.js is of interest to me.