How many records have the key n times

Databases
1

I have a table like the above.

I have two variable, i.e. targetKey and targetCount.
I like to get how many records have the targetKey with targetCount.

When the targetKey is “is” and the targetCount is “2”,
The recordCount will be “1”. because The record (3) has “is” 2 times like the following.

When the targetKey is “is” and the targetCount is “3”,
The recordCount will be “2”. because The record (1) and (2) have “is” 3 times like the following.

When the targetKey is “city” and the targetCount is “2”,
The recordCount will be “0”. because The record (1) has 3 times and (3) have “city” just 1 time.

However the targetKey is “city” and the targetCount is “3”
The recordCount will be “1”.

Since I don’t know how to call the recordCount in mySQL, I made the PHP code below for it.

$testLD="SELECT n, say FROM myTable";
$testLT=mysqli_query($DBconnect, $testLD);

$targetKey='city';  $targetCount=3; 

$keyLen=strLen($targetKey);  $index=0;
$testL='<hr><hr>'; 
while ( $testLV=mysqli_fetch_assoc($testLT) ) {

$sayLen=strLen($testLV['say']);
$sayLen_without_key = strLen( str_replace($targetKey, '', $testL2V['say']) );
$lenDiffer=$sayLen-$sayLen_without_key;
$noKeyCount=$lenDiffer/$keyLen; 

if ($noKeyCount == $targetCount ) {
$index++; 
$testLV['say']=str_replace($targetKey, '<div class="ilb red">' .$targetKey . '</div>', $testLV['say']); 
}  
$testL=$testL.'(' .$testLV['n']. ') ' .$testLV['title']. '<br>' .$testLV['say']. '<br> 
(sayLen='.$sayLen.') (' .$sayLen_without_key. ') (' .$lenDiffer. ')' .$noKeyCount. ' <hr>'; 
}  

echo $testL.' ' .$index;

I like to get it in mySQL instead of PHP.

The following would -be SQL doesn’t work correctly but I hope it shows what I want.

SELECT count(*) as recordCount FROM myTable
WHERE say = ($targetKey, $targetCount  time)

I expect the would-be result below of the SQL above

Can I get my target result above(when $targetKey is “city” and $targetCount is “3”) in SQL instead of PHP with your help?

2

please do a SHOW CREATE TABLE for your table

3

The following is the SQL for copying from “myTable” to “myTable02”.

`CREATE  TABLE  `test`.`myTable02` ( `n` int( 11  )  NOT  NULL  AUTO_INCREMENT , `title` varchar( 256  )  NOT  NULL , `say` text NOT  NULL , PRIMARY  KEY (  `n`  ) ) ENGINE  =  MYISAM  DEFAULT CHARSET  = utf8; INSERT  INTO  `test`.`myTable02` SELECT  * FROM  `test`.`mytable` ;`

And there is a typo like the following in #1 post

4

something is not right…

if n is a primary key, how can there be multiple rows for n=1?

and which field, title or say, contains these phrases?

5

Yes, n is a column and primary key.

Field “say” has those phrases.
Actually the field “title” has no relation to my quesion.

The following is the rephrase of myTable

6

wow, what a dramatic difference from the first post

okay, let’s start

suppose you know the character length of targetKey – if the value is ‘is’ then the length is 2, if the value is ‘city’ then the length is 4

now, take a string value like this –

New York is a city. Tokyo is city in Japan. Berlin is a city in Europe

suppose you knew the length of that entire string

and then you removed every instance of ‘city’, and calculated the new length of the entire string

and if this new length was 12 less than the original length, you would conclude that ‘city’ was removed 3 times, right?

please say you understand this logic

7

Yes, I do. I understand it.

8

so how much more of a hint do you need?

( length of say - length of say with Targetkeys replaced ) / length of Targetkey = number of times Targetkey occurs in say

when this number equals TargetCount, return the row

9

to be a lot more explicit…

SELECT ...
  FROM ...
 WHERE $Targetcount =
        ( CHAR_LENGTH(say) - REPLACE(...
10

I understand your logic.
I already wrote the logic in PHP at post #1

Although I understand the logic and I can write it in PHP
But I don’t know how to write it in SQL.

11

there is a huge hint in post #9

YOU . MUST . TRY .

12

I tried the code below

$testD="SELECT count(*) as myCount FROM myTable
WHERE $targetCount = ( CHAR_LENTH(say) - REPLACE($targetKey) )";
$testT=mysqli_query($DBconnect, $testD);
$testV=mysqli_fetch_assoc($testT);

The code above says the WARNING below.

13

please go to your MySQL manual, where you will learn that the REPLACE function takes more than one paramter

14

Yes, it is a huge hint.
I tried the following SQL code.

$testD="SELECT count(*) as myCount FROM myTable
WHERE $targetCount = ( CHAR_LENTH(say) - REPLACE($targetKey) )";
$testT=mysqli_query($DBconnect, $testD);
$testV=mysqli_fetch_assoc($testT);

But it produces the WARNING below.

15

please go to your MySQL manual, where you will learn that the REPLACE function takes more than one parameter

17

How about the following.

I mean 2 times of singleQuote means empty value…

But it has still the same WARNING.

18

The following is another trial, but failed

4 means the length of $targetKey.

19

you are on the right path… change 4 to CHAR_LENGTH(‘$Targetkey’)

and please make sure you spell CHAR_LENGTH correctly

and remember that SQL requires strings to be enclosed in quotes… so CHAR_LENGTH($Targetkey) is incorrect and CHAR_LENGTH('$Targetkey') is correct

20 
$testD="SELECT count(*) as myCount FROM myTable
WHERE $targetCount = ( CHAR_LENGTH(say) - CHAR_LENGTH(REPLACE(say, '$targetKey', '') )/CHAR_LENGTH('$targetKey') )";
$testT=mysqli_query($dbConnect, $testD);
$testV=mysqli_fetch_assoc($testT);

The code above has no WARNING.

but the value of $myCount seems NOT correct.
The value of $myCount seems always zero(0).

What is wrong with the code above?

21

no idea

here’s a hint on how to debug your own queries –

SELECT n
     , ( CHAR_LENGTH(say) - CHAR_LENGTH(REPLACE(say, 'city', '') )/CHAR_LENGTH('city') as howmany
  FROM myTable

p.s. you are running out of questions

pretty soon i am going to stop holding your hand

apologies if that sounds harsh

1 Like