Topic awaiting preservation: Something to group results with a little "artificial intelligence" (Page 1 of 1)

Ok, I don't know how exactly to explain this, but here it goes...

I have a database full of records (2,005 atm) and I want to relate them in some way... for example:

I have this stuff in the database:

• Shiny Bead Shoes
• Shiny Bead Hat
• Shiny Bead Pants
• Shiny Beed Shirt
• Blue Brass Thing
• Red Brass Thing
• Green Brass Thing
• Yellow Brass Thing
• Orange Brass Thing
• Purple Brass Thing
• Stupid Shoes
• Dumb Shoes
• Bright n Yellow Shirt
• Bright n Yellow Pants
• Bright n Yellow Hat

I want a script to identify the different groups based on words and phrases in the database:

• Shiny Beed
  • Shiny Bead Shoes
  • Shiny Bead Hat
  • Shiny Bead Pants
  • Shiny Beed Shirt
• Brass Thing
  • Green Brass Thing
  • Yellow Brass Thing
  • Orange Brass Thing
  • Purple Brass Thing
• Bright n Yellow
  • Bright n Yellow Shirt
  • Bright n Yellow Pants
  • Bright n Yellow Hat

Do you see what I'm asking?
I have no idea how to make a script with "artificial intelligence" nor do I know where to start.
I don't even know if it's possible. I'm sure it is possible, but is it beyond me?

Right now I have a relation table that relates the items via id but it has to be updated manually, and with over 2,000 records in the database it's going to take awhile to get all the groups related with each other.

Using: PHP, MySQL, Linux (I'm not root btw)



(Edited by zavaboy on 06-22-2005 01:34)

I Would write a script to add the correct ID, should be able to loop through those results in a couple seconds....

As for identifying them - if that is the actual set up (or a close enough mimic), couldn't you just use the LIKE operator in your SQL? 'WHERE description LIKE shiny bead%'

Or, you could pull it all out at once and use php's subtr() to identify which items go in which group -

if (subtr("$description", 0, 10) == "shiny bead") {
$group = whatever id goes with shiny beads....;
}

etc...

Maybe I'm thinking to complicated, but I'm wanting to relate the groups without the script initially knowing what to look for. Just to try and find exact phrases in records next to each other. I allready have IDs accociated with the records in the database. The IDs do not determine the order the records go in, there is a special order they go in and related items are allways grouped up next to each other in that order.

Like is there a way I can take one of the records and see if phrases within the name of the record match the record after it? If there is a match, it makes a group for it based on what has been matched. It could match things like "Blah * Big", "Blah Here *", or "* Blahs". Basically any words that match the neighboring records.

Do you have a better idea of what I'm after?



(Edited by zavaboy on 06-22-2005 02:48)

What your asking about is not easy. What you are asking for is something that you will find hundreds of PHD students working on at any given time, and much of that work is just in models, and will never be implimented.

Stick with what DL has give you. The 'LIKE' keyword of your database is pretty powerful.

The idea.

Here is some psuedo type code.

$rank = 3;

$sql = "select title from table";
$results = query($sql);
while($line = fetch($result)){
  $wordarray = explode($line['title'])
  for($i = 0; $i<len($wordarray);$i++){
    for($j = $i+1; $j <len($wordarray); $j++){
      $substring = getSubstring($wordarray,$i,$j); //creates a substring of the words form $i to $j in $wordarray
      $sql = "select id, title from table where title like %substring%"
      $resuts = query($sql);
      if(numrows($results) > $rank){
        //some code to put all of the ids into a relationship table
      }
    }
  }
}

There are definately some logic errors in there. Especially the nested for loops, but I hope that gives you some idea on how you might approach this. It can take for ever to do something like this, that is why the PHD's spend so much time on this, because the outline I have above could not be used effectively in many real life situations. It only works when you can spare the time on an infrequent basis to do this, and where you do not have thousands upon thousands of records.

You might speed this up by keeping a hashtable of the phrases you have searhed for, and checking that to make sure you don't hit the DB with the same phrase twice.

Good luck.

Dan @ Code Town

Thank you...

I was kinda thinking it was a hard one. It doesn't need to be accurate or fast, as it will only be used by a small group of authorized people for finding possible groups to relate with each other. If it doesn't work out, I'm probably going to drop my idea, because it's a bit complicated and not really needed for what I need it for.

I'm not sure I follow the 'special order that they go in' bit - you have over 2000 records, and you have them all in 'special order' based on things that can be grouped together?

The whole idea of entering such items in a database, IMO, is that they do not require any special order - you can specify anything you want for each item and then sort and display them in countless ways based on that.

It definately seems more complciated to me try to work things the way you are saying than to simply predefine groups and use those groups to sort the items. That way you can also add an item to more than one group - another key purpose for this type of setup - and groups can be added or removed whenever needed...

I suppose you could grab each string, compare it to the previous string using explode(), then compare each word in the current array to each word in the previous array....grabbing any result with more than one match....
Not sure where to take it from there.

I think I'm going to stick with manualy grouping stuff... not only is it more acurate but (IMO) easier. I think I'll alter the relation table so there can be sub groups and such.

This could be something I could work on when I have time, right now I don't have much of it. Thanks for the help though!