select with likewise data

Question

I have a table in database that is having some fields one of which is 'action'

action is having data like bse-similar,bse-action.....nse-similar,nse-action...etc.

now i want to fetch the data that is having 'bse' in its action field.

How can i do that in mysql?????

One more thing i want to copy this data to another table.How can i do that in simple query?????

Answer 1

SELECT * FROM table WHERE action LIKE "bse-%"

or, in PHP:

mysql_connect($host, $user, $pass);
mysql_select_db($database);
$res = mysql_query("SELECT * FROM table WHERE action LIKE 'bse-%'");
while (($row = mysql_fetch_array($res)) != null)
{
    echo $row[0] . "<br />";
}

just fill out $host, $user, $pass, and $database with your information and your good to go.

You should really look at these tutorials:

http://w3schools.com/php/php_mysql_intro.asp

:)

EDIT

INSERT INTO table2 (column1, columnx, action) SELECT column1, columnx, action FROM table1 WHERE action LIKE "bse-%"

should be what your looking for

Answer 2

Use pattern-matching:

mysql> SELECT action FROM mytable WHERE action LIKE 'bse%';
+-------------+
| action      |
+-------------+
| bse-action  |
| bse-similar |
+-------------+

SQL pattern matching allows you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters).

Answer 3

INSERT INTO table2 (field1) SELECT action FROM table1 WHERE action LIKE 'bse%';

This should insert into table 2, column "field1" the value of "action" from table1. If the value you are searching on could be UPPER or LOWER case then try it this way instead assuming you want to match both uppercase and lowercase actions with "bse".

INSERT INTO table2 (field1) SELECT action FROM table1 WHERE LOWER(action) LIKE 'bse%';