Sub Queries within MySQL

In general day to day, basic web development with PHP and MySQL I find that the basic functions:

ORDER BY, LIMIT and the like usually suffice.. however every now and again I build a small addon or app which requires a little sub query in the main query…

Example Scenario:
A chat system which on page load requires the latest 20 entries entered into the table. This query could fetch the results:

$message_query = mysql_query(“SELECT
                                                          msgID,
                                                          posterID,
                                                          msgTime,
                                                          msg
                                                    FROM
                                                          messages
                                                    ORDER BY
                                                          msgTime DESC
                                                    LIMIT
                                                          20
                                               “);

The above query will work fine, it will retrieve the latest 20 messages from the table. However if your chat system looked like facebook’s chat then you would want the messages printed to screen on top of each other with the oldest at the top….

This query will not achieve what you are looking for then. The resource will be the latest 20 messages from the entire table, but the first row from the resource will be the newest message. Thus if you wanted to use this query, you would have to write one php loop to place all the rows into one php array, and then another loop to run to print them all to screen in the correct order (oldest out first, ei at the top of the stack).

You could alternatively change the query. Including the above in a sub query, then ORDER BY msgTime ASC:

$message_query = mysql_query(“SELECT
                                                          *
                                                    FROM
                                                          (
                                                          SELECT
                                                               msgID,
                                                               posterID,
                                                               msgTime,
                                                               msg
                                                         FROM
                                                               messages
                                                         ORDER BY
                                                               msgTime DESC
                                                         LIMIT
                                                               20
                                                          )  subsq
                                                     ORDER BY
                                                           msgTime ASC
                                               “);

Colour Wheel HEX Codes for CSS

Colour wheels are brilliant, they remind you of colours you haven’t used in a while and which colours can in theory compliment others.

However one annoying thing most colours wheels seem to lack for web developers are the HEX codes.. so today I put one together to help me speed up the process. At some point I intend to make one to include all the HEX codes of all colours on the wheel but this will do for now.



Stage 1 colour wheel


Slide in and out with Javascript

The following will switch the content of an element around using jQuery’s fade functions based on the content of other hidden containers on the screen. Phase 1 is a basic version of the final Phase 3 which is optimized and uses least repeatable code, plus is more flexible.

I sometimes get asked by people to make easy to use little scripts.. I enjoy making them and have decided to start putting them up here. To enable anyone to understand the

Phase 1 – The basic function and least efficient method:
The JS


function fadeInOut(id){
    /* 0 – set the fade in and out time in milli seconds*/
   
var fadeTime = ‘200’;

    /* 1 – get the replacement text from the hidden container on the html page into a variable ready to use*/
    var replacementText = $(‘#’ + id + ”).html();
     
    /*2 – fade out the div which is to display the content in question*/
    $(‘#DivExample’).fadeOut(
fadeTime, function(){
            /* Callback jobs..*/
            /* 3 – with the div faded out, switch content like a magicians trick..*/
            $(‘#DivExample’).html(replacementText);
           
            /* 4 – with the new content in place fade the
element back in */

            $(‘#DivExample’).fadeIn(
fadeTime);
        });   
}

 The HTML

Click To Switch To One


<span style="display:none;" id="content1″>Content 1 lfdlknv fjdfv d

Click To Switch To One


<span style="display:none;" id="content2″>Content 2 asdcsr awce d


Click To Switch To One


<span style="display:none;" id="content3″>Content 3 aede plkoih d 


Content to display on load

So that is basic function. To use it simply attach it to an mouse event like onclick. However, phase 1 is a little inflexible. If you wanted to use the function on a page with more than on switch content display div you would need to replicated the fadeInOut funciton multiple times.

Phase 2 aims to make the same function a little more flexible (Again read on to Phase 3 as I aim to improve further…)

Phase 2 – An improved method of Phase 1
The JS 
function fadeInOut(obj){
    /* 0 – Get the id of the element to fill, and the id of the element with the new content */
    var fadeTime = ‘200’;

    /* 1 – set the fade in and out time in milli seconds*/
   
var idToFill = $(obj).attr(‘idToFill’);

    var idHolding = $(obj).atta(‘idHolding’);


    /* 2 – get the replacement text from the hidden container on the html page into a variable ready to use*/
    var replacementText = $(‘#’ + idHolding + ”).html();
     
    /*3 – fade out the div which is to display the content in question*/
    $(‘# + idToFill +”).fadeOut(fadeTime, function(){
            /* Callback jobs..*/
            /* 4 – with the div faded out, switch content like a magicians trick..*/
            $(‘# + idToFill +”).html(replacementText);
           
            /* 5 – with the new content in place fade the
element back in */

            $(‘# + idToFill +”).fadeIn(fadeTime);
        });   
}

The HTML 

Click To Switch To One

 

Click To Switch To One

 

Click To Switch To One

 

<div id="displayDiv“>Content to display on load

Phase 2, the function is now flexible. It acquires which element to fill based on an attribute placed on the html elements. However now the html contains a lot of repeated code and looks a little messy… In phase three I will tighten up the html and the JS function..

Phase 3
JS

function fadeInOut(obj){
      /*0 – grab the constants*/
      var targetContainer = $(obj).parent().attr(‘target’);
      var speed = $(obj).parent().attr(‘speed’);

      /*1 – grab the content to replace with*/
      var replacementVal = $(obj).find(‘span’).html();
      
      /*2 – fade out the target, switch content and fade back in */
      $(‘#’+targetContainer+).fadeOut(speed,function(){
          $(this).html(replacementVal);
          $(this).fadeIn(speed);
      })
 }


HTML

<div class=“fadeInOutContainer” speed=“200” target=“displayDiv”>
      <div>
            Switch to content 1
            <span style=“display:none”>The switch content of switcher number one</span>
      </div>

      <div>
            Switch to content 2
            <span style=“display:none”>The switch content of switcher number two</span>
      </div>

      <div>
            Switch to content 3
            <span style=“display:none”>The switch content of switcher number three</span>
      </div>
</div>


<div id=“displayDiv”></div>

<script type=“text/javascript”>
   /*add the onclick event listeners to the switcher divs sat in the parent with class fadeInOutContainer*/
   $(‘.fadeInOutContainer div’).bind(‘click’, function() {
      fadeInOut(this)
    })
</script>


Finally we have the html looking reasonably tidy, and the js function never needs to be altered thus allowing you to place multiple instances of a set of “switch content” divs on screen…


Please note Phase 1 and Phase 2 were written off the top of my head and are not tested: They are simply there to assist someone who is new to understand the logic I have followed. Phase three definitely works and is something I have personally put to use every now and again.

Obviously styling is needed, as the parent div is set applying CSS is not a problem..

NB – Phase 4 coming soon… will include an auto content load for the display div so the page can load up displaying info…