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?

[brion]

Here is the top of the file

Code Select Expand

<?php require("SSI.php");
mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('database_name') or die(mysql_error());

if (isset($_GET["x"])) {
$x = explode(":",$_GET["x"]);

    switch($x[0])
{
case 'area1':
area1();
break;

case 'area2':
area2();
break;

case 'area3':
area3();
break;

 }
}
 else { main(); }
?>

I removed the user/pass/db_name for safety. Here is the main function part which is suppose to show the amount of items in their account.

Code Select Expand

<?php
function main() {

echo '<table align="right"><tr><td>';
ssi_welcome();
echo '</td></tr></table>';
echo '<h2>Metal Detecting Game.</h2>';

$checkk = db_query("SELECT clad FROM smf_members WHERE ID_MEMBER='{$user_info['id']}'", __FILE__, __LINE__);
$check2 = mysql_fetch_array($checkk);

echo '<table width="20%" align="right">';
echo "<tr><td><b>Clad:</b> ".$check2['clad']."</td></tr>";
echo "<tr><td><b>Silver Coins:</b> 0</td></tr>";
echo '<tr><td><b>Relics:</b> 0</td></tr>';
echo '</table><br />';
echo '<center><img src="/game/park1.jpg" usemap="#park1" border="0">

<map name="park1">
<area shape="rect" coords="413,317,477,354" href="game.php?x=area1" target="_SELF">
<area shape="rect" coords="334,310,395,354" href="game.php?x=area2" target="_SELF">
<area shape="rect" coords="233,305,304,351" href="game.php?x=area3" target="_SELF">
<area shape="rect" coords="174,302,196,351" href="game.php?x=area4" target="_SELF">
<area shape="rect" coords="88,324,153,340" href="game.php?x=area5" target="_SELF">
<area shape="rect" coords="8,311,73,348" href="game.php?x=area6" target="_SELF">
<area shape="rect" coords="26,233,54,256" href="game.php?x=area7" target="_SELF">
<area shape="rect" coords="112,248,147,280" href="game.php?x=area8" target="_SELF">
<area shape="rect" coords="199,237,242,285" href="game.php?x=area9" target="_SELF">
<area shape="rect" coords="285,233,310,248" href="game.php?x=area10" target="_SELF">
<area shape="rect" coords="334,246,391,288" href="game.php?x=area11" target="_SELF">
<area shape="rect" coords="417,250,466,279" href="game.php?x=area12" target="_SELF">
<area shape="rect" coords="14,208,63,217" href="game.php?x=area13" target="_SELF">
<area shape="rect" coords="97,205,76,190" href="game.php?x=area14" target="_SELF">
<area shape="rect" coords="124,167,176,182" href="game.php?x=area15" target="_SELF">
<area shape="rect" coords="122,196,146,209" href="game.php?x=area16" target="_SELF">
<area shape="rect" coords="168,193,193,208" href="game.php?x=area17" target="_SELF">
<area shape="rect" coords="225,199,251,214" href="game.php?x=area18" target="_SELF">
<area shape="rect" coords="307,203,328,222" href="game.php?x=area19" target="_SELF">
<area shape="rect" coords="411,204,449,233" href="game.php?x=area20" target="_SELF">

</map>
</center><br /><br />';

echo '<center><a href="index.php">Back to Forum</a></center>';
}

And here is one of the things when you find an item, it's suppose to add +1 to the clad field of that users account.

Code Select Expand

