MySQL is the second most widely used open-source relational database management system in the world. It has become so popular because of its consistent fast performance, high reliability and ease of use. This article presents some of the best practices in MySQL.

1.Use CHAR (1) over VARCHAR(1)

If you are string a single character, use CHAR(1) instead of VARCHAR(1) because VARCHAR(1) will take extra byte to store information

2.Avoid using regional date formats

When you use DATETIME or DATE datatype always use YYYY-MM-DD date format or ISO date format that suits your SQL Engine. Other regional formats like DD-MM-YYY, MM-DD-YYYY will not be stored properly.

3.Always use proper datatype

Use datatypes based on the nature of data. If you use irrelevant datatypes it may consume more space or may lead to errors.

Example: Using varchar (20) to store date time values instead of DATETIME datatype will lead to errors during date time related calculations and there is also possible case of storing invalid data.

4.Use CHAR datatype to store only fixed length data

Example: Using char(1000) instead of varchar(1000) will consume more space if the length of data is less than 1000

5. Index key columns

Make sure to index the columns which are used in JOIN clauses so that the query returns the result fast.

If you use UPDATE statement that involves more than one table make sure that all the columns which are used to join the tables are indexed

6. Do not use functions over indexed columns

Using functions over indexed columns defeats the purpose of index. Suppose you want to get data where first two character of customer code is AK, do not write

SELECT columns FROM table WHERE left (customer_code,2)=’AK’

but rewrite it using

SELECT columns FROM table WHERE customer_code like ‘AK%’

which will make use of index which results to faster response time.

7. Use SELECT * only if needed

Do not just blindly use SELECT * in the code. If there are many columns in the table, all will get returned which will slow down the response time particularly if you send the result to a front end application.

Explicitly type out the column names which are actually needed.

8. Use ORDER BY Clause only if needed

If you want to show the result in front end application, let it ORDER the result set. Doing this in SQL may slow down the response time in the multi user environment.

9. Choose proper Database Engine

If you develop an application that reads data more often than writing (ex: search engine), choose MyISAM storage engine.

If you develop an application that writes data more often than reading (ex: real time bank transactions), choose INNODB storage engine.

Choosing wrong storage engine will affect the performance

10. Use EXISTS clause wherever needed

If you want to check the existence of data, do not use

If (SELECT count(*) from Table WHERE col=’smart’)>0

instead, use EXISTS clause

If EXISTS(SELECT * from Table WHERE col=’smart’)

which is faster in response time.

MySQL Can aborting a query with Ctrl-C harm the table?

Q :Can aborting queries harm the column that the query was operating on? If so, how can I recover from the problem?

A : If the transaction wasn’t completed, no. MySQL does ALTER TABLE by creating a new table and then updates the reference to the old table. You shouldn’t have altered your original table if you interrupted the original ALTER TABLE query.

The easiest way to terminate a renegade query is to use the MySQL shell as the root user:

SHOW PROCESSLIST;
This will give you a list of the current connections and a process ID for each one. To terminate any given query, such as number 19, use:

KILL 19;
Usually this will undo and roll back the query.

Validate Real Email Address Using MX record? send test mail to that real email address and if your message doesn’t bounce and it is safe to assume* that the address is real email address.

1.Ping an Email Address to Validate it! 

When you send an email to someone, the email message goes first to an SMTP server which then looks for the MX (Mail Exchange) records of the email recipient’s domain.

2.Enable telnet in Windows. Or if you already have the PuTTY utility, skip this step.

3.Open the command prompt and type the following command:

nslookup –type=mx gmail.com
This command will extract and list the MX records of a domain as shown below. Replace gmail.com with the domain of the email address that you are trying to verify.

gmail.com MX preference=30, exchanger = alt3.gmail-smtp-in.l.google.com
gmail.com MX preference=20, exchanger = alt2.gmail-smtp-in.l.google.com
gmail.com MX preference=5, exchanger = gmail-smtp-in.l.google.com
gmail.com MX preference=10, exchanger = alt1.gmail-smtp-in.l.google.com
gmail.com MX preference=40, exchanger = alt4.gmail-smtp-in.l.google.com

a: Connect to the mail server:

telnet gmail-smtp-in.l.google.com 25
b: Say hello to the other server

HELO
c: Identify yourself with some fictitious email address

mail from:<info@smartwebtutorials.com>

d: Type the recipient’s email address that you are trying to verify:

rcpt to:<abc123@gmail.com>

The server response for ‘rcpt to’ command will give you an idea whether an email address is valid or not. You’ll get an “OK” if the address exists else a 550 error like:

abc@gmail.com — The email account that you tried to reach does not exist.
support@gmail.com — The email account that you tried to reach is disabled.
That’s it! If the address is valid, you may perform a reverse email search to find the person behind the address

