Help putting together SQL query

[420Connect.co.uk]

Hello anyone and everyone!

I'm trying to customize a certain mod, but unfortunately the author who I've asked for support has little spare time at the moment..
anyway.. I would be forever grateful if someone could turn my attempts into a working query..

Code Select Expand

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

Coffee, tea, cake and kisses to anyone who can help out! 

[Illori]

most likely you need to use a unix timestamp. try converting the time using http://www.unixtimestamp.com/

[420Connect.co.uk]

I can almost guarantee you know more about this sort of stuff than I do but, the 'date' column' in {db_prefix}log_activity is stored like "YYYY-MM-DD" so I'm not sure the unix time stamp is necessary here but thanks for that link! - I'm sure I'll find a use for that!

[420Connect.co.uk]

*Update*

I've had someone review my attempts elsewhere and think it should work after including " ' " single quotes around my specified date.

my next issue is I am now receiving "Hacking attempt..." when I try and use it now?

[JBlaze]

*Update*

I've had someone review my attempts elsewhere and think it should work after including " ' " single quotes around my specified date.

my next issue is I am now receiving "Hacking attempt..." when I try and use it now?

You're getting that error because the constant 'SMF' is not being defined, which means you are trying to access the file directly rather than through SMF. You can solve this by simply adding this on the line after <?php

Code Select Expand

require_once('/path/to/SSI.php');

But what would be most helpful is seeing the entire file, and how it's being used.

[Kindred]

Additionally, If you ARE using that query externally (e.g. from a php file) and requiring SSI, then you SHOULD be using the SMF db functions instead of directly pushing the query.

[margarett]

Aren't you using a subselect? The full query code would help. If that second WHERE (inside parenthesis) is a subselect, $smcFunc will reject it (not sure if Hacking atttempt is the answer you get, but it's clearly rejected)

So maybe you can provide the full query?

[420Connect.co.uk]

As always, thank you very much for your support in dealing with my questions everyone!

As you probably know by know, I really don't know much about PHP/SQL etc. but I've been reading things on the internet and trying to use examples as templates but without the knowledge in the first place it makes it ten times harder as you can imagine! lol

The full chunk of code I am trying to use is:

Code Select Expand

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 can provide the full file if need be & Bruno, your account is still active if you're able to take a look?

Basically it will check if my specified date exists in the 'date' column of {db_prefix}log_activity.
It will also check to see if a membersID is found in {db_prefix}log_online.

& if both are found, award the badge..

I've been trying to solve this one for a while now and have tried vaaarious different ideas of how to do it, but I think I've almost cracked it!

Any pointers or help putting a working code together would be really appreciated and I have many plans for how to use the code if I can just get it to work! 

[margarett]

Yeah you need to replace that second SELECT with an INNER JOIN. Furthermore:

Code Select Expand

$totalRow = $smcFunc['db_fetch_assoc']($resultgroup);

{
$badgeAction = 'test';
You are missing something before that opening bracket?

And after that still misses a

Code Select Expand

$smcFunc['db_free_result']($resultgroup);
when you don't need the query result anymore.

[420Connect.co.uk]

I'm just about to go offline for tonight, but thanks for your reply!
(although now I'm even more confused about it! )

I'll take a fresh look into this tomorrow after having a look into how to use INNER JOINs?

& I had another one of those made up in a previous attempt but no doubt I was using it wrong

[420Connect.co.uk]

Couldn't resist having a go at this but using

Code Select Expand

COUNT(*) AS total

really confused me from the examples I was reading.. and using another

Code Select Expand

$smcFunc['db_free_result']($resultgroup); threw me too..

I've got this at the moment, which to my surprise 'runs' without errors but doesn't actually do what I had hoped..

Code Select Expand

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

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

$resultgroup2 = $smcFunc['db_query']('', "
SELECT COUNT(*) AS date FROM {db_prefix}log_activity
WHERE DATE = '2015-04-20'
");
$totalRow2 = $smcFunc['db_fetch_assoc']($resultgroup2);

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

}


}
//end badge code
}

[margarett]

Maybe we should start over forgetting what you have right now.

What exactly do you want to do? You said this

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

This actually requires 2 separate queries, the second depends on the first one's result.

Something like this, maybe?

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS total FROM {db_prefix}log_activity
WHERE DATE = {string:date}',
array(
$date => '2015-04-20',
),
);
list ($total) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
if (!empty($total) && ($total > 0))
//DO SECOND STUFF

