How many records have the key n times

SELECT n, $targetCount = ( CHAR_LENGTH(say) - CHAR_LENGTH(REPLACE(say, 'city', '') )/CHAR_LENGTH('city') ) as howMany

The query above produces the result below.

My target result is the below.

first of all, that’s not the query i wanted you to run

second, did you really say $targetCount without substituting a value?

third, did you really search for 'city' instead of the actual word in your language

finally, do you realize that x=y in a SELECT clause will evaluate only as 0 or 1

Oh, it is a variable.
My final target result is the following.

My target result in #22 and below was my target result of step 1 for finding my final target result above

I am afraid I don’t realize it yet.

let’s go back to the query i wanted you to run in post #21

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

keep working on this until it produces correct results

yes i know it’s not the final result that you want, but you have to keep working on smaller pieces of the bigger puzzle until you understand the smaller pieces

I misunderstood the quote above that you asked whether it is a text “city” or a variable.

With the query what I want for my final target result, all variables $targetKey are English, not my ownLanguage.

I tried the query below.

$test5D="SELECT n
     , ( CHAR_LENGTH(say) - CHAR_LENGTH(REPLACE(say, 'city', '') )/CHAR_LENGTH('city') as howmany
  FROM myTable";
$test5T=mysqli_query($dbConnect, $test5D);

The code above produces the below.

the warning message you posted gives absolutely no clue as to whether it’s an SQL error or a php error

always test your SQL directly in the database, ~not~ via php

use heidisql, phpmyadmin, or similar… or even the command line

i already know what’s causing the error, but i want you to learn how to find errors too


I don’t know heidisql and I am using phpmyadmin.
How can I test the error with phpmyadmin?

The error is occurred in the line below

Although I know the error line, I don’t know how to fix it at the moment.

do you know how to run a query in phpmyadmin?

just open the sql tab, paste in the query, and run it

let me know what happens

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

I paste the code above in the query box after selecting myTable.

It says the following.


so the error occurred right before “as howmany”

let’s see if you can find the syntax error

what could cause a syntax error? well, you might have spelled a keyword incorrectly, like you did once before with CHAR_LENTH, but that’s not the case this time

your column name is also spelled correctly

that leaves only your sql punctuation that could be causing the error – parentheses, minus sign, division sign

i wonder what it could be

I tried several times with various query in the box of query in phpmyadmin. all failed.
I am sorry I have no idea about what’s wrong in punctuation.

it’s either the minus sign

or else it’s the division sign

or else it’s the parentheses

that’s it, i’m all out of hints for this week

1 Like
SELECT n, (CHAR_LENGTH(say) MINUS CHAR_LENGTH(REPLACE(say, 'city', '')) DIV CHAR_LENGTH('city') as howmany
FROM myTable

The code above produces the message below.

Is there any problems in parentheses?
(I can’t find any substitutes for parentheses)

so obviously MINUS and DIV are wrong – where in the world did you dig those up?

they couldn’t have come out of the MySQL manual, right? i mean, you do regularly check what you’re doing in the manual, right?

edit: turns out DIV is actually valid, sorry – but it doesn’t apply here, because we’re dealing with CHAR_LENGTH integers

i ran out of hints earlier

but yes

`SELECT n, (CHAR_LENGTH( say )  - CHAR_LENGTH( REPLACE( say,  'city',  ''  )  )  / CHAR_LENGTH(  'city'  ) ) AS howmany`

The code above works fine.

I passed the step 1 gate on the way of getting my final target.

How can I get my target result with 2 variables, i.e. $targetkey and $targetCount?

it might actually run, but it produces the wrong results

please try to interpret what the “howmany” values actually mean

I guess this is going to be another long puzzle.

“interpret the howmany means” in the quote above, explain what I want in English or write it in SQL language?

“howmany” means how many times the string ‘city’ occurs in the column say

look at your data in the column say – mentally count a few of them – for several rows

then compare how many times ‘city’ actually occurs with the values produced by your recent query

I think I have to make the machine memorize +1 whenever the row is matched to $keyCount
AND the last value of memorized count is going to be the target result I want to get.

I have no idea for comparing.

Anyway I have to put the variable $keyCount somewhere in the query.

This topic is becoming very long at the moment.
How about giving me the just code at this moment.

I admire you endurance.
By the way, I found the query box in phpMyadmin is a good tool.
I’ll use it from now. Thank you for that.