How To Get Sql Present Matching Rows In DESC Order After Adding Total Values Of Many Cols?

idrisalisylbd · April 10, 2023, 6:30pm

Hello Developers,

How do I write Sql command for Mysql to add up number values of given columns and rank rows based on total values in ascending order ?

If there was only a single “keyword point” (kw1_point) to calculate, then I would have written the SQL like this using prepared statements:

$sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? order by kw1_point desc LIMIT 100";

Difficulty popsup when there are more than one column’s values to add up.

Example:

MySql table: keywords

“kw” - keyword.

id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
----------------------------------------------------------------------------------------------
0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
----------------------------------------------------------------------------------------------
1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3
----------------------------------------------------------------------------------------------
2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
----------------------------------------------------------------------------------------------
3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
----------------------------------------------------------------------------------------------

Glancing at each of the above rows points, we see this which is not in descending order:

id 0 = 10 points
id 1 = 8 points
id 2 = 11 points
id 3 = 20


NOTE: All 4 keywords exists on the first 3 matching rows. 
However, only 3 words exist in the final matching row.
And the final matching row's keywords are not in the order of my keyword search either.
But, this should not matter. 
Sql should ignore in which order the keywords are in on each column when comparing the order of my searched keywords. Sql should just:

**A). Find matching rows, regardless of how many of my searched keywords exist on each row;**
**B). Count the totals of each points, (count more than one column in this case), in each row; And**
**C) List the rows in the point's descending order.**

So, in this case, the SQL query should present rows in this descending order:

id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point

3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5

2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3

0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2

1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3

Calculating each of the above rows points, we get rows ranked in this order:

id 3 = 20
id 2 = 11 points
id 0 = 10 points
id 1 = 8 points

These following SQL commands do not work:
I was suggested the following but it only presents me with 1 matching row (**id:0**) even though there exist other matching rows in my mysql tbl.

**SQLs**
Exact match

SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? ORDER BY (kw_1_point+kw_2_point+kw_3_point+kw_4_point) DESC


Fuzzy match:

SELECT * from keywords WHERE kw1 LIKE ? OR kw2 LIKE ? OR kw3 LIKE ? OR kw4 LIKE ? ORDER BY (kw_1_point+kw_2_point+kw_3_point+kw_4_point) DESC

Drummin · April 10, 2023, 8:29pm

I see no logic to this DB table design. What is the point of adding totals across first record with id = ‘3’, when all the key values don’t match.

A record should have distinct fields, which in this case looks like id, kw and kw_point. Remember if you need to store more than 1 of any field it should be in a different table.
There should not be kw1, kw2 etc. In fact, this table should probably be called keyword_points with the 3 fields I mentioned and another table called keywords with just the fields id and kw so there is a single record to search against.

You can then query for keywords and the sum of points from the keyword_points table. You would GROUP BY the keyword so the sums are totaled on each keyword. The query would look something like this.

SELECT 
 k.kw 
, sum(p.kw_points) as 'points' 
FROM `keywords` k 
	LEFT JOIN `keyword_points` p
		ON p.kw = k.kw 
GROUP BY p.kw

This would give you a result like this.
result1

If you are going to be searching for a keyword it would be by 1 field.

SELECT 
 k.kw 
, sum(p.kw_points) as 'points' 
FROM `keywords` k 
	LEFT JOIN `keyword_points` p
		ON p.kw = k.kw
WHERE k.kw = ?
GROUP BY p.kw

Added ORDER BY the sum DESC

SELECT 
 k.kw 
, sum(p.kw_points) as 'points' 
FROM `keywords` k 
	LEFT JOIN `keyword_points` p
		ON p.kw = k.kw 
GROUP BY p.kw 
ORDER BY sum(p.kw_points) DESC

m_hutley · April 10, 2023, 8:41pm

I think I understand the intent of this a bit, Drummin. Try this logic:

For a given row, points equals to
CASE WHEN kw1 IN (searchterm1, searchterm2…) THEN kw1_point ELSE 0 END +
CASE WHEN kw2 IN (searchterm1, searchterm2…) THEN kw2_point ELSE 0 END +
CASE WHEN kw3 IN (searchterm1, searchterm2…) THEN kw3_point ELSE 0 END +
CASE WHEN kw4 IN (searchterm1, searchterm2…) THEN kw4_point ELSE 0 END

