How to change the default collation of a database ?

change database collation:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_general_ci;

change table collation:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;


change column collation:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;

Enable Multilingual Support in Mysql

Snippets to Support Multilingual  in MySQL

$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
$mysqli->set_charset("utf8");  

Resolved : codeigniter call to member function on non object

To Resolve the error add  one more check before looping through the data

Check Added (  foreach ($query && $query->result() as $row)  )

Final Code.

$query = $this->db->query("YOUR QUERY");

foreach ($query  && $query->result() as $row)

{

        echo $row->title;

        echo $row->name;

        echo $row->body;

}

What is the difference between single-quoted and double-quoted strings in PHP?

pted

PHP strings can be specified not just in two ways, but in four ways. 1.    Single quoted strings will display things almost completely "as is." Variables and most escape sequences will not be interpreted. The exception is that to display a literal single quote, you can escape it with a back slash \', and to display a back slash, you can escape it with another backslash \\ (So yes, even single quoted strings are parsed). 2.    Double quote strings will display a host of escaped characters (including some regexes), and variables in the strings will be evaluated. An important point here is that you can use curly braces to isolate the name of the variable you want evaluated. For example let's say you have the variable $type and you what to echo "The $types are" That will look for the variable $types. To get around this use echo "The {$type}s are" You can put the left brace before or after the dollar sign. Take a look at string parsing to see how to use array variables and such. 3.    Heredoc string syntax works like double quoted strings. It starts with <<<. After this operator, an identifier is provided, then a newline. The string itself follows, and then the same identifier again to close the quotation. You don't need to escape quotes in this syntax. 4.    Nowdoc (since PHP 5.3.0) string syntax works essentially like single quoted strings. The difference is that not even single quotes or backslashes have to be escaped. A nowdoc is identified with the same <<< sequence used for heredocs, but the identifier which follows is enclosed in single quotes, e.g. <<<'EOT'. No parsing is done in nowdoc. Speed: I would not put too much weight on single quotes being faster than double quotes. They probably are faster in certain situations. Here's an article explaining one manner in which single and double quotes are essentially equally fast since PHP 4.3 (Useless Optimizations toward the bottom, section C). Also, this benchmarks page has a single vs double quote comparison. Most of the comparisons are the same. There is one comparison where double quotes are slower than single quotes.

How to prevent SQL-injection in PHP?

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

