database optimization and repair database if your database is corrupted database

How to optimize database?
Database Optimization
Repair Database if your database is corrupted

Database optimization is required to increase the speed of our application, however database optimization is not easy and we have to careful when we optimize any database.

if you are using the wordpress for your web-site then its really easy to optimize your wordpress database. as you know that wordpress is open source and there is lots of plugin which we need to install to get the functionality. same way there is one plugin named ‘Automatic Database Optimizing’ which you need to install and your database optimization will be done very quick and with accuracy.

Automatic Database Optimizing plugins also repair your database periodically, you just need to insert one line code in your wp-config.php which is located where you install wordpress blog. please insert below code at the end of the wp-config.php file.

if you forgot this and your database is corrupted then you can run the code manually as the file is located at /wp-admin/maint/repair.php. so if your site name is www.freelancephpdevelopment.com then you need to run the code like www.freelancephpdevelopment.com/wp-admin/maint/repair.php.

I Hope this will helpful to you.

How to sort a varchar field as numeric field in MySQL

How to sort a varchar field as numeric field in MySQL

How to sort a varchar field as integer field in MySQL

Sorting varchar field numerically in MySQL

MySQL provide the facility to sort the column in the SQL statement but some time we take field as a varchar and store integer or float values in that column, now when you sort it then it will sort it as alphabetically because we took that field type as varchar. now we need to sort it as numerically because we store the value in that field are integer or float.

we can do this using the type casting and update your SQL statement as below

SELECT * FROM table_name ORDER BY CAST(field_name as SIGNED INTEGER) ASC

Above statement sort your field as numerically even its varchar type, so this way you can sort string as numerically or sort string as number or sort string as numeric.

Some time solution is around us but we are not able to get it.

 

what is SQL Injection and SQL injection attacks

What is SQL Injection?
What is SQL injection attack?

When you are working with the SQL in your web-site then you need to careful on SQL injection, make sure that your programmer consider all SQL statements in that way that no one can hack your website using SQL injection. SQL injection is a technique used to hack your website, attacker use the non-validated input vulnerabilities and then pass it to SQL commands via a Web application or web-site for execution in the database. when any web application or web-site made by the programmer then attackers take advantage of that and pass the parameters in the SQL statement which is not done by the programmers, but they pass the parameters with the parameters set by the programmers, and because of that their parameters embed in SQL commands with the programmers parameters. On the result of this attacker/hackers can execute  SQL queries/commands with their conditions on the database server through the Web application or web-site.

All programmers make a query strings using the SQL statement and the parameters, when they need parameters in the web application then they make the sql statement run time and then pass it to the database server. so when they collect their parameter at that time the attackers/hackers pass their parameters and because of that its joint in the SQL statement, this is knows as the SQL Injection. i will show you this with example.

SQL Injection Example:

suppose your site made in php and php programmer made the login screen and take username and password from the end user to login, in this case he wrote below code to make SQL statement.

$SQL = “SELECT * FROM QATRICKS WHERE usename='”.$_REQUEST[“username”].”‘ AND password='”.$_REQUEST[“password”].”‘”;

Here when php programmer collect information from $_REQUEST parameter then attackers pass like as below

USERNAME : php
PASSWORD : qatricks AND password=’php freelancer’ AND password=’patel’

Now in this case SQL statement made wrong because attackers pass parameters in the passwords fields.

I hope this should be clear on the SQL Injection, and if you are going to make your web-site then you need to careful on this and have to inform your programmer so he will be more careful on the SQL Injection.


delete duplicate records in mysql table

How to delete duplicate records in mysql table?

 

if you want to delete the duplicate records from the mysql table then there is not any easy way to do this using mysql, but i find one way which is very easy and you can delete all duplicate records from the table. please follow below steps to remove the duplicate records from the table.

1) Create new table with the same fields which is in the table where you face issue with duplicate records.

2) Once the table created then use below SQL statement where you need to set some values as per your table.

INSERT INTO newtablename  (SELECT * FROM existingtablename GROUP BY field name)

3) In the above SQL statement change the tables name and specify the field name which have duplicate record, generally this field name is the field name on which you need to unique the table data.

4) Delete the existing table which have the duplicate data.

5) Rename the newtable which is created with your existing table name.

Thats it and you feel relax from the duplicate data issue.

How to Delete Duplicate Rows in a MySQL Database

When you are working with SQL and MySQL then its developer responsibility to make the perfect database structure and data. now if you have database and there is some redundant data in that database and you need to remove the redundant data from the table then you can do it using one SQL statement.

you can remove the duplicate data using Below SQL statement

DELETE FROM Tablename USING Tablename, Tablename as VirtualTable  WHERE (NOT Tablename.ID=VirtualTable.ID) AND (Tablename.Fieldname=VirtualTable.Fieldname)

