Advertisement:

Author Topic: SMF 2.x SQL Question  (Read 637 times)

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,704
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
SMF 2.x SQL Question
« on: June 18, 2018, 09:43:20 AM »
I'd like to know if I've done this correctly:

Code: [Select]
$dbresult = $smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}messages
WHERE body = {string:topicName} AND subject = {string:topicName}',
array(
'topicName' => '%$topicName%',
)
);

$found = db_affected_rows();
$smcFunc['db_free_result']($dbresult);

Notice the % around $topicName.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,398
    • StoryBB/StoryBB on GitHub
Re: SMF 2.x SQL Question
« Reply #1 on: June 18, 2018, 09:48:14 AM »
WHERE body LIKE {string:topicName}

Note that as you have it, the term must be in both the subject and the body of the message.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,704
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: SMF 2.x SQL Question
« Reply #2 on: June 18, 2018, 10:04:19 AM »
I'm trying to convert my custom SMF 1 code to SMF 2, and even with your suggestion, I can't get a favorable result.

SMF 1 version looks like this: [it works]

Code: [Select]
$topicName = 'Plants convert the energy of sunlight into';

$dbresult = db_query("
SELECT *
FROM {$db_prefix}messages
WHERE
body LIKE '%$topicName%' AND
subject LIKE '%$topicName%'", __FILE__, __LINE__);

$found = db_affected_rows();
mysql_free_result($dbresult);

// Found in the database, skip it (continue;)
if ($found == true)
{
echo'FOUND!';
}


SMF 2 version [does not work]

Code: [Select]
$topicName = 'Plants convert the energy of sunlight into';

$dbresult = $smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}messages
WHERE body LIKE {string:topicName} AND subject LIKE {string:topicName}',
array(
'topicName' => $topicName,
)
);

$found = $smcFunc['db_affected_rows'];
$smcFunc['db_free_result']($dbresult);

// Found in the database, skip it (continue;)
if ($found == true)
{
echo'FOUND!';
}

It always outputs FOUND! even when this query isn't in the database.

Do you see any issues?

Offline Shambles

  • SMF Hero
  • ******
  • Posts: 5,157
  • Gender: Male
    • i30 Owners Club
Re: SMF 2.x SQL Question
« Reply #3 on: June 18, 2018, 10:15:26 AM »
From what I see of other calls to $smcFunc, maybe

Code: [Select]
$found = $smcFunc['db_affected_rows'];
should look like:

Code: [Select]
$found = $smcFunc['db_affected_rows']();

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,704
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: SMF 2.x SQL Question
« Reply #4 on: June 18, 2018, 10:21:35 AM »
That didn't do it either. + no errors in apache error log.

Related question. Is LIKE the same operator as %...% ?


Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,398
    • StoryBB/StoryBB on GitHub
Re: SMF 2.x SQL Question
« Reply #5 on: June 18, 2018, 10:26:14 AM »
LIKE is how you tell the database to even respect the % symbol...
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline Shambles

  • SMF Hero
  • ******
  • Posts: 5,157
  • Gender: Male
    • i30 Owners Club
Re: SMF 2.x SQL Question
« Reply #6 on: June 18, 2018, 10:27:06 AM »
Could this maybe work?

Code: [Select]
array(
'topicName' => '%' . $topicName . '%',
)

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,704
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: SMF 2.x SQL Question
« Reply #7 on: June 18, 2018, 10:30:20 AM »
Here's the working solution:

Code: [Select]
global $smcFunc;

$topicName = 'Plants convert the energy of sunlight into';

$dbresult = $smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}messages
WHERE body LIKE {string:topicName} AND subject LIKE {string:topicName}',
array(
'topicName' => '%'.$topicName.'%',
)
);

$found = $smcFunc['db_affected_rows']();
$smcFunc['db_free_result']($dbresult);

if ($found == true)
{
echo'FOUND!';
}

I just realized how fun converting SMF 1.x code to SMF 2.x will be. :-X

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 587
Re: SMF 2.x SQL Question
« Reply #8 on: June 18, 2018, 01:00:25 PM »
why you use an "AND" between the two columns in you where statement?

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,704
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: SMF 2.x SQL Question
« Reply #9 on: June 18, 2018, 01:12:40 PM »
why you use an "AND" between the two columns in you where statement?

Do you have a better suggestion?

Online vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,503
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: SMF 2.x SQL Question
« Reply #10 on: June 18, 2018, 01:15:47 PM »
Basicly wondering why an AND and not an OR

Does both the subject and body need to match?
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,704
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: SMF 2.x SQL Question
« Reply #11 on: June 18, 2018, 01:22:30 PM »
Quote
Does both the subject and body need to match?

Basically I want it to search both. Wherever there's a match, it's found.

Online vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,503
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: SMF 2.x SQL Question
« Reply #12 on: June 18, 2018, 01:24:01 PM »
Then AND subject
Should be
OR subject
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,430
  • Gender: Male
    • Kindred-999 on GitHub
Re: SMF 2.x SQL Question
« Reply #13 on: June 18, 2018, 02:07:02 PM »
AND means search both and BOTH must contain the string to return true.

OR means search both, and EITHER can contain the string to return true.
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,704
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: SMF 2.x SQL Question
« Reply #14 on: June 18, 2018, 02:15:19 PM »
Thank you both, I'll correct it.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,398
    • StoryBB/StoryBB on GitHub
Re: SMF 2.x SQL Question
« Reply #15 on: June 18, 2018, 03:11:55 PM »
Y'know, I did point this out up front, as I wasn't sure that was actually your intention :P
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.