ORDER BY points.

(It’s a Relevancy sort)

Drummin · April 10, 2023, 9:08pm

But is it a good design to have kw1, 2, 3 and 4 in the first place?
Is it OK to have a CASE where searchterm1 and searchterm2 points are added together?
I get your point though.

m_hutley · April 10, 2023, 9:11pm

Is it a good design? Maybe, maybe not. I assume there are either more fields in the row, or keywords.id is a foreign key to another table somewhere. shrug

idrisalisylbd · April 10, 2023, 10:48pm

@Drummin
@m_hutley

kw stands for “keyword”. So, it is a column.

“kw_1_point” is another column.

“kw_2” is another column.
“kw_2_point” is another column.

“kw_3” is another column.
“kw_3_point” is another column.

“kw_4” is another column.
“kw_4_point” is another column.

I am putting one phrase per row or record.
One keyword per column. Multiple keywords per row.

So, if I got this phrase:

“mark pro deveoper”.

Then my script will break the phrase up and put each individual keyword on separate columns like so:

id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point

0 | mark | 5 | pro | 100 | developer | 50

As you can see, the whole phrase is in one record or one row. But each individual keyword per column.

And I give each keyword a point.

Now, all I need to learn is to count all points (from all cols) per row/record and rank them according to their points. Rank each row/record, I mean.

Need the sql that does this.
My tables looked like these

users
members
domains
links

Changed the links to keywords.

It had cols like this:

But I did not want to add more than one keyword in the same row/record (under “meta keywords column”) and I did not want to present results to my searchengine users based on “id” cols ranking. And so …

Now, I have changed it to something like this to give each keyword a point per record (link/url):

id|domain|url|title|kw_1|kw_1_point|kw_2|kw_2_point|kw_3|kw_3_point|kw_4|kw_4_point|

Anyway, how to write the SQL for it to calc following rows number values

kw_1_point, kw_2_point, kw_3_point & kw_4_point

And then rank the rows based on points (number values) in DESC order ?

This failed:


$sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? ORDER BY (kw_1_point+kw_2_point+kw_3_point+kw_4_point) DESC LIMIT

It only presents the first row (id: 0) even though other rows got matching keywords of my search.

And yes, building a searchengine.
In short, I need to learn to add up values of each column that has INT values and then get SQL to present/rank in DESC order (of the values).

idrisalisylbd · April 10, 2023, 10:59pm

Frankly, I have no idea why this failed:


$sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? ORDER BY (kw_1_point+kw_2_point+kw_3_point+kw_4_point) DESC

idrisalisylbd · April 10, 2023, 11:12pm

@Drummin

Sorry mate!
I made a mistake on my original post which I have now fixed. Talking about the first table. Fixed it.
My table actually looks something like this:

id | url | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point

0 | drumminDOTcom | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2

1 | m_huntleyDOTcom| mobile | 1 | phone | 1 | apps | 3 | tutorial | 3

2 | idrisDOT.com | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3

3 | sitepointDOTcom | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5

Now, I need to write SQL so it counts the total INT values (kw1_points, kw2_points, kw_3_points & kw4_points) of each row/record and rank them (present them to keywpord searcher) based on row/record points.
So from our example, it would rank like this:

3 | sitepointDOTcom | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
2 | idrisDOTcom| tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
0 | drumminDOTcom | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
1 | m_huntleyDOTcom| mobile | 1 | phone | 1 | apps | 3 | tutorial | 3

This might not be a traditional way of building a searchengine but I know what I am doing. I just need to learn to get SQL count the values of each cols on each row and then rank the rows based on total point of each row.
That is all. Pretty simple for you PROS to thinkup the SQL on the spot.

idrisalisylbd · April 10, 2023, 11:13pm

@m_huntley

Sorry mate! I hope my above post clears your confusion too.

Drummin · April 11, 2023, 12:12am

Well then you probably want something like this.

SELECT * 
, SUM(coalesce(`kw_1_point`,0) +coalesce(`kw_2_point`,0)+coalesce(`kw_3_point`,0)+coalesce(`kw_4_point`,0)) AS total 
FROM `keywords` 
GROUP BY `id` 
ORDER BY SUM(coalesce(`kw_1_point`,0) +coalesce(`kw_2_point`,0)+coalesce(`kw_3_point`,0)+coalesce(`kw_4_point`,0)) DESC