function area2() {
echo '<center><b>You found a memorial penny!</b><br /><br /><img src="/game/cladpenny.jpg"></center>';
db_query("UPDATE smf_members SET clad=clad+1 WHERE ID_MEMBER='{$user_info['id']}'", __FILE__, __LINE__) or die( mysql_error());
echo '<center><img src="/game/park1.jpg" usemap="#park1" border="0">

<map name="park1">
<area shape="rect" coords="413,317,477,354" href="game.php?x=area1" target="_SELF">
<area shape="rect" coords="334,310,395,354" href="game.php?x=area2" target="_SELF">
<area shape="rect" coords="233,305,304,351" href="game.php?x=area3" target="_SELF">
<area shape="rect" coords="174,302,196,351" href="game.php?x=area4" target="_SELF">
<area shape="rect" coords="88,324,153,340" href="game.php?x=area5" target="_SELF">
<area shape="rect" coords="8,311,73,348" href="game.php?x=area6" target="_SELF">
<area shape="rect" coords="26,233,54,256" href="game.php?x=area7" target="_SELF">
<area shape="rect" coords="112,248,147,280" href="game.php?x=area8" target="_SELF">
<area shape="rect" coords="199,237,242,285" href="game.php?x=area9" target="_SELF">
<area shape="rect" coords="285,233,310,248" href="game.php?x=area10" target="_SELF">
<area shape="rect" coords="334,246,391,288" href="game.php?x=area11" target="_SELF">
<area shape="rect" coords="417,250,466,279" href="game.php?x=area12" target="_SELF">
<area shape="rect" coords="14,208,63,217" href="game.php?x=area13" target="_SELF">
<area shape="rect" coords="97,205,76,190" href="game.php?x=area14" target="_SELF">
<area shape="rect" coords="124,167,176,182" href="game.php?x=area15" target="_SELF">
<area shape="rect" coords="122,196,146,209" href="game.php?x=area16" target="_SELF">
<area shape="rect" coords="168,193,193,208" href="game.php?x=area17" target="_SELF">
<area shape="rect" coords="225,199,251,214" href="game.php?x=area18" target="_SELF">
<area shape="rect" coords="307,203,328,222" href="game.php?x=area19" target="_SELF">
<area shape="rect" coords="411,204,449,233" href="game.php?x=area20" target="_SELF">

</map>
</center><br /><br />';
echo '<center><a href="index.php">Back to Forum</a></center>';
}

[Arantor]

Code Select Expand

mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('database_name') or die(mysql_error());

^^ can be removed. SSI.php does that itself anyway.


The problem is that in your function, $user_info is never pulled in from the global scope, so it's not available. Just add this just after the { in function main():

Code Select Expand

global $user_info;

[brion]

I tried global and it didn't check anything, still is blank. When I remove the database stuff at the top I get this error.

No database selected
File: /home/mine/public_html/game.php
Line: 40

[Arantor]

Weird, you shouldn't have to do that. All that'll happen is you use a little extra memory.

$user_info is declared by SSI.php and what it loads. Since I can't see all your file I have to guess at parts of it but you will need to global it in any function you're using. I assumed it was main() that was doing it but on further examination that's not the case.

[brion]

This is the entire file. The rest didn't have anything to do with the functions.

Code Select Expand

<?php require("SSI.php");
mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('database_name') or die(mysql_error());

if (isset($_GET["x"])) {
$x = explode(":",$_GET["x"]);

    switch($x[0])
{
case 'area1':
area1();
break;

case 'area2':
area2();
break;

case 'area3':
area3();
break;

 }
}
 else { main(); }
?>

<html>
<head>
<title>Detectors Paradise - Metal Detecting Game</title>
</head>
<body bgcolor="tan">

<?php
function main() {
global $user_info;
echo '<table align="right"><tr><td>';
ssi_welcome();
echo '</td></tr></table>';
echo '<h2>Metal Detecting Game.</h2>';

$checkk = db_query("SELECT clad FROM smf_members WHERE ID_MEMBER='{$user_info['id']}'", __FILE__, __LINE__);
$check2 = mysql_fetch_array($checkk);

echo '<table width="20%" align="right">';
echo "<tr><td><b>Clad:</b> ".$check2['clad']."</td></tr>";
echo "<tr><td><b>Silver Coins:</b> 0</td></tr>";
echo '<tr><td><b>Relics:</b> 0</td></tr>';
echo '</table><br />';
echo '<center><img src="/game/park1.jpg" usemap="#park1" border="0">

<map name="park1">
<area shape="rect" coords="413,317,477,354" href="game.php?x=area1" target="_SELF">
<area shape="rect" coords="334,310,395,354" href="game.php?x=area2" target="_SELF">
<area shape="rect" coords="233,305,304,351" href="game.php?x=area3" target="_SELF">
<area shape="rect" coords="174,302,196,351" href="game.php?x=area4" target="_SELF">
<area shape="rect" coords="88,324,153,340" href="game.php?x=area5" target="_SELF">
<area shape="rect" coords="8,311,73,348" href="game.php?x=area6" target="_SELF">
<area shape="rect" coords="26,233,54,256" href="game.php?x=area7" target="_SELF">
<area shape="rect" coords="112,248,147,280" href="game.php?x=area8" target="_SELF">
<area shape="rect" coords="199,237,242,285" href="game.php?x=area9" target="_SELF">
<area shape="rect" coords="285,233,310,248" href="game.php?x=area10" target="_SELF">
<area shape="rect" coords="334,246,391,288" href="game.php?x=area11" target="_SELF">
<area shape="rect" coords="417,250,466,279" href="game.php?x=area12" target="_SELF">
<area shape="rect" coords="14,208,63,217" href="game.php?x=area13" target="_SELF">
<area shape="rect" coords="97,205,76,190" href="game.php?x=area14" target="_SELF">
<area shape="rect" coords="124,167,176,182" href="game.php?x=area15" target="_SELF">
<area shape="rect" coords="122,196,146,209" href="game.php?x=area16" target="_SELF">
<area shape="rect" coords="168,193,193,208" href="game.php?x=area17" target="_SELF">
<area shape="rect" coords="225,199,251,214" href="game.php?x=area18" target="_SELF">
<area shape="rect" coords="307,203,328,222" href="game.php?x=area19" target="_SELF">
<area shape="rect" coords="411,204,449,233" href="game.php?x=area20" target="_SELF">

</map>
</center><br /><br />';

echo '<center><a href="index.php">Back to Forum</a></center>';
}

