SQL / MySQL SERVER – Count Duplicate Records – Rows

In my previous article SQL SERVER – Delete Duplicate Records – Rows, we have seen how we can delete all the duplicate records in one simple query. In this article we will see how to find count of all the duplicate records in the table. Following query demonstrates usage of GROUP BY, HAVING, ORDER BY in one query and returns the results with duplicate column and its count in descending order.

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Database using simple PHP file without the help of shell access or phpmyadmin

<?php

backup_tables(‘hostname’,’username’,’password’,’db_name’);

/* backup the db OR just a table */

function backup_tables($host,$user,$pass,$name,$tables = ‘*’)

{

$return=”“;

$link = mysql_connect($host,$user,$pass);

if (!$link) {

    die(‘Could not connect: ’ . mysql_error());

}

mysql_select_db($name,$link);

//get all of the tables

if($tables == ‘*’)

{

$tables = array();

$result = mysql_query(‘SHOW TABLES’);

while($row = mysql_fetch_row($result))

{

$tables[] = $row[0];

}

#

}

else

{

$tables = is_array($tables) ? $tables : explode(‘,’,$tables);

}

//cycle through

foreach($tables as $table)

{

$result = mysql_query(‘SELECT * FROM ‘.$table);

$num_fields = mysql_num_fields($result);

//$return.= ‘DROP TABLE ‘.$table.’;’;

$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));

$return.= “nn”.$row2[1].”;nn”;

for ($i = 0; $i < $num_fields; $i++) 

{

while($row = mysql_fetch_row($result))

{

$return.= ‘INSERT INTO ‘.$table.’ VALUES(‘;

for($j=0; $j<$num_fields; $j++) 

{

$row[$j] = addslashes($row[$j]);

$row[$j] = ereg_replace(“n”,”\n”,$row[$j]);

if (isset($row[$j])) { $return.= ‘”’.$row[$j].’”’ ; } else { $return.= ‘”“’; }

if ($j<($num_fields-1)) { $return.= ‘,’; }

}

$return.= “);n”;

}

}

$return.=”nnn”;

}

//save file

$handle = fopen(‘mmmm/db-backup-‘.time().’-‘.(md5(implode(‘,’,$tables))).’.sql’,’w+’);

fwrite($handle,$return);

fclose($handle);

}

?>

IE 7 CSS @font-face Issues

Use EOT font types to avoid these issues

We can easily convert the ttf fonts to eot fonts easily by these tools.

http://www.kirsle.net/wizards/ttf2eot.cgi

OR

http://www.fontsquirrel.com/fontface/generator

WordPress black and white thumbnail

First we need to create a new thumbnail using add_image_size(). We’ll use the same thumbnail settings from the WordPress Media page in the wp-admin. Add this to your functions.php file within the PHP tags:

add_action(‘after_setup_theme’,’bw_images_size’);function bw_images_size() {    $crop = get_option(‘thumbnail_crop’)==1 ? true : false;    add_image_size(‘thumbnail-bw’, get_option(‘thumbnail_size_w’), get_option(‘thumbnail_size_h’), $crop);}

With that in place, we can now add the following function which will automatically create a black and white thumbnail:

add_filter(‘wp_generate_attachment_metadata’,’bw_images_filter’);

function bw_images_filter($meta) {

    $file = wp_upload_dir();

    $file = trailingslashit($file[‘path’]).$meta[‘sizes’][‘thumbnail-bw’][‘file’];

    list($orig_w, $orig_h, $orig_type) = @getimagesize($file);

    $image = wp_load_image($file);

    imagefilter($image, IMG_FILTER_GRAYSCALE);

    //imagefilter($image, IMG_FILTER_GAUSSIAN_BLUR);

    switch ($orig_type) {

        case IMAGETYPE_GIF:

            $file = str_replace(“.gif”, “-bw.gif”, $file);

            imagegif( $image, $file );

            break;

        case IMAGETYPE_PNG:

            $file = str_replace(“.png”, “-bw.png”, $file);

            imagepng( $image, $file );

            break;

        case IMAGETYPE_JPEG:

            $file = str_replace(“.jpg”, “-bw.jpg”, $file);

            imagejpeg( $image, $file );

            break;

    }

    return $meta;

}