Note: With the different posts in this thread I am not 100% sure of your table field names so adjust accordingly. Also you WHERE search conditions can added to the query I posted before the GROUP BY statement.

idrisalisylbd · April 12, 2023, 7:53pm

@Drummin

I am an intermediate level student and I have a list of SQL query basics which I must learn. And so, I just structured the following table which you see below, to get my point across to you fine folks what I intend to learn from senior programmers like yourselves. And so thanks for baring with me and try aiding my learning as much as possible, even though my table is unorthodox. Thanks for understanding.

I made one serious mistake on my ORIGINAL POST which someone pointed-out to me.
I was intending to ONLY COUNT the POINTS of MATCHING KEYWORDS on EACH ROW/RECORD.
And NOT COUNT the POINTS of the non-MATCHING KEYWORDS on the MATCHING RECORDS before assigning the point score to the record.
And then RANK the MATCHING records in DESC ORDER.
So, how to DO IT ? The SQL, I mean ?

Maybe you are confused and so let us draw-up a new table example here for your convenience and mine.
Say, I did an EXACT MATCH (no wildcard) keywords/keyphrase search for:

php book usa

Or,

php+book+usa

Now, it should not matter in what arrangements the searched keywords are in OR how many MATCHING keywords exist in a record.
Aslong as any keyword exists in a record, that record should be presented to the searcher.
Say my table records look like this:

Table: Links
id | url | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
0 | drumminDOTcom | usa | 3 | phone | 3 | apps | 2 | tutorial | 2
1 | mypointDOTcom| uk | 1 | php | 4 | apps | 3 | price | 3
2 | borobhaisabDOTcom | tutorial | 3 | book | 3 | php | 2 | usa | 3
3 | phpDOTcom | PHP’s | 5 | books | 5 | united states america | 5 | prices | 5
4 | phpDOTcom | python | 5 | book | 5 | apps | 5 | usa | 5

I have highlighted the matching keywords.

NOTE 1:
Notice that the row, id:3, actually got the matching keywords. But since the keywords are not EXACT, then SQL should NOT draw-up this record.

PHP’s is not an exact match of php.
Books is not an exact match for book.
united states america is not an exact match for usa.

Hence, record: id:3, should be IGNORED.
Also notice record id:4. It got php.com. But that is not an exact match for php. Hence, that Mysql cell should be ignored too.

NOTE 2:
Record id: 2 got 3 matches, while record id: 4 got 2.
However, record id:3 scores a total of 10 points while record id:2 scores 8.
Hence, in this case, record 1d:3 should get better ranking when presented to the keyword searcher.

Because ranking should not be based on how many keywords matched in a record but total points of all matching keywords in a record.

So, this is how the SQL should rank things before presenting the records to the keyword searcher:

Result …
4 | phpDOTcom | python | 5 | book | 5 | apps | 5 | usa | 5 -------------------- 10 point
2 | borobhaisabDOTcom | tutorial | 3 | book | 3 | php | 2 | usa | 3 ------------------8 point
1 | mypointDOTcom| uk | 1 | php | 4 | apps | 3 | price | 3 ---------------------- 4 point
0 | drumminDOTcom | usa | 3 | phone | 3 | apps | 2 | tutorial | 2 ---------------------- 3 point

The total point section on the right on the above presented result, is for your convenience ONLY to understand things how I want done. Obviously, I do not expect SQL to echo points in that manner. I can write php to do that, if I really want to.

You may have a question, why should SQL present record id: 0 here, since the record is totally irrelevant.

ANSWER: It does not matter if the record is irrelevant or not to the keyword search in such examples. There is an EXACT matching keyword here “usa”. And so, this record counts in this EXACT MATCH query.

So, how to write the SQL ?

Thanks for your time & effort.

idrisalisylbd · April 12, 2023, 8:14pm

@m_huntley

I have another SQL query similar to the SQL query I asked for on my previous post to drummin.
I do not think it will be fair on drummin to work out for me 2 sophisticated SQL queries and so I hope you won’t mind me expecting you to show me how this 2nd SQL should be ?

