Tuesday, October 30, 2012

MySQL select today and last 30 days records


If you need to select records from MySQL table records with date today and last 30 days records.
based on the Server date.

MySQL select today and last 30 days records


SELECT * FROM posts WHERE 
date_of_create BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();
In case if your field data type were DATETIME format you need to convert your datetime format into date
SELECT DATE_FORMAT(date_of_create, '%m/%d/%Y') FROM posts
WHERE   date_of_create BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Wednesday, October 24, 2012

MySQL Date Functions

Working with date in MySQL is very important for date based query processing. today we going to get some important MySQL date functions frequently used in projects.

Here is the date functions with example queries from Article table  have the column date_of_post

CURDATE() - Return the current date

Example:

SELECT * FROM articles WHERE date_of_post = CURDATE()

 

DAY()   - Return day number from the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = 1

 

MONTH() - Return Month number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2

 

YEAR() – Return year number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2011

 

LAST_DAY() -  Return Last date as number, From the month of the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = LAST_DAY(date_of_post)

 

DAYNAME() - Return day name as string from the date column

SELECT DAYNAME (date_of_post) AS `day_of_post` FROM article

 

MONTHNAME() - Return Month name as string from date column

Example:
SELECT MONTHNAME(date_of_post) as `month_of_post` FROM article

 

QUARTER() - Return Quarter Number as 1 to 4 Format as number

Example:
SELECT QUARTER(date_of_post) AS `post_quarter` FROM article

 

WEEK() - Return Week Number Number (0-53)

Example:
SELECT WEEK(date_of_post) AS `week ` FROM article

Saturday, October 20, 2012

Get Yahoo contacts from address book


Mr. Antony request me how to list all email ids from user's yahoo email account. here is the simple solution to get all Emails from user contact list in yahoo address book.

In order to work with Yahoo Address book we need to create oauth Project on yahoo developer network. let us create it first.

Step: 1


Select My Projects on Top Right below the search box. (You are ask to Login with existing yahoo account)

Step: 2

 Click New Project Button

Step 3:


Select Application Type "Standard"

Step 4: ( Fill your Application as per the form below with your own values)

Step 5: (Get your Project Keys)

Application ID: below the Project Name
Consumer Key and Consumer Secret Inside the Box

Step 6: (Verify your Domain)

 

Step: 7 (Download Yahoo PHP SDK -  Included in Demo Download)



index.php ( Show the Button labled "Get Yahoo Contacts")
<p align="center"><br />

<a href="getContact.php" style="background:#939; color:#FFF; 
padding:10px; font-family:'Trebuchet MS', Arial, Helvetica, sans-serif; 
text-decoration:none; font-size:14px; font-style:italic; margin-top:50px; 
text-align:center;">Get Yahoo Contacts</a></p>

getContacts.php

Get List of contacts using YQL after user access
http://developer.yahoo.com/social/rest_api_guide/contacts_table.html


<?php session_start();

error_reporting(0);

// Replace your Own KEYS 

require("lib/Yahoo.inc");
// Your Consumer Key (API Key) goes here.
define('OAUTH_CONSUMER_KEY', "j0yJmk9M1Zzc0F3YURkV3AwJmQ9WVdrOU5tMVRhSFJETjJNbWNHbzlNVEUxTXpRek9UUTJNZy0tJnM9Y29uc3VtZXJzZWNyZXQmeD0wMg--");
// Your Consumer Secret goes here.
define('OAUTH_CONSUMER_SECRET', "febded04011a8e6e2f1227b9e24688f2252587a");
// Your application ID goes here.
define('OAUTH_APP_ID', "mShtC7c");
// Call back URL
define('CALL_BACK_URL','http://demos.w3lessons.com/yahoo-contact/getContact.php');

/*
    Consumer Key:
    Consumer Secret:
    Application URL:
    App Domain:

   dj0yJmk9M1Zzc0F3YURkV3AwJmQ9WVdrOU5tMVRhSFJETjJNbWNHbzlNVEUxTXpRek9UUTJNZy0tJnM9Y29uc3VtZXJzZWNyZXQmeD0wMg--
    afebded04011a8e6e2f1227b9e24688f2252587a
    http://demos.w3lessons.com/yahoo-contact/index.php
    demos.w3lessons.com
 
*/ 

