Simple Machines Community Forum

Customizing SMF => SMF Coding Discussion => Topic started by: Biology Forums on June 18, 2018, 09:43:20 AM

Title: SMF 2.x SQL Question
Post by: Biology Forums 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.
Title: Re: SMF 2.x SQL Question
Post by: Arantor 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.
Title: Re: SMF 2.x SQL Question
Post by: Biology Forums 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?
Title: Re: SMF 2.x SQL Question
Post by: Shambles 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']();
Title: Re: SMF 2.x SQL Question
Post by: Biology Forums 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 %...% ?

Title: Re: SMF 2.x SQL Question
Post by: Arantor on June 18, 2018, 10:26:14 AM
LIKE is how you tell the database to even respect the % symbol...
Title: Re: SMF 2.x SQL Question
Post by: Shambles on June 18, 2018, 10:27:06 AM
Could this maybe work?

Code: [Select]
array(
'topicName' => '%' . $topicName . '%',
)
Title: Re: SMF 2.x SQL Question
Post by: Biology Forums 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
Title: Re: SMF 2.x SQL Question
Post by: albertlast on June 18, 2018, 01:00:25 PM
why you use an "AND" between the two columns in you where statement?
Title: Re: SMF 2.x SQL Question
Post by: Biology Forums 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?
Title: Re: SMF 2.x SQL Question
Post by: vbgamer45 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?
Title: Re: SMF 2.x SQL Question
Post by: Biology Forums 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.
Title: Re: SMF 2.x SQL Question
Post by: vbgamer45 on June 18, 2018, 01:24:01 PM
Then AND subject
Should be
OR subject
Title: Re: SMF 2.x SQL Question
Post by: Kindred 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.
Title: Re: SMF 2.x SQL Question
Post by: Biology Forums on June 18, 2018, 02:15:19 PM
Thank you both, I'll correct it.
Title: Re: SMF 2.x SQL Question
Post by: Arantor 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