Archive for 'MySQL'
Search comma separated values db column in Rails
I wanted to search for a value in DB and the column data is comma separated values say …
foo,bar,hey,hay
Now I wanted to find a record based on the value …say …hay
MySQL’s FIND_IN_SET comes into play and it works as follows
1 2 3 | <% Model.find(:first, :conditions => ["FIND_IN_SET(?,search_column)", search_string]) %> |
Hope this helps!
Installing MySQL Gem on Mac OSX with XAMPP
I was having lot of trouble on installing the MySQL Gem for Mac OSX Leopard 10.5.9 via XAMPP. I have the XAMPP running and also mySQL. Here are the steps.
1. Install XAMPP for MAC
2. Install XAMPP Dev Package
3. From your terminal type the following command and you’ll see the following
1 | sudo gem install mysql -- --with-mysql-dir=/Applications/XAMPP/xamppfiles/ -- with-mysql-lib=/Applications/XAMPP/xamppfiles/lib -- with-mysql-include=/Applications/XAMPP/xamppfiles/include/ -- with-mysql-config=/Applications/XAMPP/xamppfiles/bin/mysql_config |
Results:
1 2 3 4 5 | Building native extensions. This could take a while... Successfully installed mysql-2.7 1 gem installed Installing ri documentation for mysql-2.7... Installing RDoc documentation for mysql-2.7... |
Select Random Records
I wanted for some reason to have random records from a model without any condition, just random records and here’s how you have to do it.
1 | <% User.find(:all, :order => 'RAND()', :limit => 3) %> |
Hope this helps somebody!
Cheers!!!
Joins Introduction
Hello All,
Welcome to this MySQL Join tutorial. Joins sometimes seems to very difficult for the newbie. Earlier, when I started it was a nightmare for me and i was sounding difficult on this and later as you work with this, it becomes so simple. Ok lets get into the topic:
What is Joins?
Joins is a keyword in MySQL which is used to join one or more tables (atleast 2 tables) to retrieve the records based on the conditions like how you used to write a simple where condition.
Types of Joins?
There are different types of Joins in MySQL:
Join/Inner Join: Return rows when there is at least one match in both tables i.e where the condition matches on both the tables:
Self Join: It is joining a table to itself.
Left Join: Return all rows from the left table, even if there are no matches in the right table.
Right Join: Return all rows from the right table, even if there are no matches in the left table.
Outer Join: It does not require each record in the two joined tables to have a matching record. The joined table retains each record, even if no other matching record exists.
There are two types of Outer Join: Left Outer Join (Left Join) and Right Outer Join (Right Join)
Now lets see each types of Joins with a illustrated example.
Backup a MySQL Table
mysqldump db_name table_name > /file/to/path/dump.sql
Hope this helps!
Cheers!
Pads
Column Count in MySQL
I was looking for a way to count the column in a table and found this code:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=’mls_listings’;
-Pads
MySQL Restore Data
To restore the data from the .sql file to the database, please use the following command. It worked for me.
mysql -u root -p db_name < path_to_sql_file
Enter password:
Hope this helps!
