Magento: Delete All Products Using PhpMyAdmin

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS = 1;

Using Videos as Backgrounds with Responsive

<style>
body {
    margin: 0;
    padding: 0;   
}
#container {
    position: relative;
    overflow: hidden;
}
</style>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script>
$(function() {
   
    // IE detect
    function iedetect(v) {

        var r = RegExp('msie' + (!isNaN(v) ? ('\\s' + v) : ''), 'i');
        return r.test(navigator.userAgent);
           
    }

    // For mobile screens, just show an image called 'poster.jpg'. Mobile
    // screens don't support autoplaying videos, or for IE.
    if(screen.width < 800 || iedetect(8) || iedetect(7) || 'ontouchstart' in window) {
   
        (adjSize = function() { // Create function called adjSize
           
            $width = $(window).width(); // Width of the screen
            $height = $(window).height(); // Height of the screen
           
            // Resize image accordingly
            $('#container').css({
                'background-image' : 'url(poster.jpg)',
                'background-size' : 'cover',
                'width' : $width+'px',
                'height' : $height+'px'
            });
           
            // Hide video
            $('video').hide();
           
        })(); // Run instantly
       
        // Run on resize too
        $(window).resize(adjSize);
    }
    else {

        // Wait until the video meta data has loaded
        $('#container video').on('loadedmetadata', function() {
           
            var $width, $height, // Width and height of screen
                $vidwidth = this.videoWidth, // Width of video (actual width)
                $vidheight = this.videoHeight, // Height of video (actual height)
                $aspectRatio = $vidwidth / $vidheight;
               
                       
            (adjSize = function() { // Create function called adjSize
                           
                $width = $(window).width(); // Width of the screen
                $height = $(window).height(); // Height of the screen
                           
                $boxRatio = $width / $height; // The ratio the screen is in
                           
                $adjRatio = $aspectRatio / $boxRatio; // The ratio of the video divided by the screen size
                           
                // Set the container to be the width and height of the screen
                $('#container').css({'width' : $width+'px', 'height' : $height+'px'});
                           
                if($boxRatio < $aspectRatio) { // If the screen ratio is less than the aspect ratio..
                    // Set the width of the video to the screen size multiplied by $adjRatio
                    $vid = $('#container video').css({'width' : $width*$adjRatio+'px'});
                } else {
                    // Else just set the video to the width of the screen/container
                    $vid = $('#container video').css({'width' : $width+'px'});
                }
                               
            })(); // Run function immediately
                       
            // Run function also on window resize.
            $(window).resize(adjSize);
                       
        });
    }
   
});
</script>
<body>
<div id="container">
    <video autoplay loop muted>
        <source src="video.mp4" type="video/mp4">       
    </video>   
</div>

How to Reduce Image File Size Using PHP code?

<?php
function compress($source, $destination, $quality) {

    $info = getimagesize($source);

    if ($info['mime'] == 'image/jpeg')
        $image = imagecreatefromjpeg($source);

    elseif ($info['mime'] == 'image/gif')
        $image = imagecreatefromgif($source);

    elseif ($info['mime'] == 'image/png')
        $image = imagecreatefrompng($source);

    imagejpeg($image, $destination, $quality);

    return $destination;
}

$source_img = '1.jpg';
$destination_img = 'destination .jpg';

$d = compress($source_img, $destination_img, 70);
?>

Continuous image scrolling using jquery

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js" type="text/javascript"></script>
<script src="http://www.jqueryscript.net/demo/jQuery-Endless-Div-Scroll-Plugin/endless_scroll_min.js" type="text/javascript"></script>

<div id="s1" class="is">
<a href="#"><img src="imgs/1.jpg" /></a>
<a href="#"><img src="imgs/2.jpg" /></a>
</div>

<script type="text/javascript">
$(window).load(function () {
$("#s1").endlessScroll({
width: '100%',
height: '100px',
steps: -2, speed: 40,
mousestop: true });
});
</script>

Top 80 + SQL Query Interview Questions and Answers with Examples

