Your Open Source

MYSQL

How to Delete duplicate data into a single query

How to Delete duplicate data / rows into a single query You can delete the duplicate data into a single SQL query.  We cannot delete the data use the DISTINCT mysql function into a query, but this function used to avoid the duplicate data from the SELECT statement.

MYSQL REPLACE function

REPLACE FUNCTION :-             The Replace function used to replace the string in MYSQL. The Syntax as follows,REPLACE(string , find_str ,replace_str )

Import or Restore MYSQL Database

How to Import or Restore MYSQL DatabaseYou can import or restore the mysql database by using mysqlimport command. The Syntax given below,mysqlimport -u user -p password databasename file_name.sqlThe example as followsmysql > mysqlimport -u  root -p php phpkit restore.sql

How to convert timestamp to date in MYSQL

How to convert timestamp to date in MYSQL :-You can convert the timestamp to date in mysql by using DATE and FROM_UNIXTIME function. The command given bellow,

Database Servers Functions

Database Servers Functions:There are two primary functions that database servers fulfill;*                   First is at the user archival level and the other is as information servers based on query languages. Examples of the archival type are the common..

MYSQL Backup Command - mysqldump

Mysql Backup Command The MYSQLDUMP command used to you can backup your databases, the command as follows, mysqldump -u [user] -p [pwd] [dbname] > [backup.sql] . user > username of the database . pwd > password of the database . dbname > name of the MYSQL database . backup > the file to which the backup should be written. ..

What is BLOB

BLOB in MYSQL BLOB stand for Binary Large Object. BLOB is a field type for storing Binary Data in MYSQL. MySQL has four BLOB types as follows : 1. BLOB 2. TINYBLOB 3. MEDIUMBLOB 4. LONGBLOB Example : create table test(id tinyint(3) not null default '0',image blob not null);

Export the data from the SELECT Statement in MYSQL

How to Export the data from the SELECT Statement :- The SELECT statement with outfile options to export the data into the SQL query. The example query given bellow, SELECT name FROM `tbl_user` into outfile 'foobar.txt'

Replication correctly in MYSQL Functions or Procedures

Difference between DETERMINISTIC and NOT DETERMINISTIC characteristics in MYSQL:- NOT DETERMINISTIC implies that the procedure may produce different result, given same inputs. DETERMINISTIC procedure or function always gives the same results given same input. How replication correctly in MYSQL Function or Procedure ? Set the characteristic t..

InnoDB And MyISAM

InnoDB :-• InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities • InnoDB has been designed for maximum performance when processing large data volumes • InnoDB supports FOREIGN KEY constraints. We can freely mix InnoDB tables with tables from other MySQL ..

When You are use the HAVING when you r use the WHERE conditions

In Mysql when you are using the Where condition when your using the Having if your going to list the data with group by means the WHERE condition is not working so in that situation we are used to HAVING example :SELECT user_abstract,`user_id` FROM `user` group by `user_id` having `user_id` < 4 try this you have any suggestion post ..

Stored Procedure / Views / Trigger

whats Stored Procedure / Views / Trigger ? Stored Procedure : A stored procedure is simply a procedure that is stored on the database server. call the procedure name to access the function ex: mysql> CREATE PROCEDURE test() SELECT 'test'; call the procedure: mysql> CALL molo()\G TRIGGER : ex: mysql> CREATE TABLE account (acct_num ..

Selecting Odd and Even Rows in SELECT Query

The MOD function to you can sort the Odd / Even rows from the table. The Example query given below, To Select Odd Rows from a table SELECT ID FROM table WHERE MOD(ID,2) = 1 To select Even Rows from a tableSELECT ID FROM table WHERE MOD(ID,2) = 0 Note :- Where the Id needs to be a primary key or unique.

Add two string in MYSQL

CONCAT MYSQL function to add the two strings from the SELECT Query, the example and output given below, SELECT CONCAT('My', 'S', 'QL'); Output is MySQL

LEFT OUTER JOIN

Example of Left out JOIN SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0 Example of Left out JOIN with Order By SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0 ORDER BY T3.C ASC

Date between in mysql

DATE BETWEEN IN MYSQL Syntax, SELECT * FROM table WHERE dateColumn BETWEEN {from_date} AND {to_date} Example :- SELECT * FROM table WHERE cdate BETWEEN '2009/01/01' AND '2009/06/23'

Get the total number of rows of my table.

i want to get the total number of rows of my table. <?php $sql = "SELECT * FROM ....."; $query = mysql_quer($sql); $count = mysql_num_rows($query); // here you get the total count values.. ?>

How to get the Random Record in mysql

To get the Random record in MYSQl : "SELECT * FROM `myplace` ORDER BY RAND() LIMIT 1" ========================== To get the Random record USING PHP : $query =mysql_query("SELECT * FROM `myplace` ORDER BY RAND() LIMIT 1");

Backup MySQL With PHP

Creating back-up is a golden role in the I.T. world. Keep in mind that accidents happen; there are hackers out there or someone who just want to ruin someone else’s sites and/or files Here are three ways to backup your MySQL Database I found over the net: 1. Implement a database backup query from PHP file Use the command SELECT INTO OUTF..

SELECT statement in Mysql

The SELECT statement has several clauses that you combine as necessary to retrieve the information in which you're interested. Each of these clauses can be simple or complex, so SELECT statements as a whole can be simple or complexA simplified syntax of the SELECT statement is:SELECT what to retrieveFROM table or tablesWHERE conditions that da..

Update query in myssql

Update query  in myssql UPDATE tbl_nameSET which columns to changeWHERE which records to update;The WHERE clause is  optional, so if you don't specify one, every record in the table will be updated.Example :UPDATE employee SET name='subu' WHERE emp_id='12';

SQL Query for null values in a table

Perform searches for NULL values, you must use a special syntax. Instead of using =, <>, or != to test for equality or inequality, use IS NULL or IS NOT NULLFor exampleEg 1 : Null values SELECT last_name, first_name FROM user WHERE last_name IS NULL;Eg 2 : NOT  Null values SELECT last_name, first_name FROM user  WHERE last_name..

Using SQL - Copy a row to the same table or Copy a row to the another table

In SQL you can copy a row in to the same table usingINSERT ...SELECT Query.Syntax:  INSERT INTO  [table_name] (col_name1,col_name2,..)  SELECT (col_name1,col_name2,..) FROM [table_name] Eg: Copy a row to the another tableINSERT INTO User (first_name,last_name,email)SELECT (first_name,last_name,email) FROM Employee WHERE sa..

Difference between MySQL function and mysql procedure

MYSQL Function It must return value.In,OUT and INOUT cannot be used in function.But return datatype must be declare when create a function.function can be called from a SQL statement.Function return one values.MYSQL ProcedureReturn Values is not mandatory but may be uses the OUT parameter to procedure  returns.Can use the IN | OUT | INOUT..

SQL COUNT

SQL COUNT : Is used to count the number of records in a tableexample :Table user-----------------------------------------ID          USERNAME  phone-----------------------------------------1           Ant      12342 ..

How to connect signle table in mysql database using sql query ?

MySql Connection :  Connection a single table using my mysql query . GRANT SELECT , DELETE , CREATE , DROP , INDEX , ALTER , CREATE VIEW , SHOW VIEW ON `database name`.`your-table-name` TO 'username'@'localhost' WITH GRANT OPTION ;