Free Android CakePHP GMaps Articles by Bali Web Design

I am encountering problem to add increment column on existing table mysql database. The existing table has some columns except increment column. So now i need increment column, but i don’t want to put this column at the end of the table, this is not look good for me.

So i check to mysql manual and found interesting sintak, beside use AFTER column syntax, there is FIRST syntax. So the query is look like this

ALTER TABLE table_name ADD COLUMN `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT First,
ADD PRIMARY KEY (`ID`);

I hope this article can help you to add first column on existing table mysql database server.

I want to show you a situation that probably happen to you when build a mysql query. The situation can be vary but basically same condition. For example we have places table to store all beautiful places around the world. And a categories table to classify our places data. The rule is a place can be put on one or some categories. To achieve this function we just add a field into places table that save category id list.

So places table will have id, name, categories fields. and categories table have id, name. We want to save category list into categories field on places table. The data will look like this ‘2,3,4,5,6,7,8’. A question will come to your mind about how to grab or find a matching id inside this categories, for example we want to show all place with category id 5.

We cannot use LIKE in this example because it cannot match perfectly on many digit number. So i found a solution that easy to do. The query to find all data inside mysql Array field is

SELECT * FROM places WHERE FIND_IN_SET(5, categories)

Another solution is by adding new table to save category list for place. But my solution look nice, right?

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.