Top 80 + SQL Query Interview Questions and Answers with Examples

10 MySQL Database Interview Questions for Beginners and Intermediates

1. Define SQL?

Answer : SQL stands for Structured Query Language. SQL is a programming Language designed specially for managing data in Relational Database Management System (RDBMS).

2. What is RDBMS? Explain its features?

Answer : A Relational Database Management System (RDBMS) is the most widely used database Management System based on the Relational Database model.

Features of RDBMS

1. Stores data in tables.
2. Tables have rows and column.
3. Creation and Retrieval of Table is allowed through SQL.

3. What is Data Mining?

Answer : Data Mining is a subcategory of Computer Science which aims at extraction of information from set of data and transform it into Human Readable structure, to be used later.

4. What is an ERD?

Answer : ERD stands for Entity Relationship Diagram. Entity Relationship Diagram is the graphical representation of tables, with the relationship between them.

5. What is the difference between Primary Key and Unique Key?

Answer : Both Primary and Unique Key is implemented for Uniqueness of the column. Primary Key creates a clustered index of column where as an Unique creates unclustered index of column. Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.

6. How to store picture file in the database. What Object type is used?

Answer : Storing Pictures in a database is a bad idea. To store picture in a database Object Type ‘Blob’ is recommended.

7. What is Data Warehousing?

Answer : A Data Warehousing generally refereed as Enterprise Data Warehousing is a central Data repository, created using different Data Sources.

8. What are indexes in a Database. What are the types of indexes?

Answer : Indexes are the quick references for fast data retrieval of data from a database. There are two different kinds of indexes.

Clustered Index

1. Only one per table.
2. Faster to read than non clustered as data is physically stored in index order.

Non­clustered Index

1. Can be used many times per table.
2. Quicker for insert and update operations than a clustered index.

9. How many TRIGGERS are possible in MySql?

Answer : There are only six triggers are allowed to use in MySQL database and they are.

1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete

10. What is Heap table?

Answer : Tables that are present in the memory are called as HEAP tables. These tables are commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.

How do I redirect my site using a .htaccess file?

301 (Permanent) Redirect: Point an entire site to a different URL on a permanent basis. This is the most common type of redirect and is useful in most situations. In this example, we are redirecting to the "mt-example.com" domain:

# This allows you to redirect your entire website to any other domain
Redirect 301 / http://mt-example.com/

302 (Temporary) Redirect: Point an entire site to a different temporary URL. This is useful for SEO purposes when you have a temporary landing page and plan to switch back to your main landing page at a later date:

# This allows you to redirect your entire website to any other domain
Redirect 302 / http://mt-example.com/

Redirect index.html to a specific subfolder:

# This allows you to redirect index.html to a specific subfolder
Redirect /index.html http://example.com/newdirectory/

Redirect an old file to a new file path:

# Redirect old file path to new file path
Redirect /olddirectory/oldfile.html http://example.com/newdirectory/newfile.html

 Redirect to a specific index page:

# Provide Specific Index Page (Set the default handler)
DirectoryIndex index.html

Mysql order by specific ID values

1). select * from table_name order by case when id=8 then 0 else id end

2). select * from table_name order by field(8,id) desc,id

Imported products do not show up in frontend

First check this option in Product option

• are enabled
• have stock quantity > 0
• have stock availability = In Stock
• have visibility = "Catalog, search"
• have the correct website assigned to them
• have the right tax class associated with them

Solution 1 : Manage Products > Select All, then select the action Update Attributes and add the products to the correct website(s).
Left side Display this information :  
Products Information
Attributes
Inventory
Websites <-- select this option

And select Main website under the Add Product To Websites 
 

Don't forget to rebuild the indexes.


Solution 2 :

<?php
require_once('app/Mage.php');
umask(0);
Mage::app('admin');

$website_ids = array(1, 2); // I'm assuming your website IDs are 1 and 2.

$product_collection = Mage::getModel('catalog/product')->getCollection();
foreach($product_collection as $product) {
    $product->setWebsiteIds($website_ids);
    $product->save();
}

