asked on

How to get only distinct value from a mysql column in php

I have a column in MySql table which saves multiple values in one column seperated by pipe sign:

i.e: A|B| 
B|C|E|
A|
B|E|
A|E|F|

Open in new window

Is there any way to retrieve only distinct values from the above data in PHP?

Thanks

gr8gonzo

If the values are always in the same order, then yes, it should just be SELECT DISTINCT `yourcolumnname` FROM `yourtable`.

But if you have rows like this:
A|B|
B|A|

...then MySQL would see those as two distinct values, and there would be no simple way to exclude "B|A|" without some code that reorders the values.

noci

And also whitespace counts as a breaker for SELECT DISTINCT...

'A' != 'A '...

Sharath S

For the given input dataset, what is your expected output?
Are you expecting something like this?
A
B
C
E
F

Yuri Boyz

ASKER

Yes my expected output according to the above data is:
A
B
C
E
F
It means these records are unique

gr8gonzo

So you MIGHT be able to do this in a convoluted mix of a stored procedure and UDF in MySQL, but it wouldn't be very efficient.

It would likely be more effective to pull the values and process them with PHP.

Yuri Boyz

ASKER

Yes I need it in PHP. Can you provide some sample?

gr8gonzo

<?php
// Connect to the database and get the rows
$db = new mysqli(...);
$results = $db->query("SELECT pipelistfield FROM table");

// Loop through the rows and get the unique values
$unique_values = array();
while($row = $results->fetch_assoc())
{
  // Trim off any starting or ending | values and then split it into an array of $values
  $values = explode("|", trim($row["pipelistfield"],"|"));

  // Add in unique values
  foreach($values as $value)
  {
    if(!in_array($value, $unique_values)
    {
      $unique_values[] = $value;
    } 
  }
}

Open in new window

This should give you an array called $unique_values with that data.

Yuri Boyz

ASKER

Thanks let me check it and then I will get back to you.

Yuri Boyz

ASKER

One little issue. I just need single unique values. Where in output I get following long array:

Array
(
    [0] => E
    [1] => C
)
Array
(
    [0] => E
    [1] => C
)
Array
(
    [0] => E
    [1] => C
    [2] => B
)
Array
(
    [0] => E
    [1] => C
    [2] => B
)
Array
(
    [0] => E
    [1] => C
    [2] => B
)
Array
(
    [0] => E
    [1] => C
    [2] => B
)
Array
(
    [0] => E
    [1] => C
    [2] => B
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)
Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)

Open in new window

Any solutions?

gr8gonzo

That looks like you might have a typo or something in your looping code. Can you post the exact code you have right now?

Yuri Boyz

ASKER

I have copy your code from your above post and use it by just changing the table names and convert mysqli functions into procedural styles and I got the output which I posted above.So where is the issue?

gr8gonzo

I'm not sure - I just tried it here using an array instead of a database, and I noticed a syntax error - I'm missing an extra ) at the end of the in_array() line, but once I corrected that, it worked as expected:

<?php
$rows = array(
  array("pipelistfield" => "E|C|"), 
  array("pipelistfield" => "E|C|"),
  array("pipelistfield" => "E|C|B|"),
  array("pipelistfield" => "E|C|B|"),
  array("pipelistfield" => "E|C|B|"),
  array("pipelistfield" => "E|C|B|"),
  array("pipelistfield" => "E|C|B|D|"),
  array("pipelistfield" => "E|C|B|D|"),
  array("pipelistfield" => "E|C|B|D|"),
  array("pipelistfield" => "E|C|B|D|")
);

$unique_values = array();
foreach($rows as $row) // Simulate the loop through the db records
{
  // Trim off any starting or ending | values and then split it into an array of $values
  $values = explode("|", trim($row["pipelistfield"],"|"));

  // Add in unique values
  foreach($values as $value)
  {
    if(!in_array($value, $unique_values))
    {
      $unique_values[] = $value;
    } 
  }
}
print_r($unique_values);

Open in new window

I get this output:

Array
(
    [0] => E
    [1] => C
    [2] => B
    [3] => D
)

Open in new window

So something else must have changed - maybe during the mysqli conversion. Can you paste your exact code from the query to the loop (I'm assuming the table and field names do not contain sensitive data, but if they do, just mask them before pasting)

noci

The best way would be to convert the table to split this columnt in two fields:
1) group number
2) element #...

so:
A|B|E
E|C|D
would become: (a group number, and the value). if order needs to be preserved then a third column would be needed..
1,A ,1
1,B ,2
1,E ,3
2,E ,1
2,C ,2
2,D ,3

Then a straight forward select distinct would suffice, and original records can still be selected... even in the correct order if needed.

Yuri Boyz

ASKER

@noci

For that I need to change lof of my code which is not possible yet.

Yuri Boyz

ASKER

@gr8gonzo

Here is my full code

