News:

Wondering if this will always be free?  See why free is better.

Main Menu

SMF 2.x SQL Question

Started by Biology Forums, June 18, 2018, 09:43:20 AM

Previous topic - Next topic

Biology Forums

I'd like to know if I've done this correctly:

$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.

Arantor

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.

Biology Forums

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]

$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]

$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?

Shambles

From what I see of other calls to $smcFunc, maybe

$found = $smcFunc['db_affected_rows'];

should look like:

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

Biology Forums

That didn't do it either. + no errors in apache error log.

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


Arantor

LIKE is how you tell the database to even respect the % symbol...

Shambles

Could this maybe work?


array(
'topicName' => '%' . $topicName . '%',
)

Biology Forums

Here's the working solution:

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

albertlast

why you use an "AND" between the two columns in you where statement?

Biology Forums

Quote from: albertlast on June 18, 2018, 01:00:25 PM
why you use an "AND" between the two columns in you where statement?

Do you have a better suggestion?

vbgamer45

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

Biology Forums

QuoteDoes both the subject and body need to match?

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

vbgamer45

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

Kindred

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.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Biology Forums

Thank you both, I'll correct it.

Arantor

Y'know, I did point this out up front, as I wasn't sure that was actually your intention :P

Advertisement: