ster ster - 3 months ago 10
MySQL Question

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!

Answer

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.

Comments