[MySql] FIND_IN_SET() function

Objectif: update multiple rows in mysql

Quick Solution (for example):

UPDATE my_table SET my_field=first_value WHERE id=1;
UPDATE my_table SET my_field=second_value WHERE id=2;
UPDATE my_table SET my_field=third_value WHERE id=3;

Problem : performance

Other solution with function FIND_IN_SET() :

UPDATE `my_table`
SET my_field = find_in_set(id, 'first_value, second_value, third_value, ...')
WHERE id IN (1, 2, 3, 4, ...)

Find more explaination :

[OpenShift] Deploy PHP/MySQL in OpenShift got 503 Service Temporarily Unavailable / 504 Gateway Time-out

Context :

The objective is to deploy the connection of MySQL with PHP in an OpenShift Server.
OpenShift is PaaS provided by Red Hat. For more info, read wikipedia OpenShift.

Problem :

The server returns :

503 Service Temporarily Unavailable


504 Gateway Time-out

Continue reading

Mysql useful commands

### Mysql dump ###

== Syntax global == 
mysqldump [options] db_name [tables]
== Syntax to backup only one database == 
mysqldump -u [username]  -p [password] [database_name] > [backup_file.sql]
== Syntax to backup multiple databases ==
mysqldump -u [username]  -p [password] –databases [database_names] > [backup_file.sql]
== Syntax to restore databases ==
mysqldump -u [username]  -p [password] [database_to_restore] < [backup_file.sql]
ex. [Only one database]
mysqldump -uroot -ppassword database_name > file_name
ex. [Multiple databases]
mysqldump -uroot -ppassword –databases database_name1 database_name2 > file_name

Add user for phpMyAdmin in command line

Background :

Wamp + phpMyAdmin

Objective :

By default, there is no user for phpMyAdmin, so we will create one.

Method :

1. Open the terminal of mysql in Wamp.

2. Type Enter.

3. Commands :

# create a user

CREATE USER 'yourUsername'@'localhost' IDENTIFIED BY 'yourPassword';

# grant all the privilege to the user

GRANT ALL PRIVILEGES ON *.* TO 'yourUsername'@'localhost'