Above SQL Statement is used to delete duplicate rows in table.

In the SQL Statement you need to alter the Tablename and Fieldname as per your Database

My SQL Insert Query Syntax

To insert data into MySQL table you would need to use SQL INSERT INTO command. You can insert data into MySQL table by using mysql> prompt or by using any script like PHP.

Syntax:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
Example:
Following example will create 2 records into posts_tbl table:
mysql> use POSTS;
Database changed
mysql> INSERT INTO posts_tbl 
     ->(post_title, post_author, submission_date)
     ->VALUES
     ->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl
     ->(post_title, post_author, submission_date)
     ->VALUES
     ->("Learn MySQL", "Abdul S", NOW());
NOTE:

Please note that all the arrow signs (->) are not part of SQL command they are indicating a new line and they are created automatically by MySQL prompt while pressing enter key without giving a semi colon at the end of each line of the command.

In the above example we have not provided tutorial_id because at the time of table create we had given AUTO_INCREMENT option for this field. So MySQL takes care of inserting these IDs automatically. Here NOW() is a MySQL function which returns current date and time.

Inserting Data Using PHP Script:

You can use same SQL INSERT INTO command into PHP function mysql_query() to insert data into a MySQL table.

Example:

This example will take three parameters from user and will insert them into MySQL table:

<html>
<head>
<title>Add New Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST['add']))
{
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

if(! get_magic_quotes_gpc() )
{
   $tutorial_title = addslashes ($_POST['tutorial_title']);
   $tutorial_author = addslashes ($_POST['tutorial_author']);
}
else
{
   $tutorial_title = $_POST['tutorial_title'];
   $tutorial_author = $_POST['tutorial_author'];
}
$submission_date = $_POST['submission_date'];

$sql = "INSERT INTO tutorials_tbl ".
       "(tutorial_title,tutorial_author, submission_date) ".
       "VALUES ".
       "('$tutorial_title','$tutorial_author','$submission_date')";
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="600" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="250">Tutorial Title</td>
<td>
<input name="tutorial_title" type="text" id="tutorial_title">
</td>
</tr>
<tr>
<td width="250">Tutorial Author</td>
<td>
<input name="tutorial_author" type="text" id="tutorial_author">
</td>
</tr>
<tr>
<td width="250">Submission Date [ yyyy-mm-dd ]</td>
<td>
<input name="submission_date" type="text" id="submission_date">
</td>
</tr>
<tr>
<td width="250"> </td>
<td> </td>
</tr>
<tr>
<td width="250"> </td>
<td>
<input name="add" type="submit" id="add" value="Add Tutorial">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>

While doing data insert its best practice to use function get_magic_quotes_gpc() to check if current configuration for magic quote is set or not. If this function returns false then use functionaddslashes() to add slashes before quotes.

You can put many validations around to check if entered data is correct or not and can take appropriate action.




find and replace the string in entire database

How to replace string in entire database?

if you want to find and replace keyword in entire database then there is not any functionality in the MySQL, of course you can find and replace keyword for particular fields in entire table records, but if you want to find and replace string or keyword in each field and each table then you can not do it, it does not mean that its not possible, below is the magic script written by me when i was stuck in this issue. here i share my code with you, i hope it will useful to all php developers and other developer who is not working with php can also generate code like this and resolve their issue.

Are you facing find and replace issue, here is the magic code:

 
<?php
 
    // Please define all your keyword or sting which you need to replace in array, there is no limitation on the length of array
    $keywords = array( 'example1' => 'demo1', 'example2' => 'demo2', 'example3' => 'demo3', 'example4' => 'demo4', 'example5' => 'demo5');
 
    $connection = mysql_connect( 'localhost', 'username', 'password' );
    if( ! $connection) {
        die( 'Could not connect: ' . mysql_error() );
    }
 
    $selectdatabase = mysql_select_db( 'databasename', $connection );
    if(! $selectdatabase ) {
        die( 'Can\'t select database: ' . mysql_error() );
    }
 
    $select_tables = 'SHOW TABLES';
    $results = mysql_query( $select_tables );
    while( $rows = mysql_fetch_row( $results ) ) {
        foreach( $rows as $table ) {
 
            $sql_columns = 'SHOW COLUMNS FROM ' . $table;
            $columns_result = mysql_query( $sql_columns);
            while( $columns_row = mysql_fetch_assoc( $columns_result ) ) {
 
                $column = $columns_row['Field'];
                $type = $columns_row['Type'];
 
                foreach( $keywords as $old_string => $new_string ) {
                        $replace_query = 'UPDATE ' . $table . 
                            ' SET ' .  $column . ' = REPLACE(' . $column . 
                            ', \'' . $old_string . '\', \'' . $new_string . '\')';
                        mysql_query( $replace_query );
                    }                
            }
        }
    }
 
    mysql_free_result( $columns_result );
    mysql_free_result( $results );
    mysql_close( $connection );
 
    echo 'Your Find and Replace Completed Successfully!';
 
?>

You can create new php file and put above code and set the database details, when you run this file its show message when complete. enjoy the magic……………..

how many storage engine in mysql

How Many Storage Engine in MySQL?

MySQL provide 8 type of storage engine which is as below:

1) MEMORY: Hash based, stored in memory, useful for temporary tables.
2) FEDERATED: Federated MySQL storage engine.
3) MyISAM: Default engine as of MySQL 3.23 with great performance.
4) BLACKHOLE: /dev/null storage engine (anything you write to it disappears).
5) MRG_MYISAM: Collection of identical MyISAM tables.
6) CSV: CSV storage engine.
7) ARCHIVE: Archive storage engine.
8) InnoDB:  Supports transactions, row-level locking, and foreign keys.