1. Using PDO (for any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   
   $stmt->execute(array('name' => $name));
   
   foreach ($stmt as $row) {
       // do something with $row
   }

2. Using MySQLi (for MySQL):

   $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
   $stmt->bind_param('s', $name);
   
   $stmt->execute();
   
   $result = $stmt->get_result();
   while ($row = $result->fetch_assoc()) {
       // do something with $row
   }

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (e.g. pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And it gives the developer the chance to catch any error(s) which are thrown as PDOExceptions.

What is mandatory however is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN.

Explanation

What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit with using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array('column' => $unsafeValue));

Can Prepared Statements Be Used For Dynamic Queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
  // $dir can only be 'DESC' or 'ASC'
$dir = !empty($direction) ? 'DESC' : 'ASC'; 

Migrating WordPress To A New Server

When you are developing any website you will always have different environments for your website, the number of environments you need will depend on the size of the project and how many people are involved in the project.

The main environments you will find in any website project are:

• Development Server - The developers local machine to development the website.
• Testing Server - Once development is finished this will be where all testing takes place.
• User Acceptance Testing Server - Where the client will review the changes to the website.
• Production Server - The live website.

The two environments you will always need to have is a development environment and a production environment. You will normally only have these two environments if the client doesn't need to view the site before it goes live. This means that once the developer has finished working on the site they can simply put this onto the production server. These are normally for the very basic of projects where the developer can simply test on their local development server before going live.

Database Config

In a previous article we discussed about how you can change your wp-config.php file to handle multiple environments and change the database settings of the content. The way we do this is by adding a switch in the wp-config.php to change the database config depending on the server name.

switch( $_SERVER['SERVER_NAME'] )
{
    case 'dev':
  // Dev Environment
 define( 'DB_NAME', 'project_dev' );
 define( 'DB_USER', 'project_dev_user' );
 define( 'DB_PASSWORD', 'password' );
 define( 'DB_HOST', 'localhost' );
 
 define( 'WP_HOME', 'http://project.dev');
 define( 'WP_SITEURL', WP_HOME);
    break;

    case 'uat':
 // UAT Environment
 define( 'DB_NAME', 'project_uat' );
 define( 'DB_USER', 'project_uat_user' );
 define( 'DB_PASSWORD', 'password' );
 define( 'DB_HOST', 'localhost' );
 
 define( 'WP_HOME', 'http://project.uat'); 
 define( 'WP_SITEURL', WP_HOME);
    break;

    case 'live':
 // Production Environment
 define( 'DB_NAME', 'project_live' );
 define( 'DB_USER', 'project_live_user' );
 define( 'DB_PASSWORD', 'password' );
 define( 'DB_HOST', 'localhost' );
 
 define( 'WP_HOME', 'http://project.com'); 
 define( 'WP_SITEURL', WP_HOME); 
    break;
}

Export Database

To export the database you can either use the command line and export a file from there by using the following command

mysqldump -p -u username -h hostname database_name > dbname.sql

Or you can use PHPMyAdmin GUI and export the database from here, if you have PHPMyAdmin installed on your server then you'll most likely be able to get to it by going to http://example.com/phpmyadmin, where you will be presented with a login screen to get access to your database.

Once you have logged in you can export the database by clicking on the export button in the top menu.

This screen will download a sql file of all the data and table structure in the database. You can now use this database file to import into the new database on the other server with all the content and settings from the test environment.

Import Database On New Server

To import your database file into the new server you again will have two options either the command line import or through the PHPMyAdmin GUI. The problem that you might face with using PHPMyAdmin is the upload limit on your application, by default this is most likely set at 2MB therefore if your database file is more than this you will have to use the command line to import your database file.

To import using the command line you need to upload your SQL data file to your server you can do this by using FTP and place your data file in a non-public folder.

Then you can use the following command to import the file into your database.

mysql -p -u username -h hostname database_name < /var/data/backup/dbname.sql

Change Domain In Database

As we were working on our local machines the domain we use in the database will be different than the one used on the other servers. Therefore when you were testing on one environment you will have your development domain in the database for things such as links in the posts, post guids,HOME_URL, SITE_URL, user meta, comments.

This means you will need to replace the old domain and with your new domain, the quickest way of doing this is by running a MySQL search and replace on the old domain to the new domain. The following SQL queries can be ran on your database to change the domain URL in the database all you have to replace the olddomain.com with your test environment domain and replace thenewdomain.com with your name server domain.

UPDATE `wp_commentmeta` SET `meta_value` = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_comments` SET `comment_content` = replace(comment_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_options` SET `option_value` = replace(option_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_posts` SET `post_content` = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_posts` SET `guid` = replace(guid, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_postmeta` SET `meta_value` = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_usermeta` SET `meta_value` = replace(meta_value, 'olddomain.com', 'newdomain.com');

Multisite Tables

Within a multisite install you will have additional tables that you need to change the domain one, therefore if you have this setup you can use the following queries.

UPDATE `wp_blogs` SET `domain` = replace(domain, 'olddomain.com', 'newdomain.com');
UPDATE `wp_site` SET `domain` = replace(domain, 'olddomain.com', 'newdomain.com');
UPDATE `wp_sitemeta` SET `meta_value` = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

All-in-One WordPress Migration Plugin

 You can use the plugin All-in_one WordPress Migration to help move your theme files, media files, plugins and export your database.

The plugin allows you to export your database, media files, plugins, and themes. You can apply unlimited find and replace operations on your database and the plugin will also fix any serialization problems that occur during find/replace operations.

Plugin Download Link : https://wordpress.org/plugins/all-in-one-wp-migration/

Import single database from --all-databases dump

mysqldump output is just a set of SQL statements.

You can provide the desired database in the command line and skip the commands against the other databases using:

mysql -D mydatabase -o < dump.sql

This will only execute the commands when mydatabase is in use

You can add --disable-keys for avoid errors of foreign keys.

mysql -u user -D --disable-keys database -o <dump.sql

Get A List Of Registered Actions In WordPress

WordPress actions and filters is an event driven architecture that allow you to run specific code at certain times in your application. To do this WordPress uses two functions the add_action() to add new callback functions to an action, and the do_action() function which will run all the callback functions assigned to this action.

In a recent project I was testing some code that used AJAX to run an action in a WordPress plugin. The problem I was having was that this action wasn't being called when it should of been, because this was using AJAX I needed a way of finding out if the code that add the action was actually being ran.

So I needed a way of finding out if the action I add was registered with WordPress so it understood what code to run on this action.

Whenever you want to add an action to WordPress you need to use the function add_action(), the code underneath this will add a new element to an array called $wp_filter.

Then if you look at the function that runs the actions do_action() function this will search for the action in the same $wp_filter variable. Which means all the registered actions will be added to this array, so if you want to view what actions have been registered at a certain time in your code you simply have to print out the elements in this array.

global $wp_filter;

echo '<pre>';
print_r($wp_filter);
echo '</pre>';

Debugging In WordPress

Debugging your website is very important for any project, you will need to be able to debug your site in all the difference environments from local to live. But depending on the environment you will want to debug in different ways.

For example on your local environment you may want to output all PHP errors on the screen to make it easier to fix the problems, but you will not want any of these errors to be displayed on the screen in your live environment. You won't want to display these errors to the visitors of your website, but you will also want to catch these errors and store them in a place you can see what is going wrong.

The advantage of storing these errors allows you to come in at a later date and fix any errors that might be happening on your live site.

WordPress comes with a built in debugging system thats allows you to catch/display errors in any way you want, changing these settings in the wp-config.php file allows you to change how you debug in different environments.

WP_DEBUG

The WP_DEBUG is a PHP constant that allows you to put WordPress into debug mode, when WordPress is in debug mode it allows you to define if you want to output the error on the screen or into a debug log file.

If you want to turn on debug you need to add the following line in your wp-config.php file.

// Turn on wp_debug
define('WP_DEBUG', true);

// Turn off wp_debug
define('WP_DEBUG', false);

When WP_DEBUG is enabled it will override the default error_reporting settings in your php.ini file and change it to display all PHP errors, notices and warnings. Displaying errors will show problems that will break your code. Notices and warnings will display problems with the code that may not break the site but do not following correct PHP guidelines.

WP_DEBUG_LOG

If you want to log all the errors into a file so you can investigate them later then you should use the constant WP_DEBUG_LOG in your wp-config.php file. When this constant is set to true it will output all errors into a file debug.log which will be stored inside the wp-content folder.

define('WP_DEBUG_LOG', true);

WP_DEBUG_DISPLAY

By default the WordPress errors will be displayed on the screen, if you are just logging the errors in a debug.log file then you might want to turn off displaying the errors on the screen but setting the constant to false.

define('WP_DEBUG_DISPLAY', false);

SCRIPT_DEBUG

By default WordPress core will use minified versions of CSS and JS files, if you want to change this to make WordPress use the full dev version of the CSS and JS then you can set the SCRIPT_DEBUGvariable to true.

define('SCRIPT_DEBUG', true);

SAVEQUERIES

To debug database queries you can use this constant variable SAVEQUERIES, this will store each query that is made on the database and how long it takes to execute. Be aware that this can slow down your site so only make sure this is only turned on while debugging.

define('SAVEQUERIES', true);

To check on these queries you can access them in the global variable $wpdb.

echo '<pre>';
var_dump($wpdb->queries);
echo '</pre>';

Changing Debug Depending On Environment

Understanding what these different settings means that we can make sure that we only do certain things depending on the environment.

Dev In development environment you would want to turn on debugging, display them on the screen and store the errors in the debug file.

UAT In a UAT environment you would still want debug on but you won't want to display these on the screen so we can store them in the debug file.

Live In the Live environment you would of hopefully resolved all the errors in your code so you can turn off debugging.

To change this in the wp-config.php you can use the following code.

switch( $_SERVER['SERVER_NAME'])
{
    // Dev
    case 'dev.example.com':
        define('WP_DEBUG', true);
        define('WP_DEBUG_LOG', true);

        define('ENV', 'dev');
    break;
    
    // UAT
    case 'uat.example.com':
        define('WP_DEBUG', true);
        define('WP_DEBUG_LOG', true);
        define('WP_DEBUG_DISPLAY', false);

        define('ENV', 'uat');
    break;
    
    // Live
    case 'live.example.com':
        define('WP_DEBUG', false);

        define('ENV', 'live');
    break;
}