You see, here, I am searching for an SQL that will search for records with EXACT MATCHING keyword per column but RANK the records first based on number of matching keywords found per record and then based on score per record.

Say, I did an EXACT MATCH (no wildcard) keywords/keyphrase search for:

php book usa

Or,

php+book+usa

Now, it should not matter in what arrangements the searched keywords are in BUT …

how many MATCHING keywords exist in a record. (1st priority).
total score of record of matching keywords’ points.

Aslong as any keyword exists in a record, even one matching keyword, that record should be presented to the searcher.
Say my table records look like this:

Table: Links
id | url | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
0 | drumminDOTcom | usa | 3 | phone | 3 | apps | 2 | tutorial | 2
1 | mypointDOTcom| uk | 1 | php | 4 | apps | 3 | price | 3
2 | borobhaisabDOTcom | tutorial | 3 | book | 3 | php | 2 | usa | 3
3 | phpDOTcom | PHP’s | 5 | books | 5 | united states america | 5 | prices | 5
4 | phpDOTcom | python | 5 | book | 5 | apps | 5 | usa | 5

I have highlighted the matching keywords.
NOTE 1:
Notice that the row, id:3, actually got the matching keywords. But since the keywords are not EXACT, then SQL should NOT draw-up this record.

PHP’s is not an exact match of php.
Books is not an exact match for book.
united states america is not an exact match for usa.

Hence, record: id:3, should be IGNORED.
Also notice record id:4. It got php.com. But that is not an exact match for php. Hence, that Mysql cell should be ignored too.

NOTE 2:
Record id: 2 got 3 matches, while record id: 4 got 2.
However, record id:3 scores a total of 10 points while record id:2 scores 8.
And so, even though record id:4 got more points, you can clearly see that, it has less matching keywords than record id:2.
Hence, in this case, record 1d:2 should get better ranking when presented to the keyword searcher.

Because here, in this 2nd SQL I seek, ranking should BE based on how many keywords matched in a record AND after that, ranking should be based on total points of all matching keywords in a record.

So, this is how the SQL should rank things before presenting the records to the keyword searcher:

Result …
2 | borobhaisabDOTcom | tutorial | 3 | book | 3 | php | 2 | usa | 3 -------8 point
4 | phpDOTcom | python | 5 | book | 5 | apps | 5 | usa | 5 -------------------- 10 point
1 | mypointDOTcom| uk | 1 | php | 4 | apps | 3 | price | 3 ---------------------- 4 point
0 | drumminDOTcom | usa | 3 | phone | 3 | apps | 2 | tutorial | 2 ---------------------- 3 point

The total point section on the right on the above presented result, is for your convenience ONLY to understand things how I want done. Obviously, I do not expect SQL to echo points in that manner. I can write php to do that, if I really want to.

You may have a question, why should SQL present record id: 0 here, since the record is totally irrelevant.

ANSWER: It does not matter if the record is irrelevant or not to the keyword search in such examples. There is an EXACT matching keyword here “usa”. And so, this record counts in this EXACT MATCH query.

So, how to write the SQL ?

Thanks for your time & effort.

idrisalisylbd · April 12, 2023, 8:17pm

Folks,

Reading my two previous posts may confuse you.
You may think I am asking the same question or asking for the same SQL here.
But I am not. If you notice the result … of both posts and glance at the two final tables on both posts then things will become clear.
And so, I asked drummin one question and asked m_huntley a different question. They are similar but not same.
You are welcome to chime in and answer my above two posts if you wish.

Drummin · April 12, 2023, 11:42pm

And again, why is the table not designed with the fields id, url, kw and kw_point?

Drummin · April 13, 2023, 7:21am

Well, well, well. So normally you search against a single table field, i.e. kw = 'search'.
In this configuration all fields in this record can be identified by the field searched by and so to get kw_point you can search for the term in kw and these 2 fields directly relate to each other.

But because of your table structure we are searching 4 fields on 1 record we need to present this search term 4 times to these 4 fields that could get you a record match, but because your searching against 4 fields we can’t get the kw_point value based on the search result.

