0

Consider following are my two tables of DB and contents in them are being fetch to an array via mysql_fetch_array($result):

Table 1                                                                       Table 2

+-------+-------------------------+      +-------+------------------------------+
| USD    | Model                   |     | USD   | Model                        |
+-------+-------------------------+      +-------+------------------------------+
| 700    | iPad 2 WiFi 16GB        |      | 710     | iPad2 WiFi 16GB              |
| 400    | iPhone 4S 16GB          |      | 450     | iPhone4S 16GB                |
| 250    | iPod Touch(4th Gen)8GB  |      | 200     |iPod Touch 4th Generation 8GB |
+-------+-------------------------+      +-------+------------------------------+

Now what I wanna do is to create a new array and compare the two arrays where similar_text($array1,$array2) percentage = 90 or above and then sort the third array by asc onbehlaf of USD. Something like in terms of PHP + mysql

Select * from Table1,Table2 where similar_text(Table1.model,Table2.model) > 90 order by Table1.USD asc

Note: I tried LEVENSHTEIN in mysql but its extremely slow Especially when there are 1000 of rows in your table

So output should look like the following:

+-------+-------------------------------------------------------------+
| USD1    | Model1                         USD2   | Model2            |
+-------+----------------------------------------+------------------------------+
| 250    | iPod Touch(4th Gen)8GB  |      | 200     |iPod Touch 4th Generation 8GB |
| 400    | iPhone 4S 16GB          |      | 450     | iPhone4S 16GB                |
| 700    | iPad 2 WiFi 16GB        |      | 710     | iPad2 WiFi 16GB              |
+-------+-------------------------+      +-------+------------------------------+

Kindly let me know how can i make it happen?

soft genic
  • 2,016
  • 3
  • 27
  • 44
  • 1
    Are you aware that if your tables contain `m` and `n` number of rows this `similar_text` routine will be called `m*n` times? – Shiplu Mokaddim Nov 18 '12 at 11:10
  • Where is your `similar_text` function? I mean before you have not solved that, I would not make up much of my mind about the database structure you start your question with and which is the largest part of it. Also look at the title of your question. As long as you're just concerned about sorting an array, we have QA material on site already about it. But I thin you are more concerned about finding similar texts. – hakre Nov 18 '12 at 11:13
  • 1
    http://stackoverflow.com/search?q=%5Bphp%5D+similar+strings – hakre Nov 18 '12 at 11:14
  • Get the EAN codes of the products, then you don't need to compare (non-matching) product names. – hakre Nov 18 '12 at 11:20
  • @hakre i just need a little push on this rest i can do all my own i dont have efficient solution of searching via sql as it doesnot work for `ifone4gb` and `ifone 4gb` cause sql works for exact matches and i cannot take ifone as standard cause ifone has various models. And yes i am much concerned about searching similar text and result should not take more than 30 seconds to generate. – soft genic Nov 18 '12 at 14:57
  • @shiplu.mokadd.im it means `similar_text` and `LEVENSHTEIN`in mysql has similar searching time due to which they are extremely slow like for 1000 of rows??? – soft genic Nov 18 '12 at 14:58

0 Answers0