Free Android CakePHP GMaps Articles by Bali Web Design

Sometimes we have data in a field on our table like this “1,2,3,4,5,6,7”, and we want to search for example number 2. Beside doing searching inside php, we can do it in sql query. I found one function for retrieving position of a value inside a set : FIND_IN_SET(str,strlist).

So the query will look like this :

SELECT * FROM table_name WHERE FIND_IN_SET(2, field_name) > 2;

This query will return all recordset that have number 2 in field_name set.

Another way to searching a value inside a set, is by manipulating on how we save the set in field. So if we want to save a set of “1, 2, 3, 4, 5, 6, 7”, we will save it as “<1>,<2>,<3>,<4>,<5>,<6>,<7>” in database. And doing query using this method :

SELECT * FROM table_name WHERE field_name LIKE ‘%<2>%’;

but of course the first way is more faster. because first way we search value in array / set, but second way search value in string.

Sometimes we need to copied a small pieces of one table to another table. Usually we run SELECT FROM source table first, and then iteratively add into target table, the query is something like this

mysql_query(‘SELECT id, name, address FROM member’);

and then using php, we add all results into target table

mysql_query(‘INSERT INTO target_table(id, name,date_f) VALUES (‘. $id .’, ‘. $name .’, NOW())’);

but using this way we need at least 3 lines code and it is not efficient, there is another way to do that more efficient. Mysql has INSERT INTO SELECT FROM query command, the query will look like below

mysql_query(‘INSERT INTO target_table (id, name, date_f)
SELECT source_table.id, source_table.name, NOW()
FROM source_table;’);

if you want to overwrite old value, you can use REPLACE instead INSERT.