The WHERE clause would be false in the following query if tableB.column1 were NULL:
SELECT * FROM tableA LEFT JOIN tableB ON (column1) WHERE tableB.column2=5;
Therefore, it is safe to convert the query to a normal join:
SELECT * FROM tableA, tableB WHERE tableB.column2=5 AND tableA.column1=tableB.column1;
Monday, November 2, 2009
Friday, October 30, 2009
Group by in custom query in drupal
We should Keep in mind the followings when we use group by in custom queries.
ex: SELECT pname, MIN(price) FROM products GROUP BY pname;
1. The column that you GROUP BY must also be in your SELECT statement.
2. Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the pname and the aggregate function was applied to the price column.
ex: SELECT pname, MIN(price) FROM products GROUP BY pname;
1. The column that you GROUP BY must also be in your SELECT statement.
2. Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the pname and the aggregate function was applied to the price column.
More Mysql Indexes affect performance on heavy traffic site
I want to share about index on table columns. Indexes are something extra that you can enable on your MySQL tables to increase performance, but they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance. So avoid using index unnecessarily. That will affect the performance.
Thursday, August 20, 2009
Check the user submitting date
checkdate() is a function to check the date is valid or not.
eg:
var_dump(checkdate(12, 31, 2000)); // return true
var_dump(checkdate(2, 29, 2001)); // return false
eg:
var_dump(checkdate(12, 31, 2000)); // return true
var_dump(checkdate(2, 29, 2001)); // return false
Tuesday, June 2, 2009
Drupal watchdog query into file
To avoid record growing in watchdog table I am doing the followings. outfile in mysql is used to write output in a file.
I am using following code to write watchdog output into file.
<?php
function customfunction() {
$datetime = date("Y_m_d_Hi");
$path = "c:/";
db_query("SELECT wid,type,uid,message,severity,link,location,referer,hostname,timestamp FROM watchdog ORDER BY wid DESC LIMIT 0,10 INTO OUTFILE '".$path."watchdog".$datetime.".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' ");
}
?>
I am using following code to write watchdog output into file.
<?php
function customfunction() {
$datetime = date("Y_m_d_Hi");
$path = "c:/";
db_query("SELECT wid,type,uid,message,severity,link,location,referer,hostname,timestamp FROM watchdog ORDER BY wid DESC LIMIT 0,10 INTO OUTFILE '".$path."watchdog".$datetime.".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' ");
}
?>
Friday, May 29, 2009
Implode in Javascript - Method 1
toString() method returns one string that contains all the elements in the array separated with commas.
The toString() method is used to convert an array to a string when the array is used in string context.
In this method we cannot modify the seperator. To change seperator go to join().
The following example shows JavaScript to Use an Array's toString() Method.
<html>
<script language="JavaScript">
colors = new Array("Blue","Green","Red");
document.write(colors.toString());
</script>
</html>
Output is Blue,Green,Red
The toString() method is used to convert an array to a string when the array is used in string context.
In this method we cannot modify the seperator. To change seperator go to join().
The following example shows JavaScript to Use an Array's toString() Method.
<html>
<script language="JavaScript">
colors = new Array("Blue","Green","Red");
document.write(colors.toString());
</script>
</html>
Output is Blue,Green,Red
Implode in Javascript - Method 2
To make implode in javascript like php use join method.The join() method converts all the elements to strings and then concatenates all the strings into a longer string.
If an argument is provided in the parameter list, it is used to separate the elements in the string returned by the method.
Ex:
<html>
<script language="JavaScript">
fruit = new Array("1","2","3");
String = fruit.join("-");
document.write("The fruit array contains: ",String);
</script>
</html>
Output : 1-2-3
If an argument is provided in the parameter list, it is used to separate the elements in the string returned by the method.
Ex:
<html>
<script language="JavaScript">
fruit = new Array("1","2","3");
String = fruit.join("-");
document.write("The fruit array contains: ",String);
</script>
</html>
Output : 1-2-3
Wednesday, May 20, 2009
Steps to install memcache in drupal
1.Install the memcached binaries on your server. See How to install Memcache on Debian Etch[ http://www.lullabot.com/articles/how_install_memcache_debian_etch ] or How to install Memcache on OSX [ http://www.lullabot.com/articles/setup-memcached-mamp-sandbox-environmen... ]
2.Install the PECL memcache extension for PHP.
3. In settings.php add ini_set('memcache.hash_strategy','consistent');
4. Put your site into offline mode.
5. Download and install the memcache module [ http://drupal.org/project/memcache ]
6. If you have previously been running the memcache module, run update.php.
7. Apply the DRUPAL-5-cache-serialize.patch that comes with memcache module [memcache/patches]
I applied DRUPAL-5-3-cache-serialize.patch hence I am using drupal 5.3
8. Start at least one instance of memcache on the server.
[ex ./memcached -d -m 2048 -l 10.0.0.40 -p 11211 ]
Here 11211 is an instance of memcache, 10.0.0.40 is the IP of the server, 2048 is the allocated memory.
9. Add following in the settings.php
$conf = array(
'cache_inc' => './sites/all/modules/memcache/memcache.inc',
'memcache_servers' => array(
'localhost:11211' => 'default',
),
'memcache_bins' =>array(
'cache' => 'default',
'cache_views' => 'default',
'cache_page' => 'default',
'cache_path' => 'default',
'cache_filter' => 'default',
'cache_menu' => 'default',
),
);
10.Change memcache_stampede_semaphore time from 15 seconds to 600 in dmemcache.inc lineno:86
if ($result->expire && $result->expire <= time() && $mc->add($full_key .'_semaphore', '', FALSE, variable_get('memcache_stampede_semaphore', 600))) {
located in modules/memcache to get clear result of memcache.
Every 600 seconds cache from memcache will be cleared. If this is not set we will not see any performance benefit. Refer links… http://drupal.org/files/issues/stampede.patch. http://drupal.org/node/295738.
11. First time after install, truncate all cache tables that are memcached.
12. Bring your site back online.
13. To check the memcache status login as admin and check memcahce logs at /admin/logs/memcache.
Monday, May 11, 2009
variable_get in drupal
In drupal 5.x dont use variable_get with one argument this will cause the watchdog error. High performance site will be affected.
Use:
variable_get($variable,'0'); if you no need of second argument
R.Navaneethakrishnan
Use:
variable_get($variable,'0'); if you no need of second argument
R.Navaneethakrishnan
Tuesday, March 24, 2009
Focus module in Drupal
Focus module is Simple module that improves the user interface by setting focus on the first field in a form.
A set of default forms (log-in, search, create content etc.) are defined in the module which can be overridden by the administrator.
A set of default forms (log-in, search, create content etc.) are defined in the module which can be overridden by the administrator.
Tuesday, March 17, 2009
Drupal Auto logout using custom code
Drupal Auto logout using custom code without autologout module
Add this line in the nodeapi to get last access time
switch ($op){
case "insert":
case "update":
case "execute":
case "validate":
case "prepare":
case "delete":
$_SESSION['lastaccess'] = time();
break;
}
Set $auto_timeout in settings.php
global $auto_timeout;
$auto_timeout=900;
Add the following code to check the time in hook_footer
function <modulename>_footer(){
global $user;
global $auto_timeout;
if($user->uid>0){
if (!isset($_SESSION['lastaccess']) || ((int)$_SESSION['lastaccess'] + $auto_timeout) > time()) {
$_SESSION['lastaccess'] = time();
return;
}else {
unset($_SESSION['lastaccess']);
drupal_set_message("Session timed out");
user_logout();
exit;
return;
}
}
return;
}
Add this line in the nodeapi to get last access time
switch ($op){
case "insert":
case "update":
case "execute":
case "validate":
case "prepare":
case "delete":
$_SESSION['lastaccess'] = time();
break;
}
Set $auto_timeout in settings.php
global $auto_timeout;
$auto_timeout=900;
Add the following code to check the time in hook_footer
function <modulename>_footer(){
global $user;
global $auto_timeout;
if($user->uid>0){
if (!isset($_SESSION['lastaccess']) || ((int)$_SESSION['lastaccess'] + $auto_timeout) > time()) {
$_SESSION['lastaccess'] = time();
return;
}else {
unset($_SESSION['lastaccess']);
drupal_set_message("Session timed out");
user_logout();
exit;
return;
}
}
return;
}
Monday, March 9, 2009
Add filter format to textarea in Drupal custom form
To add filter format to textarea in Drupal custom form, We can use following code
$form['']['format'] = filter_form($node->format);
R.Navaneethakrishnan.
$form['
R.Navaneethakrishnan.
Saturday, March 7, 2009
Exclude one CCK from drupal search
To exclude one cck in drupal search please follow as in the given steps.
1.Add this into hook_form in your custom module
case 'search_form':
$excluded_types = variable_get('search_excluded_node_types', array());
$types = array_map('check_plain', node_get_types('names'));
foreach($excluded_types as $excluded_type) {
unset($types[$excluded_type]);
}
$form['advanced']['type']['#options'] = $types;
$form['basic']['inline']['keys']['#prefix'] = '<div id="pref">';
$form['basic']['inline']['keys']['#suffix'] = '</div>';
// echopre($form['basic']['inline']);
break;
2.Add this in to hook_search in your custom module
function <modulename>_search($op = 'search') {
if ('admin' == $op) {
$form = array();
$form['search_excluded_node_types'] = array(
'#type' => 'select',
'#multiple' => TRUE,
'#title' => t('Excluded Node Types'),
'#default_value' => variable_get('search_excluded_node_types', array()),
'#options' => node_get_types('names'),
'#size' => 9,
'#description' => t('Node types to exclude from search results.'),
);
return $form;
}
}
3.Add this in to hook_db_rewrite_sql in your custom module
function <modulename>_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
if ($query == '' && $primary_table == 'n' && $primary_field = 'nid' && empty($args)) {
$excluded_types = variable_get('search_excluded_node_types', array());
if (!empty($excluded_types)) {
$where = " n.type NOT IN ('". join("','", $excluded_types) ."') ";
return array('where' => $where);
}
}
}
Note : This is for Drupal 5.x
R.Navaneethakrishnan.
1.Add this into hook_form in your custom module
case 'search_form':
$excluded_types = variable_get('search_excluded_node_types', array());
$types = array_map('check_plain', node_get_types('names'));
foreach($excluded_types as $excluded_type) {
unset($types[$excluded_type]);
}
$form['advanced']['type']['#options'] = $types;
$form['basic']['inline']['keys']['#prefix'] = '<div id="pref">';
$form['basic']['inline']['keys']['#suffix'] = '</div>';
// echopre($form['basic']['inline']);
break;
2.Add this in to hook_search in your custom module
function <modulename>_search($op = 'search') {
if ('admin' == $op) {
$form = array();
$form['search_excluded_node_types'] = array(
'#type' => 'select',
'#multiple' => TRUE,
'#title' => t('Excluded Node Types'),
'#default_value' => variable_get('search_excluded_node_types', array()),
'#options' => node_get_types('names'),
'#size' => 9,
'#description' => t('Node types to exclude from search results.'),
);
return $form;
}
}
3.Add this in to hook_db_rewrite_sql in your custom module
function <modulename>_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
if ($query == '' && $primary_table == 'n' && $primary_field = 'nid' && empty($args)) {
$excluded_types = variable_get('search_excluded_node_types', array());
if (!empty($excluded_types)) {
$where = " n.type NOT IN ('". join("','", $excluded_types) ."') ";
return array('where' => $where);
}
}
}
Note : This is for Drupal 5.x
R.Navaneethakrishnan.
Wednesday, February 18, 2009
Module to backup drupal site
Backup and Migrate Module[http://drupal.org/project/backup_migrate]. is to take backup your drupal site. :)
R.Navaneethakrishnan
R.Navaneethakrishnan
Drupal user table : Users list joined before a week
Drupal stores dates in Unix time format. To find email addresses for users who joined after a certain date Use SQL query in phpmyadmin and then exported into an OpenOffice spreadsheet:
SELECT mail, FROM_UNIXTIME(created) FROM users WHERE created > 1199145600 LIMIT 500;
That returns 500 emails from users who registered since January 1, 2008. The FROM_UNIXTIME(created) converts the Unix formatted time into something human-readable.
SELECT mail, FROM_UNIXTIME(created) FROM users WHERE created > 1199145600 LIMIT 500;
That returns 500 emails from users who registered since January 1, 2008. The FROM_UNIXTIME(created) converts the Unix formatted time into something human-readable.
Thursday, February 12, 2009
Effective Sample code to get category tree in drupal or php
<?php
echo '<select name="category">
<option value="0">Root</option>';
echo $allcats = getTree();
foreach($allcats as $key=>$value) {
echo "<option value='$key'>$value</option>";
}
echo '</select>';
$con = mysql_connect("localhost","root","password") or die();
mysql_select_db('test',$con)or die();
function getTree($id=0) {
static $cates = array();
static $times = 0;
$times++;
$result = mysql_query("SELECT category_id,category_name FROM recusrsive WHERE parent_id=$id ORDER BY category_name") or die();
while($row = mysql_fetch_assoc($result))
{
$cates[$row['category_id']] = str_repeat(" ",$times-1)."|-".$row['category_name'];
getTree($row['category_id']);
}
$times--;
return $cates;
}
?>
Table Content
category_id parent_id category_name
1 0 CMS
2 0 Blogs
3 0 Forums
4 0 E-Commerce
5 1 Joomla
6 1 Mambo
7 6 Templates
8 6 Mods/Components
9 4 OSCommerce
10 2 Wordpress
11 10 Themes
12 10 Plugins
echo '<select name="category">
<option value="0">Root</option>';
echo $allcats = getTree();
foreach($allcats as $key=>$value) {
echo "<option value='$key'>$value</option>";
}
echo '</select>';
$con = mysql_connect("localhost","root","password") or die();
mysql_select_db('test',$con)or die();
function getTree($id=0) {
static $cates = array();
static $times = 0;
$times++;
$result = mysql_query("SELECT category_id,category_name FROM recusrsive WHERE parent_id=$id ORDER BY category_name") or die();
while($row = mysql_fetch_assoc($result))
{
$cates[$row['category_id']] = str_repeat(" ",$times-1)."|-".$row['category_name'];
getTree($row['category_id']);
}
$times--;
return $cates;
}
?>
Table Content
category_id parent_id category_name
1 0 CMS
2 0 Blogs
3 0 Forums
4 0 E-Commerce
5 1 Joomla
6 1 Mambo
7 6 Templates
8 6 Mods/Components
9 4 OSCommerce
10 2 Wordpress
11 10 Themes
12 10 Plugins
Wednesday, February 11, 2009
To add taxonomy in drupal custom form.
By using taxonomy_form() we can form a texonomy in form.
For Ex:
$vid=4;
$category1 = taxonomy_form($vid, $value = 0, $help = NULL, $name = 'taxonomy');
$form['taxonomy_info']['taxonomy1'] = array(
'#type' => 'select',
'#title' => t('Terms for the Website'),
'#options' => $category1,
'#description' => t('Website category'),
);
R.Navaneethakrishnan
http://navaneethakrishnan-drupal.blogspot.com/
For Ex:
$vid=4;
$category1 = taxonomy_form($vid, $value = 0, $help = NULL, $name = 'taxonomy');
$form['taxonomy_info']['taxonomy1'] = array(
'#type' => 'select',
'#title' => t('Terms for the Website'),
'#options' => $category1,
'#description' => t('Website category'),
);
R.Navaneethakrishnan
http://navaneethakrishnan-drupal.blogspot.com/
Wednesday, January 28, 2009
Drupal Set Taxonomy Breadcrumb Programmatic
To Set Breadcrumb to Taxonomy Programmatically.
Add this code in Template.php
function theme_breadcrumb($breadcrumb) {
if (!empty($breadcrumb)) {
$cat = taxonomy_get_term(arg(1)) ;
if(taxonomy_get_parents($cat->tid) == array()) {
$breadcrumb[] = l($cat->name , str_replace(" ","-",(strtolower($cat->name))));
}
else {
$parent_term = taxonomy_get_parents($cat->tid);
foreach($parent_term as $key => $value){
$name = $parent_term[$key]->name;
}
$breadcrumb[] = l($name , str_replace(" ","-",(strtolower($name))));
$breadcrumb[] = l($cat->name , str_replace(" ","-",(strtolower($name))."/".str_replace(" ","-",(strtolower($cat->name)))));
}
}
}
Add this code in Template.php
function theme_breadcrumb($breadcrumb) {
if (!empty($breadcrumb)) {
$cat = taxonomy_get_term(arg(1)) ;
if(taxonomy_get_parents($cat->tid) == array()) {
$breadcrumb[] = l($cat->name , str_replace(" ","-",(strtolower($cat->name))));
}
else {
$parent_term = taxonomy_get_parents($cat->tid);
foreach($parent_term as $key => $value){
$name = $parent_term[$key]->name;
}
$breadcrumb[] = l($name , str_replace(" ","-",(strtolower($name))));
$breadcrumb[] = l($cat->name , str_replace(" ","-",(strtolower($name))."/".str_replace(" ","-",(strtolower($cat->name)))));
}
}
}
Tuesday, January 27, 2009
Drupal view count result
Code to drupal view count result
<?php
// Lift the globals into local scope to save typing $GLOBALS[''] every time...
global $pager_page_array, $pager_total_items, $pager_total, $current_view;
if ($pager_total[0] == 1) { // Number of pages == 1
echo "Showing <b>" . $pager_total_items[0] . "</b> results";
}else{
// Page number is zero-based (first page is 0)
// Multiply pager_limit by page number (eg 0, 15, 30) and add 1 to get first item
$start = 1 + ($pager_page_array[0] * $current_view->pager_limit);
// Multiply pager_limit by page number + 1 (eg 15, 30, 45) to get last item
$end = (1 + $pager_page_array[0]) * $current_view->pager_limit;
// Use total items count if this is less than that
if ($end > $pager_total_items[0]) $end = $pager_total_items[0];
echo "Showing $start-$end of <b>" . $pager_total_items[0] . "</b> results";
}
<?php
// Lift the globals into local scope to save typing $GLOBALS[''] every time...
global $pager_page_array, $pager_total_items, $pager_total, $current_view;
if ($pager_total[0] == 1) { // Number of pages == 1
echo "Showing <b>" . $pager_total_items[0] . "</b> results";
}else{
// Page number is zero-based (first page is 0)
// Multiply pager_limit by page number (eg 0, 15, 30) and add 1 to get first item
$start = 1 + ($pager_page_array[0] * $current_view->pager_limit);
// Multiply pager_limit by page number + 1 (eg 15, 30, 45) to get last item
$end = (1 + $pager_page_array[0]) * $current_view->pager_limit;
// Use total items count if this is less than that
if ($end > $pager_total_items[0]) $end = $pager_total_items[0];
echo "Showing $start-$end of <b>" . $pager_total_items[0] . "</b> results";
}
Monday, January 26, 2009
Floating Search Box in drupal
Code to Float search box in drupal....
Add IN page.tpl.php
<?php if ($search_box): ?>
<div id="float" >
<?php print $search_box ?>
</div>
<?php endif; ?>
Add IN any css file in your theme folder
#floater {
padding-bottom: 10px;
position: relative;
float: right;
margin-top: 10px;
width: 205px;
z-index: 2;
}
Add IN page.tpl.php
<?php if ($search_box): ?>
<div id="float" >
<?php print $search_box ?>
</div>
<?php endif; ?>
Add IN any css file in your theme folder
#floater {
padding-bottom: 10px;
position: relative;
float: right;
margin-top: 10px;
width: 205px;
z-index: 2;
}
Tuesday, January 20, 2009
drupal : count number of tables in database [mysql]
To count the number of tables in mysql database in cutom drupal page.
=======================================================
select count(*) from information_schema.tables where table_schema = 'intr';
=======================================================
=======================================================
select count(*) from information_schema.tables where table_schema = 'intr';
=======================================================
Split the p tag
<html>
<head>
<meta name="description" content="Split the <p> tag into some numbers" />
<meta name="keywords" content="Split the p tag ,Split the p tag using regular expression,Split the p tag using php " />
</head>
<body>
<?php
$inp = '<p>Paragraph1</p><p>Paragraph2</p><p>Paragraph3</p><p>Paragraph4</p><p>Paragraph5</p><p>Paragraph6</p>';
$expression = '/<p>(.*)<\/p>/';
preg_match_all($expression,$inp,$match);
$match = explode("</p><p>",$match[1][0]);
for($i=0;$i<2;$i++){
echo '<p>'.$match[$i].'</p>';
}
echo '=======================';
for($i=0;$i<count($match);$i++){
echo '<p>'.$match[$i].'</p>';
}
?>
</body>
</html>
<head>
<meta name="description" content="Split the <p> tag into some numbers" />
<meta name="keywords" content="Split the p tag ,Split the p tag using regular expression,Split the p tag using php " />
</head>
<body>
<?php
$inp = '<p>Paragraph1</p><p>Paragraph2</p><p>Paragraph3</p><p>Paragraph4</p><p>Paragraph5</p><p>Paragraph6</p>';
$expression = '/<p>(.*)<\/p>/';
preg_match_all($expression,$inp,$match);
$match = explode("</p><p>",$match[1][0]);
for($i=0;$i<2;$i++){
echo '<p>'.$match[$i].'</p>';
}
echo '=======================';
for($i=0;$i<count($match);$i++){
echo '<p>'.$match[$i].'</p>';
}
?>
</body>
</html>
Monday, January 19, 2009
Extract src from IMG tag using regular expression
Here is the code to extract src from IMG tag using regular expression. We can use this also in drupal
------------------------------------------------------------------------------------
<?php
$field_lead_image_fid = '<img src="src.jpg" alt="Get groomed for the industry" title="title" />';
$epression = "/<img src=\"(.*)\" alt=\"(.*)\" title=\"(.*)\" \/>/";
preg_match_all($epression,$field_lead_image_fid,$matches);
echo '<pre>'; print_r($matches); echo '</pre>';
?>
----------------------------------------------------------------------------------
Note:
$epression Order [ src, alt, title] should be same as in $field_lead_image_fid.
------------------------------------------------------------------------------------
<?php
$field_lead_image_fid = '<img src="src.jpg" alt="Get groomed for the industry" title="title" />';
$epression = "/<img src=\"(.*)\" alt=\"(.*)\" title=\"(.*)\" \/>/";
preg_match_all($epression,$field_lead_image_fid,$matches);
echo '<pre>'; print_r($matches); echo '</pre>';
?>
----------------------------------------------------------------------------------
Note:
$epression Order [ src, alt, title] should be same as in $field_lead_image_fid.
Thursday, January 8, 2009
google0e91f60caf156ad3.html
I've uploaded the file to http://navaneethakrishnan-drupal.blogspot.com/ and can view http://navaneethakrishnan-drupal.blogspot.com/2009/01/google0e91f60caf156ad3html.html in my browser.
Subscribe to:
Posts (Atom)