Query help please

[War_Hero]

Hi all.

I created this 'mod' to go with one of my news articles. My mod allows the users to search a research facility. There are a possible of 6 outcomes. One of the outcomes is that they find an item. (The item can then be used to enter a part of the research facility, but I haven't got to that part yet.)

My problem is that I can't insert the item into the user's inventory when that particular outcome comes up.

This is my researchcollect.php code:

<?php
include "globals.php";
if($ir['user_level'] != 2) { die("Under construction"); }
if($ir['jail'] or $ir['hospital']) { die("You cannot access this page when in the jail or hospital."); }
if($ir['collect'] == 1) { die("It's illegal to enter the research centre without permission. Why risk it again? Click [url='index.php']here[/url] to go home."); }
else 
{
print "<h3 />Welcome to the Research Centre.</h3>";
if ($_GET['action'] != 'collect') {
print "You begin searching the research center. After 45 minutes, ";
}
$rcollect = rand(1,6);
if($rcollect == 1) {
print "you find nothing and leave the research centre.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
event_add($ir['userid'],"You didn't find anything while searching the research centre.",$c);
}

if($rcollect == 2) {
$item=$db->query("SELECT * FROM items WHERE itmid=1",$c);
print "you find a Research Centre Area 1 Key Card. You pocket the key card and head out of the research centre.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
$db->query("INSERT INTO inventory VALUES('',$item,$userid,1)",$c);
event_add($ir['userid'],"You find a ['itmname'] while searching the research centre.",$c);
}

if($rcollect == 3) {
$ranmon = rand(200,500);
print "you find nothing but a few pounds. You pocket the £$ranmon and head out of the centre.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
$db->query("UPDATE users SET money=money+$ranmon WHERE userid=$userid",$c);
event_add($ir['userid'],"You find $ranmon while searching the research centre.",$c);
}

if($rcollect == 4) {
$jtime = rand(10,15);
print "you find nothing. As you check the back corridor one last time, a security guard turns the corner and finds you rummaging through a bin. You're sent to jail for $jtime minutes.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
$db->query("UPDATE users SET jail=jail+$jtime,jail_reason='Found rummaging through a bin at the research centre' WHERE userid=$userid",$c);
event_add($ir['userid'],"You're sent to jail for $jtime minutes for rummaging through the bins at the research centre.",$c);
}

if($rcollect == 5) {
print "you didn't find anything and head out of the research centre.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
event_add($ir['userid'],"You didn't find anything while searching the research centre.",$c);
}

if($rcollect == 6) {
$rancrys = rand(5,8);
print "you find <font color=gold />$rancrys Golden Coins</font>, but that's it. So you head out of the research centre.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
$db->query("UPDATE users SET crystals=crystals+$rancrys WHERE userid=$userid",$c);
event_add($ir['userid'],"You gained $rancrys Golden Coins while searching the researching centre.",$c);
}
}
$h->endpage()
?>

 

This is the area that is causing me trouble:

if($rcollect == 2) {
$item=$db->query("SELECT * FROM items WHERE itmid=1",$c);
print "you find a Research Centre Area 1 Key Card. You pocket the key card and head out of the research centre.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
$db->query("INSERT INTO inventory VALUES('',$item,$userid,1)",$c);
event_add($ir['userid'],"You find a ['itmname'] while searching the research centre.",$c);
}

 

The query error I get is:

QUERY ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id #12,2,1)' at line 1

Query was INSERT INTO inventory VALUES('',Resource id #12,2,1)

I'm using V2 and I'm a n00b to PHP, so it's probably something very simple and easy, but any help of any sort would be much appreciated. :)

Thanks,

War_Hero :)

[Floydian]

Re: Query help please

 

if($rcollect == 2) {
$item=$db->query("SELECT * FROM items WHERE itmid=1",$c);
print "you find a Research Centre Area 1 Key Card. You pocket the key card and head out of the research centre.";
$db->query("UPDATE users SET collect=1 WHERE userid=$userid",$c);
$db->query("INSERT INTO inventory VALUES('',1,$userid,1)",$c);
event_add($ir['userid'],"You find a ['itmname'] while searching the research centre.",$c);
}

 

Note that I change $item to 1 in the line:

$db->query("INSERT INTO inventory VALUES('',1,$userid,1)",$c);

Give that a shot.

[War_Hero]

Re: Query help please

Thank you very much. It worked.

Now, I used ItemId 1 as a test. That's the Will Potion. So, when I want to change the item given, would I change it here:

$item=$db->query("SELECT * FROM items WHERE itmid=95",$c);

and here:

$db->query("INSERT INTO inventory VALUES(",95,$userid,1)",$c);

?

I'm not too sure about the second one....was the first 1 the item id?

 

And thanks again for helping me. :)

 

EDIT: Nevermind, I tried it and it worked. :) Thanks a lot, Floydian, for helping me. :D

[War_Hero]

Re: Query help please

