Struggling putting together an SQL query

Question

I'm trying to further customize an existing modification for my site/forum, but unfortunately the author who I've asked for support has little spare time at the moment.

Can someone advise where I might be going wrong with this query?

            SELECT date FROM {db_prefix}log_activity
            WHERE date = 2015-12-25
            ( SELECT COUNT(*) AS total FROM {db_prefix}log_online 
            WHERE ID_MEMBER = $memberID );

I am basically looking to check if my specified date appears in {db_prefix}log_activity and if it does, trigger my action for all members found in {db_prefix}log_online

(more to the code but I think the above part is what causing me issues..)

Full code of my attempts:

            if (!in_array('test',$currentBadges))
            {

            $resultgroup = $smcFunc['db_query']('', "
            SELECT date FROM {db_prefix}log_activity
            WHERE date = 2015-04-20
            ( SELECT COUNT(*) AS total FROM {db_prefix}log_online 
            WHERE ID_MEMBER = $memberID );
            ");
            $totalRow = $smcFunc['db_fetch_assoc']($resultgroup);

            {
            $badgeAction = 'test';
            if (!in_array($badgeAction,$currentBadges) && $totalRow['total'] >= 1)
            {
            $badgeID = GetBadgeIDByAction($badgeAction);
            $ret = AddBadgeToMember($memberID,$badgeID,false);
            if ($ret == true)
            {
            $currentBadges[] = $badgeAction;
            $newBadges[]  = $badgeAction;
            }

            }


            }
            //end badge code
            }

I've never used this site before so hopefully that format's somewhat readable.. :P

Basically what I am attempting to do is: IF (date) = today AND (member) has logged in THEN award badge...

Log of changed attempts:

            "WHERE date = 2015-04-20"
            WHERE date = "2015-04-20"

this for one thing, needs to be quoted `WHERE date = 2015-12-25` otherwise MySQL figures you want to do math, as in substract, in turn spewing out a syntax error; something you're not checking for. — Funk Forty Niner, Apr 20 '15 at 16:48
We'll certainly help you here, but I advise against asking people to do it for you - that's a sure way to get your question closed. I've edited this out. — halfer, Apr 20 '15 at 16:52
I have very little knowledge and too much I want to learn!, I find I receive worse errors when using quotes around the date / the whole line :( — Fourtwenty Connect, Apr 20 '15 at 16:54
Much appreciated and apologies, I'm not very familiar with this site! Like I said, I have very little knowledge of PHP/SQL but seem to have been able to achieve everything I have looked into so far from reading/ using examples as templates / support etc. — Fourtwenty Connect, Apr 20 '15 at 16:57
if you've used quotes around the date, then modify your question with the way you have used it. What are you using to connect with, `mysql_`? `mysqli_`? or PDO? — Funk Forty Niner, Apr 20 '15 at 16:59
I have since removed the quotes again but I'll edit my question to show where I had them and mysql I believe. — Fourtwenty Connect, Apr 20 '15 at 17:03
"Much appreciated... I'm not very familiar with this site" - you're most welcome. FWIW, I'd say that the guidelines I outline are the case _everywhere on the web_, not just here. I've now upvoted, as the question looks quite good after the various edits. — halfer, Apr 20 '15 at 17:29

score 1 · Answer 1 · edited May 23 '17 at 11:50

As per your edit in using "WHERE date = 2015-04-20"

You are using double quotes inside a double-quoted query.

Use single quotes like this and as per what you have in your originally posted code:

$resultgroup = $smcFunc['db_query']('', "
            SELECT date FROM {db_prefix}log_activity
            WHERE date = '2015-04-20'
            ( SELECT COUNT(*) AS total FROM {db_prefix}log_online 
            WHERE ID_MEMBER = $memberID );
            ");

Plus, if $memberID is not an integer, it would also need to be quoted.

I.e.:

$resultgroup = $smcFunc['db_query']('', "
            SELECT date FROM {db_prefix}log_activity
            WHERE date = '2015-04-20'
            ( SELECT COUNT(*) AS total FROM {db_prefix}log_online 
            WHERE ID_MEMBER = '$memberID' );
            ");

But I somewhat doubt it. However, I needed to point that out just in case.

For more information on this, read the following on Stack

When to use single quotes, double quotes, and backticks in MySQL

and string functions on the MySQL.com website:

https://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Ahh, I now see where I was going wrong with the double / singles quotes. Thank you for that! :P Unfortunately my new code results in "Hacking Attempt..." but I presume this is SMF related to know why that is happening now? — Fourtwenty Connect, Apr 20 '15 at 17:28
@FourtwentyConnect that I won't be able to help with. Something else is causing that and I've no idea how, because I'm not familiar with it. — Funk Forty Niner, Apr 20 '15 at 17:31
I shall investigate the cause of that at the SMF support site. - Many thanks for your help, I knew I wasn't too far off solving this one! - It's been bugging me for a while now! — Fourtwenty Connect, Apr 20 '15 at 17:32

Struggling putting together an SQL query

1 Answers1