0

I'v been looking at several tutorials and stackoverflow answers, but I can't get this to work for me. Basically, I want tp echo the number of instances a certain string is mentioned through all posts.

Here's what I've cobbled together so far using answers I've read. I'm putting this inside a loop for a page template:

$count = 0;
$sql = mysql_query("SELECT LENGTH(post_content) - LENGTH(REPLACE(post_content,'string I want to count','')) AS 'occurs' FROM wp_db WHERE post_content LIKE '%string I want to count%'");
while ($data = mysql_fetch_assoc($sql)) {
    $count += $data['occurs'];
}
echo $count;

This seems to break my template though and nothing gets echoed.

Hoping someone can help me recognize what I'm doing wrong here. Any help is, as always, greatly appreciated!

Eric Brockman
  • 824
  • 2
  • 10
  • 37
  • `'occurs'` in the query should have no quotes, or you can use backticks. – Nick Jan 21 '20 at 22:52
  • Note you need to divide the returned value by `strlen('string I want to count')` to get the actual count of occurrences – Nick Jan 21 '20 at 22:54
  • @Nick where do I inject that into my code? – Eric Brockman Jan 21 '20 at 23:01
  • You can do it in the query e.g. `(LENGTH(post_content) - LENGTH(REPLACE(post_content,'string I want to count',''))) / LENGTH('string I want to count') AS occurs` or in your PHP `$count += $data['occurs'] / strlen('string I want to count');` – Nick Jan 21 '20 at 23:02
  • Unfortunately, neither of those options worked. Still just getting a blank page. – Eric Brockman Jan 21 '20 at 23:25
  • @Nick I also took the quote marks off of the 'occurs' in the query - thanks for that. Still not working though, and I'm afraid people won't help with this question now that it's been closed. Please advise. – Eric Brockman Jan 21 '20 at 23:29
  • Just noticed you're using `mysql` functions - surely you've upgraded to `mysqli` by now? – Nick Jan 21 '20 at 23:31
  • I'm taking a stab at this, but not really fluent with sql yet. I assume I used some depreciated code. Can I just change the 2 `mysql` functions to `msqli`? – Eric Brockman Jan 21 '20 at 23:34
  • I've run this code using `mysqli` functions on my server and it runs: `$conn = mysqli_connect('test', 'test', 'test', 'test'); $conn->query('create table wp_db (post_content text)') or die($conn->error); $conn->query("insert into wp_db values ('hello world. hello yourself!')") or die($conn->error); $count = 0; $sql = $conn->query("SELECT LENGTH(post_content) - LENGTH(REPLACE(post_content,'hello','')) AS occurs FROM wp_db WHERE post_content LIKE '%hello%'") or die($conn->error); while ($data = $sql->fetch_assoc()) { $count += $data['occurs']; } echo $count / strlen('hello');` – Nick Jan 21 '20 at 23:34
  • What does your db connection code look like? – Nick Jan 21 '20 at 23:36
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/206384/discussion-between-eric-brockman-and-nick). – Eric Brockman Jan 21 '20 at 23:39

0 Answers0