CODEWITHSUNDEEP
mysqlconcatenation
ster
ster

Reputation: 199

MySQL-PHP concatenate more value from one column

I have this table (table1) and contain in column COUNTRY, single country or many contry delimited by ";".

table1
+----+---------------+----------+------+-------+
| ID | COUNTRY       | QUANTITY | EACH | PRICE |
+----+---------------+----------+------+-------+
| 1  | U.S.A         |     1    |  12  | 1*12  |
| 2  | U.K.          |     2    |   3  | 2* 3  |
| 3  | GERMANY       |   NULL   |   3  |       |
| 4  | FRANCE;GERMANY;          |      |       |
|    | U.S.A         |     0    |   7  |       |
| 5  | U.S.A;GERMANY |     3    |   8  | 3*8   |
| 6  | FRANCE;U.K.   |     1    |  10  | 1*10  |
| 7  | U.S.A;FRANCE  |     2    |   6  | 2*6   |
| 8  | FRANCE;FRANCE |     9    |   3  | 9*3   |
+----+---------------+----------+------+-------+

SELECT COUNTRY, PRICE 
FROM table1 
WHERE COUNTRY = ('U.S.A' and 'ANY COUNTRY');

I have this select:

<?php
<select id="get_country" autocomplete="off">
   <option value="U.S.A">  U.S.A</option>
   <option value="FRANCE"> FRANCE</option>
   <option value="U.K.">   U.K.</option>
   <option value="GERMANY">GERMANY</option>
</select>
?>

My select return only country (U.S.A or U.K. or GERMANY or FRANCE).

How can I do when I select the country U.S.A from It should appear in SQL the rows which contains U.S.A.

Thank you!

Upvotes: 0

Views: 36

Answers (1)

c.bear
c.bear

Reputation: 1445

Try

WHERE COUNTRY LIKE '%U.S.A%'

Note: this approach works, but it's far from optimal. If you have a country named ABC and another one named ABCDE searches for ABC will include also ABCDE. I suggest a database refactor with foreign keys, so you can also read countries directly from database.

Upvotes: 1

Related Questions