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 YourColumnCOUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC


Advertisements

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);

}

?>

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>’;

}

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;
}

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>