Whenever you upload an image, this function will create a new black and white thumbnail automatically. I also added a Gaussian Blur but commented it out. Remove those two slashes “//” to add a Gaussian Blur to your new black and white thumbnail. See otheravailable filters.

get_post_thumbnail();1 within your WordPress loop to display the two images for the effect:

Once you’ve added the above code, you can use.

get_post_thumbnail();1 within your WordPress loop to display the two images for the effect:

1if(function_exists(‘has_post_thumbnail’) && has_post_thumbnail()) {

    echo ‘<a href=”’.get_permalink().’” class=”fade-image”>’;

    the_post_thumbnail(‘thumbnail-bw’, array(‘class’=>’fade-image-a’));

    the_post_thumbnail(‘thumbnail’, array(‘class’=>’fade-image-b’));

    echo ‘</a>’;

}

WordPress custom fields – Post

We can display meta values like this

<p>Meta information for this post:</p>
<?php the_meta(); ?>

Output is 

<ul class='post-meta'>
<li><span class='post-meta-key'>your_key:</span> your_value</li>
</ul>

PHP Random password generator function

<?php
 
function generatePassword($length=9, $strength=0) {
	$vowels = 'aeuy';
	$consonants = 'bdghjmnpqrstvz';
	if ($strength & 1) {
		$consonants .= 'BDGHJLMNPQRSTVWXZ';
	}
	if ($strength & 2) {
		$vowels .= "AEUY";
	}
	if ($strength & 4) {
		$consonants .= '23456789';
	}
	if ($strength & 8) {
		$consonants .= '@#$%';
	}
 
	$password = '';
	$alt = time() % 2;
	for ($i = 0; $i < $length; $i++) {
		if ($alt == 1) {
			$password .= $consonants[(rand() % strlen($consonants))];
			$alt = 0;
		} else {
			$password .= $vowels[(rand() % strlen($vowels))];
			$alt = 1;
		}
	}
	return $password;
}

Export to CSV file and download – PHP

function CSVExport() {
$query=”SELECT id,email FROM `newsletterusers` WHERE `status` = 1”;
$sql_csv = mysql_query($query) or die(“Error: ” . mysql_error()); //Replace this line with what is appropriate for your DB abstraction layer

header(“Content-type:text/octect-stream”);
header(“Content-Disposition:attachment;filename=newsletterusers.csv”);
while($row = mysql_fetch_row($sql_csv)) {
print ‘”’ . stripslashes(implode(‘”,”’,$row)) . “"n”;
}
exit;
}

Facebook smiley codes

WordPress sitemap HTML using template

Create a template and Paste this

<h2 id="authors">Authors</h2>
<ul>
<?php
wp_list_authors(
  array(
    'exclude_admin' => false,
  )
);
?>
</ul>

<h2 id="pages">Pages</h2>
<ul>
<?php
// Add pages you'd like to exclude in the exclude here
wp_list_pages(
  array(
    'exclude' => '',
    'title_li' => '',
  )
);
?>
</ul>

<h2 id="posts">Posts</h2>
<ul>
<?php
// Add categories you'd like to exclude in the exclude here
$cats = get_categories('exclude=');
foreach ($cats as $cat) {
  echo "<li><h3>".$cat->cat_name."</h3>";
  echo "<ul>";
  query_posts('posts_per_page=-1&cat='.$cat->cat_ID);
  while(have_posts()) {
    the_post();
    $category = get_the_category();
    // Only display a post link once, even if it's in multiple categories
    if ($category[0]->cat_ID == $cat->cat_ID) {
      echo '<li><a href="'.get_permalink().'">'.get_the_title().'</a></li>';
    }
  }
  echo "</ul>";
  echo "</li>";
}
?>
</ul>

WordPress changing order of posts.

Paste this just before calling Loop

global $query_string;

query_posts($query_string.’&order=ASC’); 

The above code is to display older posts first (Just reverse of wordpress default posts display).