<?php

	// DB SETTINGS For Local
	$DB_host = "localhost";
	$DB_user = "root";
	$DB_pass = "";
	$DB_name = "testdb";
	
//FOR PDO CONNECTIONS
try
{
    $DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
    $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
    echo "ERROR : ".$e->getMessage();
}



// FOR MYSQLi

$conn = mysqli_connect($DB_host, $DB_user, $DB_pass, $DB_name);
// Check connection
if (!$conn)
{
    die("Connection failed: " . mysqli_connect_error());
}
///// DB  CONNECTION END /////


$results_qry = "select * from project_detail where node_id=1503";
$results = mysqli_query($conn,$results_qry);

// Loop through the rows and get the unique values
$unique_values = array();

while($row = mysqli_fetch_assoc($results))
{
  // Trim off any starting or ending | values and then split it into an array of $values
  $values = explode("|", trim($row["project_categories"],"|"));

  // Add in unique values
  foreach($values as $value)
  {
    if(!in_array($value, $unique_values))
    {
      $unique_values[] = $value;
    } 
  }
  
  print"<pre>";
  print_r($unique_values);
  print "</pre>";
}

?>

Open in new window

SQL script is attached.

I have already posted the output in above comments.
project_detail.sql

Sharath S

try this. I assume that you have max 5 values in a column. If more than that, you may need to extend the inner UNION ALL query.

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX(name, '|', n), '|', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN test_table
  on CHAR_LENGTH(name)
     -CHAR_LENGTH(REPLACE(name, '|', ''))>=n-1
where SUBSTRING_INDEX(SUBSTRING_INDEX(name, '|', n), '|', -1) <> ''

Open in new window

sample:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (name varchar(50));
INSERT INTO test_table VALUES ('A|B|');
INSERT INTO test_table VALUES ('B|C|E|');
INSERT INTO test_table VALUES ('A|');
INSERT INTO test_table VALUES ('B|E|');
INSERT INTO test_table VALUES ('A|E|F|');

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX(name, '|', n), '|', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN test_table
  on CHAR_LENGTH(name)
     -CHAR_LENGTH(REPLACE(name, '|', ''))>=n-1
where SUBSTRING_INDEX(SUBSTRING_INDEX(name, '|', n), '|', -1) <> ''
/*
  	name
	A
	B
	C
	E
	F
*/

Open in new window

Yuri Boyz

ASKER

@Sharath: I need it in php.

Sharath S

Not a PHP guy but tried editing your code. Can you check this.

<?php

	// DB SETTINGS For Local
	$DB_host = "localhost";
	$DB_user = "root";
	$DB_pass = "";
	$DB_name = "testdb";
	
//FOR PDO CONNECTIONS
try
{
    $DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
    $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
    echo "ERROR : ".$e->getMessage();
}



// FOR MYSQLi

$conn = mysqli_connect($DB_host, $DB_user, $DB_pass, $DB_name);
// Check connection
if (!$conn)
{
    die("Connection failed: " . mysqli_connect_error());
}
///// DB  CONNECTION END /////


$results_qry = "select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX(project_categories, '|', n), '|', -1) project_categories
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN project_detail
  on CHAR_LENGTH(project_categories)
     -CHAR_LENGTH(REPLACE(project_categories, '|', ''))>=n-1
where SUBSTRING_INDEX(SUBSTRING_INDEX(project_categories, '|', n), '|', -1) <> ''";
$results = mysqli_query($conn,$results_qry);

// Loop through the rows and get the unique values
$unique_values = array();

while($row = mysqli_fetch_assoc($results))
{
  $unique_values[] = $row['project_categories']
  
  }
  
  print"<pre>";
  print_r($unique_values);
  print "</pre>";
}

?>

Open in new window

gr8gonzo

Okay so my suggestion code worked but you're simply displaying the unique_values variable inside your query loop - that's why you're seeing multiple arrays. Just move the display code outside the loop.

BEFORE:

while($row = mysqli_fetch_assoc($results))
{
  // Trim off any starting or ending | values and then split it into an array of $values
  $values = explode("|", trim($row["project_categories"],"|"));

  // Add in unique values
  foreach($values as $value)
  {
    if(!in_array($value, $unique_values))
    {
      $unique_values[] = $value;
    } 
  }
  
  print"<pre>";
  print_r($unique_values);
  print "</pre>";
}                            // <------

Open in new window

AFTER:

while($row = mysqli_fetch_assoc($results))
{
  // Trim off any starting or ending | values and then split it into an array of $values
  $values = explode("|", trim($row["project_categories"],"|"));

  // Add in unique values
  foreach($values as $value)
  {
    if(!in_array($value, $unique_values))
    {
      $unique_values[] = $value;
    } 
  }
}                            // <------
print"<pre>";
print_r($unique_values);
print "</pre>";

Open in new window

Yuri Boyz

ASKER

oh i see. let me deploy it.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.