$session = YahooSession::requireSession(OAUTH_CONSUMER_KEY, OAUTH_CONSUMER_SECRET, OAUTH_APP_ID);   

$user = $session->getOwner();

$query = sprintf("select * from social.contacts where guid=me;");  
$response = $session->query($query); 



if(isset($response)){

   foreach($response->query->results->contact as $id){

       foreach($id->fields as $subid){

               if( $subid->type == 'email' )
               $emails[] = $subid->value;
       }
   }
}

$session->clearSession();

echo "<pre>";
print_r($emails);
?>
<br />
<br />
<a href="index.php">Back</a>

Download This Script     Live Demo     Download Script

Friday, October 12, 2012

Change of Domain 301 Redirect htaccess



For SEO best practices, when you change domain name of existing website, we need to setup 301 redirect of all existing web pages exactly to a new domain name. This is very important for Search engines detect a new version of URLs

Using the following htaccess code we will redirect all the existing pages of our website to new.


RewriteEngine On
# Redirect all urls with new domain name
RewriteCond %{HTTP_HOST} ^domainone.com$ [OR]
RewriteCond %{HTTP_HOST} ^www.domainone.com$
RewriteRule ^(.*)$ http://www.domaintwo.com/$1 [r=301,nc]

# Redirect with www of new domain.
RewriteCond %{http_host} ^domaintwo.com [nc]
RewriteRule ^(.*)$ http://www.domaintwo.com/$1 [r=301,nc]

about code will redirect all the existing pages of current website to new website with same request URL.

Thursday, October 11, 2012

Protect email address in web content.


While building web content we may need to include email address. In this case spam bots are may access our web page and collect email address inside our web content. based on this email address extracting script.

The only way to protect Email address inside the content is convert all email address into image.

Here is the solution to convert email address into dynamic text images


email-to-image.php

<?php

define("F_SIZE", 10);
define("F_ANGLE", 0);
define("F_FONT", "verdana.ttf");

$text = base64_decode(base64_decode($_GET['id']));

$Leading=0;
$W=0;
$H=0;
$X=0;
$Y=0;
$_bx = imagettfbbox(F_SIZE, F_ANGLE, F_FONT, $text);
$s = preg_split("/\n]+/", $text);  // Array of lines
$nL = count($s);  // Number of lines

$W = ($W==0)?abs($_bx[2]-$_bx[0]):$W;
$H = ($H==0)?abs($_bx[5]-$_bx[3])+2+($nL>1?($nL*$Leading):0):$H;

$img = @imagecreate($W+8, $H) or die("Cannot Initialize new GD image stream");

$white = imagecolorallocate($img, 255,255,255);
$txtColor = imagecolorallocate($img, 255,50,0);

// Adjust padding right:
$W+=8;
$bgwhite = imagecolorallocatealpha($img,238,238,238,0);
imagefilledrectangle($img, 0, 0,$W,$H, $bgwhite);

$alpha = "".range("a", "z");
$alpha = $alpha.strtoupper($alpha).range(0, 9);

// Use the string to determine the height of a line
$_b = imageTTFBbox(F_SIZE, F_ANGLE, F_FONT, $alpha);
$_H = abs($_b[5]-$_b[3]);
$__H = 0;

// Use the string to determine the width of a line
$_b = imagettfbbox(F_SIZE, F_ANGLE, F_FONT, $text);
$_W = abs($_b[2]-$_b[0]);

// Final width and height
$_X = abs($W/2)-abs($_W/2);
$__H += $_H;

imagettftext($img, F_SIZE, F_ANGLE, $_X, $__H, $txtColor, F_FONT, $text);

header("Content-Type: image/png");
imagepng($img);
imagedestroy($img);
// END EMAIL IMAGE  
exit();
?>

index.php