As m_hutley pointed out in #3 above you need to use a CASE condition against each field for a match to the search term and if there is a match added up and defined as points. Because we add GROUP BY id the points are tallied for each record. So at this point we have passed our search term to the query 8 times.

You also wish the records displayed in DESC order based on this point total we need to use this CASE statement in the ORDER BY condition. We have passed our search term to the query 12 times.

You give an example of using multiple search words e.g. php book usa so in this case you might be throwing 36 terms at the query… Adding those terms into a query would look something like this.

SELECT 
*
, (
(CASE WHEN kw1 IN ('php','book','usa') THEN kw1_point ELSE 0 END) +
(CASE WHEN kw2 IN ('php','book','usa') THEN kw2_point ELSE 0 END) +
(CASE WHEN kw3 IN ('php','book','usa') THEN kw3_point ELSE 0 END) +
(CASE WHEN kw4 IN ('php','book','usa') THEN kw4_point ELSE 0 END)
) AS 'points' 
FROM `keywords`
WHERE kw1 IN('php','book','usa') OR kw2 IN('php','book','usa') OR kw3 IN('php','book','usa') OR kw4 IN('php','book','usa')
 
GROUP BY id 
ORDER BY SUM((CASE WHEN kw1 IN ('php','book','usa') THEN kw1_point ELSE 0 END) +
(CASE WHEN kw2 IN ('php','book','usa') THEN kw2_point ELSE 0 END) +
(CASE WHEN kw3 IN ('php','book','usa') THEN kw3_point ELSE 0 END) +
(CASE WHEN kw4 IN ('php','book','usa') THEN kw4_point ELSE 0 END)
) DESC

Which against my records comes up as this.

Now to make this a dynamic working query each of those “search terms” inside the IN() condition would need to be replaced by a ? separated by a comma. We will need to count() the search terms and this is pretty easy to do if the data is in an array so we can convert the search string into an array.using explode().

$search = "php book usa";
$searchArr = explode(" ",$search);

We can now count our search array and using array_fill() we can build an array of question marks representing each of our search terms.

array_fill(0, count($searchArr), '?')

Array
(
    [0] => ?
    [1] => ?
    [2] => ?
)

we then can use implode() to convert this into the comma separated list of question marks for the IN() conditions in our query.

$bindClause = implode(',', array_fill(0, count($searchArr), '?'));

and update our query.

