“SQL query to match the data in the passed comma sepearted string”

There are several occasions when we have to match whether a passed collection of id is there in the database or not , in this case we can design the SQL in such a fashion that it checks for the id = (1,2,3), so for that we need to use IN operator.

Eg:
select * from table_name where table_name.id IN (array of integer or anything else)
Now here the array of integer or anything else should be in a comma separated manner like (1,2,3) or (‘asa’,’sa’,’sasa’)

“MySQL query compatible with both linux and windows”

I just came across this issue when i made the migrations on a windows and tried running them on linux

I am of the opinion that we should be using the below given syntax so
that the migration works on both windows and linux machines.(DO correct
me if i am wrong)

The below syntax doesn’t work on LINUX:

CREATE TABLE ‘employee_roles’ (

‘id’ INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,

‘employee_role_type’ VARCHAR(50) NOT NULL,

PRIMARY KEY(‘id’)

)

TYPE = MYISAM;

The below code works on LINUX and windows:

CREATE TABLE employee_roles (

id INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,

employee_role_type VARCHAR(50) NOT NULL,

PRIMARY KEY(id)

)

ENGINE = MYISAM

The only difference is that we are not using the single inverted quote ,
so just do find and replace all .

Also mention ENGINE=MYISAM not TYPE=MYISAM as type is deprecated.

Technorati Tags: mysql, syantax, linux, rails, linus vs windows compatibility, syntax error for migrations on linux

“mysql commands for server and localally using command prompt”

1>mysql – u login_name -p (press enter)
2>it will ask for your password
3>show databases;
it will list down all the databases
4>say “use database_name”
5>
then you can run your normal queries there itself.

Technorati Tags: mysql, command prompt, deployment

Powered by ScribeFire.

“Hey SQL fans, check out foreign key ON UPDATE CASCADE”

Foreign key update show-off test:

– every genre (Rock, Jazz, etc) has a unique id#
CREATE TABLE genre (
id serial PRIMARY KEY UNIQUE,
name varchar(16));

– every subgenre (Metal, Bebop, etc) – references
– the unique id# of the parent-genre
– meaning genre_id *MUST* match genre.id
CREATE TABLE subgenre (
name varchar(16),
genre_id int REFERENCES genre(id) ON UPDATE CASCADE);

– the important thing is “ON UPDATE CASCADE”
– which means if the referenced genre.id is updated
– then the changes “cascade” down to the referencing table
– (in this case, the subgenre)

– THIS MEANS: if I update the id# of a genre,
– subgenre.genre_id will *automatically* update!
– Let’s test it…

INSERT INTO genre(name) VALUES (’Rock’);
INSERT INTO genre(name) VALUES (’Jazz’);

INSERT INTO subgenre VALUES(’Metal’, ‘1′);
INSERT INTO subgenre VALUES(’Bebop’, ‘2′);

– look at it…
SELECT * FROM subgenre, genre WHERE subgenre.genre_id=genre.id;

 name  | genre_id | id | name-------+----------+----+------ Metal |        1 |  1 | Rock Bebop |        2 |  2 | Jazz

– now alter that id# — for whatever reason:
UPDATE genre set id=’9′ WHERE name=’Rock’;

– look at it again…
SELECT * FROM subgenre, genre WHERE subgenre.genre_id=genre.id;

 name  | genre_id | id | name-------+----------+----+------ Bebop |        2 |  2 | Jazz Metal |        9 |  9 | Rock

– it updated the genre_id in subgenre!!

I was doing all this kind of stuff manually in PHP whenever database
tables were updated, but sometimes with old code, you may add a
database table, and forget to update some 3-year-old PHP code, and
accidently end up with database tables with unmatched ID #’s.

I LOVE this foreign-key thing.

SIMilarly we can do for on delete cascade, which means dependent destroy is no more required (rails)

Powered by ScribeFire.