Suppose you want to search a keyword in 5 different columns or almost in all the fields of the table, then MySQL
provides us with fulltext indexing and searching method.
Before using this method , you need to add FullText to the columns in which you want to search.
ALTER TABLE member ADD FULLTEXT(firstname, lastname,address);
Then comes your main query :
SELECT firstname, lastname,address FROM member
WHERE MATCH (firstname,lastname,address) AGAINST (’test’);
The Match will look for “exact match” in the above 3 columns and on successfull match it will return that row.
Suppose in member table there is a an entry with firstname = test then the output will be :
firstname lastname address
test abc xyz
Suppose you want to use wildcards then modify your query :
SELECT firstname, lastname,address FROM member
WHERE MATCH (firstname,lastname,address) AGAINST (’*test*’);
Tags: mysql
Posted in tricks on July 29th, 2010 by dharmesh22>
MySQL has a function called FOUND_ROWS which is faster than any other function for counting rows which is returned by the latest query, it is used same as MySQL “LASTINSERT_ID()”.
For example: if we are developing a pagination script and we want the total number of results returned by the Query, at the same case in pagination we use LIMIT for paging the record using the above function it will not return the limit results it will return the total available results.
How to use? suppose we have 20 records in our table and we are fetching only 10 records in a page, then the $total will return total 20 not 10.
$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM tablename LIMIT 1, 10 “;
$results = mysql_query($sql);
$total = mysql_query(’SELECT FOUND_ROWS() as total’);
Tags: found_rows, mysql
Posted in tricks on July 20th, 2010 by Vinodkumar>
Here is a small trick to get first day of the month
SELECT DATE_SUB(now(),INTERVAL (DAY(now())-1) DAY)
This will give you the first day of the current month. You can also get first day of the month for a given date just pass the date instead of now()
SELECT DATE_SUB('2010-08-10',INTERVAL (DAY('2010-08-10')-1) DAY)
Tags: mysql, mysql functions
Posted in tricks on July 8th, 2010 by jimy>
the mysql manual can be accessed from the command line itself as follows :
help “<search term>”;
Tags: mysql
Posted in tricks on June 21st, 2010 by admin>
GROUP_CONCAT() function is used to concatenate column values into a single string.
Example:
Table ‘abc’ has following columns:
id customer_id
3 14
3 15
3 16
4 18
4 18
You can use this query:
SELECT id,GROUP_CONCAT(customer_id) FROM abc WHERE id = 3 GROUP BY id;
output:
id customer_id
3 14,15,16
It is useful when you want PHP array without looping inside PHP:
Tags: mysql
Posted in tricks on June 19th, 2010 by dharmesh22>
To convert a null value to zero returned by a mysql query use the function COALESCE(value,…)
Mysql Query :
SELECT COALESCE(column_that_might_be_null,0) as column_with_null_converted_to_zero FROM table_with_column_that_might_be_null;
Tags: COALESCE, mysql, mysql functions
Posted in tricks on June 14th, 2010 by jimy>
If we want to use “if then” conditions in our code, in some cases, it can be done in mysql queries itself. For eg .
SELECT IFNULL(a,b) ) as val
In the above statement, the value of val will be equal to “a” if “a” is not null or it will be “b” if “a” is null.
another peculiar eg is “(SELECT IFNULL(special_price,p.price) ) as current_price” where the value of special_price is coming from another query in the same sql string (multiple queries).
Tags: mysql
Posted in tricks on May 4th, 2010 by vineet>
myisam is the default storage engine . it is optimised for speed .innodb is optimised for data integrity . I am not sure which one to use when 
(feel free to comment on this ) . To change from myisam to innodb -
ALTER TABLE tbwithMyISAM CHANGE TYPE=InnoDB
Tags: mysql
Posted in tricks on March 19th, 2010 by karan>
if you have a listbox called “department” and want to insert multiple values from the listbox into the database,then:
when the user submits the form,store the value in a variable
$company_dept=$_POST['department'];
query:INSERT into table_name values(’”.join(”,”,$company_dept).”‘)
Tags: mysql, php
Posted in tricks on January 28th, 2010 by dharmesh22>
select (basicsal + hra) as salary from employee where salary > 5000
It will give error (Unkown column salary in where clause.
Instead you can use :
select (basicsal + hra) as salary from employee having salary > 5000
Tags: mysql
Posted in tricks on January 21st, 2010 by jimy>