$sql = "SELECT 
*
, SUM(
	(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
	(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
	(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
	(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
) AS 'points' 
FROM `keywords`
WHERE `kw1` IN(".$bindClause.") OR `kw2` IN(".$bindClause.") OR `kw3` IN(".$bindClause.") OR `kw4` IN(".$bindClause.")  
GROUP BY id 
ORDER BY SUM(
	(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
	(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
	(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
	(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
) DESC";

Now we need to define the type of value being passed and in this case search terms are strings defined as s and because of this query we need 12 s for every search term being passed. Again we can count the search array and multiply by 12 and use str_repeat() to build this string.

$bindString = str_repeat('s', count($searchArr)*12);

Now we our search array extended so the terms are repeated 12 times.
I just used a for statement to loop through the array 12 times to build a search values array.

$searchvalues = array();
for($n=1;$n<=12;$n++):
	foreach($searchArr as $term):
		$searchvalues[] = $term;
	endforeach; 
endfor;

It looks a bit like this.

Array
(
    [0] => php
    [1] => book
    [2] => usa
    [3] => php
    [4] => book
    [5] => usa
    [6] => php
etc...
}

We can now add the $bindString and $searchvalues to our query.

$query = $db->prepare($sql);			
$query->bind_param($bindString, ...$searchvalues); 		
$query->execute();

Note: The ... before $searchvalues tells bind_param() to unpack the $searchvalues array so it is handled appropriately.

All in all my copy looks like this.

$search = "php book usa";
$searchArr = explode(" ",$search);
 
$bindClause = implode(',', array_fill(0, count($searchArr), '?'));
$bindString = str_repeat('s', count($searchArr)*12);

$searchvalues = array();
for($n=1;$n<=12;$n++):
	foreach($searchArr as $term):
		$searchvalues[] = $term;
	endforeach; 
endfor;

$sql = "SELECT 
`id`, `url`,  `kw1`, `kw1_point`, `kw2`, `kw2_point`, `kw3`, `kw3_point`, `kw4`, `kw4_point`
, SUM(
	(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
	(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
	(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
	(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
) AS 'points' 
FROM `keywords`
WHERE `kw1` IN(".$bindClause.") OR `kw2` IN(".$bindClause.") OR `kw3` IN(".$bindClause.") OR `kw4` IN(".$bindClause.")  
GROUP BY id 
ORDER BY SUM(
	(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
	(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
	(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
	(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
) DESC"; 

	
$query = $db->prepare($sql);			
$query->bind_param($bindString, ...$searchvalues); 		
$query->execute();
$result = $query->get_result();
	
while($row = $result->fetch_assoc()){

echo "<pre>";
print_r($row);	
echo "</pre>";

}

Drummin · April 13, 2023, 6:26pm

Doing ORDER BY MATCHES then POINTS is whole other matter I am not going to mess with at this time but as far as displaying by Points or Matches that is rather simple to do. I’ll start by making an array of the sort by options.

$sortbyOptions = array('Points','Matches');

I’ll be making a form to send the selected 'sortby' using method POST so I will look for this, compare the value against the $sortbyOptions array and if found set this value to the variable $sortby, otherwise set a default value.

$sortby = ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['sortby']) && in_array($_POST['sortby'], $sortbyOptions) ? $_POST['sortby'] : 'Points');

I’ll now put together the small form I mentioned.

echo '<form action="" method="post">
	<select name="sortby" onchange="this.form.submit()">
		<option value="">Order By</option>'."\r";
		foreach($sortbyOptions as $sortbyOption):
			$selected_sortby = (!empty($sortby) && $sortby == $sortbyOption ? ' selected="selected"' : '');
			echo '<option value="'.$sortbyOption.'"'.$selected_sortby.'>'.$sortbyOption.'</option>'."\r";
		endforeach;
	echo '</select>
</form>'."\r";

Now we take our query and break it into 2 parts separating the ORDER BY section from the rest of the query.

$sql = "SELECT 
*
, SUM(
	(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
	(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
	(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
	(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
) AS 'points' 
FROM `keywords`
WHERE `kw1` IN(".$bindClause.") OR `kw2` IN(".$bindClause.") OR `kw3` IN(".$bindClause.") OR `kw4` IN(".$bindClause.")  
GROUP BY id  ";	 

$sql .= "ORDER BY SUM(
	(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
	(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
	(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
	(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
) DESC";

As this ORDER BY section is based on points we can wrap it in an IF condition so it will be used if `$sortby == “Points” so it now looks like this

if(!empty($sortby) && $sortby == "Points"):
	$sql .= "ORDER BY SUM(
		(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
		(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
		(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
		(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
	) DESC";  
endif;

We can then COPY this section of code and modify it for Matches. Notice in the CASE statements above, which basically says “when kw is found in our search values use kw_point” and then we add those points together to give us a point value for the record. So for Matches section we can simply give it a value of 1 for a match or 0 if not instead of points like so.

if(!empty($sortby) && $sortby == "Matches"):
	$sql .= "ORDER BY SUM(
		(CASE WHEN kw1 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw2 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw3 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw4 IN (".$bindClause.") THEN 1 ELSE 0 END)
	) DESC";   
endif;

ALSO you mentioned that search terms might be entered with a + sign between terms so I addressed this by first replacing the + with a space and then only allowing letters and a space.

$search = "php+book   usa";
$search = str_replace("+", " ", $search);
$search = preg_replace("/[^a-zA-Z \s]/", "", $search);

And this statement?

I do not expect SQL to echo points in that manner. I can write php to do that, if I really want to.

Really? Using your table structure and knowing that you can’t get point values directly from search terms, how did you expect to do that?

You can see all the hoops you need to go through to get results from this “questionably designed” table. A table with the fields id, url, kw and kw_point would be quite easy to search through and add up points from different urls for matching keywords.

To build your query so you can ORDER BY MATCHES then POINTS you would need to modify the way the query is built when you sort by Matches because you would need to input 4 more search terms into your query during this mode going from 12 times to 16. You can make a little controlling variable to set that number.

$bindtimes = (!empty($sortby) && $sortby == "Matches" ? 16 : 12);

Then on the lines where I hard coded 12 I can now replace with this variable.

$bindString = str_repeat('s', count($searchArr)*$bindtimes);

$searchvalues = array();
for($n=1;$n<=$bindtimes;$n++):
	foreach($searchArr as $term):
		$searchvalues[] = $term;
	endforeach; 
endfor;

Then it’s a matter of changing the ORDER BY statement to use Matches then Points.

if(!empty($sortby) && $sortby == "Matches"):
	$sql .= "ORDER BY SUM(
		(CASE WHEN kw1 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw2 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw3 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw4 IN (".$bindClause.") THEN 1 ELSE 0 END)
	), SUM(
		(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
		(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
		(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
		(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
	) DESC";   
endif;

All in All my version now looks like this.

$sortbyOptions = array('Points','Matches');
$sortby = ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['sortby']) && in_array($_POST['sortby'], $sortbyOptions) ? $_POST['sortby'] : 'Points'); 	
echo '<form action="" method="post">
	<select name="sortby" onchange="this.form.submit()">
		<option value="">Order By</option>'."\r";
		foreach($sortbyOptions as $sortbyOption):
			$selected_sortby = (!empty($sortby) && $sortby == $sortbyOption ? ' selected="selected"' : '');
			echo '<option value="'.$sortbyOption.'"'.$selected_sortby.'>'.$sortbyOption.'</option>'."\r";
		endforeach;
	echo '</select>
</form>'."\r";	

 //php book usa tutorial
$search = "php+book   usa";
$search = str_replace("+", " ", $search);
$search = preg_replace("/[^a-zA-Z \s]/", "", $search);

//echo $search;

$searchArr = explode(" ",$search);
$bindtimes = (!empty($sortby) && $sortby == "Matches" ? 16 : 12); 
 
$bindClause = implode(',', array_fill(0, count($searchArr), '?'));
$bindString = str_repeat('s', count($searchArr)*$bindtimes);

$searchvalues = array();
for($n=1;$n<=$bindtimes;$n++):
	foreach($searchArr as $term):
		$searchvalues[] = $term;
	endforeach; 
endfor;

$sql = "SELECT 
*
, SUM(
	(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
	(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
	(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
	(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
) AS 'points' 
FROM `keywords`
WHERE `kw1` IN(".$bindClause.") OR `kw2` IN(".$bindClause.") OR `kw3` IN(".$bindClause.") OR `kw4` IN(".$bindClause.")  
GROUP BY id  ";
	 
if(!empty($sortby) && $sortby == "Points"):
	$sql .= "ORDER BY SUM(
		(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
		(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
		(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
		(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
	) DESC";  
endif;

if(!empty($sortby) && $sortby == "Matches"):
	$sql .= "ORDER BY SUM(
		(CASE WHEN kw1 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw2 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw3 IN (".$bindClause.") THEN 1 ELSE 0 END) +
		(CASE WHEN kw4 IN (".$bindClause.") THEN 1 ELSE 0 END)
	), SUM(
		(CASE WHEN kw1 IN (".$bindClause.") THEN kw1_point ELSE 0 END) +
		(CASE WHEN kw2 IN (".$bindClause.") THEN kw2_point ELSE 0 END) +
		(CASE WHEN kw3 IN (".$bindClause.") THEN kw3_point ELSE 0 END) +
		(CASE WHEN kw4 IN (".$bindClause.") THEN kw4_point ELSE 0 END)
	) DESC";   
endif;
	
$query = $db->prepare($sql);			
$query->bind_param($bindString, ...$searchvalues); 		
$query->execute();
$result = $query->get_result();
	
while($row = $result->fetch_assoc()){

echo "<pre>";
print_r($row);	
echo "</pre>";

}

Anyway, best of luck on your project.

idrisalisylbd · April 13, 2023, 6:51pm

@Drummin

Gosh! That’s a lot of work!
Thanks man!
But, it will probably take me more than a month to learn & digest all that. Not familiar with CASE. Others have suggested it. Just had a glance at the tutorial lastnight. Gonna have to continue it tonight.
I see you have written code to auto generate the prepared statements (preps). This will become handy for my thread here:

Do you mind helping me out on that one too ? A very tiny issue.

system · July 14, 2023, 1:51am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.