Linking queries to smf database per user

[brion]

I am creating a game that you can only play if you are logged into the forum, and when you visit the game logged in, it welcomes you, shows your mail, etc.

I have different categories such as coins, relics, trash, etc, and more to come. I want that to update when someone finds something when playing the game, but I only want it to update to that users account.

So my question is, how can I do an update query to check which user it is and then change that users items?

http://www.detectorsparadise.com/game.php
Version: 1.1.9

[Arantor]

Where are you storing this data for the user?

[brion]

Right now it's just plain text in the file, no queries yet. Would it be better to create a new table with all the values and merge the users table to it? So just insert the new fields to SMF's users table?

[Arantor]

Depends what you're trying to do, how many columns you're adding, what types of columns, whether you expect all users to use this and so on.

I'm thinking new table that can be JOIN'd to the members table.

[brion]

It'd only need 7 fields, coins, trash, silver coins, etc. Every time something is found it adds +1 to that field in that users account. That is all, real simple.

But it needs to be attached to SMF, so they don't have to create an account, so when a new member registers to the forum, they can just go play the game. (If they are logged into the forum)

[Arantor]

You would make the table in the same database and use queries to modify it.  Though it isn't quite as simple as it sounds.

Essentially this new table needs 8 columns, the 7 you mention, plus an 8th which would be the user id that the other 7 relate to.

How exactly would the users update this?

[brion]

It'd update when a user clicks a link, if they found something it'd update their table. If you click the link you will see an image, it's an image map full of links.

So the query would need to be like this,

Code Select Expand

$query = mysql_query("UPDATE users SET silvercoin=silvercoin+1 WHERE id='$id'");

So we'd need to define $id as who the user is.

[Arantor]

Assuming that query runs inside SMF, it should be written:

Code Select Expand

db_query("UPDATE user_stats SET silvercoin=silvercoin + 1 WHERE id='{$user_info['id']}'", __FILE__, __LINE__);

Where would this link be?

I'm just thinking how this would work. I'm thinking you could save yourself a lot of effort by using the Custom Profile Fields mod. Perhaps not quite as stylish as what you're after, but would mean you wouldn't have to write a ton of code. Though instead of a link your users would type in the number.

[brion]

Yeah I was just using that as an example. The queries would be in an IF/ELSE condition inside a function. Something like this

Code Select Expand

function area1() {
$chance = rand(1,5);

if ($chance == '3') {
echo 'You found a gum wrapper!';
//Query Here to add +1 to trash
}

else {
echo 'Nothing was found, try somewhere else.';
}
}

[Arantor]

Oh, so it's a game.

So where will all this be shown to the user then?


Have to say this is sounding more like a candidate for mod requests than a coding support topic.

[brion]

The user would play and see it here
http://www.detectorsparadise.com/game.php

Later I can build a ranking page for it. To show all users their scores vs others.

I am building this myself, so not looking to have it made into a mod. I only need help figuring out how to update the users account, the rest I can do.

[Arantor]

Well, if you add them to the members table, updateMemberData($userid, array('columnname' => newvalue)); would do it for you.

Alternatively, the query I posted above would do it too.

[brion]

Alright I put the 7 values into the smf_members table. I would rather use the query because it's something I am familiar with.

I am using require("SSI.php");
Do I need to include any other files to be able to use that query? or would it link into SSI?

[Arantor]

SSI.php loads half of SMF, part of which is db_query, so you should be able to use it exactly as is.

[brion]

I get the following error when I tried it. It connects to the database, but can't figure out the id part.

Unknown column 'id' in 'where clause'
File: /home/mine/public_html/game.php
Line: 107

Line: 107

Code Select Expand

db_query("UPDATE smf_members SET clad=clad+1 WHERE id='{$user_info['id']}'", __FILE__, __LINE__);

[Arantor]

Code Select Expand

db_query("UPDATE {$db_prefix}members SET clad=clad+1 WHERE ID_MEMBER='{$user_info['id']}'", __FILE__, __LINE__);

At that point I still assumed you were using your own table, hence using 'id' instead of ID_MEMBER.


(Btw, you still need to update to 1.1.10 at some point)

[brion]

Got rid of the error, but now it won't update. If I use this query it works perfect.

Code Select Expand

db_query("UPDATE smf_members SET clad=clad+1 WHERE ID_MEMBER='1'", __FILE__, __LINE__) or die( mysql_error());

However if I use this query which I need, it won't work, I am guessing because $user_info isn't found?

Code Select Expand

db_query("UPDATE smf_members SET clad=clad+1 WHERE ID_MEMBER='{$user_info['id']}'", __FILE__, __LINE__) or die( mysql_error());

[Arantor]

What about it doesn't work, exactly?

[brion]

It doesn't update because the ID_MEMBER field is blank in the query.

[Arantor]

Can I see the full code you're using to do that?