Tuesday, July 26, 2011

PHP - Is it a leap year?

After spending 20 minutes writing a short function to test a date to see if it is a leap year, I find that php has a built in date function to do the job. Never mind it was interesting anyway to discover that there is a method in the madness of leap years. According to wikipedia, if a year is divisible by 4 it is a leap year. But not if it is divisible by 100, unless it is divisible by 400....get it?

So this was my waste of time leap year calculating function..


<?php
$year = date('Y') ;
if ( $year %400 == 0 ) { $isleap = 'yes' ; }
elseif ( $year %100 == 0 ) { $isleap = 'no' ; }
elseif ( $year %4 == 0 ) { $isleap = 'yes' ; }
else {$isleap='no';}
if ( $isleap == 'yes')
{echo $year." is a leap year";}
else
{echo $year." is not a leap year";}
?>


The smart way to do it though is just with date('L'), this php function returns 1 if it is a leap year and 0 if it is not...much simpler...


<?php
$year=date('Y');
$isleap = date('L') ;
if ( $isleap == '1')
{echo $year." is a leap year";}
else
{echo $year." is not a leap year";}
?>

Tuesday, July 19, 2011

mysql compare two large tables - find records only in 1 table - for sitemap last modified date

I needed to compare two large tables each having over 2 million records and find the records that are in table 1 but not in table 2.

I only wanted the records from table 1 and not all records that were different from both tables, so on each select query I added a temporary column that stated which table the result was from, so that after the insert select, those with table2 in the temp column could be deleted, leaving just the table 1 records...which are new records that have been added to a database since the last update.

The UNION results are inserted into a new table, so that after being inserted, all records from table2 can be deleted...


INSERT INTO products_new (name,merchantid,tab)
SELECT MIN( name ) AS name, merchantid, tab
FROM (
SELECT name, merchantid, 'table1' AS tab
FROM products_import AS alias1
UNION ALL SELECT name, merchantid, 'table2' AS tab
FROM products_bak AS alias2
)AS alias_table
GROUP BY name
HAVING COUNT( name ) =1
ORDER BY name

So after populating the new products table, the records that were only in table 2 can be deleted, leaving a table with the unique values from table 1.


DELETE FROM products_new WHERE tab='table2'

I have used this in order to be able to make my sitemaps last modified date more accurate, as currently at each product update, all dynamically created product pages have a recent last modified date, even if they were previously present in the DB. Using this method, the last modified date of new products can be accurately shown as new pages, by accessing this new table.

Increase size of tmpDSK cPanel

The default temp partion size in cpanel is 512mb which is usually not enough and creates errors such as "Drive Critical: /usr/tmpDSK (/tmp) is 99% full".

It can be increased as follows using the following commands....


stop services, mysql,apache and cpanel
root@server [~]# /etc/init.d/mysql stop
root@server [~]# /etc/init.d/httpd stop
root@server [~]# /etc/init.d/cpanel stop

backup the /tmp folder
root@server [~]# cp -rfp /tmp /tmp_backup

lazy unmount of /tmp
root@server [~]# umount -l /tmp

remove tmpDSK
root@server [~]# rm -rf /usr/tmpDSK

edit securetmp script, find my $tmpdsksize = 512000; # Must be larger than 250000
increase to necessary for example 2gb my $tmpdsksize = 2072000;
root@server [~]# nano /scripts/securetmp

run securetmp script to recreate /tmp (tmpDSK) partition at size specified
root@server [~]# /scripts/securetmp

Monday, July 11, 2011

Bash - loop through files updated in last 7 days

From command line or in a bash script, to loop thorugh all files that have been updated in the last however many days (in this example 7)...use the FIND command...


FILES=$(find /path/to/directory/ -type f -mtime -7)
for f in $FILES
do

echo $f

done

Saturday, July 09, 2011

php to display mysql query results in html table


The following code would go in an html document (that is able to display php) and display dynamic results from a mysql table into the table. This example builds a table that is 3 columns width, so the code to write the table is based on the number of rows returned from the mysql result, adding extra empty cells at the end if required.

A screen shot below, shows how the numbers are counted through to write the necessary html code to display the mysql results. In this example 19 rows are returned from mysql, so two extra table cells were required at the end, as 7 rows of 3 cells is 21 cells required. The table cells broke with a row after every count of 3.




<!-- open the table tags in the html document -->
<table border="1px"><tr>


<?php

//select the columns required from the mysql table
$result=mysql_query("SELECT name,description FROM gifts ORDER BY name");

//get count of rows returned
$numrows=mysql_num_rows($result);

//for a table with 3 columns divide the number of results by 3 and round up
$numtr=ceil($numrows/3);

//get the remainder
$remainder=$numrows % 3;
//from that calculate how many extra table cells are required in the final row
$xtratd=3-$remainder;


$i=1;//count for <td>
$x=1;//count for <tr>

//loop through $result declaring variables for any result required to go in the table
while($row=mysql_fetch_array($result))
{
$catname=$row['name'];
$catdesc=$row['description'];

//for demo purposes the values of $x and $i are shown in the table cells here
echo "<td>&#36;i is ".$i."<br />&#36;x is ".$x."<br /></td>";

//this line starts and end a new row in the table and increments the count of table rows (tr)
if(($i % 3 == 0) && ($x!=$numtr)){echo "</tr><tr>";$x++;}

//if the total number of rows is reached get how many extra table cells are needed and write them, then close the final row
if($i==$numrows)
{
for($z=1;$z<=$xtratd;$z++){
echo "<td>&#36;xtratd is ".$z."</td>";
}
echo "</tr>";
}

$i++;//increment the count of table cells (td)
}

?>

<!-- close the table tags in the html document -->
</table>



MYSQL - Update one columns data from another table

To copy one columns data to another existing tables column in mysql, it is possible with an update query like this :

UPDATE table1,table2
SET table1.coltocopy=table2.coltocopy

WHERE table1.coltocompare=table2.coltocompare;

Tuesday, July 05, 2011

PHP - Limit A Loop - FOREACH,WHILE etc

If you just want to loop through (for example) 2 iterations of a loop, rather than returning the whole set of data, you can insert a break clause to limit the number of times the data is looped through and so limit the results to two iterations.

$i = 0;

foreach($vars as $var){

//code to run


if(++$i > 2) break;

}