?>

Format price in the current locale and currency

Unformatted and formatted:

$price = $product->getPrice();
$formatted = Mage::helper('core')->currency($price, true, false);

Magento – current store currency details – currency code, currency symbol, currency name

To get the current store currency details eg. Currency code, currency symbol, currency name use the following code.
This code returns the current store currency details.

// store currency code eg. USD, INR
$currency_code = Mage::app()->getStore()->getCurrentCurrencyCode();

// store currency symbol eg. $ 
$currency_symbol = Mage::app()->getLocale()->currency( $currency_code )->getSymbol();

// store currency name eg. Indian Rupee
$currency_name = Mage::app()->getLocale()->currency( $currency_code )->getName();

This code returns the current store currency details. - See more at: http://www.techdilate.com/code/magento-current-store-currency-details-currency-code-currency-symbol-currency-name/#sthash.hwglkaVw.dpuf

This code returns the current store currency details. - See more at: http://www.techdilate.com/code/magento-current-store-currency-details-currency-code-currency-symbol-currency-name/#sthash.hwglkaVw.dpuf

This code returns the current store currency details. - See more at: http://www.techdilate.com/code/magento-current-store-currency-details-currency-code-currency-symbol-currency-name/#sthash.hwglkaVw.dpuf

WooCommerce : How to Disable Shopping Cart Function

Step : 1 :-  Open header.php file of your current theme and Add this css code.

/*disabled shopping cart function for woocommerce*/

#wrap_all .sub_menu {

 display:none;

}

 

.woocommerce-message {

 display:none;

}

 

.thumbnail_container div.thumbnail_container_inner a.product_type_variable,

.thumbnail_container div.thumbnail_container_inner a.product_type_simple {

 display:none;

}

 

.quantity {

 display:none;

}

 

.summary button {

 display:none;

}

 

.summary button[type="submit"] {

 display:none;

}

 

.cart_dropdown {

 display:none;

}

 

/*Custom styling for Mystile theme*/

ul.nav li.cart a,

ul.nav li.checkout a {

display:none;

}

  

Step : 2 :-  Open function.php file of your current theme and Add this hook code.

 

// Remove add to cart button on the navigation bar

remove_action( 'woo_nav_after', 'wootique_cart_button', 10);

remove_action( 'woo_nav_after', 'wootique_checkout_button', 20);

 

// Remove add to cart button from the product loop

remove_action( 'woocommerce_after_shop_loop_item', 'woocommerce_template_loop_add_to_cart', 10, 2);

 

// Remove add to cart button from the product details page

remove_action( 'woocommerce_before_add_to_cart_form', 'woocommerce_template_single_product_add_to_cart', 10, 2);

 

//disabled actions (add to cart, checkout and pay)

remove_action( 'init', 'woocommerce_add_to_cart_action', 10);

remove_action( 'init', 'woocommerce_checkout_action', 10 );

remove_action( 'init', 'woocommerce_pay_action', 10 );

 

// check for clear-cart get param to clear the cart
add_action( 'init', 'woocommerce_clear_cart_url' );
function woocommerce_clear_cart_url()
{   
    global $woocommerce;
    $woocommerce->cart->empty_cart();   
}

 

Create a WordPress custom post type

