CODEWITHSUNDEEP
phpmysql
user2009951
user2009951

Reputation: 23

mysql fetch unique data with comma separated values

In PHP, I have mysql record like below

id country
 1  India
 2  Usa,Germany
 3  India,usa
 4  Germany,Uk,India
 5  Uk

I want to fetch country in drop down, the data want to be display only unique country not duplicate entry and comma separate values

Drop drown list should be display like below

 India
 Usa
 Germany
 Uk

Upvotes: 2

Views: 2230

Answers (4)

Arslaan Ejaz
Arslaan Ejaz

Reputation: 1001

Please see the full working example with databasse name test and table table1:

<?php
$mysqli = new mysqli("localhost", "root", "", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

   $query = "SELECT * FROM table1";
$result = $mysqli->query($query);

while($row = $result->fetch_row()) {
  $rows[]=$row['1']; //getting aal country
}


/* free result set */
$result->free();

/* close connection */
$mysqli->close();
 ?>

 <form>
 <select>
 <?php foreach ($rows as $mod){?>
 <option value="<?php echo $mod?>"><?php echo $mod?></option>
 <?php }?>
 </select>
 </form>
 <?php $all_country = array();
 foreach ($rows as $mod){
     $arr = explode(',', $mod);
     foreach($arr as $row){ if(trim($row) != '')
         $all_country[] = trim($row);
         }
     }
     $input = array();
     $input = array_unique($all_country);
 ?>
  <form>
 <select>
 <?php foreach ($input as $mod){?>
 <option value="<?php echo $mod?>"><?php echo $mod?></option>
 <?php }?>
 </select>
 </form>

This is result: note that Uk and UK have uper and lower case problem else it is unique.

Simple data:
Array
(
    [0] => India
    [1] => Usa
    [2] => Germany
    [4] => usa
    [6] => Uk
    [8] => UK
)
unique country;


Array
(
    [0] => India
    [1] => Usa
    [2] => Germany
    [4] => usa
    [6] => Uk
    [8] => UK
)

Upvotes: 1

Mihir Vadalia
Mihir Vadalia

Reputation: 93

MySQL query

select group_concat(country) from table

After that in php,

$duplicateCountry = explode(',',$sqlResult);
$duplicateCountry = array_unique($duplicateCountry);

gives your array.

Upvotes: 0

Dipesh Parmar
Dipesh Parmar

Reputation: 27364

MySQL Query

SELECT country FROM table

In PHP

You can get array of country.

now on using mysql_fetch_assco you will have associative array.

Perform implode on that array values and them explode them by ,.

now perform array_unique on that array and you will have your array.

Example

$queriedResult = array
(
    'India',
    'Usa,Germany',
    'India,usa',
    'Germany,Uk,India',
    'Uk'
);

$result = implode(',',$queriedResult);

after that $result will be as below.

$result = 'India,Usa,Germany,India,usa,Germany,Uk,India,Uk';

now we are exploding above string by ,.

$newArray = explode(',',$result);

we will have 

$newArray = array
(
    'India',
    'Usa',
    'Germany',
    'India',
    'usa',
    'Germany',
    'Uk',
    'India',
    'Uk'
);

now perform final step.

$finalArray = array_unique($newArray);

produce.

$finalArray = array
(
    'India',
    'Usa',
    'Germany'
    'Uk'
);

Upvotes: 0

Frums
Frums

Reputation: 1

You should probably consider rethinking the model you store your data in. As you would want to use the DISTINCT keyword in your SQL statement to get unique countries.

If however you don't want to do that, you can use explode to get the comma seperated values.

Lets say we have your data in the array $results and you want your unique countries in the array $unique_countries

foreach($results as $result){
    $result_countries = explode(',',$result['country']);
    foreach($result_countries as $country){
        if(!in_array($country,$unique_countries)){
            $unique_countries[] = $country;
        }
    }
}

Upvotes: 0

Related Questions