Magento get products without category

Get a list of all orphaned products which are not associated with any category in Magento. Sometimes you may have missed to select categories for products by mistake when filling new product details, and now want to list them and assign categories to those products. You can’t get it from the Magento admin screen so you will need to run below MySQL query to get all the products without categories.

SELECT e.entity_id, e.sku, e.name FROM catalog_product_entity AS e LEFT JOIN catalog_category_product AS cp ON cp.product_id = e.entity_id WHERE cp.category_id IS NULL

Above sql query will simply bring the product ID, SKU, Name of products where category is NULL. You can add more columns in select (like e.*) to list full details of products.

MYSQL LIKE match whole word only

Mysql LIKE clause match exact whole word only while searching. Search the words with space in mysql database.

If you want to search for exact word using LIKE clause, you can’t do it just using percentage (%) sign around the keyword as it will search for partial words too. For example, the below query will match all the words which contain “some”:

SELECT * FROM tablename WHERE columnname LIKE '%some%';

It will find all the instances where it finds some, as a whole or a part. some, someone, something, handsome, isometric are all MATCHED using above query. Ofcourse there will be many results which are UNRELATED.

But what if you only want some, as an exact word WITH space and WITHOUT any extra heading and trailing characters?

This query will do exactly that:

SELECT * FROM tablename WHERE columnname = 'some' OR columnname LIKE '% some' OR columnname LIKE 'some %' OR columnname LIKE '% some %';

In above query we are making sure that all the some instances are retrieved, whether they are present in the START or in the END or in the MIDDLE of the columnname field.

pdo_mysql extension is not installed

PHP Mysql Error: pdo_mysql extension is not installed.

Magento needs PDO Mysql extension for database connection and related things, so if you don’t have pdo_mysql extension enabled Magento will complain about this and will not proceed further installation. If you are not sure what PDO is, it’s high time for you to look at http://php.net/manual/en/ref.pdo-mysql.php

Coming back to error, to resolve this you will need to edit your php.ini file where it says:
;extension=pdo_mysql.so

Just uncomment the line by removing front semincolon, so it becomes
extension=pdo_mysql.so

Save it and restart the server, the error should go.

If you are on Windows, then that line should read:

extension=php_pdo_mysql.dll

If you don’t find pdo_mysql in php.ini, install php5-mysql by running the command:

sudo apt-get install php5-mysql (on Ubuntu)

sudo yum install php-mysql (on Redhat, Fedora, CentOS)

Mysql root password reset or create

Mysql root password reset easily by following below instructions. For those who want to create (if no root user in mysql) or reset the password for the Root user in Mysql (in Linux), this post is definately going to help you. I had a tough time in getting this to work, so thought to share it with anyone facing the same problem.

You need to use –skip-grant-tables if you don’t know mysql password to login. You can use it like this:

/usr/bin/mysqld_safe –skip-grant-tables &

Once you run the above command on terminal, write mysql and hit enter. That’s because you already said Mysql that you want to skip all the privileges checking and allowing you with all the database access without password.

You should be inside mysql now. Now execute the command use mysql; because we are going to create/change the user in “mysql” database.
Continue reading »

Mysql: Find column name in any table(s) having it in whole database

There may be instances where we want to find in how many tables of some particular database some column (field) occurs. Or you know that some column field existed but don’t remember in which table of a database, what will you do? And if there are hundreds of tables, it would be nightmare to go through each table schema and searching.

Below Mysql query will get you all the tables where the specified column occurs in some database.

SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE '%column_name_to_search%';

Remember, don’t use % before column_name_to_search if you know the starting characters of that column. It slows down, as Mysql will not use any indexes for fields searched with leading wildcard (%).