Pull Data From One Field And Remove Duplicates

[Perry Mason]

I've been trying to do this all day with no result and would be grateful for a push in the right direction.

 

So I have a cooking website with a table similar to this

 

id recipe ingredients

 

1 Apple pie Apples, eggs, flour

2 Peach pie Peaches, butter, flour

3 Cherry pie Cherries, flour, eggs, butter

 

I would like to pull all the data from the Ingredients field, get rid of the duplicates, sort them alphabetically and echo a list like this:

Apples

Butter

Cherries

Eggs

Flour

Peaches

 

I've tried

<?php

$result = mysqli_query($dbconnect, "SELECT ingredients FROM recipes");

$result1 = join(",",$result);

$result2 = array_unique($result1);

foreach ($result2 as &$value)

{

echo "<li>$value</li>";

}

 

Which does not work.

 

Would really appreciate any suggestions.

 

Thanks!

[garbagedigger]

I think what is going on is that you are not returning an associative array in your mysql query. I think it might look like this:

 

$result = mysqli_query($dbconnect, "SELECT ingredients FROM recipes");

$result = mysql_fetch_assoc($result);

$result = array_unique($result);

foreach ($result as $value)

{

echo "<li>$value</li>";

}

 

 

Here's the manual:

 

http://php.net/manua...fetch-assoc.php

Edited November 28, 2012 by garbagedigger

[Perry Mason]

Thanks. Unfortunately it didn't work.

 

I've also tried

 

$result= mysqli_query ($dbcnx, "SELECT key_ingredient FROM recipes");

$numrows = mysqli_num_rows($result);

while ($result)

{

$row = mysqli_fetch_array($result);

$joined = array_merge($row[0],$row[1],$row[2]);

$word_array = preg_split('/[\s?:;,.]+/', $joined, -1, PREG_SPLIT_NO_EMPTY);

$unique_word_array = array_unique($word_array);

foreach ($unique_word_array as $word)

{

echo "<li>$word</li>";

}

}

 

Any ideas? There has to be a way to combine data from multiple rows into one array. I think I know what to do from that point.

[akphidelt2007]

This would be a perfect case and task for a normalized database. If you created an ingredients table and related it to the recipe table, you can do this with a simple

 

SELECT DISTINCT(ingredient) FROM ingredients

 

Otherwise what you are doing is not properly looping through a mysql result. This is completely untested but I would try something like this.

 

$ingredients = Array();

$result= mysqli_query ($dbcnx, "SELECT key_ingredient FROM recipes");
if(mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_array($result))
{
   //explode the ingridents
   $ing = explode(',',$row[0]);
   if(is_array($ing))
   {
   foreach($ing as $ingredient)
   {
		 $trim = trim($ingredient);
		 if(!in_array($trim,$ingredients))
		 {
			  $ingredients[] = $ingredient;
		  }
    }
 }
}
}