MySQL query to trim particular character from database column field

MySQL has some default functions to trim particular cahr or whitespaces from all the column field in database rows. MySQL provide four type of methods to trim char.
1. TRIM()
2. TRIM(LEADING)
3. TRIM(BOTH)
4. TRIM(TRAILING)
we can use above four methods in select, update and delete queries.
1. TRIM()
    TRIM() function is used to remove the whitespaces from start and end of the string.
Query : select TRIM( TechniqZone );
Result : TechniqZone
    This is trim the whitespaces in all the column.
Example : update table_name set field1=TRIM(field1)
2. TRIM(LEADING)
    TRIM(LEADING) function is used to remove the given charecter from the starting postion of the string.
Query : select TRIM(LEADING "'" FROM "TechniqZone'");
Rssult : TechniqZone
  
    This method is remove the single quotes(') from al the column starting postion.
Example : update table_name set field1=TRIM(LEADING "'" FROM field1)
3. TRIM(BOTH)
    TRIM(BOTH) function is used to remove particular charecter from both start and end postion of the string.
Query : select TRIM(BOTH '"' FROM '"TechniqZone"');
Rssult : TechniqZone
    This is used to remove the double quotes (") from both position.
Example : update table_name set field1=TRIM(BOTH "'" FROM field1)
4. TRIM(TRAILING)
    TRIM(TRAILING) function is used to remove given charecter from end of the string.
Query : select TRIM(TRAILING '.com' FROM "TechniqZone.com");
Rssult : TechniqZone
    This MySQL query remove the .com from the end of the string.
Example : update table_name set field1=TRIM(TRAILING "xxx" FROM field1)
Note : TRIM() and TRIM(BOTH) is same as when we remove whitesapces from database column.

Read More

Increase Import Maximum file size in phpmyadmin

Increase Import Maximum file size in phpmyadmin

                   Here are the steps to increase the import file size in phpmyadmin. Usually phpmyadmin allows to import sql files maximum of 2M (i.e 2,048kb). To increase the maximum file size do the following changes in php.ini file.


If you are using  windows ,the php configuration file can be located in your server (wampp or xampp) -> php folder.

Find
 upload_max_filesize =2M
in the file,
change the value acording to your need. Here I changed the value to 10M i.e , it allows to import sql files maximum of 10 Mb.

upload_max_filesize = 10M

 

Read More

Basic MySQL Query Commands

Basic MySQL Query Commands :

Syntax for Query Commands

1. CREATE Command

The Create command is used to create a table by specifying the tablename, fieldnames and constraints as shown below:

Syntax:
$createSQL=("CREATE TABLE tblName");

Example:
$createSQL=("CREATE TABLE tblstudent(fldstudid int(10) NOTNULL AUTO_INCREMENT PRIMARY KEY,fldstudName VARCHAR(250) NOTNULL,fldstudentmark int(4) DEFAULT '0' ");

2. SELECT Command

The Select command is used to select the records from a table using its field names. To select all the fields in a table, '*' is used in the command. The result is assigned to a variable name as shown below:

Syntax:
$selectSQL=("SELECT field_names FROM tablename");

Example:
$selectSQL=("SELECT * FROM tblstudent");

3. DELETE Command

The Delete command is used to delete the records from a table using conditions as shown below:

Syntax:
$deleteSQL=("DELETE * FROM tablename WHERE condition");

Example:
$deleteSQL=("DELETE * FROM tblstudent WHERE fldstudid=2");

4. INSERT Command

The Insert command is used to insert records into a table. The values are assigned to the field names as shown below:

Syntax:
$insertSQL=("INSERT INTO tblname(fieldname1,fieldname2..) VALUES(value1,value2,...) ");

Example:
$insertSQL=("INSERT INTO Tblstudent(fldstudName,fldstudmark)VALUES(Baskar,75) ");

5. UPDATE Command

The Update command is used to update the field values using conditions. This is done using 'SET' and the fieldnames to assign new values to them.

Syntax:
$updateSQL=("UPDATE Tblname SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber");

Example:
$updateSQL=("UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2");

6. DROP Command

The Drop command is used to delete all the records in a table using the table name as shown below:

Syntax:
$dropSQL=("DROP tblName");

Example:
$dropSQL=("DROP tblstudent");



Read More

Grant permission to user to execute a Stored Procedure - Mysql

Grant permission to user to execute a Stored Procedure - Mysql

If you want to allow user to execute Store procedure then use GRANT EXECUTE keyword

Allow user to execute all stored procedure
GRANT EXECUTE ON *.* TO 'user'@'localhost';

Allow user to execute all stored procedure attached with a single database
GRANT EXECUTE ON db_name.* TO 'user'@'localhost';

Allow user to execute a single stored procedure
GRANT EXECUTE ON db_name.sp TO 'user'@'localhost';

Read More

Mysql - Delete duplicate rows based on column value

Mysql - Delete duplicate rows based on column value.

 Mysql - Delete duplicate rows based on column value by below query.

CREATE TABLE temp_name AS SELECT * FROM tab WHERE 1 GROUP BY colname; 
DROP TABLE tab; 
RENAME TABLE temp_name TO tab;

Read More

Mysql - Get the record randomly

Mysql - Get the record randomly

 RAND() function is used to get the random record from table.


For Example:
select * from tab_name orderby RAND() limit 0,5;

Read More