In your theme’s functions.php file, insert the following code:
  
 // Create out post type
    add_action( 'init', 'create_post_type' );
    function create_post_type() {
            $args = array(
            'labels' => post_type_labels( 'Book' ),
            'public' => true,
            'publicly_queryable' => true,
            'show_ui' => true,
            'show_in_menu' => true,
            'query_var' => true,
            'rewrite' => true,
            'capability_type' => 'post',
            'has_archive' => true,
            'hierarchical' => false,
            'menu_position' => null,
            'supports' => array('title',
                'editor',
                'author',
                'thumbnail',
                'excerpt',
                'comments'
            )
        );
   
            register_post_type( 'book', $args );
    }
   
    // A helper function for generating the labels
    function post_type_labels( $singular, $plural = '' )
    {
        if( $plural == '') $plural = $singular .'s';
     
        return array(
            'name' => _x( $plural, 'post type general name' ),
            'singular_name' => _x( $singular, 'post type singular name' ),
            'add_new' => __( 'Add New' ),
            'add_new_item' => __( 'Add New '. $singular ),
            'edit_item' => __( 'Edit '. $singular ),
            'new_item' => __( 'New '. $singular ),
            'view_item' => __( 'View '. $singular ),
            'search_items' => __( 'Search '. $plural ),
            'not_found' =>  __( 'No '. $plural .' found' ),
            'not_found_in_trash' => __( 'No '. $plural .' found in Trash' ),
            'parent_item_colon' => ''
        );
    }
   
    //add filter to ensure the text Book, or book, is displayed when user updates a book
    add_filter('post_updated_messages', 'post_type_updated_messages');
    function post_type_updated_messages( $messages ) {
            global $post, $post_ID;
   
            $messages['book'] = array(
                    0 => '', // Unused. Messages start at index 1.
                    1 => sprintf( __('Book updated. <a href="%s">View book</a>'), esc_url( get_permalink($post_ID) ) ),
                    2 => __('Custom field updated.'),
                    3 => __('Custom field deleted.'),
                    4 => __('Book updated.'),
                    /* translators: %s: date and time of the revision */
                    5 => isset($_GET['revision']) ? sprintf( __('Book restored to revision from %s'), wp_post_revision_title( (int) $_GET['revision'], false ) ) : false,
                    6 => sprintf( __('Book published. <a href="%s">View book</a>'), esc_url( get_permalink($post_ID) ) ),
                    7 => __('Book saved.'),
                    8 => sprintf( __('Book submitted. <a target="_blank" href="%s">Preview book</a>'), esc_url( add_query_arg( 'preview', 'true', get_permalink($post_ID) ) ) ),
                    9 => sprintf( __('Book scheduled for: <strong>%1$s</strong>. <a target="_blank" href="%2$s">Preview book</a>'),
                    // translators: Publish box date format, see php.net/date
                    date_i18n( __( 'M j, Y @ G:i' ), strtotime( $post->post_date ) ), esc_url( get_permalink($post_ID) ) ),
                    10 => sprintf( __('Book draft updated. <a target="_blank" href="%s">Preview book</a>'), esc_url( add_query_arg( 'preview', 'true', get_permalink($post_ID) ) ) ),
            );
   
            return $messages;
    }

Database backup using PHP

<?php
define('BACKUP_DIR', './backups' ) ;
define('HOST', 'localhost' ) ;
define('USER', 'root' ) ;
define('PASSWORD', 'pass' ) ;
define('DB_NAME', 'dbname' ) ;

$fileName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'.sql' ;
if(function_exists('max_execution_time')) {
if( ini_get('max_execution_time') > 0 )     set_time_limit(0) ;
}

// Check if directory is already created and has the proper permissions
if (!file_exists(BACKUP_DIR)) mkdir(BACKUP_DIR , 0700) ;
if (!is_writable(BACKUP_DIR)) chmod(BACKUP_DIR , 0700) ;

// Create an ".htaccess" file , it will restrict direct accss to the backup-directory .
$content = 'deny from all' ;
$file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
$file->fwrite($content) ;

$mysqli = new mysqli(HOST , USER , PASSWORD , DB_NAME) ;
if (mysqli_connect_errno())
{
   printf("Connect failed: %s", mysqli_connect_error());
   exit();
}
 // Introduction information
 $return .= "--\n";