MySQL use MyISAM as a default storage engine if you do not select any storage engine.

differences between MyISAM and InnoDB Storage Engines in MySQL

What is Differences between MyISAM and InnoDB Storage Engines in MySQL?

MyISAM: Default engine as of MySQL 3.23 with great performance.
InnoDB : Supports transactions, row-level locking, and foreign keys.

When you are using mysql there is myisam and innodb storage engine, most of the developer do not know the difference between this 2 engine. below is the explanation for both storage engine, once you read this you get idea about the storage engine difference.

1) InnoDB supports transaction and its also supports transactions, commits, rollbacks, foreign keys and row-level locking. this is one of the most important feature and because of that this storage engine give high performance then the MyISAM.
2) MYISAM does not support the foreign key constraint and transaction and because of that its give lower performance then InnoDB.
3) MYISAM is faster then the InnoDB because its not support transactions, foreign keys but in case of performing the count operation it takes more time then the InnoDB.
4) InnoDB storage engine occupy more memory space then MyISAM.

I hope now you are clear on the difference between Myisam and InnoDB storage engine.

How to connect with database using php?

you can easily connect with the database using php, php provide library function to connect with database.
mysql_connect() function connect with mysql using a non-persistent MySQL connection.

when you use mysql_connect() function its returns the connection on success, or FALSE and an error on failure. You can hide the error output by adding an ‘@’ in front of the function name. it means if you write @mysql_connect() then its not show the error.

Syntax

below syntax you can find in the php manuals

resource mysql_connect ([ string $server = ini_get(“mysql.default_host”) [, string $username = ini_get(“mysql.default_user”) [, string $password = ini_get(“mysql.default_password”) [, bool $new_link = false [, int $client_flags = 0 ]]]]] )

below is for more understanding

mysql_connect(servername,username,password,linkname,userflag)

Servername : Optional Parameter. its Specifies the server to connect to, normally this is ‘localhost’.  you can also use a port number. for example hostname:port. Default value for this parameter is  “localhost:3306”
Username : Optional Parameter. its Specifies the username to log in to the database. Default value is the name of the user that owns the server process and its root user
Password : Optional Parameter. its Specifies the password to log in to the database. Default is blank “”, you can use the password if you set it.
Linkname : Optional Parameter. If a second call is made to mysql_connect() with the same arguments, no need to make new connection and use the existing established connection,  instead the identifier of the already opened connection will be returned
Userflag : Optional Parameter. this can be  a combination of the following constants:
1) MYSQL_CLIENT_SSL – Use SSL encryption
2) MYSQL_CLIENT_COMPRESS – Use compression protocol
3) MYSQL_CLIENT_IGNORE_SPACE – Allow space after function names
4) MYSQL_CLIENT_INTERACTIVE – Allow interactive timeout seconds of inactivity before closing the connection

Examples of mysql_connect() function:

Example 1:

<?php
$link = mysql_connect(‘localhost’, ‘mysql_user’, ‘mysql_password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_close($link);
?>

Example 2:

<?php
// we connect to example.com and port 3307
$link = mysql_connect(‘example.com:3307’, ‘mysql_user’, ‘mysql_password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_close($link);

// we connect to localhost at port 3307
$link = mysql_connect(‘127.0.0.1:3307’, ‘mysql_user’, ‘mysql_password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_close($link);
?>

Example 3 :

<?php
// we connect to localhost and socket e.g. /tmp/mysql.sock

// variant 1: omit localhost
$link = mysql_connect(‘:/tmp/mysql’, ‘mysql_user’, ‘mysql_password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_close($link);

// variant 2: with localhost
$link = mysql_connect(‘localhost:/tmp/mysql.sock’, ‘mysql_user’, ‘mysql_password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_close($link);
?>

Hope you are clear on the mysql_connect Function.