Tecquila

MySQL Query to get the column names which are of a particular data type To accomplish this , you should be familiar with how the data types are declared in the information schema table of the DB. To know the Data type name just run this query on your PHP admin or MySql Client: SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME' This will return the result like: COLUMN_NAME | DATA_TYPE ----------------------------- Field1 | int Field2 | int Field3 | varchar Field4 | text Field5 | longtext Field6 | float So we will be keep the above query as the base and will add condition " AND DATA_TYPE = 'DATA_TYPE_NAME' ". MySQL Query to get all fields in a table which are of Type INTEGER SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME...

In the DB table, when a record is inserted the timestamp is also inserted. This time stamp is added in the insert query using the time() function. When The records are listed currently the php date( $timestamp , "d/m/Y h:i:s" ) function is used to dhow the data back. Is there any function in MYSQL which can  generate date from the unix timestamp stored in the table?

There is an ITEM table , CATEGORY table and an ITEM_CATEGORY relation table. Items may fall in multiple categories "Item" table has item id and item name.  "cat" table has Category Id and Category Name. There is a third table  to store the item category relation. "Item_cat" table  contains Itm_Id (item id) and cat_Id (category id). Here the question is how to generate a result like this ItmName  ##    Category Item1 ##    Cat1,Cat3 Item2 ##    Cat2 Item3 ##    Cat1,Cat4 Item3 ##    Cat1 Sample MySQL query to list Items and its categories. In this example there are tables for Items, Categories and Item_category relation. One item may fall in different categories. In the result there should be no repeat of records and the categories should be comma separated.

How to use MySQL GROUP_CONCAT() function How to concatenate field values based on a "GROUP BY" Field Let's understand this with an example There is a table "tbl_Locations" having 2 fields Location and State

MySQL String Concatenate Functions - MySQL CONCAT() function returns NULL when one of the argument field value is NULL In MySQL for concatenating two or more stings or Field values we use CONCAT() .It returns the string that results from concatenating the arguments. So even if all the argument fields except one have valid data it will return NULL

MySQL query to retrive only one record for a repeating column or duplicate rows of data Suppose there is a table tbl_BOOKS which stores records of various books. some books comes under different categories. so there may be multiple records with same BookReferenceNumber. But when user searches for a particular reference number, the search result should display only one record eventhough there are multiple entries for same BookReferenceNumber. Let us illustrate this with a sample table given below:

In a php page I am displaying some hard coded french  texts which has some accented characters like "é è ..." and also some french characters are fetched from Mysql table and displayed on the page. Here the problem was the hard coded french characters were displaying fine, but the characters fetch from db was not showing properly. It was displaying like "?" with in a black background.

HOW TO DELETE DUPLICATE RECORDS FROM A MYSQL TABLE BY KEEPING ONLY RECORD WITH HIGHEST OR LOWEST ID VALUE Deleting duplicate records from a mysql table by keeping only record with highest or lowest id value. Here I am explaining this with an example table. Let the table name be "tbl_films" . The table has fields "fId" and "fName" tbl_films fId | fName ------------ 1 | Film1 2 | Film2 3 | Film1 4 | Film1 5 | Film3

How can We do INSERT and UPDATE through a single MySQL Query? - "INSERT ..... ON DUPLICATE KEY UPDATE" Syntax INSERT and UPDATE Actions in one MySQL Statement Usually Update functions will check for a record with given key and if it exist then function will execute the UPDATE Query else perform an INSERT Query. So here happens a SELECT query on Table Followed by INSERTION / UPDATION of a Record. You can do this in single MySQL query. How?

HOW TO USE FULLTEXT SEARCH in MYSQL - MATCH ... AGINST SYNTAX First You have to apply FULL-TEXT index to the Columns of your table Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements Here is the search Query based on a sample table "tbl_Products":

HOW TO REMOVE FULLTEXT INDEX FROM COLUMN IN MYSQL TABLE for this first you check the DDL of your table Here given the example which is based on a sample table "tbl_Company" Run following Query to display the DDL for your table, including the system-assigned name for the index.

HOW TO ADD FULLTEXT INDEX TO A COLUMN IN MYSQL TABLE Here given the example which is based on a test table "tbl_Products" How to Create MySQl table with Full-Text indexed columns? To create a table with Full-Text indexed columns, use query like this: CREATE TABLE tbl_Products ( `Product_Id` int(11) NOT NULL auto_increment, `Product_Title` varchar(150) , `Product_Details` Text, PRIMARY KEY (`Product_Id`), FULLTEXT KEY `Product_Title` (`Product_Title`,`Product_Details`) ) ENGINE=MyISAM

MYSQL FULLTEXT SEARCH - QUICK REFERENCE GUIDE - ADDING AND REMOVING OF FULL-TEXT INDEX FULL-TEXT SEARCH TUTORIAL Full-Text search refers to techniques for searching a single computer-stored document or a collection in a full-text database. Here, the search engine examines all of the words in every stored document as it tries to match search criteria. Two Stages of Full-Text search Basically there are 2 stages (1) INDEXING   (2) SEARCHING

 MySQL Query for getting nested items in a single query  Here I would try to explain this with an example of categories , subcategories and maincategories which is handled via a nested structure in the table How to get subcategory name , category name  and main category name in a single MySQL query Suppose your category structure is like MAIN CATEGORY >> CATEGORY  >> SUBCATEGORY and You have a category table structure like this: Table Name: tbl_category CatId integer Autoincrement primary CatName Varchar(25) CatParent integer CatSubParent integer CatActive   integer Suppose You have a category table with values like this Understanding the nested table structure for storing categories , subcategories and maincategories in a single table catId , catName , catParent , CatSubParent 1 , Books , 0 , 0 2 , DVD , 0 , 0 3 , Science , 1 , 0 4 , Maths , 1 , 0 5 , Movies , 2 , 0 6 , Music , 2 , 0 7 , English Movies , 2 , 5 8 ,...

How to get subcategory name and parent category name  in a single row with out using multiple query Suppose You have a category table with values like this catId,catName,catParent 1 , Books , NULL 2 , DVD , NULL 3 , Science , 1 4 , Maths , 1 5 , English Movies , 2 6 , Malayalam Movies , 2 Based on above table structure category and subcategory values are identified by the presense of values in catParent field. if catParent is NULL, the it is a category and if have a numeric value then the record will be that of a subcategory. So we are here is know how we can get subcategory name and parent category name  in a single row with out using multiple query Try this query SELECT sc.catId AS catid, sc.catName AS subcat, c.catName AS cat FROM tbl_category sc LEFT JOIN tbl_category c ON c.catId = sc.catParent WHERE c.catParent IS NOT NULL this query will generate result like this catid | subcat | cat ----------------------- 3 | Science | Books 4 | Maths ...

How to find duplicate records in a table? MySQL or SQL Query to find duplicates in a table suppose you have a table like this which represent some user transactions: Table Name : tbl_user_trans transId userId transDate transAmount 1000 USR034 2012-01-01 300.25 1001 USR004 2012-01-08 100.05 1002 USR030 2012-01-11 30.59 1003 USR034 2012-01-08 1000.65 1004 USR050 2012-02-01 19.25 1005 USR034 2012-02-03 403.25 If want to find all Users from the transaction table who had made transactions more than once , then you have to do like this: SELECT userId, COUNT(userId) AS HowMany FROM tbl_user_trans GROUP BY userId HAVING ( COUNT(userId) > 1 ) The query will return userId      HowMany USR034   ...