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

Posted in Uncategorized | Leave a comment

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.

Posted in Uncategorized | Leave a comment

Drupal 7 Views Sort by Two Date Fields

To achieve this you will need to implement hook_views_query_alter() in a custom module. I did not do this in Views UI.

A recent project at Headscape required me to build view listing two different content types that would use two different date fields to sort by. For examples I’ll use the default basic page and article content types.

The Problem

Some content types need to be sorted on the node created date and others on the display date field.

Trying to use the Views UI to add the two sort fields would result in first ordering by node created, then ordering by display date which is not the result we were after.

The Solution

First we need to add a new field that we can sort by. This field will have the alias order_date.

To create it we check if the display date field is null, if it is order_date gets the node created timestamp. If display date has a value we convert it to a Unix timestamp and that value is assigned to order_date.

In hook_views_query_alter we need this code

$query->fields[] = array(
  'field' => 'IFNULL (UNIX_TIMESTAMP(field_data_field_display_date.field_display_date_value), node.created)',
  'alias' => 'order_date'
);

Next we need to add the order by clause. As this is the only field we want to order by we completley over write the order by array.

$query->orderby = array(
  array(
    'field' => 'order_date',
    'direction' => 'DESC',
  )
);

Put together hook_views_query_alter will look like this.

/**
 * Implements hook_views_query_alter().
 */
function MYMODULE_views_query_alter(&$view, &$query) {

if ($view->name == 'YOUR_VIEW_NAME') {

  $query->fields[] = array(
    'field' => 'IFNULL (UNIX_TIMESTAMP(field_data_field_display_date.field_display_date_value), node.created)',
    'alias' => 'order_date'
  );

  $query->orderby = array(
    array(
      'field' => 'order_date',
      'direction' => 'DESC',
    )
  );

 }

}
Posted in Web Development | Tagged | Leave a comment

Stepping back to move forward with ReactJS

I’ve been using ReactJS as I have been exploring Headless / Decoupled Drupal and have so far found it to be very nice to work with. However, as I am not overly familar with it I’ve found myself looking up lots of things to ensure I’m doing it “the React way”. This has meant I’ve been doing more React learning than I have Drupal learning.

My solution is to go back to an existing Drupal 7 project where I have a custom piece of search functionality and replicate it using JSON feeds provided by Drupal and a ReactJS frontend.

This has been really beneficial in my learning as I have very clea objectivies and expectations. It also means I am only focussed on learning one thing at time so I don’t skip around between documentation and tutorials.

If you ae just starting with ReactJS, or any othe JavaScript famework, and Drupal I would highly reccomend this approach to get a taste of what this decoupled appoach can bring to your skill set.

Posted in Web Development | Tagged , , | Leave a comment

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.

Posted in Uncategorized | Tagged , , | Leave a comment

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.

Posted in Uncategorized | Leave a comment

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.

Posted in Uncategorized | Leave a comment

Enjoying getting it wrong

This post also appears on the Headscape site as part of our blog.

During my teens and twenties I spent a lot of time skateboarding. It’s quite a unique sport in the way it influences your life as a whole. For example, you begin to look at architecture in a new creative way. You also fall over, a lot.

In fact you get really good at falling over: jump down ten steps roll across the concrete, hop up and do it again. You actually spend a lot of time “failing” and it doesn’t bother you. You learn that failure is part of the learning process and something that you should not be afraid of.

Over time the feeling of failure decreases and you’ll gain confidence in the knowledge that you know how to deal with problems.

This attitude can be taken into every aspect of your life and especially something as dynamic as web development. Why not try writing a little app in a new language? If it doesn’t work out it’s not a big deal. What did you learn from it? Did you not enjoy using the new language or was it just not a good fit for what you were trying to do?

Maybe to start with you could try using a new JavaScript library to see how you fair. Then progress to trying harder things. You might fail at them first go, maybe even second, but don’t let it phase you.

Over time the feeling of failure decreases and you’ll gain confidence in the knowledge that you know how to deal with problems. This could even be a huge advantage if a critical problem occurs on a live server for example. If you’ve been playing around and breaking things locally you’ll be calmer and more methodical when fixing critical issues.

When learning tricks on my skateboard I would always start off on the safe flat ground. Once I’d become comfortable I’d try the trick down a curb, adding a bit of risk but knowing it wouldn’t be too catastrophic if I didn’t land it. Then finally, once I’d got very comfortable, I would try it down a set of steps… more risk and more reward. You can carry this analogy into your work by trying out your new idea on a personal project, then maybe on a friend or colleague’s project and finally unleash it on that million dollar website and revel in the excitement!

So next time you think you may fall over, go with it, learn to roll out of it and pop back up and try again. You may even find you start to enjoy it.

Posted in Web Development | Leave a comment

Boagworld article Do people see you as a roadblock

Paul Boag has recently posted an article titled “Do people see you as a roadblock?” that includes some very nice mentions about me. So in a shameless act of self promotion I’m going to link to it.

You can read the article at https://boagworld.com/dev/do-people-see-you-as-a-roadblock/

Posted in My work, Web Development | Leave a comment

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.

Posted in Uncategorized | 2 Comments