WARNING: untested

[420Connect.co.uk]

Many thanks for having a go Bruno!
Using yours as a template, I've combined what parts I thought I needed but unfortunately, it's complaining about:

Code Select Expand

);
after the

Code Select Expand

$date => '2015-04-20',
),

What I have for now:

Code Select Expand

if (!in_array('test',$currentBadges))
{
$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS total FROM {db_prefix}log_activity
WHERE DATE = {string:date}',
array(
$date => '2015-04-20',
),
);
list ($total) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
if (!empty($total) && ($total > 0))
$resultgroup = $smcFunc['db_query']('', "
SELECT COUNT(*) AS total2 FROM {db_prefix}log_online
WHERE ID_MEMBER = $memberID
");
$totalRow = $smcFunc['db_fetch_assoc']($resultgroup);
{
$badgeAction = 'test';
if (!in_array($badgeAction,$currentBadges) && $totalRow['total2'] >= 1)
{
$badgeID = GetBadgeIDByAction($badgeAction);
$ret = AddBadgeToMember($memberID,$badgeID,false);
if ($ret == true)
{
$currentBadges[] = $badgeAction;
$newBadges[]  = $badgeAction;
}

}


}
//end badge code
}

[margarett]

Yeah, there is no comma here

Code Select Expand

$date => '2015-04-20',
)

And why do you still have this opening bracket?

Code Select Expand

$totalRow = $smcFunc['db_fetch_assoc']($resultgroup);
{
$badgeAction = 'test';

[420Connect.co.uk]

Woo!
It feels like I'm getting further now 
& all these different bracket types confuse me!

Latest code:

Code Select Expand

if (!in_array('test',$currentBadges))
{
$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS total FROM {db_prefix}log_activity
WHERE DATE = {string:date}',
array(
$date => '2015-04-20',
)
);
list ($total) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
if (!empty($total) && ($total > 0))
$resultgroup = $smcFunc['db_query']('', "
SELECT COUNT(*) AS total2 FROM {db_prefix}log_online
WHERE ID_MEMBER = $memberID
");
$totalRow = $smcFunc['db_fetch_assoc']($resultgroup);

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

}


}
//end badge code


Latest error message:

The database value you're trying to insert does not exist: date

[Shambles]

Not a DB person myself, but shouldn't this

Code Select Expand

array(
$date => '2015-04-20',
)

.. say ..

Code Select Expand

array(
'date' => '2015-04-20',
)

[JBlaze]

Code Select Expand

array(
'date' => '2015-04-20'
)

[margarett]

I was already ninja'd several times

But here both zilladotexe and Shambles are correct, the code was buggy

I reordered all you code, like this (sorry, I'm an indentation freak )

Code Select Expand

<?php

if (!in_array('test',$currentBadges))
{
$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS total FROM {db_prefix}log_activity
WHERE date = {string:date}',
array(
'date' => '2015-04-20',
)
);
list ($total) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request); 
if (!empty($total) && ($total > 0))
{
$resultgroup = $smcFunc['db_query']('', '
SELECT COUNT(*) AS total FROM {db_prefix}log_online 
WHERE id_member = {int:id_member}',
array(
'id_member' => $memberID ,
)
);
list ($total2) = $smcFunc['db_fetch_row']($resultgroup);
$smcFunc['db_free_result']($resultgroup); 

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

?>

[420Connect.co.uk]

HOLY ...

We have lift off! 

Thank you so much for your help in putting this together! - Like I said previously, you wouldn't believe how many different attempts I've tried to do this job but it looks like it's working as it should noww! - w00t! 

* 420connect.info is forever grateful!

One or 2 last questions about it though..

On the line:

Code Select Expand

'date' => '2015-04-20'

Could I use: "==" instead of "=>" to have it only trigger on that date. (not on 2015-04-21 for example.)

& would anyone happen to know if I could find or if it even exists somewhere; a log of who had logged in yesterday?
(to award only members who logged in yesterday)

[JBlaze]

Nope. Because you're using an array in that line, it needs the '=>' to pair the value to that key.

This line,

Code Select Expand

WHERE date = {string:date}',
is where you would change the operator, but since it's already looking for that exact string, you don't need to change it. Your SQL query is already correct