Comparing timestamp in MySQL

We often use timestamps in databases. In fact people often use it to make session keys. Comparing timestamp is also often required when detecting any change in database. That’s too much often. 😛

Required: MySQL

Using:

Table defination

table1(
id (int)
jointime(timestamp))

SQL query

SELECT id FROM table1 WHERE jointime > '2011-10-08 00:00:00';

Explantion:

  1. This query selects id from table where jointime is greater than 2011-10-08.

Note:

  • Timestamp is a standard data type in MySQL and it should have both date and time.
  • For current time use NOW()
  • If you are using only date then use conversion from date to timestamp using TIMESTAMP()
  • With PHP
    • First get the time if current time use time(), it will give time in Unix Epoch.
    • Convert above time to timestamp using date() and then use in query.
    • If using some other time use mktime().
  • Remember the quotes they are important.

Also see MySQL timestamp datatype and PHP date time functions, also other functions.

Cool front page

There is a site on internet  We Are Empire. This site has pretty cool front page. Page is pretty descriptive too plus it don’t irritate the user by reloading for new content. Some other site like this which I personally liked is Nike‘s nike better world site. It takes a little while to load but every information is on just one single page and designed in awesome way using HTML5, z-index and many more things. We will come to it some other time.

But we will talk about the former one here. After running a console over the site, I understood what they had done. Well its nothing but Javascript and applaud to the maker, it is used very well for designing. It do not have flash.

  1. What they had done is when you click on the front logo which is image it shows you next div which is hidden behind it and hide the top div.
  2. Now you see 4 bubbles. Thats interesting now margin of the image in background of the bubble changes when you hover it while hiding the overflow. This is a good thing, must have taken long time to do that.
  3. Open at the bottom is the coolest thing. Click on it and another page comes with no page loading. Again is the game of margin. Margin-top is changed using Javascript to animate.
  4. There are smaller effects also like close appearing and hiding, get in touch etc.

So, this is how this front page is working. Try making yours with such innovation.

Here is a example of that a very short one.

<script type="text/javascript">
function anim(mydiv)
{
   var i = 0;
   while(i < 10)
  {
     mydiv.style.marginTop = 10*i + 'px';
     i++;
  }
}
</script>
<div id="mydiv" onclick="javascript:anim(this)">
    test
</div>

There is no need to explain the code above. Its self explanatory and use basics of Javascript and HTML.

Jquery post method for JSON in php files

jQuery is the awesomest thing I have came across so far. It makes things so easy one can easily understand why upon seeing example below. “post” method of jQuery is frequently used by many sites even Facebook and Google. You must have seen that log-in to Gmail doesn’t redirect/reload and tells you that username or password is incorrect instead they post the values using AJAX or lets say using post method of jQuery and display the error response.

Required: jquery.js(I use jquery-1.6.js. Download latest!)

Using:

file1.php


$(document).ready(function(){
$.post( 'filename', {data : data}, function(data , status){
if(status == 'success'){
alert(data.name); //your code }
}, "json" );
});

In file2.php


</div>
<div>if(isset($_POST['data'])){
echo json_encode(array('name'=>$_POST['data']));
}</div>
<div>

Explanation:

file1.php

  1. checks if the current document is ready.
  2. is the jquery method “post” is given arguments, first is the “filename” is the file name which you want to handle the post variables. Second is associative array sort of thing in which first “data” is name of the post variable and second “data” is the value of the variable. Third argument is the callback function, this executes when the post is complete. This function is provided two arguments, first is “data” which is the response from the file which is handling post. We will explain it after sometime. And the second is “status” it is optional in all of this without this code will still work. It actually holds the status of response.
  3. checks if status is success
  4. if status is success we use alert to check for the JSON value which is data.name and will alert “data” because we sent it back from file2.php.
  5. “json” is the last argument of the post method which tells the that this method is expecting JSON as response.

Note:

  • post variable name can be in quotes ‘data’.
  •  argument status can be omit.
  • “json” is required as argument if you want response to be JSON.

 

file2.php

  1. checks if $_POST[‘data’] is set. The data index is the same variable name we have sent from file1.php.
  2. first we create an associative array with key as “name” and value as $_POST[‘data’]. Then we encode it in JSON using json_encode and echo it, which is accepted by first file in callback function as data and checked using alert in file1.php as data.name which is the key in associative array.

Note:

  • This file should not have any of HTML code.
  • This file should not echo anything other than JSON.
  • You can use include() but above two points should always hold.
Hope this will help you in case you find trouble. Please also go through the jQuery website, they have pretty good tutorials.