$return .= "-- A Mysql Backup System \n";
$return .= "--\n";
$return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "\n\n\n";
$return = "--\n";
$return .= "-- Database : " . DB_NAME . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------\n";
$return .= "-- ---------------------------------------------------\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."\n" ;
$tables = array() ;
// Exploring what tables this database has
$result = $mysqli->query('SHOW TABLES' ) ;
// Cycle through "$result" and put content into an array
while ($row = $result->fetch_row())
{
$tables[] = $row[0] ;
}
// Cycle through each  table
 foreach($tables as $table)
 {
// Get content of each table
$result = $mysqli->query('SELECT * FROM '. $table) ;
// Get number of fields (columns) of each table
$num_fields = $mysqli->field_count  ;
// Add table information
$return .= "--\n" ;
$return .= '-- Tabel structure for table `' . $table . '`' . "\n" ;
$return .= "--\n" ;
$return.= 'DROP TABLE  IF EXISTS `'.$table.'`;' . "\n" ;
// Get the table-shema
$shema = $mysqli->query('SHOW CREATE TABLE '.$table) ;
// Extract table shema
$tableshema = $shema->fetch_row() ;
// Append table-shema into code
$return.= $tableshema[1].";" . "\n\n" ;
// Cycle through each table-row
while($rowdata = $result->fetch_row())
{
// Prepare code that will insert data into table
$return .= 'INSERT INTO `'.$table .'`  VALUES ( '  ;
// Extract data of each row
for($i=0; $i<$num_fields; $i++)
{
$return .= '"'.$rowdata[$i] . "\"," ;
 }
 // Let's remove the last comma
 $return = substr("$return", 0, -1) ;
 $return .= ");" ."\n" ;
 }
 $return .= "\n\n" ;
}
// Close the connection
$mysqli->close() ;
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ;
$return .= 'COMMIT ; '  . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ;
//$file = file_put_contents($fileName , $return) ;
$zip = new ZipArchive() ;
$resOpen = $zip->open(BACKUP_DIR . '/' .$fileName.".zip" , ZIPARCHIVE::CREATE) ;
if( $resOpen ){
$zip->addFromString( $fileName , "$return" ) ;
    }
$zip->close() ;
$fileSize = get_file_size_unit(filesize(BACKUP_DIR . "/". $fileName . '.zip')) ;
$message = <<<msg
  <h2>BACKUP  completed ,</h2>

  the archive has the name of  : <b>  $fileName  </b> and it's file-size is :   $fileSize  .

 This zip archive can't be accessed via a web browser , as it's stored into a protected directory .

  It's highly recomended to transfer this backup to another filesystem , use your favorite FTP client to download the archieve .
msg;
echo $message ;

// Function to append proper Unit after file-size .
function get_file_size_unit($file_size){
switch (true) {
    case ($file_size/1024 < 1) :
        return intval($file_size ) ." Bytes" ;
        break;
    case ($file_size/1024 >= 1 && $file_size/(1024*1024) < 1)  :
        return intval($file_size/1024) ." KB" ;
        break;
    default:
    return intval($file_size/(1024*1024)) ." MB" ;
}
}
?>

Database Clone using PHP

<?php
    $source_db='source';
    $target_db='target';
    
    $server='localhost';
    $user='root';
    $password='';
  
    mysql_connect($server,$user,$password);
  
   if(mysql_select_db($target_db))
   {
       $sql = "DROP DATABASE ".$target_db;
       mysql_query($sql) or die(mysql_error());
   }
  
   if(!mysql_select_db($target_db))  
   {
       $sql = "CREATE DATABASE ".$target_db;
       mysql_query($sql) or die(mysql_error());
   }
   
  
    mysql_select_db($source_db) or die(mysql_error());

    // Get names of all tables in source database
    $result=mysql_query("show tables");
    while($row=mysql_fetch_array($result)){
        $name=$row[0];
        $this_result=mysql_query("show create table $name");
        $this_row=mysql_fetch_array($this_result);
        $tables[]=array('name'=>$name,'query'=>$this_row[1]);
    }

    // Connect target database to create and populate tables
    mysql_select_db($target_db) or die(mysql_error());

    $total=count($tables);
    for($i=0;$i < $total;$i++){
        $name=$tables[$i]['name'];
        $q=$tables[$i]['query'];
         echo $i.">>>".$total;
        echo "<br>";
        mysql_query($q);
        mysql_query("insert into $name select * from $source_db.$name")  or die(mysql_error());
    }
    
?>