Data Sorting

[RebeccaD]

Happy New Year from a Newbie.

 

I have data in a MySql database table (let's call it data_table) which is input by users via a form. Their input consists of one or a list of items seperated by spaces. This ends up in one column (let's call it data_col) as in the following example format:

 

id      data_col

1       cat

2       elephant giraffe

3       dog rabbit

4       snake cat bird

5       fish dog

..       etc.

 

I need help with a PHP script to read only this column into a string variable with which I can then (a) use explode to seperate it into an array containing the seperated words and removing the spaces, if any, at the same time and (b) use array_unique to remove any duplicated words in the array, before finally displaying the array as an A-Z ordered list as below.

 

bird

cat (duplicate removed)

dog (duplicate removed)

elephant

fish

giraffe

rabbit

snake

 

Many thanks for any help offered.

[ginerjm]

So you use a query to get the data. Then you use explode to create the array, splitting it up on space chars. then you sort the array using sort() and then array_unique. Try it and let's see what problems crop up.

[kicken]

Before you go any further, you should fix your database.

 

You should never be storing a list of values in a single column in a table. Instead you want to have each value be it's own row. This might mean another table if you have other data besides these values in your existing table.

 

Once you've resolved that problem, then getting a list of unique values in alphabetical order is as simple as

SELECT UNIQUE data_col
FROM table
ORDER BY data_col

[RebeccaD]

Many thanks ginerjm and kicken for your helpful replies

 

Regarding the structure of the data, it is input via a textbox. The entry allows the user to enter a list of related things they have (not actually animals!), that is why it is stored as shown and I needed a way of selecting them all so that I can sort them into a list without the duplicates.

[kicken]

How you present the data to your users or collect the data from them is irrelevant to how it should be stored in your database. If you want them to just enter a space separated list in a text box that is fine. However, before you store that data you should explode the data into separate words, then insert each word as a single item in it's own row.

 

When you want to present the data, you can select all the words then implode them back into a list if you want (or wrap them into an HTML list structure).

[RebeccaD]

Thanks kicken.

 

The structure of the database is not of my doing. I have a number of surveys on a website constructed using the MachForm application. Unfortunately, although MachForm can present results in various formats, it does not report on any data gathered through text boxes (which are one of the default input types that can be used). I need to etract this data for presentation as results.

 

On a more general issue could you explain why you state that complex data such as this should be broken up please. I have never come across such a suggestion especially when it relates to the input from a text box. Are you suggesting that any such input should be broken up? For instance if an input to a text box required a sentence such as "Mary had a little lamb its fleece was white as snow", that this should be broken up into its constituent words and stored as individual fields in the database?

[kicken]

For instance if an input to a text box required a sentence such as "Mary had a little lamb its fleece was white as snow", that this should be broken up into its constituent words and stored as individual fields in the database?

No, the sentence as a whole is the data item there.

 

Data should be broken down into it's smallest desired unit possible. Based on your sample, each word is a separate piece of data, not all the words as a whole. For example, if the question was Enter your favorite pets: each pet type ("dog", "cat", "fish", etc.) is a piece of data and should be stored separately.

 

The reason you want to store them separately is so that you can take advantage of the databases ability to search for and manipulate data. For example you want a list of unique pet types. As I showed, if the data is stored properly the database can give that to you with a simple query. If the data is stored incorrect as in your current case, then you have to add a bunch of extra processing on the PHP end to get what you want. The same is true if you want to do something simple like "Who said they like fish?", with your current setup you have to pull the data, break it up, and search for fish yourself. With a proper design you could just ask the database with select id from favorite_pets where pet_type='fish'.

 

If you want to do further reading on the subject, google database normalization.

 

 

As for your original post, if you really can't do anything about how it's stored, then you just need to write a script to do what you already put in words for the most part.

- Query for all the data

- For each row

--- Split the field by spaces using explode or preg_split

--- Merge the list of words with a list from previous rows

- Run the large list through array_unique

- Sort the list using sort

$allWords = array();
while ($row=$db->fetch()){
   $words = preg_split('/\s+/', $row['data_col']);
   $allWords = array_merge($allWords, $words);
}
$allWords = array_unique($allWords);

[RebeccaD]

Thanks kicken for your explanation. Things like this are a great help to someone with limited knowledge.

 

Whilst we have been having this conversation I have cobbled together a script that does what I set out to achieve. I recognise that it is a bit dirty and written in mysql, but I haven't mastered mysqli or PDO statements yet.

<?php

include 'configuration file';

// Define variables
$tablename = "table name";
$col = "column to interrogate";
$ans = array();
$final = array();
$str = array();

mysql_select_db("database name");

// Count number of entries in column
$result = mysql_query("SELECT COUNT(*) FROM $tablename");
$rows = mysql_fetch_row($result);
$num = $rows[0];

// Get all the entries into an array
$result = mysql_query("SELECT $col FROM $tablename");
$store = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$store[] =  $row[$col];  
	} 

// Combine all entries in array into one array item
for($x=0; $x<=$num-1; $x++) {
	$str[0] = $str[0]." ".$store[$x];
	}

// Explode array item
$str[0] = explode(" ",$str[0]);
 
// Remove semi-colons from list
$ans = str_replace(' ', '', $str[0]); 

// Determine occurence of each word in list
$occurences = array_count_values($ans); 

// Remove duplicate words
$final = array_unique($ans);

// Sort A-Z
natcasesort($final); 

// Write results
echo "<table border='1'>";
echo "<tr><td colspan='2'>Total Responces: ".$num."</td></tr>";
echo "<tr><td>Condition</td><td>No.</td></tr>"; 

foreach($final as $i =>$key) {
	if($i<>0) {
	$out = ucfirst($key); //Upper case all first letters in case any entered as lower case
	echo "<tr><td>".$out."</td><td>".($occurences[$key])."</td></tr>";
	}
}

echo "</table>";

?>