MX records to validate email addresses

function MXRecordVerify($emailaddress){
        list($user, $domain) = explode('@', $emailaddress);
        $arr= dns_get_record($domain,DNS_MX);
        if($arr[0]['host']==$domain&&!empty($arr[0]['target'])){
                return $arr[0]['target'];
        }
}
$emailaddress= 'info@smartwebtutorials.com';

if(MXRecordVerify($emailaddress)) {
        echo('This MX records exists; I will accept this email as valid.');
}
else {
        echo('No MX record exists;  Invalid email.');


Connect POP3 mailbox using PHP

Use the following process to connect to the inbox of a specific account:

$emailbox = imap_open(“{mail.domainname:110/pop3}INBOX”, ‘username’, ‘password’);

$emailinfo = imap_check($emailbox);

$nummessages = $emailinfo->Nmsgs;

if (stripos(imap_body($emailbox, $emailinfo->Nmsgs), ‘trigger phrase’) !== false) {

// Trigger detected

}

imap_close($emailbox);

imap_open – returns a stream handler, it creates a connection to the mail server and returns it’s handler (the parameters are pretty obvious, the first one is the server data: host, port,protocol and folder, the second is the mailbox username and the third one is the password)

imap_check – returns mailbox data, this object’s Nmsgs property returns the number of new messages

imap_body – return the message with the number from the second parameter

imap_close – closes the stream

 

 

Run below file in your root directory it will fix your issue.

<?php
require_once ‘app/Mage.php’;
umask( 0 );
Mage :: app( “default” );
Mage::log(“Started Rebuilding Search Index At: ” . date(“d/m/y h:i:s”));
$sql = “truncate catalogsearch_fulltext;”;
$mysqli = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);
$mysqli->query($sql);
$process = Mage::getModel(‘index/process’)->load(7);
$process->reindexAll();
Mage::log(“Finished Rebuilding Search Index At: ” . date(“d/m/y h:i:s”));

?>

 

Magento has a very powerful admin configuration system. It allows you to create config forms to be able to configure your custom modules from admin panel, also to enable users to modify it.

t’s quite simple to do – and it should only take you a few minutes to implement this. But, the options are endless. You can add all sorts of custom configurable options that you can edit in the admin panel very easily.

Here my simplified module:

local/SWT/Mymodule/etc/config.xml

<?xml version=”1.0″?>
<config>
<modules>
<SWT_Mymodule>
<version>0.1.0</version>
</SWT_Mymodule>
</modules>
<global>
<models>
<mymodule>
<class>SWT_Mymodule_Model</class>
</mymodule>
</models>
<helpers>
<mymodule>
<class>SWT_Mymodule_Helper</class>
</mymodule>
</helpers>
<resources>
<mymodule_setup>
<setup>
<module>SWT_Mymodule</module>
</setup>
<connection>
<use>core_setup</use>
</connection>
</mymodule_setup>
</resources>
</global>

<adminhtml>
<acl>
<resources>
<admin>
<children>
<system>
<children>
<config>
<children>
<mymodule>
<title>My Module Section</title>
</mymodule>
</children>
</config>
</children>
</system>
</children>
</admin>
</resources>
</acl>
<translate>
<modules>
<SWT_Mymodule>
<files>
<default>SWT.csv</default>
</files>
</SWT_Mymodule>
</modules>
</translate>
</adminhtml>

<default>
<mymodule>
<mymoduleconfig>
<host><![CDATA[localhost]]></host>
<active>1</active>
</mymoduleconfig>
</mymodule>
</default>
</config>

local/SWT/Mymodule/etc/system.xml

<?xml version=”1.0″ encoding=”UTF-8″?>
<config>
<tabs>
<mymodule translate=”label” module=”mymodule”>
<label>SWT</label>
<sort_order>200</sort_order>
</mymodule>
</tabs>
<sections>
<mymodule translate=”label” module=”mymodule”>
<label>My Module</label>
<tab>mymodule</tab>
<frontend_type>text</frontend_type>
<sort_order>102</sort_order>
<show_in_default>1</show_in_default>
<show_in_website>1</show_in_website>
<show_in_store>1</show_in_store>
<groups>
<schedep translate=”label”>
<label>My Module Settings</label>
<frontend_type>text</frontend_type>
<sort_order>1</sort_order>
<show_in_default>1</show_in_default>
<show_in_website>1</show_in_website>
<show_in_store>1</show_in_store>
<fields>

<active translate=”label”>
<label>Enabled</label>
<frontend_type>select</frontend_type>
<source_model>adminhtml/system_config_source_yesno</source_model>
<sort_order>1</sort_order>
<show_in_default>1</show_in_default>
<show_in_website>1</show_in_website>
<show_in_store>0</show_in_store>
</active>
</fields>
</schedep>
</groups>
</mymodule>
</sections>
</config>