<?php
if(isset($_POST['content']))
{
$content=$_POST['content'];
// Keep line breaks inside textarea
$content=preg_replace("/[\n]/", '<br />', $content);
// Find All email ids inside content
preg_match_all('/([\w-\.]+)@((?:[\w]+\.)+)([a-zA-Z]{2,4})/',$content,$emails);

// Replace as image every email address
foreach($emails[0] as $email)
$content=str_replace($email,'<img src="email-to-image.php?id='.base64_encode(base64_encode($email)).'">',$content);
}
?>
<!DOCTYPE>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
body,td,th {
 font-family: Verdana, Geneva, sans-serif;
 font-size: 12px;
 color: #000;
}
body {
 margin-left: 5px;
 margin-top: 5px;
 margin-right: 5px;
 margin-bottom: 5px;
 margin:0 auto;
 width:750px;
}

textarea { width:750px; font-size:15px; font-family:Verdana, Geneva, sans-serif;}

#content
{
 font-size:13px;
 padding:10px;
 background:#EEE;
 border:1px solid #CCC; 
}

#content img
{

 position:relative;
 margin-bottom:-3px;
}

</style>
</head>
<body>

<br>
<br>
<h1>Protect Email Address inside web content</h1>
<form action="" method="post">
<textarea cols="70" rows="10" name="content">
content mixed with any email address. that email address text will be converted into as image. for example we have info@example.com mail in this text area it will be return as image after the compilation. any no of occurrence will automatically replaced as image 

For Example:
Contact
admin@example.com

Technical
tech@example.com
</textarea>
<br /><br />

<input type="submit" value="Compile" name="submit" /><br />
<br />

</form>

<div id="content">
<?php echo @$content; ?>
</div>
</body>
</html>


Download This Script     Live Demo     Download Script

Wednesday, October 10, 2012

Extract Email ID from Content PHP


In many, CMS development, We may often to check whether any email was found inside the content in this case. Here is the best solution to extract email ids from content using the power of regular expression.

Regular Expression to extract Email IDs from Content.

'/([\w-\.]+)@((?:[\w]+\.)+)([a-zA-Z]{2,4})/'

PHP function to extract All Regular Expression Matches

preg_match_all('/([\w-\.]+)@((?:[\w]+\.)+)([a-zA-Z]{2,4})/',$content,$output);

Sample Content.txt with email id mixed html

<p>example for extracting email id <b>example@gmail.com</b> 
from entire content it mean how many <b>email@example.com</b> 
<b>admin.connnect@example.com</b></p
<?php

//Extract Email ID from content.txt

$whois = file_get_contents("content.txt");
preg_match_all('/([\w-\.]+)@((?:[\w]+\.)+)([a-zA-Z]{2,4})/',$whois,$emails);


echo "<pre>";
print_r($emails[0]);

?>

Output

// OUTPUT


Array
(
    [0] => example@gmail.com
    [1] => email@example.com
    [2] => admin.connnect@example.com
)



Tuesday, October 9, 2012

Optimize All MySQL Tables PHP


If you looking for optimizing MySQL database tables from PHP dynamically, here is a solution to optimize entire tables a database.

Optimizing Tables we need to get Entire table list using "SHOW TABLES" query. as this return entire table names as record, after getting executed of this command call OPTIMIZE TABLE and REPAIR TABLE as per the table needs.

Primary Optimization will failed if the table been damage or corrupt. we will apply REPAIR TABLE

<?php

mysql_connect("localhost","root","");
mysql_select_db("your_db_name");


echo "<br /> OPTIMIZING TABLES ";

$alltables = mysql_query("SHOW TABLES");

// Process all tables.
while ($table = mysql_fetch_assoc($alltables))
{
 foreach ($table as $db => $tablename)
 {
 // Optimize them!
 if(mysql_query("OPTIMIZE TABLE ".$tablename))
  echo "<br>OK Optimized : ".$tablename;
 else
 {
  echo "<br>Error Optimizing Applying Reapir... ".$tablename;
  // Apply Reapirt if Optimization Failed;
  if(mysql_query("REPAIR TABLE ".$tablename))
  echo "Repaired !";
  else
  echo "Error Repairing Table!";
 }
 }
}
mysql_close();
?>