mysql 截取逗号并形成新的字段,在MySQL中拆分逗号分隔的字符串以获取动态下拉列表...

Right now I am having trouble with splitting up entries in my ethnicity column that contain entries that are separated by commas for example take a look at my

Ethnicity table

I want to be able to split up these strings into individual strings so they can all be their own entries (ex: asian, chinese, catina would all be seperate entries). I don't want to make any changes to my existing table I just want to be able to seperate these strings because I am using php to create a dynamic drop down list based on what is stored in this ethnicity column:

*Category

$conn = mysqli_connect(db, root, pass, table);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

$result = mysqli_query($conn,"SELECT DISTINCT restaurant_id, ethnicity FROM ethnicity");

while ($row = mysqli_fetch_assoc($result)) {

unset($restaurantID, $ethnicity);

$restaurantID = $row['restaurant_id'];

$ethnicity = $row['ethnicity'];

echo ''.$ethnicity.'';

}

?>

Right now the drop down list is including the comma separated strings and I just want it to be able to display the distinct ethnicities individually. Any suggestions?

解决方案

I'd start buy doing the query above and exploding all the ethnicities into a new array and then printing them out:

(I haven't tested syntax just used notepad, hopefully you get the idea though.)

*Category

$conn = mysqli_connect(db,root,pass,table);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

$ethnicity_array = [];

$result = mysqli_query($conn,"SELECT DISTINCT restaurant_id, ethnicity FROM ethnicity");

while ($row = mysqli_fetch_assoc($result))

{

unset($restaurantID, $ethnicity);

$restaurantID = $row['restaurant_id'];

$ethnicity = $row['ethnicity'];

$split_ethnicity = explode("," $ethnicity);

foreach ($split_ethnicity as $value)

{

if (!in_array($value, $ethnicity_array))

$ethnicity_array[] = $value;

}

}

?>

foreach ($ethnicity_array as $ethnicity)

{

echo ''.$ethnicity.'';

}

?>