• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

How do I make delimited fields come out in a list with totals

Let's say I have a list of five restaurants with two fields, name and cuisine


here are the details

1. NAME: [restaurant 1] CUISINE: [pho, vietnamese, canadian]
2. NAME: [restaurant 2] CUISINE: [asian, pho, vietnamese, sushi]
3. NAME: [restaurant 3] CUISINE: [asian, pho, vietnamese, sushi]
4. NAME: [restaurant 4] CUISINE: [asian, vietnamese]
5. NAME: [restaurant 5] CUISINE: [vietnamese, spanish, mexican, canadian]

I want to make the results look like this

pho (3)
vietnamese (5)
asian (3)
sushi (2)
canadian (2)
mexican (1)
spanish (1)

any ideas?
0
transitnetwork
Asked:
transitnetwork
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
transitnetwork,

See if this XML trick works for you. Probably another way, maybe I am missing something real easy, but since I have been recently playing in XML it was the first thing to come to mind and it works. *smile*

It utilizes ExtractValue(), so hopefully you have MySQL 5.1 or higher.
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

In order to utilize the trick, you will need a numbers table in your MySQL setup. If you don't have one, you can use this to create one.
http://www.experts-exchange.com/Database/MySQL/A_3573-A-MySQL-Tidbit-Quick-Numbers-Table-Generation.html

Once you have util.numbers created, you can use the code in the snippet to parse out the cuisine values. The technique used is explained here:
http://www.experts-exchange.com/Database/MySQL/A_3574-A-MySQL-Tidbit-In-line-XML-Parsing.html

Other notes: I figured the [] were not actually in the data so I saved the 5 rows like image 1.1 and the results come out like 1.2.

Hope that helps.

Kevin

select extractvalue(cuisinexml, '//cuisine[$@rownum]/text()') as cuisine
     , count(*) as count
from util.numbers nums
inner join (
   select concat('<cuisines><cuisine>', 
                 replace(cuisine, ', ', '</cuisine><cuisine>'), 
                 '</cuisine></cuisines>') as cuisinexml
   from restaurants
) tbl on nums.n between 1 and extractvalue(cuisinexml, 'count(//cuisine)')
   and @rownum:=n
group by extractvalue(cuisinexml, '//cuisine[$@rownum]/text()')
;

Open in new window

Q26429210-1.PNG
Q26429210-2.PNG
0
 
Scott MadeiraCommented:
There may be a more elegant solution than this but you could do something like this:

$allcuisine = null;

foreach ($cuisine as $c) {
     array_merge($allcuisine, explode(",",$c));
}

$count_of_each = array_count_values($allcusine);


0
 
Marco GasiFreelancerCommented:
smadeira is right suggesting to use array_count_values() function, but there is some work to do in order to can use that function. If I understood correctly, you have a multidimansional array, that is an array wich each key in is an associative array formed by two keys: "NAME" and "CUISINE" and the respective values. In the code below you find this array (as I have understood it) and the solution.
Hope this solves your problem.
Best regards
<?php

$arr = array();
$arr[0] = array("name" => "restaurant 1", "cuisine" => "pho,vietnamese,canadian");
$arr[1] = array("name" => "restaurant 2", "cuisine" => "asian,pho,vietnamese,sushi");
$arr[2] = array("name" => "restaurant 3", "cuisine" => "asian,pho,vietnamese,sushi");
$arr[3] = array("name" => "restaurant 4", "cuisine" => "asian,vietnamese");
$arr[4] = array("name" => "restaurant 5", "cuisine" => "vietnamese,spanish,mexican,canadian");

$cuisine = array();
for ($i = 0; $i < count($arr); $i++) {
    $temp = array();
    $temp = explode(",", $arr[$i]["cuisine"]);
    foreach ($temp as $t) {
        $cuisine[] = $t;
    }
}
$cuisinecount = array_count_values($cuisine);
foreach ($cuisinecount as $key => $value) {
    echo $key . ": " . $value . "<br />";
}
?>

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
transitnetworkAuthor Commented:
There is no array in my question, just an example of what the mysql table looks like
0
 
Marco GasiFreelancerCommented:
Then you can get data form database  with a normal mysql query then you can create an associative array with mysql_fetch_assoc()

$sql = "SELECT cuisine FROM mytable";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)){
  $cuisine[] = explode(",", $row['cuisine']);
}
$cuisinecount = array_count_values($cuisine);
foreach ($cuisinecount as $key => $value) {
    echo $key . ": " . $value . "<br />";
}

This should works.

0
 
transitnetworkAuthor Commented:
Warning: array_count_values() [function.array-count-values]: Can only count STRING and INTEGER values! in /home2/richmoq3/public_html/markhameats/header.php on line 141

That's the error I'm getting with that code
0
 
transitnetworkAuthor Commented:
<?php
function array_count_values_multidim($a,$out=false) {
  if ($out===false) $out=array();
  if (is_array($a)) {
    foreach($a as $e)
      $out=array_count_values_multidim($e,$out);
  }
  else {
    if (array_key_exists($a,$out))
      $out[$a]++;
    else
      $out[$a]=1;
  }
  return $out;
}
?>

I am using this function to replace (array_count_values) in margusg's solution and it works

thanks!
0
 
Marco GasiFreelancerCommented:
Sorry, I forgot to delete explode statement. Try this (sorry, but I can't tested since is database driven):

$sql = "SELECT cuisine FROM mytable";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)){
  $arr[] = $row['cuisine'];
}
$cuisine = array();
foreach ($arr as $a) {
    $temp = array();
    $temp = explode(",", $a);
    foreach ($temp as $t) {
        $cuisine[] = $t;
    }
}
$cuisinecount = array_count_values($cuisine);
foreach ($cuisinecount as $key => $value) {
    echo $key . ": " . $value . "<br />";
}
0
 
Kevin CrossChief Technology OfficerCommented:
Glad that worked for you.  After the PHP code suggestions were posted I noticed you had this in those zones as primary.  Glad you got some Experts in that field to help as I am more of a SQL guy.

Anyway, just for my own knowledge to help with research for my article I wrote after posting here -- did you ever try the MySQL methodology I posted?  It worked for me, but want to know in the real-world if it performed okay for you as the XML does add slight overhead.

A MySQL Tidbit: In-line CSV Parsing

If you have time of course.  You can comment right in the article any feedback.  

Thanks in advance.

@Experts, great suggestions.  Nice work!!

Best regards and happy coding,

Kevin
0
 
Marco GasiFreelancerCommented:
Thanks for points. But you have forgot smadeira work: he was the first to mention array_count_values() function. I would suggest you to request attention to moderator  and to split points to give to smadeira what he gained.

Best regards.
0
 
transitnetworkAuthor Commented:
Thank you for the update margus, that code works perfectly, and I no longer need the custom function, perfect! Here's the finished code

<?

$sql = "SELECT cuisine FROM restaurants order by cuisine asc";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)){
  $arr[] = $row['cuisine'];
}
$cuisine = array();
foreach ($arr as $a) {
    $temp = array();
    $temp = explode(",", $a);
    foreach ($temp as $t) {
        $cuisine[] = $t;
    }
}
$cuisinecount = array_count_values($cuisine);
foreach ($cuisinecount as $key => $value) {
      $fkey = str_replace(" ","-",$key);
    echo "<li><a href=\"$fkey\">" . $key . " (" . $value . ")</a></li>";
}

?>
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now