Unicode error using PDO. Mysql Unicode problem

Are you having those ???? things on your webpage when something you entered on Mr mysql is unicode character?
Here is a fix for that

Assuming you are using PDO object in your Db Class..

$db = array containing information ....
$this->pdo = new PDO("mysql:host={$db['host']};dbname={$db['database']};charset=utf8", $db['username'], $db['password']);

Now the next task to to tell Mysql to honor unicodes:

$this->query('SET NAMES utf8');
$this->query('SET CHARACTER SET utf8');
$this->query('SET COLLATION_CONNECTION="utf8_general_ci"');

where your query function could look like

public function query($query, $params=array())
		if (!empty($this->pdo))
			catch(PDOException $exception)
				echo "error occured";
				//logit or whatever
		return $results;

Yea this would take care of the problem. There are certain things you have to do on the mysql database itself to handle unicode but I will not discuss those here
Hope it would help somebody..

Changing column datatype in mysql

From time to time I have been changing the column datatype for different reasons. Majorly being for performance and efficiency and some other time based on how the mysql engines would be strong on some types vs the other
here is the simple command i use for changing the column datatype

alter table table_name modify column column_name datatypegoeshere

Allowing others to use mysql from user machine on ubuntu- simple one!

It is customary sometimes to share the part of the database from sandbox to a fellow developer or to access it from the other machine – umm.. is it not how the servers are doing it?? what are you talking about 😉

Anyway here is a simple note to make it happen.1. We need to bind the machine name for the server. to do that
open your my.cnf – inside the /etc/mysql/my.cnf for debianish machines and search for the
[mysqld] and add the following
bind-address = your ip address goes here
you can find your ip address from


then save the file and restart your mysql

sudo /etc/init.d/mysql restart

There are ways to assign for a specified user from the specified ip addresses as well.
Like if you have database db1 and user1 from ip address then

grant select, insert, update on db1.* to 'user1'@''

Newer version of mysql exists error while installing on Mac OS

I was reinstalling my mac machine and wanted to install my goodies [PHP, Apache, MySQL] on it and I come up with the newer version error when i try to install mysql.
here is what I gather from different sites and blogs:
Go to the terminal and issue these commands
sudo rm /usr/local/mysql*
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm /etc/my.cnf
sudo rm -rf /var/db/receipts/com.mysql.*

This has taken care of the error for me

Number of rows that would have been returned in case of Limit in mysql

The thing is, you would have sql that have LIMIT appended at the end like

select * from sometable limit 25

but what if you wanted to know how many rows would have been returned if it was not limited?
the trivial approach is

select count(*) from sometable

and then issuing query with limit on it.
This is slower solution.
here is the way to do it in a better way

select sql_calc_found_rows * from sometable limit 25;
select found_rows();

Right after execution the select with sql_calc_found_rows, we would get the ‘would have been’ value on the second query.

dumping mysql database ignoring some tables

Mysql comes with a handy tool for dumping database, like from remote server to local machine or vice versa.
Sometimes you might want to dump some tables but not the other here you go!

mysqldump -h[HOST] -u[USERNAME] --port=[PORT] -p --ignore [ignoretablename] databasename > databasename.sql

The database.sql would contain all the schema + data.
Then import it as

mysql -h[host] -u[username] -p databasename < databasename.sql

You can provide -p[password] or if you leave it as -p you would be prompted for password later which is a cool way to proceed.

Adding Unicode character to mysql from Java-hibernate

How to enable/add unicode character in hibernate with mysql

Unicode is ruling, it would come handy when working with non ASCII characters like when dealing with Chinese or Ethiopian alphabet amharic characters.

With internalization being the main concern in softwares, bit it website or mobile application, there is a chance you will need unicode in your application.

Hibernate is an ORM, object relational mapper, that is being used with Java and .net. It will allow to abstract all the database related stuffs with simple interface. In this unicode hibernate tutorial, I will show how you can insert, select update and delete records with unicode.

Why do I get No provider for Entity manager Error and how should I fix it

I will show the fix from the hibernate side for MySQL. But keep in mind that unicode characters have to be enabled on the database side as well with proper encoding.

See how you can avoid could not open hibernate session error

The solution for having unicode characters to be recognized as they are passing though the hibernate world would be done on the config file.

Enabling unicode character in hibernate

Here are the steps to follow:

1. go to your hibernate.cfg.xml file

2. make your the connection url something like: jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8 – make sure the the ampersand is the html-encoded ampersand.

Do you know you can have boilerplate java app with maven?

Find k complementary numbers – algorithm in Java

Adding NULL as value to mySql database

Have you wondered how to update/insert NULL as value to the existing row in mysql? Too simple:

UPDATE table_name SET field_name = NULL WHERE some_criteria 

Watch! there is no any quote on the NULL part, if you do so, it would be inserted/updated as string with value “NULL”

Using If else conditional statement on where condition mysql

Using if / else on the mysql would be much easier to express with simple query. Her we go:

Assume you have the following table:

| Field        | Type         | Null | Key | Default | Extra          |
| file_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| file_path    | text         | NO   |     | NULL    |                |
| file_name    | varchar(120) | NO   | MUL | NULL    |                |
| file_size    | bigint(20)   | YES  |     | NULL    |                |
| file_content | text         | NO   | MUL | NULL    |                |

Then you want to collect records either by file_id or by file_size based on the file_size, if the file size above some value, check the name of the file to contain xls, otherwise, consider only files that have file id more than y

select * from file where if (file_size > x, file_name like '%.xls', file_id > y)


PHP is displaying questionmark (???) for unicode

One of the fun things in php-mysql is working with Unicode characters. It is so cooked that, we should almost eat those with a little bit adjustment.
I am not going to tutor on how to use php-mysql unicode stuff – there are a bunch of ’em out there.
Rather I would discuss an error that would be common while doing your stuff..
Once you configure everything and put your unicode to mysql, you might see the output of it being questionmark on the browser – but you see the correct unicode in the database through the phpmyadmin.. it is this single line of code you would ever need…
mysql_query(“SET NAMES ‘utf8′”, conn); where the conn is the result of your connection – mysql_connect();
You can modify it accordingly, for example I am using an object oriented one so I can issue the query from my object – you would need to do this only once.

Happy unicoding..