function area1() {
echo '<center><b>You found a chewing gum wrapper!</b><br /><br /><img src="/game/gum.png"></center>';
echo '<center><img src="/game/park1.jpg" usemap="#park1" border="0">

<map name="park1">
<area shape="rect" coords="413,317,477,354" href="game.php?x=area1" target="_SELF">
<area shape="rect" coords="334,310,395,354" href="game.php?x=area2" target="_SELF">
<area shape="rect" coords="233,305,304,351" href="game.php?x=area3" target="_SELF">
<area shape="rect" coords="174,302,196,351" href="game.php?x=area4" target="_SELF">
<area shape="rect" coords="88,324,153,340" href="game.php?x=area5" target="_SELF">
<area shape="rect" coords="8,311,73,348" href="game.php?x=area6" target="_SELF">
<area shape="rect" coords="26,233,54,256" href="game.php?x=area7" target="_SELF">
<area shape="rect" coords="112,248,147,280" href="game.php?x=area8" target="_SELF">
<area shape="rect" coords="199,237,242,285" href="game.php?x=area9" target="_SELF">
<area shape="rect" coords="285,233,310,248" href="game.php?x=area10" target="_SELF">
<area shape="rect" coords="334,246,391,288" href="game.php?x=area11" target="_SELF">
<area shape="rect" coords="417,250,466,279" href="game.php?x=area12" target="_SELF">
<area shape="rect" coords="14,208,63,217" href="game.php?x=area13" target="_SELF">
<area shape="rect" coords="97,205,76,190" href="game.php?x=area14" target="_SELF">
<area shape="rect" coords="124,167,176,182" href="game.php?x=area15" target="_SELF">
<area shape="rect" coords="122,196,146,209" href="game.php?x=area16" target="_SELF">
<area shape="rect" coords="168,193,193,208" href="game.php?x=area17" target="_SELF">
<area shape="rect" coords="225,199,251,214" href="game.php?x=area18" target="_SELF">
<area shape="rect" coords="307,203,328,222" href="game.php?x=area19" target="_SELF">
<area shape="rect" coords="411,204,449,233" href="game.php?x=area20" target="_SELF">

</map>
</center><br /><br />';
echo '<center><a href="index.php">Back to Forum</a></center>';
}

function area2() {
echo '<center><b>You found a memorial penny!</b><br /><br /><img src="/game/cladpenny.jpg"></center>';
db_query("UPDATE smf_members SET clad=clad+1 WHERE ID_MEMBER='{$user_info['id']}'", __FILE__, __LINE__) or die( mysql_error());
echo '<center><img src="/game/park1.jpg" usemap="#park1" border="0">

<map name="park1">
<area shape="rect" coords="413,317,477,354" href="game.php?x=area1" target="_SELF">
<area shape="rect" coords="334,310,395,354" href="game.php?x=area2" target="_SELF">
<area shape="rect" coords="233,305,304,351" href="game.php?x=area3" target="_SELF">
<area shape="rect" coords="174,302,196,351" href="game.php?x=area4" target="_SELF">
<area shape="rect" coords="88,324,153,340" href="game.php?x=area5" target="_SELF">
<area shape="rect" coords="8,311,73,348" href="game.php?x=area6" target="_SELF">
<area shape="rect" coords="26,233,54,256" href="game.php?x=area7" target="_SELF">
<area shape="rect" coords="112,248,147,280" href="game.php?x=area8" target="_SELF">
<area shape="rect" coords="199,237,242,285" href="game.php?x=area9" target="_SELF">
<area shape="rect" coords="285,233,310,248" href="game.php?x=area10" target="_SELF">
<area shape="rect" coords="334,246,391,288" href="game.php?x=area11" target="_SELF">
<area shape="rect" coords="417,250,466,279" href="game.php?x=area12" target="_SELF">
<area shape="rect" coords="14,208,63,217" href="game.php?x=area13" target="_SELF">
<area shape="rect" coords="97,205,76,190" href="game.php?x=area14" target="_SELF">
<area shape="rect" coords="124,167,176,182" href="game.php?x=area15" target="_SELF">
<area shape="rect" coords="122,196,146,209" href="game.php?x=area16" target="_SELF">
<area shape="rect" coords="168,193,193,208" href="game.php?x=area17" target="_SELF">
<area shape="rect" coords="225,199,251,214" href="game.php?x=area18" target="_SELF">
<area shape="rect" coords="307,203,328,222" href="game.php?x=area19" target="_SELF">
<area shape="rect" coords="411,204,449,233" href="game.php?x=area20" target="_SELF">

</map>
</center><br /><br />';
echo '<center><a href="index.php">Back to Forum</a></center>';
}

