MySQL-PHP concatenate more value from one column

Question

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!

c.bear · Accepted Answer · 2016-08-29 14:19:12Z

1

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.

edited Aug 29, 2016 at 14:19

answered Aug 29, 2016 at 14:09

c.bear

1,45514 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL-PHP concatenate more value from one column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related