Tag: MySql

MySQL zerofill and lpad – Shape digits in DB

I’m working with an application to have the item code. Item code column is set as an INT(5) but not all the item code values are 5 digits. So I need to round off the item code based on 5-digits. If the item code is 199, it should be display as 00199. How to make …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/09/mysql-zerofill-and-lpad-shape-digits-in-db/

Best way to order the best matching records in MySQL

If we are searching a word (string) in the mysql table, usually we will use the below method: [sql] SELECT * FROM user WHERE name LIKE ‘%searchstring%’ ORDER BY name ASC; [/sql] If you search like this, It will order the result set based on the ASC of name. It will not order the result …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/08/best-way-to-order-the-best-matching-records-in-mysql/

How to use MySQL for case sensitive string comparison

By default MYSQL character set and collation are latin1 and latin1_swedish_ci, so non-binary string comparisons are case insensitive. This means that if you search with col_name LIKE ‘a%’, you get all column values that start with A or a. To make this search as case sensitive, make sure that one of the operands has a …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/07/how-to-use-mysql-for-case-sensitive-string-comparison/

phpmyadmin – Cannot start session without errors, please check errors

Unfortunately sometimes we get the problem with phpMyAdmin, here is the error from phpMyAdmin on ubuntu machine. phpMyAdmin – Error [code] Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly. [/code] To find out where the session.save path http://wiki.phpmyadmin.net/pma/session.save_path run this script …

Continue reading

Permanent link to this article: https://blog.openshell.in/2014/06/phpmyadmin-cannot-start-session-without-errors-please-check-errors/

How to use serialized object in Active Record using Rails?

Serializing Object Will help us to store multiple column values in single Database column and even making processing using that column simple. It helps to extended the table without adding new column in table. Because we can store multiple column values in single column as Hash value. So, It reduce the difficulty of retriving and …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/11/how-to-use-serialized-object-in-active-record-using-rails/

How to retrieve the last record in each group using mysql

In single SQL query itself we can retrieve the last record in each group. It will help us to perform action better, faster and simpler. In this post I will explain about you how to do it. Here is my table structure for your reference. [sql] desc post_status; +—————-+————-+——+—–+———+—————-+ | Field | Type | Null …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/10/how-to-retrieve-the-last-record-in-each-group-using-mysql/

How to Reset a WordPress Password from Mysql Server

Its essential thing to knowing, how to reset the wordpress password from Mysql server without following traditional method. Because your site may hacked, you may forgot your admin password and its email id to reset the password, you are no longer able to login into wordpress using login credentials. This method will very useful to …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/09/how-to-reset-a-wordpress-password-from-mysql-server/

Find number of working days between two dates in Mysql

This which helps to find number of working days between two dates in Mysql. There is no specific or inbuilt function in mysql, For that we need to use the SQL query as below. [sql] SELECT 5 * ((DATEDIFF(@end_date, @start_date) ) DIV 7) + MID(‘0123455501234445012333450122234501101234000123450’, 7 * WEEKDAY(@start_date) + WEEKDAY(@end_date) + 1, 1) [/sql] For …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/08/find-number-of-working-days-between-two-dates-in-mysql/

MySQL Error – SQL_BIG_SELECTS

While executing JOIN SQL query in live server, i got the following error. [code] The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay: [/code] To resolve this error, I have used the following SQL query before executing the JOIN SQL query. …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/08/mysql-error-sql_big_selects/

Loop mysql result set multiple times or twice in php

First we will select something from the database and loop through it like so: [php] $result = mysql_query("SELECT * FROM my_table"); while($row = mysql_fetch_assoc($result)) { // inside the loop } [/php] Problem: if you want to loop through the same result set again, you will get an error because the internal pointer is currently at …

Continue reading

Permanent link to this article: https://blog.openshell.in/2013/01/loop-mysql-result-set-multiple-times-or-twice-in-php/