function area3() {
echo '<center><b>You found a modern nail!</b><br /><br /><img src="/game/modnail.jpg"></center>';
echo '<center><img src="/game/park1.jpg" usemap="#park1" border="0">

<map name="park1">
<area shape="rect" coords="413,317,477,354" href="game.php?x=area1" target="_SELF">
<area shape="rect" coords="334,310,395,354" href="game.php?x=area2" target="_SELF">
<area shape="rect" coords="233,305,304,351" href="game.php?x=area3" target="_SELF">
<area shape="rect" coords="174,302,196,351" href="game.php?x=area4" target="_SELF">
<area shape="rect" coords="88,324,153,340" href="game.php?x=area5" target="_SELF">
<area shape="rect" coords="8,311,73,348" href="game.php?x=area6" target="_SELF">
<area shape="rect" coords="26,233,54,256" href="game.php?x=area7" target="_SELF">
<area shape="rect" coords="112,248,147,280" href="game.php?x=area8" target="_SELF">
<area shape="rect" coords="199,237,242,285" href="game.php?x=area9" target="_SELF">
<area shape="rect" coords="285,233,310,248" href="game.php?x=area10" target="_SELF">
<area shape="rect" coords="334,246,391,288" href="game.php?x=area11" target="_SELF">
<area shape="rect" coords="417,250,466,279" href="game.php?x=area12" target="_SELF">
<area shape="rect" coords="14,208,63,217" href="game.php?x=area13" target="_SELF">
<area shape="rect" coords="97,205,76,190" href="game.php?x=area14" target="_SELF">
<area shape="rect" coords="124,167,176,182" href="game.php?x=area15" target="_SELF">
<area shape="rect" coords="122,196,146,209" href="game.php?x=area16" target="_SELF">
<area shape="rect" coords="168,193,193,208" href="game.php?x=area17" target="_SELF">
<area shape="rect" coords="225,199,251,214" href="game.php?x=area18" target="_SELF">
<area shape="rect" coords="307,203,328,222" href="game.php?x=area19" target="_SELF">
<area shape="rect" coords="411,204,449,233" href="game.php?x=area20" target="_SELF">

</map>
</center><br /><br />';
echo '<center><a href="index.php">Back to Forum</a></center>';
}

?>
</body>
</html>

[Arantor]

Except that you have several functions, area1(), area2() which call DB queries, which as I said you'll need to global first.

[brion]

Right, that is true, but right now I just want to get the main function to work correctly by showing the number that is in the database per user. All of them have a default of 0, so it should at least show 0 in the clad section, not blank.

[Arantor]

Did you actually set it to 0 when constructing the new column... presumably in phpMyAdmin?

[brion]

Yes in PHPmyAdmin, when I make the 7 fields, I put 0 in the default.

[Arantor]

And are the fields actually 0 right now?

[brion]

Yeah all of them, except on my account my clad is 3, because when I did

WHERE ID_MEMBER='1'

as a test, it updated my account, but regardless it should still show 3 on my main function page.

[Arantor]

And you are logged in at the time this happens and you get an appropriate welcome message?

Hmm.

[brion]

Yes it says Hey, fayt, you have 133 messages, 0 are new.

Compared to the please register or login message.

It's so weird that this is happening.

[Arantor]

In that case, global $context and use $context['user']['id'] instead of $user_info['id'].

[brion]

Perfect, it works now. Thanks so much. I have one more question. Do you know a quick and easy code that I can put in each function, that if they aren't logged into the forum, then it shows an error message?

Something like this

Code Select Expand

if (!empty($session)) {
//Show the function
}
else {
echo 'Please login to the forum to play this game';
}

[Arantor]

Put this just after SSI.php. It'll give them a message and stop the rest of the page loading.

Code Select Expand

if($context['user']['is_guest'])
  die('Please log in to the forum to play this game');

[brion]

Works perfect, thanks again