local/SWT/Mymodule/Helper/Data.php

<?php

class SWT_Mymodule_Helper_Data extends Mage_Core_Helper_Abstract {

}

?>

local/SWT/Mymodule/Model/Mymodule.php

<?php

class SWT_Mymodule_Model_Mymodule extends Mage_Core_Model_Abstract
{

protected function _construct()
{
$this->_init(‘mymodule/mymodule’);
}

protected function _beforeSave()
{
return parent::_beforeSave();
}

}

 

Creating a PHP website template starting with HTML and CSS.

The actual template will be created mentioned below steps

Step 1 :

create a new folder and named folder “newwebsite”

Inside of this folder we are now going to create two new files. One is index.html and the other file is going to be named style.css

Step 2 :

Next we are going to create two more folders inside of our main folder “newwebsite”. The first folder is going to be named includes, and the second folder will be named variables.

Step 3 :

create a basic html website document

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<meta http-equiv="content-type" content="text/html; charset=utf-8" />

<meta name="description" content="" />

<meta name="keywords" content="" />

<meta name="author" content="" />

<link rel="stylesheet" type="text/css" href="style.css" media="screen" />

<title>SMARTWEBTUTORIALS Template</title>

</head>

	<body>

		<div id="wrapper">

<div id="header">

</div> <!-- end #header -->

<div id="nav">

</div> <!-- end #nav -->

<div id="content">

</div> <!-- end #content -->

<div id="sidebar">

</div> <!-- end #sidebar -->

<div id="footer">

</div> <!-- end #footer -->

		</div> <!-- End #wrapper -->

	</body>

</html>

Step 4:

open up the style.css file and add the divs we will be using.

This is the CSS I am adding to the stylesheet:

body {
background-color:#f1f1f1;
font-family: georgia,sans-serif;
color:#333;
margin:0;
padding:0;
}

#wrapper {
width:960px;
background-color:#f8f8f8;
margin:0 auto;
border-left:1px solid #ccc;
border-right:1px solid #ccc;
}

#header {
width:960px;
height:135px;
margin:0 auto;
margin-bottom:25px;
border-bottom:1px solid #ccc;
border-top:1px solid #ccc;
}

#header h2 {
padding:10px;
}

#nav {
width:960px;
height:40px;
border-bottom:1px solid #ccc;
}

#nav a {
display:inline;
padding:10px;
text-decoration:none;
background-color:#f1f1f1;
}

#nav a:hover {
background-color:#bababa;
height:80px;
}

#content {
width:675px;
float:left;
padding:10px;
}

#sidebar {
width:200px;
float:right;
margin-bottom:25px;
}

#sidebar a {
text-decoration:none;
}

#sidebar li {
list-style:none;
}

#footer {
clear:both;
width:960px;
height:135px;
border-top:1px solid #ccc;
}

#footer p {
padding:10px;
}

Step 5:

create some more files under includes folder

header.php, nav.php, sidebar.php, footer.php

Step 6:

 

sending Magento order confirmation emails for orders placed  using order id.

<?php
require ‘app/Mage.php’;
Mage::app(‘admin’)->setUseSessionInUrl(false);
//replace your own orders numbers here:
$test_order_id = ‘100000014’;

$order = Mage::getModel(‘sales/order’)->loadByIncrementId($test_order_id);

if ($order->getId()) {
try {
$order->sendNewOrderEmail();
echo “Order $orderIncrement successfully sent\n”;
} catch (Exception $e) {
echo $e->getMessage();
}
} else {
echo “Order $orderIncrement not found\n”;
}

 

?>

If you want to protect your Magento backend against attackers, you can follow below mentioned steps to change your admin url/path.

Follow these steps to change the admin URL/path.

Step1 :

First, open the local.xml configuration file in your favorite text editor, or use the Text Editor in the cPanel File Manager. The file is usually located in the app/etc/ directory under your Magento installation. Locate the following code segment:

<admin>
<routers>
<adminhtml>
<args>
<frontName><![CDATA[admin]]></frontName>
</args>
</adminhtml>
</routers>
</admin>

 

Now, replace admin with your new admin path. This should be something personal which is hard to guess, use only letters or numbers – no special characters, then save the file.

Step 2 – Refresh Cache

The final step is to refresh your cache. Use an FTP client to delete the content of the var/cache/ directory, or use the following SSH command.

rm -rf var/cache/*

Step 3

The change is now complete and you should try to log in via the new admin url – http://domain/adminpath/, replacing ‘adminpath’ with the path you chose in the step above. If everything went fine, you should now be presented with the Admin Panel login screen at the new URL. The old admin login URL should return a 404 error message.