The page above works well. But now, I get another query error with a different page, but related to the same topic. (I'm not much good with inventories. lol)

I have a page called researchfacility.php, which gives the user two options, to go home or to enter an area called Area 1. If they choose to go to Area 1, they have to have a Research Facility Area 1 Key Card, which they could have obtained from searching the facility. I would like it so that if they don't have a card, they can't access Area 1. But if they do have a card, they can enter and they can get £5,000 by testing medication. But it doesn't work. :(

Here is my researcharea1.php:

<?php
include "globals.php";
if($ir['user_level'] != 2) { die("Under construction"); }
if($ir['jail'] or $ir['hospital']) { die("This page cannot be accessed when in jail or hospital."); }
if($ir['use'] == 1) { die("You've been tested enough for today. Anymore tests, and you'll die. Come back when you're fit."); }
$query = $db->query("SELECT * FROM inventory WHERE itmid=95,userid=$userid",$c);
if($ir['$query']) {
print "You have entered Area 1 of the research facility. You volunteer yourself to be used to test vacinations and medication for the virus.


You were given £5,000 for testing the medication.";
$db->query("UPDATE users SET money=money+5000,hospital=hospital+35,hospreason='Tested new medication for money' WHERE userid=$userid",$c);
$db->query("UPDATE users SET use=1 WHERE userid=$userid",$c);
}
else
{
die("You need a Research Facility Area 1 Key Card before you can enter Area 1."); }

$h->endpage();
?>

 

And this is my query error:

QUERY ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'userid=2' at line 1

Query was SELECT * FROM inventory WHERE itmid=95,userid=2

And, I haven't tried it as of yet, but how would I make it so that when the user accesses Area 1, the card is removed from their inventory. Would it be something like:

$db->query("REMOVE FROM inventory WHERE itmid=95,userid=$userid,1",$c);

or something on those lines?

Again, any help would be much appreciated. :)

Thanks,

War_Hero :)

[Floydian]

Re: Query help please

where blah = blah aaaaaaaaaaaaaaaannnnnnnnnnnnndddddddddd something = something

you used a comma instead of and

[War_Hero]

Re: Query help please

Oh, so would it be:

$db-query("SELECT * FROM inventory WHERE itmid=95 AND userid=$userid",$c);

I'll give that a go. :)

And, would that be the same with removing the item from the inventory?

 

Thank you. :)

[War_Hero]

Re: Query help please

Hmm. I get a different query error now, but it's not as 'bad' as before:

QUERY ERROR: Unknown column 'itmid' in 'where clause'

Query was SELECT * FROM inventory WHERE itmid=95 AND userid=2

I'm thinking, do I have to encase the item id in ' '? :?

[Isomerizer]

Re: Query help please

itmid column does not exist in inventory.

No need to encase numbers.

Try..

SELECT * FROM `inventory` WHERE `inv_itmid` = 95 AND `userid` = 2

[fbiss]

Re: Query help please

 

itmid column does not exist in inventory.

No need to encase numbers.

Try..

SELECT * FROM `inventory` WHERE `inv_itmid` = 95 AND `userid` = 2

it would be...

 

SELECT * FROM `inventory` WHERE `inv_itmid` = 95 AND `inv_userid` = `$userid` 

[War_Hero]

Re: Query help please

Thanks. It worked, because I'm no longer getting query errors.

But now, there's something else wrong. It's not printing what I'd like it to. I have the required item, but it still says that I need that item to access the area.

Here is my researcharea1.php with the updated queries:

<?php
include "globals.php";
if($ir['user_level'] != 2) { die("Under construction"); }
if($ir['jail'] or $ir['hospital']) { die("This page cannot be accessed when in jail or hospital."); }
if($ir['use'] == 1) { die("You've been tested enough for today. Anymore tests, and you'll die. Come back when you're fit."); }
$db->query("SELECT * FROM inventory WHERE inv_itemid=95 AND inv_userid=$userid",$c);
if($ir['inv_itemid'] == 95) {
print "You have entered Area 1 of the research facility. You volunteer yourself to be used to test vacinations and medication for the virus.


You were given £5,000 for testing the medication.";
$db->query("UPDATE users SET money=money+5000,hospital=hospital+35,hospreason='Tested new medication for money' WHERE userid=$userid",$c);
$db->query("UPDATE users SET use=1 WHERE userid=$userid",$c);
$db->query("REMOVE * FROM inventory VALUES('',95,$userid,1)",$c);
event_add($ir['userid'],"You tested some medication for the research centre and gained £5,000 but went into hospital for 35 minutes.",$c);
}
else
{
die("You need a Research Facility Area 1 Key Card before you can enter Area 1."); }

$h->endpage();
?>

 

What's wrong with it? :(

[fbiss]

Re: Query help please

Give this a shot

 

<?php
include "globals.php";
if($ir['user_level'] != 2) { die("Under construction"); }
if($ir['jail'] or $ir['hospital']) { die("This page cannot be accessed when in jail or hospital."); }
if($ir['use'] == 1) { die("You've been tested enough for today. Anymore tests, and you'll die. Come back when you're fit."); }
$q = $db->query("SELECT * FROM `inventory` WHERE `inv_itmid` = 95 AND `inv_userid` = $userid");
if($db->num_rows($q) > 0)
{
print "You have entered Area 1 of the research facility. You volunteer yourself to be used to test vacinations and medication for the virus.


You were given £5,000 for testing the medication.";
$db->query("UPDATE users SET money=money+5000, use=1, hospital=hospital+35,hospreason='Tested new medication for money' WHERE userid=$userid");
item_remove($userid, 95, 1);
}
else
{
die("You need a Research Facility Area 1 Key Card before you can enter Area 1."); }

$h->endpage();
?>

[War_Hero]

Re: Query help please

Yay! Thank you soo much fbiss. It works. I had to tweak a few things, but other than that, it works really well.

And thank you to everyone else who helped me. :) Very much appreciated.

Cheers,

War_Hero :)

[War_Hero]

Re: Query help please

I've posted the entire mod under Free Game Modifications. :)

Thanks to all of you. :)