Order a SQL Query by number of results

Question

I want to do the following:

$searchParams is an array, which contains some strings (dynamicly generated).

Now the Statement would be something like this:

SELECT manufacturer FROM shop_articles WHERE manufacturer LIKE '".$searchParams."%'

But what I want is the result with the most matches. Can I code that in one statement? So it would be something like

ORDER BY MATCHES DESC

How do I do that?

Do you want the count of the result? Like if you want a number of rows that have manufacturer like the input string? — Talha Ahmed Khan, Jul 05 '11 at 10:34
When you say match you mean that if searchParams containts 4 keywords and 3 of them exist in the string then MATCHES = 3 ? — niktrs, Jul 05 '11 at 10:38
No what I mean is, that I want the manufacturer with the most rows. Let's say I input two manufacturers, so $searchParams would contain those two strings and I want to know which one of those two has the most products in the database and return the name of this manufacturer — Johannes Klauß, Jul 05 '11 at 11:19

score 2 · Answer 1 · answered Jul 05 '11 at 10:35

2

SELECT COUNT(*) AS matches, manufacturer FROM shop_articles WHERE manufacturer LIKE '".$searchParams."%' GROUP BY(manufaturer) ORDER BY matches ASC

answered Jul 05 '11 at 10:35

Fidi

5,754
1
18
25

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

Actually you have to measure matching (likeness) between manufacturer and $searchParam. Unfortunatelly LIKE does not provide such functionality. You may use Lavenshtein distances. See this post - Implementation of Levenshtein distance for mysql/fuzzy search?

edited May 23 '17 at 12:13

Community

1
1

answered Jul 05 '11 at 10:42

Ēriks Daliba

708
4
4

niktrs · Accepted Answer · 2011-07-05T11:32:52.307

1

For each param in your searchParam array you have to make a like clause

    SELECT Manufacturer, COUNT(*) AS Matches FROM
    FROM shop_articles WHERE (
manufacturer LIKE '".$searchParams[0]."%' OR
manufacturer LIKE '".$searchParams[1]."%' OR 
...
manufacturer LIKE '".$searchParams[n]."%' OR )
    GROUP BY Manufacturer
    ORDER BY Matches

edited Jul 05 '11 at 11:32

answered Jul 05 '11 at 10:51

niktrs

9,858
1
30
30

that just returns the names of the manufacturers sorted by their position in the array. But I want to know, which of these manufacturers has the most products in the db. – Johannes Klauß Jul 05 '11 at 11:22
Updated based on your comments – niktrs Jul 05 '11 at 11:33

score 0 · Answer 4 · answered Jul 05 '11 at 10:37

0

SELECT manufacturer, COUNT(manufacturer) FROM shop_articles WHERE manufacturer LIKE '".$searchParams."' GROUP BY manufacturer ORDER BY COUNT(manufacturer) DESC

answered Jul 05 '11 at 10:37

Daniel Gruszczyk

5,379
8
47
86

Tim · Answer 5 · 2011-07-05T11:46:51.650

No, you cannot submit an array to the LIKE operator. It is more tedious than that. :-)

When your data are not yet properly regularized, so that you have variants of the manufacturer name in the PRODUCTS table (e.g. "HP", "Hewlett Packard") rather than an integer ManufacturerID, you have to go through the grunt work of reducing those variants to a single entity.

A typical approach for doing that (quite unavoidable) work is to create a Manufacturers table like this:

  Table: MANUFACTURER

                   manufacturerid  INTEGER primary key
                   manufacturername  varchar
                   primarymanufacturerid  INTEGER  FOREIGN KEY REFERENCES MANUFACTURER(manufacturerid)

The last column allows you to associate a variant name (e.g. "HP") with the row where the main manufacturer record is stored (e.g. "Hewlett Packard").

                   124,   Hewlett Packard,    124
                   367,   HP,                 124

The row where primarymanufacturerid = manufacturerid is the the main entity.

Then you could do this during an interim cleanup phase when you have not yet added a manufacturerid to the PRODUCTS table but it still has the name:

                 select * from products 
                 where manufacturer in
                 (
                   select manufacturername from manufacturer
                   where primarymanufacturerid = 
                    (
                     select primarymanufacturerid from manufacturer
                     where manufacturername = 'Hewlett Packard'
                     )
                 )

P.S. With a database engine that had support for functions and stored procedures, you could write your own function that accepted a delimited string of name variations, built a dynamic SQL statement, possibly using a temporary table to store the variant names one name per row, and returned a count of the matches. This would be a resource-intensive approach recommended only to assist in the clean-up phase -- not something I'd put into production for end-users to consume as their daily bread.

P.P.S. And, of course, once you have your MANUFACTURER table properly created, with the primarymanufacturerid references completed, you could add a [manufacturerid] column to your PRODUCTS table and update it accordingly, and then dispense with all of this roundabout stuff.

The data is regularized, but the thing is, that this array contains manufacturers and products. And I want to know, which values are manufacturers and which values are products. And if possible I'd like to get that in one SQL statement — Johannes Klauß, Jul 05 '11 at 11:50

score 0 · Answer 6 · answered Jul 05 '11 at 11:52

0

$sql = 'SELECT manufacturer FROM shop_articles WHERE 1=1'; for($i=0;$i

by this you can run query for $searchParams array dynamically

answered Jul 05 '11 at 11:52

Rukmi Patel

2,619
9
29
41

Order a SQL Query by number of results

6 Answers6